Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Connect / Update External Database

Posted on 2009-05-05
2
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
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 connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
Suggested Courses

636 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