AdsSQLServer Query with Parameters not working

[wriedmann]

Hi Kees,
to do that you don't have to use the RDD, but the Advantage.Data.Provider.dll.

Code: Select all

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:

AdsReader.zip

(3.21 KiB) Downloaded 111 times

HTH
Wolfgang

[g.bunzel@domonet.de]

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

[robert]

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

[Kees Bouw]

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.

[wriedmann]

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

[wriedmann]

Hi Kees,

here you can find a working sample as VS solution.

AdsReader.zip

(460.96 KiB) Downloaded 96 times

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

[Kees Bouw]

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?

[wriedmann]

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

[Jamal]

Hi Kees,

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

https://devzone.advantagedatabase.com/d ... latform=11

Other versions: https://devzone.advantagedatabase.com/d ... spx?Key=20

Jamal

[Kees Bouw]

Hi Kees,

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

https://devzone.advantagedatabase.com/d ... latform=11

Other versions: https://devzone.advantagedatabase.com/d ... spx?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.