Page 2 of 3
AdsSQLServer Query with Parameters not working
Posted: Tue Jul 05, 2022 3:34 pm
by 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:
HTH
Wolfgang
AdsSQLServer Query with Parameters not working
Posted: Tue Jul 05, 2022 7:15 pm
by 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
AdsSQLServer Query with Parameters not working
Posted: Tue Jul 05, 2022 7:28 pm
by 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
AdsSQLServer Query with Parameters not working
Posted: Wed Jul 06, 2022 7:57 am
by Kees Bouw
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.
AdsSQLServer Query with Parameters not working
Posted: Wed Jul 06, 2022 8:06 am
by 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
AdsSQLServer Query with Parameters not working
Posted: Wed Jul 06, 2022 8:18 am
by wriedmann
Hi Kees,
here you can find a working sample as VS solution.
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
AdsSQLServer Query with Parameters not working
Posted: Wed Jul 06, 2022 10:06 am
by 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?
AdsSQLServer Query with Parameters not working
Posted: Wed Jul 06, 2022 10:17 am
by 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
AdsSQLServer Query with Parameters not working
Posted: Wed Jul 06, 2022 2:42 pm
by Jamal
AdsSQLServer Query with Parameters not working
Posted: Thu Jul 07, 2022 9:01 am
by Kees Bouw
Hi Jamal,
Both links produce the error: "Our apologies, but an error has occurred. Please check the URL or try the operation again."
Kees.