Macros and ODBC connection to Oracle-Update Table

[hsqamar]

I'm trying to open an ODBC connection to an oracle database from a macro that automates Transformer Cube builds....In the Macro I also need to update a table on the database side with cube success/failure information. I have already coded the select statement that returns rows after opening an odbc connection, but not sure how the update would work.

thanks in advance!

The following select statement works:

'--------Open ODBC Connection----------
'Test database ogdw connection
connection = SQLOpen("DSN=ogdw; UID=xxxx; PWD=xxxx", outputStr, intPrompt)
'
'
If connection < 0 Then 'Connection stopped
MsgBox &quot;Error: &quot; & connection
Exit Sub
End If
'
MsgBox &quot;Connected !&quot;
'------------Enter Select Statement--------------

strQuery = &quot;select ogdw_source_system_code, ogdw_subject_area, ogdw_mrt_process_started_date, ogdw_mrt_process_started_flag, ogdw_mrt_process_complete_date, ogdw_mrt_process_complete_flag from ogdwstg.ogdw_batch_proc_cntl where ogdw_source_system_code in ('O', 'P') and ogdw_subject_area='MV' and ogdw_mrt_process_complete_flag='Y'&quot;
'
'-----------EXECUTE QUERY-------------------------------
'
lngNbColumn = SQLExecQuery(connection , strQuery)
If lngNbColumn < 0 Then 'Query Error
msgbox &quot;Error &quot; & lngNbColumn & &quot; occurred during query execution&quot;
'
Else
lngNbLine = SQLRetrieve(connection ,varLine, lngNbColumn , , 0, ,0)
End If

 

[griffindm]

You're on the right track. Just do a SQL 'Update' statement in your strQuery variable instead of the Select and it will update your table and return the number of rows affected.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[hsqamar]

I did what suggested earlier and tried this again with the update statement in the strQuery variable... but I get an error. Do you know if there's some other variable or another execute command that has to be used for an update statment to work?

thanks.