Forum Discussion

CraigB's avatar
CraigB
Contributor
6 years ago

Python - Parameters in ADO queries

Hi

 

I have various SQL queries that need to be executed to insert, select and delete.  I'm trying to figure out how to get parameters working.  I can get unnamed parameters working in select queries by putting in ? everywhere a parameter is needed and then making sure that the parameters are added in the correct order using a list of values.  However, this means the code is difficult to read because ? is used for every parameter, obscuring what the parameters are.  I haven't tried a delete yet, but ? doesn't work at all in inserts.

 

A string parameter is created using a call like this:

CreateParameter(paramName, adVarChar, adParamInput, 256, paramVal)

 

paramName is currently always an empty string so that use of ? works for selects.


I've tried the :<param name> shown here https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/databases/using-ado-and-bde-objects/ado-createadocommand.html, but I just get an error that there's a : in the query.  I've tried @ instead of :, but that just gives an error stating that the named parameter has to be defined.  Note that for testing this I switched the paramName in the CreateParameter call to the parameter name, I didn't leave it an empty string.  And yes, the name and the paramName values match.

 

Do I really have to construct the exact query string using string formatting and no parameters except for selects?  And am I then forced to use hard to read code to get parameters to work at all?

  • tristaanogre's avatar
    tristaanogre
    6 years ago

    I'm looking through some code that I have for SQL queries and I am using a named query parameter in the SQL string. 

     

    For example, I have the following query

     

    SELECT COUNTERID 
 FROM AUTOMATIONCOUNTERS (NOLOCK)
 WHERE NAME = :NAME

    I use, then the ADO.CreateADOCommand method to generate a command object.  I set the CommandText property of that object to the query as indicated. The command object has a Parameters collection with a method called ParseSQL which builds the parameters collection based upon what's in the query.  You can then use the ParamByName method on that same collection to grab the parameter and assign the value.

     

    So... rough pseudocode looks like this

     

    var localCommand
var SQLString
SQLString = 'SELECT COUNTERID  FROM AUTOMATIONCOUNTERS( NOLOCK) WHERE NAME = :NAME'
localCommand = ADO.CreateADOCommand();
localCommand.CommandText = SQLString;
localCommand.Parameters.ParseSQL(SQLString, true);
localCommand.Parameters.ParamByName('NAME') = 'TestCounter'

    To execute, you just need to make sure you assign a proper active connection to the command object and then call the Execute method on the command object.

     

    This works very well for me... I have, actually, a complex ScriptExtension that does all this for me.  

     

    Hope this helps.