Forum Discussion

CDurkin's avatar
CDurkin
Contributor
14 years ago

ADO reading parameters into SQL

I am sending some sql to Oracle which has double quotes inside a text string, fine no problem.  However if there is a colan inside the double quotes then ADO throws a exception.  The exception it throws indicated it thinks there is a parameter.  The exception I get is:

        "Parameter object is improperly defined. Inconsistent or incomplete information was provided"





Oracle has not problem with the SQL, this is a ADO problem.  An simplified example of the SQL is:

        var Qry = ADO.CreateADOQuery();

        Qry.ConnectionString = getConnectionString();

        Qry.SQL = "select '\" : \"' as text from dual";

        Qry.Open();



Some other results to demo the problem are

    ERROR>        select ' " : " ' as text from dual

    ERROR>        select ' " :  ' as text from dual

    GOOD>        select ' "  " : ' as text from dual

    GOOD>        select ' "   ' as text from dual

    GOOD>        select ' :   ' as text from dual

  • radov's avatar
    radov
    Staff
    14 years ago
    Hi Chris,



    Have you tried to escape the colon character in your query by using the backslash character before the colon? Please try to use the following code:



    Qry.SQL = "select '\" \: \"' as text from dual";




    I'm not sure about this, but I hope, this can help in your case if you need the colon character to be interpreted just as the colon character in the sought-for string.
  • radov's avatar
    radov
    Staff
    14 years ago
    Hi Chris,



    After some investigations, I've found a workaround for you. You can execute an SQL query to your database by using another object, IAQAADOConnection, which is returned by the ADO.CreateADOConnection method. Please try using this object instead of IAQAADOQuery returned by ADO.CreateADOQuery in your test where you need to use the colon character enclosed into quotes. You can use something like the following example (it seems to work fine in my sample test):







      // Create an IAQAADOConnection object

      var connection = ADO.CreateADOConnection();

      

      // Specify the connection string

      connection.ConnectionString = getConnectionString();

      connection.LoginPrompt = false;

      

      // Specify the SQL query

      var sql = "select '\" : \"' as text from dual";

      

      connection.Open();

      

      // Execute the query and get a recordset

      var rs = connection.Execute_(sql);

      

      // Move through the received records and use their fields

      while (!rs.EOF)

      {

        Log.Message(rs.Fields.Item("name"), rs.Fields.Item("address"));

        rs.MoveNext();

      }

      

      connection.Close();





    Does this workaround help in your case? Please let me know your results.