OLE ADODB.Connection help with Connection.Execute

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";
}
Bjoern_Karpenstein - Sat Aug 25 12:11:39 EDT 2012

Re: OLE ADODB.Connection help with Connection.Execute
Mathias Mamsch - Sat Aug 25 12:33:13 EDT 2012

Re: OLE ADODB.Connection help with Connection.Execute
Bjoern_Karpenstein - Sun Aug 26 06:24:18 EDT 2012

Re: OLE ADODB.Connection help with Connection.Execute
Bjoern_Karpenstein - Sun Aug 26 06:25:51 EDT 2012

Re: OLE ADODB.Connection help with Connection.Execute
Mathias Mamsch - Mon Aug 27 04:38:10 EDT 2012

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

Re: OLE ADODB.Connection help with Connection.Execute
Bjoern_Karpenstein - Fri Aug 31 04:53:07 EDT 2012

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

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???
        }

Re: OLE ADODB.Connection help with Connection.Execute
Mathias Mamsch - Fri Aug 31 06:04:15 EDT 2012

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???
        }

Re: OLE ADODB.Connection help with Connection.Execute
Mathias Mamsch - Fri Aug 31 06:18:00 EDT 2012

Re: OLE ADODB.Connection help with Connection.Execute
Bjoern_Karpenstein - Mon Sep 03 02:51:06 EDT 2012