Guidance
指路人
g.yi.org
Guidance Forums / Reginald Rexx / RxOdbc

Register 
新用户注册
Search 搜索
首页 
Home Home
Software
Upload

  
Forum List • Thread List • Reply • Refresh • New Topic • Search • Previous • Next First 1 Last
Message1. 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.
/* Set the stmt and database variables depending on the TARGET database */
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?
Message2.
#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).
/* Get DB2 database handle */
odbcallocdatabase('db2_handle', etc...)

/* Get MFE database handle. It's now the current one */
odbcallocdatabase('mfe_handle', etc...)

/* Execute a statement on the current database (MFE) */
odbcexecute(...)

/* Get results of that statement */
odbcfetch(...)

/* Switch to DB2 database handle */
odbcallocdatabase('db2_handle')

/* Execute a statement on the current database (DB2) */
odbcexecute(...)

/* Get results of that statement */
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:
/* Set current database */
SELECT database_sw
    WHEN 'MFE' THEN
       odbcallocdatabase('mfe_database')  /* PC connect handle. NOTE: pass var name, not its value! */
    OTHERWISE
       odbcallocdatabase('db2_database')   /* mainframe connect handle */
END    

/* All following calls operate on current database handle (and its
 * current statement handle).
 */
odbcsetconnectopt(...)
odbcexecute(...)
odbcfetch(...)
odbcgetdata(...)
odbctypeinfo(...)
odbcfreestmt()
Message3. A question about error handling
#11265
Posted by: Michael S 2007-07-26 18:22:14
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
  /* Catch the error via CONDITION('D') so I can analyze its content */

The following seems to be tautology (?)

err = odbcallocdatabase("DB2.db2t_handle", "ConnectString", "stmt")
CATCH ERROR
  /* Catch the error via CONDITION('D') so I can analyze its content */

Can I ever get to the catch error if I catch the return result from the ODBC call ?????
Message4.
#11268
Posted by: Jeff Glatt 2007-07-26 20:59:50
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.
Message5. 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	/* User pressed CANCEL */
			END	
		OTHERWISE
			NOP
	END			

	CATCH ERROR
		err = CONDITION('D') 
		temp = WORDS(err)
		cause = WORD(err,temp)
		SELECT 
			WHEN cause = 'CANCEL' THEN
				/* They obviously pressed Cancel (for whatever reason) */
				RETURN 1
			WHEN POS('SQL30082N',err) <> 0 THEN 
				/* This seems to be correct */
				temp = "Cannot connect to DB2T - incorrect password ?"
			WHEN (POS('SQL30081N',err) <> 0) & (POS('10061',err) <> 0) THEN 
				/* This worked for me */
				temp = "Cannot connect to DB2T - try rebooting your machine"
			WHEN POS('IM002',err) <> 0 THEN
				DO
					/* The following line is an example of what Fredrik received when trying to
						 connect to DB2T when he had no ODBC DB2 driver */
				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.
Message6.
#11273
Posted by: Jeff Glatt 2007-07-26 22:32:21
Did you set the variable "OdbcErr" to "ERROR"?
Message7. Yes
#11274
Posted by: Michael S 2007-07-26 23:12:32
My starting code is based on the online help

LIBRARY rxodbc
odbcerr = "ERROR"
odbcheading = 1
DO
  odbcallocenv()
  /* If OdbcAllocEnv fails, we do the following. */
	CATCH ERROR
  	/* Display an error message box, and end the script. */
  	CONDITION("M")
  	RETURN
END
Message8.
#11284
Posted by: Jeff Glatt 2007-07-28 04:33:22
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?
Message9. That's strange  it suddenly seems to be working
#11381
Posted by: Michael S 2007-08-03 22:39:33
Here's the code I have today (for anyone else)

  defaultdatabase = 'DB2 Express'
  user = 'DB2admin'
  DROP stmt   /* Preparatory to them possibly hitting Cancel */
  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	/* User pressed CANCEL - return */
		END
		
	CATCH ERROR
		err = CONDITION('D')
		temp = WORDS(err)
Message10. 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
Message11. Jeff can probably answer this better, but I'm tempted to say ...
#11604
Posted by: Michael S 2007-08-22 06:33:10
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 First 1 Last
掌柜推荐
 
 
 
 
 
 
 
 
 
 
 
 
© Wed 2021-4-14  Guidance Laboratory Inc.
Email:webmaster1g.yi.org Hits:0