Page 1 of 3
AdsSQLServer Query with Parameters not working
Posted: Fri Apr 01, 2022 8:04 am
by hilberg.it
Hi,
I am having difficulties to get a query with parameters to work with ADS Server. It works without params though. Here is my code:
Code: Select all
LOCAL dwRet AS DWORD
LOCAL hConnect AS IntPtr
LOCAL oServer AS AdsSQLServer
LOCAL moNummer as String
dwRet := AdsConnect60( String2Psz( "<IP>:<PORT><DB>" ), ACE.ADS_REMOTE_SERVER, NULL_PSZ, NULL_PSZ, ACE.ADS_DEFAULT, hConnect )
IF dwRet = 0
AX_SetConnectionHandle( hConnect )
//oServer := AdsSQLServer{ "SELECT * FROM DMODELL", , , "AXSQLCDX"}
oServer := AdsSQLServer{ "SELECT * FROM DMODELL WHERE MONUMMER = ?", , , "AXSQLCDX", , {{ 1, "00010225" }}}
oServer:GoTop()
while ! oServer:EoF
moNummer := AllTrim( oServer:FieldGet( #MONUMMER ) )
System.Diagnostics.Debug.WriteLine("MoNummer: " + moNummer)
oServer:Skip()
end
/* Clear the connection handle */
AX_SetConnectionHandle(0)
oServer:Close()
ENDIF
Any ideas why approach with params is throwing an exception?
Thanks
AdsSQLServer Query with Parameters not working
Posted: Fri Apr 01, 2022 9:19 am
by hilberg.it
I am a newbie from switching from DBFCDX to AXSQLCDX. I just found this
thread which offers another promissing approach for using sql with ads in x#.
What are your opinions about the two approaches?
AdsSQLServer Query with Parameters not working
Posted: Sat Apr 02, 2022 8:03 am
by g.bunzel@domonet.de
Hi,
..I use AXSQLCDX and Selects with parameters only with VO at the moment and it's working fine.
In VO the last parameter of AdsConnect60(..) is 'by Ref' - so you have to use ...., @hConnect ) to get the connection handle.
To work always with the correct table, I use the fieldnames with it's tablename:
oServer := AdsSQLServer{ "SELECT * FROM DMODELL WHERE DMODELL.MONUMMER = ?", , , "AXSQLCDX", , {{ 1, "00010225" }}}
That should work.
HTH
Gerhard Bunzel
AdsSQLServer Query with Parameters not working
Posted: Mon Apr 04, 2022 3:38 pm
by Karl-Heinz
Hi Gerhard,
the
AdsConnect60( ... , hConnect ) call should work, because the function is declared as:
Code: Select all
FUNCTION AdsConnect60( ... , phConnect OUT IntPtr) AS DWORD
But, to make the "@" reference operator work, the /VO7+ setting must be used.
try this:
Code: Select all
// VO dialect
FUNCTION Start( ) AS VOID
LOCAL p AS IntPtr
? p // 0x00000000
GetIntPtr ( p )
? p // 0x00000400
?
p := IntPtr { 0 }
? p // 0x00000000
GetIntPtr ( OUT p )
? p // 0x00000400
?
p := IntPtr { 0 }
? p // 0x00000000
GetIntPtr ( REF p )
? p // 0x00000400
/*
?
p := IntPtr { 0 }
? p // 0x00000000
GetIntPtr ( @p ) // to compile this the Vo7 switch must be enabled
? p // 0x00000400
*/
RETURN
FUNCTION GetIntPtr ( phConnect OUT IntPtr ) AS VOID
// Deactive the phConnect assignment and you ll see the expected compile error:
// XS0177: The OUT parameter 'phConnect ' must be assigned to before control leaves the current method
// ? phConnect == NULL // true
phConnect := IntPtr { 1024 }
// ? phConnect == NULL // false
RETURN
regards
Karl-Heinz
AdsSQLServer Query with Parameters not working
Posted: Mon Jul 04, 2022 1:26 pm
by hilberg.it
FYI: Don't know if someone is using the parameterized approach of AdsSQLServer, but it is not working. I receive a 7200 ADS Error, so something with the query is supposedly wrong. But the query works fine in ADS Query Editor. Now I use this as a workaround:
Code: Select all
...
cQuery := "SELECT * FROM DMODELL WHERE DMODELL.MONUMMER = '00010225'"
oServer := AdsSQLServer{ cQuery, , , "AXSQLCDX"}
...
AdsSQLServer Query with Parameters not working
Posted: Mon Jul 04, 2022 2:50 pm
by robert
If you have a small example that demonstrates the problem then we'll look into it.
Robert
AdsSQLServer Query with Parameters not working
Posted: Mon Jul 04, 2022 6:43 pm
by ic2
Hello,
I assume it doesn't work with using the 'regular' init method:
Code: Select all
METHOD Init( oFile, lShareMode, lReadOnlyMode, xDriver, aRdd ) CLASS AdsSQLServer
One of the reasons I had a 7200 once was that I passed a path without :, like dMydirMyDBFFile. Second reason I had once is by using these quots ``, normal in MySQL, which ADS doesn't like.
Maybe this already helps.
Dick
AdsSQLServer Query with Parameters not working
Posted: Mon Jul 04, 2022 7:48 pm
by hilberg.it
Hi,
thanks. Maybe this is not supported by X# RDD? I was mixing up ADS and X# Userguides. Here is a call to ADSSqlServer with Parameters:
Link and here I cannot find a sixth parameter in the X# CTOR
Link
If it's simply not supported and not my fault, than the workaround is fine for me.
AdsSQLServer Query with Parameters not working
Posted: Tue Jul 05, 2022 6:37 am
by robert
We have derived our X# ADS support from the Vulcan support for ADS.
I see now that SAP did not add the extra parameter for ADSSqlServer to the Vulcan code.
I can add that to X#. Do you have a complete example so I can test it ?
Robert
AdsSQLServer Query with Parameters not working
Posted: Tue Jul 05, 2022 1:01 pm
by Kees Bouw
Does anyone know where I can find a working example of approaching a DBF with SQL commands, using ADS? I have it working in VO but can't get the code to work in X#. The ultimate goal is to be able to use a DBF as datasource for a Winforms datagridview so any comments on that are also very welcome.