?
Solved

Connect / Update External Database

Posted on 2009-05-05
2
Medium Priority
?
320 Views
Last Modified: 2013-12-26
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,
0
Comment
Question by:byteboy11
2 Comments
 
LVL 18

Accepted Solution

by:
diasroshan earned 2000 total points
ID: 24314055
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
 
LVL 14

Expert Comment

by:sandeep_patel
ID: 24315566
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question