Topic
8 replies Latest Post - ‏2012-09-03T06:51:06Z by Bjoern_Karpenstein
Bjoern_Karpenstein
Bjoern_Karpenstein
32 Posts
ACCEPTED ANSWER

Pinned topic OLE ADODB.Connection help with Connection.Execute

‏2012-08-25T16:11:39Z |
Hello!

I created a small ms sql server library that can make SELECT Statements and enables cursor iteration through a skip list with a DxlObject.

Now i have a problem with the easier way: The Connection.Execute for INSERT/UPDATE/DELETE/CREATE... statements without resultset.

Here is my code for a selection, can someone help me make a Connection.Execute (instead of Open)?
// Connection details
string dbServer="SERVER";
string dbName="DATABASE";
string dbUsername="USER";
string dbPassword="PASS";

string connectionString = "Data Source='" dbServer "';
Initial Catalog='" dbName "';User Id='" dbUsername "';Password='" dbPassword "';";

OleAutoArgs oleAutoArgs=create;
OleAutoObj adodbConnection, adodbRecordset, objFields, objField;

string fieldName, result, err;
int numFields, index;

// Instantiate a new ADODB Connection object
adodbConnection = oleCreateAutoObject "ADODB.Connection";

if (null adodbConnection)
{
print "Unable to instantiate database connection\n";
halt;
}

// Instantiate a new ADODB Recordset object
adodbRecordset = oleCreateAutoObject "ADODB.Recordset";

if(null adodbRecordset)
{
print "Unable to create Recordset object\n";
halt;
}

// Connection details

// Set the provider and data source of the connection
// based on information from connectionstrings.com
olePut(adodbConnection, "Provider", "sqloledb");
clear oleAutoArgs;
put(oleAutoArgs, connectionString );
// "Password=" dataPass ";")
// Open the connection to the database
err=oleMethod(adodbConnection, "Open", oleAutoArgs);

if(!null err "")
{
print "Error opening database: " err "\n";
halt;
}

// SQL Command: Open a cursor to return all columns and rows of 'tableName'
clear oleAutoArgs

put(oleAutoArgs, "select * from alarms order by 1") // SQL Command
put(oleAutoArgs, adodbConnection) // ACTIVE CONNECTION
put(oleAutoArgs, 1) // CURSOR TYPE - 'adOpenKeyset'
put(oleAutoArgs, 1) // LOCK TYPE - 'adLockReadOnly'
put(oleAutoArgs, 1) // OPTIONS - 'adCmdText'

err=oleMethod(adodbRecordset, "Open", oleAutoArgs);

if(!null err "")
{
print "Error opening table: " err "\n";
halt;
}

// From the Recordset object, list each field name (defined in database)
oleGet(adodbRecordset, "Fields", objFields);
oleGet(objFields, "Count", numFields);

for(index=0; index<numFields; index++)
{
clear oleAutoArgs;
put(oleAutoArgs, index);
oleGet(objFields, "Item", oleAutoArgs, objField);
oleGet(objField, "Name", fieldName);
print (index>0?"\",":"") "\"" fieldName;
}

print "\"\n";

