Solved

Connect / Update External Database

Posted on 2009-05-05
2
310 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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Remove unused usings from code in MonoDevelop 8 410
notepad++ shortcuts 6 134
Doxygen plugin for Android Studio 1 470
Java Help 2 69
Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
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: …
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 new code templates in NetBeans IDE 8.0 for Windows.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now