| Forum List • Thread List • Reply • Refresh • New Topic • Search • Previous • Next 1 | 1. RxOdbc #11138 Posted by: Michael S 2007-07-18 02:03:47 Last edited by: Jeff Glatt 2007-07-20 09:14:08 (Total edited 3 times) | My old ODBC code (FUNCDEF'ing the MS Odbc API) did:ret = sqlexecdirect(stmt, sql_string, LENGTH(sql_string))
...where the variable Stmt was the handle to the database.
RxOdbc's equivalent OdbcExecute() doesn't take a statement handle as an arg. How do I tell OdbcExecute what the statement handle is?
With the old code I'm running, I can migrate data from a mainframe DB2 database to a PC based one. What this (obviously) means is that I connect to the mainframe and connect to the PC at the same time using two different database handles.
SELECT db2_source
WHEN 'MFE' THEN
DO
stmt = mfe.!statement
database = mfe.!database
END
OTHERWISE
DO
stmt = db2.!statement
database = db2.!database
END
END
get_table_name_and_coltypes(statement, tablename)
sqlexecdirect(stmt, sql_string, LENGTH(sql_string)) If I connect to 2 databases using the new RxOdbc functions, how do I tell RxOdbc which statement and database handles to use? | 2. #11157 Posted by: Jeff Glatt 2007-07-18 15:25:16 Last edited by: Jeff Glatt 2007-07-25 00:25:22 (Total edited 6 times) | All RxODBC functions keep internal copies of any SQL handles you obtain. The functions always use the "current handles" where needed. So there is always a "current" environment, database, and typically statement handle.
To open 2 databases simultaneously, call OdbcAllocDatabase to get a database (and one statement) handle. For the first arg, pass the quoted name of the variable to store the database handle. Call OdbcAllocDatabase again to get a second database/statement handle, using a different (quoted) variable name. Now you have two database handle variables. To set a particular one as the "current database handle", call OdbcAllocDatabase again, passing only that quoted variable name. RxOdbc will see that your variable is filled in with a legitimate database handle and set it as the "current handle". Now all subsequent calls operate upon this handle. To change to the other handle, pass that one's quoted name to OdbcAllocDatabase. After you close your database, you can DROP the variable if you ever want to reuse it with OdbcAllocDatabase,
In other words, you can switch the RxOdbc functions between different database handles via OdbcAllocDatabase.
Likewise, you can allocate numerous statement handles for a given database, if you needed more than one. You always get one with OdbcAllocDatabase. But you can get more with OdbcAllocStmt. And to switch between various statement handles, pass a particular quoted variable name that has been filled in by OdbcAllocStmt to make it the "current statement handle". Now all RxOdbc functions that work on a statement handle, use that particular handle (for that particular database).
odbcallocdatabase('db2_handle', etc...)
odbcallocdatabase('mfe_handle', etc...)
odbcexecute(...)
odbcfetch(...)
odbcallocdatabase('db2_handle')
odbcexecute(...)
odbcfetch(...) So, you just open your two databases with two OdbcAllocDatabase calls. Then you only need to set your database handle once, and the rest of the calls use it automatically:
SELECT database_sw
WHEN 'MFE' THEN
odbcallocdatabase('mfe_database')
OTHERWISE
odbcallocdatabase('db2_database')
END
odbcsetconnectopt(...)
odbcexecute(...)
odbcfetch(...)
odbcgetdata(...)
odbctypeinfo(...)
odbcfreestmt() | 3. A question about error handling #11265 | It seems I have two ways of handling errors in ODBC. Either something like
err = odbcallocdatabase("DB2.db2t_handle", "ConnectString", "stmt")
or
odbcallocdatabase("DB2.db2t_handle", "ConnectString", "stmt")
CATCH ERROR
The following seems to be tautology (?)
err = odbcallocdatabase("DB2.db2t_handle", "ConnectString", "stmt")
CATCH ERROR
Can I ever get to the catch error if I catch the return result from the ODBC call ????? | 4. #11268 | No. When an error is triggered on a function call, the function doesn't return a value, and no assignment is made. Reginald jumps to the CATCH ERROR. When using such "exception handling", your assignment to "err" is pointless. Just omit it. | 5. Then ..... #11272 Posted by: Michael S 2007-07-26 21:38:51 Last edited by: Jeff Glatt 2007-08-22 07:19:53 (Total edited 1 time) | take the following code and scenario
DO
defaultdatabase = 'DB2T'
user = TRANSLATE(USERID())
DROP stmt
connectstring = "DSN=" || defaultdatabase || ";UID=" || user || ";"
err = odbcallocdatabase("DB2.db2t_handle", "ConnectString", "stmt")
SELECT
WHEN err <> '' THEN
rc = show_db2_error_msg(err)
WHEN EXISTS('stmt') = 0 THEN
DO
guisay("Access to DB2T deliberately cancelled")
RETURN 1
END
OTHERWISE
NOP
END
CATCH ERROR
err = CONDITION('D')
temp = WORDS(err)
cause = WORD(err,temp)
SELECT
WHEN cause = 'CANCEL' THEN
RETURN 1
WHEN POS('SQL30082N',err) <> 0 THEN
temp = "Cannot connect to DB2T - incorrect password ?"
WHEN (POS('SQL30081N',err) <> 0) & (POS('10061',err) <> 0) THEN
temp = "Cannot connect to DB2T - try rebooting your machine"
WHEN POS('IM002',err) <> 0 THEN
DO
temp = "Your PC has not been configured for ODBC connection to DB2T - investigate"
END
OTHERWISE
PARSE VAR err . ']' . ']' temp
END
guisay(temp)
rc = 'Error'
Based on your comment above, I removed the "err =" on the odbcAllocDatabase and debugged the code. When I came to odbcAllocDatabase, since I was connecting to our mainframe, I was prompted for a password. I then entered (deliberately) an invalid one. I get no catch error, so how do I know what's happened ? If I use the "err = " then I get a message string containing SQL30082N - and as you can see, I can issue a message to the user telling them what they did. | 6. #11273 | Did you set the variable "OdbcErr" to "ERROR"? | 7. Yes #11274 | My starting code is based on the online help
LIBRARY rxodbc
odbcerr = "ERROR"
odbcheading = 1
DO
odbcallocenv()
CATCH ERROR
CONDITION("M")
RETURN
END
| 8. #11284 | Ok, now that I've seen some example scripts you've sent, I'll ask the question:
Did you set the variable "OdbcErr" to "ERROR" in the script that actually calls the Odbc functions? | 9. That's strange it suddenly seems to be working #11381 | Here's the code I have today (for anyone else)
defaultdatabase = 'DB2 Express'
user = 'DB2admin'
DROP stmt
connectstring = "DSN=" || defaultdatabase || ";Database=" || database ";UID=" || user || ";"
odbcallocdatabase("DB2.db2_express_handle", "ConnectString", "stmt")
IF EXISTS('stmt') = 0 THEN
DO
rc = wdwsay.rex('No access since you pressed Cancel', 'STOP')
RETURN 1
END
CATCH ERROR
err = CONDITION('D')
temp = WORDS(err)
| 10. ODBC across objects? #11602 Posted by: chucksim 2007-08-21 18:24:48 Last edited by: chucksim 2007-08-21 18:48:29 (Total edited 1 time) | Can ODBC activity be done across object scripts like this?
MAIN.REX OdbcAllocEnv() ... OdbcAllocDatabase("jobdb", "ConnectString", "MyStatement") ... OdbcAllocDatabase("sysdb", "ConnectString1", "MyStatement1") ... OdbcAllocDatabase("jobdb")
...
CreateObject("admin.rex","input_admin","" , !global.parent , "NORMAL") inputhandle = input_admin~gethandle()
CreateObject("reports.rex","reports", "", !global.parent , "HIDE") reportshandle = reports~gethandle()
Then I need to be able to call my database functions (scripts) that read from and wite to either database from any of by objects or in my MAIN script. Or will I need to make a new connection to each database and use that handle inside of each object?Chuck Simmons | 11. Jeff can probably answer this better, but I'm tempted to say ... #11604 | yes. I have an application that does NOT do creatobject - rather, I call sub-scripts that do the actual reading/writing to and from different databases. For example, read from DB2 in z/OS and writes to a DB2 system on the PC. I think the main thing you need to remember is to "connect" to the correct database before accessing it via something like
SELECT
WHEN db2.source = 'DB2T' THEN
odbcallocdatabase('DB2.db2t_handle')
WHEN db2.source = 'MFE' THEN
odbcallocdatabase('DB2.mfe_handle')
OTHERWISE
odbcallocdatabase('DB2.db2t_handle')
END
In the above case, I've exposed any variables called DB2. so I can get at the handles.
Can't see any reason why your approach shouldn't work. | Forum List • Thread List • Reply • Refresh • New Topic • Search • Previous • Next 1 |
|
|