// From the Recordset object cursor, loop through and print each row
while(true)
{
result="";
clear oleAutoArgs;
put(oleAutoArgs, 2); // StringFormat - 'adClipString'
put(oleAutoArgs, 1); // NumRows
put(oleAutoArgs, "\",\""); // ColumnDelimiter
put(oleAutoArgs, "\""); // RowDelimiter
put(oleAutoArgs, ""); // NullExpr
oleMethod(adodbRecordset, "GetString", oleAutoArgs, result);
if(length(result)<=0) break else print "\"" result "\n";
}
Updated on 2012-09-03T06:51:06Z at 2012-09-03T06:51:06Z by Bjoern_Karpenstein
  • Mathias Mamsch
    Mathias Mamsch
    1921 Posts
    ACCEPTED ANSWER

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-25T16:33:13Z  in response to Bjoern_Karpenstein
    So what help do you need? It seems pretty straightforward to call Connection.Execute "command" ...? Regards, Mathias


    Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
    • Bjoern_Karpenstein
      Bjoern_Karpenstein
      32 Posts
      ACCEPTED ANSWER

      Re: OLE ADODB.Connection help with Connection.Execute

      ‏2012-08-26T10:24:18Z  in response to Mathias Mamsch
      Hello Matthias!

      Thank you for your reply. I want to have a working snipplet doing it, i already tried it for hours but i think there is sth. wrong (maybe in my understanding?).

      Can you give me a snipplet with ADODB.Connection doing a simple INSERT/UPDATE/DELETE? I think i then would see what was my problem.

      This throws an exception:

      int executeQuery(string sql, string connectionString)
      {
      OleAutoArgs oleAutoArgs=create;
      OleAutoObj adodbConnection;

      string fieldName, result, err;
      int numFields, index;

      // Instantiate a new ADODB Connection object
      adodbConnection = oleCreateAutoObject "ADODB.Connection";

      // Instantiate a new ADODB Recordset object
      adodbRecordset = oleCreateAutoObject "ADODB.Recordset";

      if (null adodbConnection)
      {
      print "Unable to instantiate database connection\n";
      return -1;
      }

      // Connection details

      // Set the provider and data source of the connection
      // based on information from connectionstrings.com
      olePut(adodbConnection, "Provider", "sqloledb");

      // SQL Command: Open a cursor to return all columns and rows of 'tableName'
      clear oleAutoArgs

      put(oleAutoArgs, sql) // SQL Command
      put(oleAutoArgs, 1) // ACTIVE CONNECTION

      err=oleMethod(adodbRecordset, "Execute", oleAutoArgs);

      if(!null err "")
      {
      print "Error opening table: " err "\n";
      return -1;
      }

      cleanup adodbConnection;

      return 1;
      }
      • Bjoern_Karpenstein
        Bjoern_Karpenstein
        32 Posts
        ACCEPTED ANSWER

        Re: OLE ADODB.Connection help with Connection.Execute

        ‏2012-08-26T10:25:51Z  in response to Bjoern_Karpenstein
        Even with this

        err=oleMethod(adoDbConnection, "Execute", oleAutoArgs);
        • Mathias Mamsch
          Mathias Mamsch
          1921 Posts
          ACCEPTED ANSWER

          Re: OLE ADODB.Connection help with Connection.Execute

          ‏2012-08-27T08:38:10Z  in response to Bjoern_Karpenstein

          Hmm .. the following works fine for me. Its an example for creating an access database (for test purposes) and inserting stuff into it. Hope it helps, regards, Mathias
           

          OleAutoObj objADOXCatalog = null, objADODBConnection = null, objRecordSet = null, objFields = null,objField = null
          OleAutoArgs args = null
           
          // cleanup stuff
          void cleanup (OleAutoObj &obj) { if (!null obj) oleCloseAutoObject obj; obj = null }
           
          void cleanup () { 
            cleanup objRecordSet; cleanup objFields; cleanup objField; 
            cleanup objADOXCatalog; cleanup objADODBConnection; 
          }
           
          // some syntax helpers for arguments (from DXL standard library) ...
          void checkNull (string s) { if (!null s) { print "Error: " s "\n" dxlHere(); cleanup; halt } }
          OleAutoArgs createArgs () { if (!null args) delete args; args = create(); return args}
          OleAutoArgs ::<-(OleAutoArgs x, int    a) { put(x, a); return x }
          OleAutoArgs ::<-(OleAutoArgs x, string a) { put(x, a); return x }
          OleAutoArgs ::<-(OleAutoArgs x, bool   a) { put(x, a); return x }
          string stringProperty (OleAutoObj obj, string s) { string result = null; checkNull oleGet (obj, s, result); return result }
           
           
          string sFile = "C:\\temp\\test2.mdb"
           
          // delete the file if it already exists
          deleteFile(sFile) 
           
          // Create an Access Database, just for testing. You can leave this out.
          objADOXCatalog = oleCreateAutoObject "ADOX.Catalog"; 
          checkNull ((null objADOXCatalog) ? "Could not create ADOX Catalog." : "")
           
          string sDatabaseString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" sFile ";Jet OLEDB:Engine Type=5"
          checkNull oleMethod (objADOXCatalog, "Create" , createArgs <- sDatabaseString)
           
          // Connect using ADODB ...
          objADODBConnection = oleCreateAutoObject "ADODB.Connection"; 
          checkNull ((null objADODBConnection) ? "Could not create ADODB Connection." : "")
           
          string stConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" sFile ";" 
          checkNull oleMethod (objADODBConnection, "Open" , createArgs <- stConnection)
           
          // Note the syntax here: the empty comment with following '-' must not have a space behind it!
          string sTableCreate = "CREATE TABLE testTable (" // -
                  "ID COUNTER PRIMARY KEY, " // -
                  "Test TEXT(255) " // -
                  ")"
                  
          checkNull oleMethod (objADODBConnection, "Execute" , createArgs <- sTableCreate)
           
          string sInsert = "INSERT into testTable (Test) VALUES ('Hello Database')"
          checkNull oleMethod (objADODBConnection, "Execute" , createArgs <- sInsert)
           
          string sItems[] = {"Hello", "World", "!"}; 
          int i; for (i = 0; i < sizeof sItems; i++) {
            string sInsert = "INSERT into testTable (Test) VALUES ('" sItems[i] "')"
            checkNull oleMethod (objADODBConnection, "Execute" , createArgs <- sInsert)
          }
           
          string sSelect = "select [Test] from [testTable]"
          checkNull oleMethod (objADODBConnection , "Execute", createArgs <- sSelect, objRecordSet)
           
          int count = 0
          while (true) {
            bool bEOF = false; checkNull oleGet(objRecordSet, "EOF", bEOF)
            if (bEOF) break
            
            print "Processing Item " (count++) ":\n" 
            
            checkNull oleGet(objRecordSet, "Fields", objFields) 
           
            checkNull oleGet(objFields, "Item", createArgs <- 0, objField)  
            print " Name:" stringProperty (objField, "Value") "\n"
            
            checkNull oleMethod (objRecordSet, "MoveNext")
          }
           
          cleanup
          

           

           


          Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

           

           

          Updated on 2014-01-06T21:15:24Z at 2014-01-06T21:15:24Z by JAntley
          • Bjoern_Karpenstein
            Bjoern_Karpenstein
            32 Posts
            ACCEPTED ANSWER

            Re: OLE ADODB.Connection help with Connection.Execute

            ‏2012-08-31T08:53:07Z  in response to Mathias Mamsch

            Thank you very much! That works for me.

            Another question: When i try to get the TYPE of the fields, the Variable is empty. Can you say what i am doing wrong here?
             

            oleGet(adodbRecordset, "Fields", objFields);
                oleGet(objFields, "Count", numFields);
                    
                    for(index=0; index<numFields; index++)
                    {
             
                            clear oleAutoArgs;
                            
                            string theType = null;          
                            put(oleAutoArgs, index);
                            
                            oleGet(objFields, "Item", oleAutoArgs, objField);
                            oleGet(objField, "Name", fieldName);
                            oleGet(objField, "Type", theType);
                            
                            print (index>0?"\",":"") "\"" fieldName "( " theType " )"; // <--- theType is empty???
                    }
            
            Updated on 2014-01-06T21:16:01Z at 2014-01-06T21:16:01Z by JAntley
            • Mathias Mamsch
              Mathias Mamsch
              1921 Posts
              ACCEPTED ANSWER

              Re: OLE ADODB.Connection help with Connection.Execute

              ‏2012-08-31T10:04:15Z  in response to Bjoern_Karpenstein
              From what I can see at MSDN the type property is an integer property. See here:

              http://msdn.microsoft.com/en-us/library/windows/desktop/ms675318%28v=vs.85%29.aspx

              Regards, Mathias


              Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
              • Mathias Mamsch
                Mathias Mamsch
                1921 Posts
                ACCEPTED ANSWER

                Re: OLE ADODB.Connection help with Connection.Execute

                ‏2012-08-31T10:18:00Z  in response to Mathias Mamsch
                Oh: Keep checking your oleGet, oleMethod calls for 'null' return values to see these kinds of errors. Regards, Mathias


                Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
                • Bjoern_Karpenstein
                  Bjoern_Karpenstein
                  32 Posts
                  ACCEPTED ANSWER

                  Re: OLE ADODB.Connection help with Connection.Execute

                  ‏2012-09-03T06:51:06Z  in response to Mathias Mamsch
                  Hello!

                  I casted it as Integer and now it works. Thank you very much