Guidance
指路人
g.yi.org
Guidance Forums / Reginald Rexx / Problem running ODBC selection twice

Register 
注册
Search 搜索
首页 
Home Home
Software
Upload

  
Forum List • Thread List • Refresh • New Topic • Search • Previous • Next First 1 Last
Message1. Problem running ODBC selection twice
#13635
Posted by: Michael S 2013-06-06 15:12:13
I put together the following script which illustrates my problem.

Basically, the main part of the script runs fine (the actual selection), but not if I try and run it twice as shown below.

I assume I'm doing something wrong (or missing doing something) but what ?

/* */
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

rc = get_number_of_rows()
IF rc <> 0 THEN
	EXIT 1
rc = get_number_of_rows()
IF rc <> 0 THEN
	EXIT 1

RETURN
/********************************************************************

********************************************************************/
get_number_of_rows:

connectstring = "DSN=MFEODBC;SERVER=DATOR;Database=userid;UID=userid;PWD=;"

db2_handlename = "DB2.MFE_handle"

rc = odbcconnect(connect_string, db2_handlename, , "PROMPT") 

DO
  rc = odbcallocdatabase(db2_handlename, "ConnectString", "stmt")  
  SELECT
  	WHEN rc <> '' THEN
  		SAY rc
  	OTHERWISE
  		NOP
  END				
	CATCH ERROR
		err = CONDITION('D')
		SAY err
		RETURN 1
END

data. = "Select count(*) from sysibm.syscolumns where name = 'NAME'"
DO
	odbcexecute(data., "columns")
	CATCH ERROR		
	  err = CONDITION('D')
		SAY err
		RETURN 1
END
DO
	DO WHILE odbcfetch("data.", columns) <> "DONE"
		SAY 'data.1 ' data.1
	END
	CATCH ERROR		
	  err = CONDITION('D')
		SAY err
		RETURN 1
END

/* Close the cursor */
odbcfreestmt('CLOSE')	
err = odbcfreedatabase()
/* drop db2_handlename */

RETURN 0
Message2. After som experimentaton
#13636
Posted by: Michael S 2013-06-08 16:59:16 Last edited by: Michael S 2013-06-08 17:05:02 (Total edited 1 time)
I got it working (halfway).

I han't understood that the actual database handle should be a simple undefined variable name. Here's what my code looks like now

/* */
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

SAY 'This selection (no 1) should prompt for your database pw'
rc = get_number_of_rows()
IF rc <> 0 THEN
	EXIT 1
SAY 'This selection (no 2) should NOT require a pw'	
rc = get_number_of_rows()
IF rc <> 0 THEN
	EXIT 1
err = odbcfreedatabase('DROP', db2_handlename)
IF err <> "" THEN
	SAY 'Error from OdbcFreeDatabase 'err
DROP db2_handlename
	
SAY 'This selection (no 3) should prompt for your database pw'
rc = get_number_of_rows()
IF rc <> 0 THEN
	EXIT 1
SAY 'This selection (no 4) should NOT require a pw'	
rc = get_number_of_rows()
IF rc <> 0 THEN
	EXIT 1
RETURN
/********************************************************************

********************************************************************/
get_number_of_rows:

IF EXISTS("db2_handlename") = 1 THEN
	NOP
ELSE	
	DO
		connectstring = "DSN=DB2 Express;Database=SAMPLE;UID=DB2admin"
  	rc = odbcallocdatabase("db2_handlename", "ConnectString", "stmt")  
  	SELECT
  		WHEN rc <> '' THEN
  			SAY rc
  		OTHERWISE
  			NOP
  	END				
		CATCH ERROR
			err = CONDITION('D')
			SAY err
			RETURN 1
	END

data. = "Select count(*) from sysibm.syscolumns where name = 'NAME'"
DO
	odbcexecute(data., "columns")
	CATCH ERROR		
	  err = CONDITION('D')
		SAY err
		RETURN 1
END
DO
	DO WHILE odbcfetch("data.", columns) <> "DONE"
		SAY 'data.1 ' data.1
	END
	CATCH ERROR		
	  err = CONDITION('D')
		SAY err
		RETURN 1
END

/* Close the cursor */
odbcfreestmt('CLOSE')	

RETURN 0
The trouble is, when I get to the third selection, I perform the OdbcAllocDatabase for the second time, I get an error message of ODBCALLOCDATABASE argument 1 must be zero or positive; found "db2_handlename"

What this results i (in reality) is that if I run various ODBC selections, I have to close the window/script and start again (then everything works fine). Having a window open and performing the selections and then trying to run the same selections again fails every time
Message3. Okay - cracked it (I think)
#13637
Posted by: Michael S 2013-06-08 17:19:38 Last edited by: Michael S 2013-06-08 17:27:56 (Total edited 1 time)
Will have to test a bit more at work on Monday, but the trick seems to be to change the line

DROP db2_handlename

to

DROP db2_handlename stmt
Note that we're now dropping the first and third arguments to the odbcallocdatabase call.
Forum List • Thread List • Refresh • New Topic • Search • Previous • Next First 1 Last
掌柜推荐
 
 
 
 
 
 
 
 
 
 
 
 
© Tue 2024-12-10  Guidance Laboratory Inc.
Email:webmaster1g.yi.org Hits:0