Connect / Update External Database

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:
 
1.
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.

2.
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:

transaction TransObj
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 ) )
      END IF
END IF

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 ;      


Thanks,
byteboy11Asked:
Who is Participating?
 
diasroshanConnect With a Mentor Commented:
Hi,

Find the code to populate the DSNs in drop down list box.
string ls_subkeylist[]
string ls_info, ls_current_dir
string DSType
integer li_rtn, I, MaxVal

li_rtn = RegistryValues("HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources", ls_subkeylist)
MaxVal = UpperBound(ls_subkeylist)

ddlb_dbf.clear()
FOR I=1 TO MaxVal
      RegistryGet("HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources", ls_subkeylist[i], RegString!, DSType)
      IF (Pos(DSType, "Adaptive Server Anywhere") = 1)  THEN
            ddlb_dbf.AddItem(ls_subkeylist[i])
      END IF      
NEXT

li_rtn = RegistryValues("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources", ls_subkeylist)
MaxVal = UpperBound(ls_subkeylist)

FOR I=1 TO MaxVal
      RegistryGet("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources", ls_subkeylist[i], RegString!, DSType)
      IF (Pos(DSType, "Adaptive Server Anywhere") = 1)  THEN
            ddlb_dbf.AddItem(ls_subkeylist[i])
      END IF      
NEXT

li_rtn= ddlb_dbf.FindItem ( 'RPS', 1)
If (li_rtn > 0) Then
      ddlb_dbf.SelectItem(li_rtn)
Else
      ddlb_dbf.SelectItem(1)
End If

return 1
//Code ends here

Now, u could query this drop list box to look for ur DSN.

The code to connect to ODBC is fine. Give it a try.

To update the table, u can look into dynamic SQLs in PB Help. There are 4 types.
You can use the below example,
These statements prepare an INSERT statement with three parameters in SQLSA and then execute it using the value of the PowerScript variables Dept_id_var, Dept_name_var, and Mgr_id_var (note that Mgr_id_var is null):

INT Dept_id_var = 156
INT Mgr_id_var    
String  Dept_name_var
Dept_name_var = "Department"
SetNull(Mgr_id_var)
PREPARE SQLSA
        FROM "INSERT INTO department VALUES (?,?,?)" using TransObj ;
EXECUTE SQLSA
       USING :Dept_id_var,:Dept_name_var,:Mgr_id_var ;

Cheers,
Rosh
0
 
sandeep_patelCommented:
To update record in another database, you may not need dynamic sql. you can simply write the sql and just commig using new transaction object.

update table1 set column1 = 1 where column1 = 2 using transobj;
commit using transobj;

update table2 set column2 = 2 where column2 = 3 using transobj;
commug using transobj;

OR

You can create updateable datawindow for each table. and use datastore with new transaction object;

datastore lds_temp
lds_temp.dataobject = 'dw_table1'
lds_temp.settransobject(Transobj)
lds_temp.SetItem(1,'column1','1')
lds_temp.update()

lds_temp.dataobject = 'dw_table2'
lds_temp.settransobject(Transobj)
lds_temp.SetItem(1,'column2','1')
lds_temp.update()

Regards,
Sandeep
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.