Connect / Update External Database
Posted on 2009-05-05
I"m looking for some code that will help update an external DB within PB. We use PB 10.5.
I need to to 3 things:
Query the ODBC on my system. The tab that says 'System DSN' and get that list of names there.
The reason is I need the code to check if a particular ODBC name is setup.
Need to connect to a particular ODBC(an external DB other then the one I'm currently connected to). I think this code will do that:
IF NOT ISVALID(TransObj) THEN
TransObj = CREATE transaction
TransObj.DBMS = "ODBC"
TransObj.AutoCommit = False
TransObj.DBParm = "ConnectString ='DSN=MyClientDB;UID=john; PWD=doe"
CONNECT USING TransObj;
IF TransObj.SqlCode <> 0 THEN
MessageBox ( 'Could not connect', 'RC : ' + String ( TransObj.SqlCode ) )
3. I need to be able to issue an update statement on that new DB. I have no problem creating the statement but there are like 5 tables to update so ideally it would be a dynamic statement something like::
'Update Transaction_Table Set Store = 5 Where Store = 1'
Could I possibly use a datastore by doing lnv_datastore.SetTrans(TransObj) and somehow set its sql to the update statement and have that execute?
OR just use the execute statement like this:
ls_sql_stmt = 'Update Transaction_Table Set Store = 5 Where Store = 1'
EXECUTE IMMEDIATE :ls_sql_stmt ;