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)
6 years ago

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.