Soapdatamodule parameter problem

[entelec]

Hi,

I created a Soapdatamodule (WebService) with an ADOQuery and DatasetProvider on it. The option poAllowCommandText of the DatasetProvider is set to true. The client application uses a Soapconnection and a ClientDataset. The RemoteServer is set to the Soapconnection and the Providername is set to the Datasetprovider of the soapserver.
On the client side I want to Execute dynamically created SQL statement with params. The following code (client side) with without params is working fine.

clds_Table := TClientDataSet.Create(Self);
clds_Table.RemoteServer := soapc_;
clds_Table.ProviderName := 'dsp_Table';
clds_Table.CommandText := ‘SELECT tes_id, tes_description FROM test’;
clds_Table.Open;

The following code with params isn’t working. The result of the select statement gives me always an empty result back.

clds_Table := TClientDataSet.Create(Self);
clds_Table.RemoteServer := soapc_;
clds_Table.ProviderName := 'dsp_Table';
clds_Table.CommandText := ‘SELECT tes_id, tes_description FROM test WHERE tes_description = :tes_description’;
clds_Table.Params.ParamByName(‘tes_description’).DataType := ftString;
clds_Table.Params.ParamByName(‘tes_description’).ParamType := ptInput;
clds_Table.Params.ParamByName(‘tes_description’).Value := ‘Frank’
clds_Table.Open;

With the web debugger I see that the SQL statement en param are passed to the webservice (Incapuslated in a SOAP-XML message).
On the server I see that I have received the SQL statement and the param BUT NO PARAM VALUE. (It look like the PARM VALUE = NULL)

Is there anyone that can help me?

(I’m using Delphi 6 with updatepack 2)

Frank

 

[Pietjeparasietje]

Are you sure the select statement is working? I think the problem is in the where statement. Or isn't this the problem at all?

 

[entelec]

Yes the SQL statement is correct and working, because in the DBGrid on the client side I see the column names after opening the ClientDataset but I don't have any result records.
The SQL statement SELECT tes_id, tes_description FROM test WHERE tes_description = 'Frank' gives me a result record but SELECT tes_id, tes_description FROM test WHERE tes_description = :tes_description gives an empty result.

Frank

 

[Pietjeparasietje]

so then there is a problem with your select statement. what does ":tes_description" contain?
If it does show the column names but no data it simply means taht the where clause does not apply so he cannot find a field where the tes_descriptione equals your :tes_description..

 

[Pietjeparasietje]

What happends when you try this :

ParamByName('tes_description').AsString := 'Frank';

 

[entelec]

Hi Pietjeparasietje

Assigning the parameter value by using AsString don't solve my problem. In the web debugger I see in SOAP-XML message that the parameter value is pass to the webservice.

<?xml version="1.0" ?>
- <SOAP-ENV:Envelope xmlns:SOAP-ENV="

http://schemas.xmlsoap.org/soap/envelope/"

xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:SOAP-ENC="

http://schemas.xmlsoap.org/soap/encoding/">

- <SOAP-ENV:Body SOAP-ENV:encodingStyle="

http://schemas.xmlsoap.org/soap/encoding/">

- <NS1:AS_GetRecords xmlns:NS1="urn:Midas-IAppServer" xmlns:NS2="

http://www.borland.com/namespaces/Types">

<ProviderName xsi:type="xsd:string">dsp_Table</ProviderName>
<Count xsi:type="xsd:int">-1</Count>
<Options xsi:type="xsd:int">1</Options>
<CommandText xsi:type="xsd:string">SELECT tes_id, tes_description FROM test WHERE tes_description = :tes_description</CommandText>
- <NS1arams>
- <NS1:V NS2:VarArrayType="8204">
<V xsi:type="xsd:string">tes_description</V>
<V xsi:type="xsd:string">Frank</V>
<V xsi:type="xsd:byte">1</V>
<V xsi:type="xsd:byte">1</V>
</NS1:V>
</NS1arams>
<NS1:OwnerData xsi:NULL="true" />
</NS1:AS_GetRecords>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

I've wrote some logging on the side of the service and I see the following

adoq_.Parameters.Command.CommandText: SELECT tes_id, tes_description FROM test WHERE tes_description = :tes_description
My SQL statement is correctly transfer from client to server

adoq_.Parameters Count: 1
I count 1 parameter

adoq_.Parameter: tes_description = NULL
my parameter has the correct name
but the value is NULL

Frank