AdsSQLServer Query with Parameters not working

[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

[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?

[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

[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

https://docs.microsoft.com/en-gb/dotnet ... r-modifier

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

[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"}
...

[robert]

If you have a small example that demonstrates the problem then we'll look into it.

Robert

[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

[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.

[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

[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.