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 que...
  • 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.

TanyaYatskovska's avatar
TanyaYatskovska
SmartBear Alumni (Retired)

Hi CraigB,

 

Just want to better clarify the issue - it's unclear when you see an empty string.

When you add a breakpoint before the CreateParameter call, do you see the value in paramName?

 

CraigB's avatar
CraigB
Contributor
6 years ago

What I meant is that when calling CreateParameter with unnamed parameters (i.e. when using ? as the placeholder in the query), I pass and empty string as the parameter name.  When trying to use named parameters I give the parameter name rather than an empty string.

So when using ? as the placeholder:

CreateParameter("", adVarChar, adParamInput, 256, paramVal)
 
CreateParameter(paramName, adVarChar, adParamInput, 256, paramVal)
 
where paramName is the same as the named parameter in the query.
    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      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.

      • CraigB's avatar
        CraigB
        Contributor
        6 years ago

        Hi

         

        Unfortunately the ADOCommand.Parameters doesn't have either a ParseSql or ParamByName.  It turns out that SQLOLEDB doesn't support those, only Jet does.  Also, none appear to support query parameters for anything other than select statements.

         

        Thanks for trying.

         

        Craig