IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 8 replies
  • Latest Post - ‏2012-09-03T06:51:06Z by Bjoern_Karpenstein
Bjoern_Karpenstein
Bjoern_Karpenstein
33 Posts

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
    1958 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-25T16:33:13Z  
    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
    33 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-26T10:24:18Z  
    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
    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
    33 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-26T10:25:51Z  
    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;
    }
    Even with this

    err=oleMethod(adoDbConnection, "Execute", oleAutoArgs);
  • Mathias Mamsch
    Mathias Mamsch
    1958 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-27T08:38:10Z  
    Even with this

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

    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
    33 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-31T08:53:07Z  

    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
     

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">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 </pre>

     

     


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

     

     

    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
    1958 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-31T10:04:15Z  

    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?
     

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">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??? } </pre>
    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
    1958 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-08-31T10:18:00Z  
    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
    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
    33 Posts

    Re: OLE ADODB.Connection help with Connection.Execute

    ‏2012-09-03T06:51:06Z  
    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
    Hello!

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