Guidance Forums / Reginald Rexx / Problem running ODBC selection twice-Guidance指路人

Guidance
指路人
g.yi.org

Guidance Forums / Reginald Rexx / Problem running ODBC selection twice

Home

Software

Upload

回顶部

⇑

Forum List • Thread List • Refresh • New Topic • Search • Previous • Next

1. 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

2. 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

3. 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