Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC:

AdsSQLServer Query with Parameters not working 01 Apr 2022 10:04 #22068

  • hilberg.it
  • hilberg.it's Avatar
  • Topic Author


  • Posts: 57
  • 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:
            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

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 01 Apr 2022 11:19 #22070

    • hilberg.it
    • hilberg.it's Avatar
    • Topic Author


  • Posts: 57
  • 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?

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 02 Apr 2022 10:03 #22074

    • g.bunzel@domonet.de's Avatar


  • Posts: 76
  • 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

    Please Log in or Create an account to join the conversation.

    Last edit: by .

    AdsSQLServer Query with Parameters not working 04 Apr 2022 17:38 #22075

    • Karl-Heinz
    • Karl-Heinz's Avatar


  • Posts: 765
  • Hi Gerhard,

    the AdsConnect60( ... , hConnect ) call should work, because the function is declared as:

    FUNCTION AdsConnect60( ... , phConnect OUT IntPtr) AS DWORD
    docs.microsoft.com/en-gb/dotnet/csharp/l...t-parameter-modifier

    But, to make the "@" reference operator work, the /VO7+ setting must be used.

    try this:
    //  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

    Please Log in or Create an account to join the conversation.

    Last edit: by Karl-Heinz.

    AdsSQLServer Query with Parameters not working 04 Jul 2022 15:26 #22962

    • hilberg.it
    • hilberg.it's Avatar
    • Topic Author


  • Posts: 57
  • 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:
    ...
    cQuery := "SELECT * FROM DMODELL WHERE DMODELL.MONUMMER = '00010225'"
    oServer := AdsSQLServer{ cQuery, , , "AXSQLCDX"}
    ...

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 04 Jul 2022 16:50 #22965

    • robert
    • robert's Avatar


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

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 04 Jul 2022 20:43 #22967

    • ic2
    • ic2's Avatar


  • Posts: 1424
  • Hello,

    I assume it doesn't work with using the 'regular' init method:
    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 d\Mydir\MyDBFFile. Second reason I had once is by using these quots ``, normal in MySQL, which ADS doesn't like.

    Maybe this already helps.

    Dick

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 04 Jul 2022 21:48 #22971

    • hilberg.it
    • hilberg.it's Avatar
    • Topic Author


  • Posts: 57
  • 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.

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 05 Jul 2022 08:37 #22976

    • robert
    • robert's Avatar


  • Posts: 2974
  • 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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 05 Jul 2022 15:01 #22982

    • KeesIC2
    • KeesIC2's Avatar


  • Posts: 42
  • 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.

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 05 Jul 2022 17:34 #22984

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3094
  • Hi Kees,
    to do that you don't have to use the RDD, but the Advantage.Data.Provider.dll.
    if cDirectory:Substring( 0, 2 ):ToUpper() == "C:"
      _oAdsConn := AdsConnection{ String.Format( "data source={0}; ServerType=local; TableType=CDX; TrimTrailingSpaces=true; CharType = OEM", cDirectory ) }
    else
      _oAdsConn := AdsConnection{ String.Format( "data source={0}; ServerType=remote; TableType=CDX; TrimTrailingSpaces=true; CharType = OEM", cDirectory ) }
    endif
    _oAdsConn:Open()
    _oCommand := _oAdsConn:CreateCommand()
    _oCommand:CommandText	:= cSelect
    _oReader := _oCommand:ExecuteReader()
    _oTable := DataTable{}
    _oTable:Load( _oReader )
    oGrid:Columns:Clear()
    oGrid:DataSource := _oTable
    Sample as XIDE export file:

    File Attachment:

    File Name: AdsReader.zip
    File Size:3 KB

    HTH
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it
    Attachments:

    Please Log in or Create an account to join the conversation.

    Last edit: by wriedmann.

    AdsSQLServer Query with Parameters not working 05 Jul 2022 21:15 #22985

    • g.bunzel@domonet.de's Avatar


  • Posts: 76
  • Hi,
    I'm new to X#, but when I look at the code for ADSSQLServer in X#, the last parameter in the init method and the refresh method to update the select with a changed parameter are missing.

    X#-Contructor:
    CONSTRUCTOR( oFile, lShareMode, lReadOnlyMode, xDriver, aRDD ) CLIPPER
    LOCAL cTemp AS STRING
    LOCAL cFileName AS STRING

    // Set the query text, this is necessary because the VO runtime doesn't like
    // some of the special characters that are used in SQL queries
    RddInfo( _SET_SQL_QUERY, oFile )

    // Some VO libraries have trouble with the alias as is. So for the SQL RDDS,
    // just grab the first word of the SQL query and use it as the alias. The VO
    // runtime will adjust it to be unique if there is a naming conflict.
    cTemp := Left( oFile, At( " ", oFile ) - 1 )

    // Call the DBServer init method which will execute the query
    SUPER( cTemp, lShareMode, lReadOnlyMode, xDriver, aRDD )
    ......

    and now the VO-Init:
    METHOD Init ( oFile, lShareMode, lReadOnlyMode, xDriver, aRdd, params ) CLASS AdsSQLServer
    LOCAL cTemp AS STRING
    LOCAL cFileName AS STRING

    // Set the query text, this is necessary because the VO runtime doesn't like
    // some of the special characters that are used in SQL queries
    RDDINFO( _SET_SQL_QUERY, oFile )

    // Pass the parameter array into the RDD
    // The params argument should be an array of parameters for the query.
    // The array should be an array of parameter names and parameter values.
    // For example:
    // {{ "lastname", "Smith" }, { "ID", 25 }}
    IF ( IsNil( params ) )
    // Pass in an empty array. Passing in NIL doesn't get to the RDD.
    RDDINFO( _SET_SQL_PARAMETERS, {} )
    ELSE
    RDDINFO( _SET_SQL_PARAMETERS, params )
    ENDIF

    // Some VO libraries have trouble with the alias as is. So for the SQL RDDS,
    // just grab the first word of the SQL query and use it as the alias. The VO
    // runtime will adjust it to be unique if there is a naming conflict.
    cTemp := Left( oFile, At( " ", oFile ) - 1 )

    // Call the DBServer init method which will execute the query
    SUPER:Init( cTemp, lShareMode, lReadOnlyMode, xDriver, aRdd )
    .....

    and the Refresh-Method from VO:
    METHOD Refresh ( params ) CLASS AdsSQLServer
    // This version of Refresh() accepts an array of SQL parameters
    // for the query. The array should be an array of parameter names and
    // parameter values. For example:
    // {{ "lastname", "Smith" }, { "ID", 25 }}

    // Set the parameters if provided.
    IF params != NIL
    SELF:RDDINFO( _SET_SQL_PARAMETERS, params )
    ENDIF

    RETURN SUPER:Refresh()

    HTH

    Gerhard

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 05 Jul 2022 21:28 #22986

    • robert
    • robert's Avatar


  • Posts: 2974
  • Gerhard,
    We looked at the code for Vulcan.Net when creating this class.
    And the parameters are also missing in that code. So that explains why they are not there.

    If someone can create a small example that shows how you are using this then I will implement it.
    I think it is the 3rd time now that I ask for an example in this thread.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 06 Jul 2022 09:57 #22988

    • KeesIC2
    • KeesIC2's Avatar


  • Posts: 42
  • wriedmann wrote: Sample as XIDE export file:


    Hi Wolfgang,

    Thank you very much, this looks promising! However, I do not have XIDE installed. Is there a way to install this afterwards or do I have to run the X# installer again?

    Kees.

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 06 Jul 2022 10:06 #22989

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3094
  • Hi Kees,
    it seems that the XIDE installer is not copied to the disk if not selected at setup.
    I will try to build a VS solution.
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 06 Jul 2022 10:18 #22990

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3094
  • Hi Kees,

    here you can find a working sample as VS solution.

    File Attachment:

    File Name: AdsReader.zip
    File Size:461 KB

    Sorry, if there is no painted window as I have copied it over from my XIDE project and has not the time to repaint that window.
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it
    Attachments:

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 06 Jul 2022 12:06 #22991

    • KeesIC2
    • KeesIC2's Avatar


  • Posts: 42
  • Where does the file Advantage.Data.Provider.dll come from? I have ADS 12 installed but can't find it anywhere. It is included in Wolfgang's sample (version 11 I think), but if it is part of ADS I should have it too?

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 06 Jul 2022 12:17 #22992

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3094
  • Hi Kees,
    ADS is distributed with some clients, in this case the .NET DataProvider. The corresponding file is named dataprovider.exe.
    I don't know if I can put it here.
    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    AdsSQLServer Query with Parameters not working 06 Jul 2022 16:42 #22993

    • Jamal
    • Jamal's Avatar


  • Posts: 286
  • Hi Kees,

    You can download Advantage.Data.Provider.dll version 11 from

    devzone.advantagedatabase.com/dz/content...roduct=4&Platform=11

    Other versions: devzone.advantagedatabase.com/dz/content.aspx?Key=20

    Jamal

    Please Log in or Create an account to join the conversation.

    Last edit: by Jamal.

    AdsSQLServer Query with Parameters not working 07 Jul 2022 11:01 #22997

    • KeesIC2
    • KeesIC2's Avatar


  • Posts: 42
  • Jamal wrote: Hi Kees,

    You can download Advantage.Data.Provider.dll version 11 from

    devzone.advantagedatabase.com/dz/content...roduct=4&Platform=11

    Other versions: devzone.advantagedatabase.com/dz/content.aspx?Key=20

    Jamal


    Hi Jamal,

    Both links produce the error: "Our apologies, but an error has occurred. Please check the URL or try the operation again."

    Kees.

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1
    • 2