A couple of small things. You didn't need SQLNumResultCols, because you already knew - from your SELECT statement - that there would be three columns in the result set.
And you didn't need to unbind variables; this will happen anyway when you use SQLFreeStmt with SQL.DROP as the second argument. I would have liked to have seen your CHECK.STATUS subroutine; this is usually the critical part. Similarly, your main (fetch) loop does not exit on error - you really want it to do so. ErrCode = SQLFetch(hStmt) LOOP WHILE ErrCode <> SQL.NO.DATA.FOUND AND ErrCODE <> SQL.ERROR CRT SQLDATA(1) : " " : SQLDATA(2) : " " : SQLDATA(3) ErrCode = SQLFetch(hStmt) REPEAT IF ErrCode = SQL.ERROR THEN GOSUB CHECK.STATUS Hope this helps; I've deliberately top-posted so that you can compare what I've suggested with your admittedly working code. ----- Original Message ----- From: [EMAIL PROTECTED] To: [email protected] Subject: RE: [U2] BCI Connect Date: Fri, 31 Dec 2004 07:55:00 -0000 > > Ray & Richard > > Thanks for you reply. Here is my code which works, but would appreciate if > you could cast a quick eye over it. > > > 029: * use Unidata Connect to get data from Siebel > 030: $INCLUDE DEMOINCLUDE ODBC.H > 031: > 032: * setup connect parameters > 033: datasource="SIEBELTEST" > 034: username="SIEBEL_READ_ONLY" > 035: passwd="PASSWORD" > 036: STATUS = SQLAllocEnv(database.env) > 037: * > 038: MODULE = "ESTABLISH.BCI" > 039: ENVTYPE = "Database" > 040: Fn = "SQLAllocEnv" > 041: GOSUB CHECK.STATUS > 042: * > 043: * Step 2 - Allocate Connection Environment > 044: * > 045: * > 046: STATUS = SQLAllocConnect(database.env,connection.env) > 047: * > 048: ENVTYPE = "Connection" > 049: fn = "SQLAllocConnect" > 050: GOSUB CHECK.STATUS > 051: * > 052: * > 053: * ---------------------------------------------------------------- > 054: > 055: * Step 3 - Connecting to Database > 056: * > 057: * > 058: STATUS = SQLConnect(connection.env, datasource, username, passwd) > 059: * > 060: ENVTYPE = "Connection" > 061: GOSUB CHECK.STATUS > 062: * > 063: ALLOC.STATEMENT.ENV: > 064: * > 065: * --------------------------------------------------------------- > 066: > 067: * Step 4 - Allocate Statement Environment > 068: * The Statement Environment is used when executing SQL > 069: * statement functions > 070: * > 071: * > 072: STATUS = SQLAllocStmt(connection.env,statement.env) > 073: * > 074: MODULE = "ALLOC.statement.env" > 075: Fn = "SQLAllocStmt" > 076: ENVTYPE = "Connection" > 077: GOSUB CHECK.STATUS > 078: > 079: SQLSTATEMENT="SELECT ROW_ID AS ACCNT_ROW_ID, NAME AS ACCOUNT_NAME, LOC > AS > ACCOUNT_LOC FROM SIEBEL.S_ORG_EXT WHERE X_BTS_BUSINESS_UNIT = 'Financial > Servic > es' AND X_BTS_SECTOR = 'Trading Systems'" > 080: > 081: STATUS = SQLExecDirect(statement.env, SQLSTATEMENT) > 082: > 083: STATUS = SQLNumResultCols (statement.env, cols) > 084: > 085: DIM SQLDATA(cols) > 086: FOR NUMCOL=1 TO cols > 087: SQL.STATUS = SQLBindCol(statement.env,NUMCOL,SQL.B.DEFAULT, > SQLDATA(NUMCO > L)) > 088: NEXT NUMCOL > 089: > 090: STATUS = 0 > 091: Fn = "SQLFetch" > 092: ENVTYPE = "Statement" > 093: > 094: LOOP > *--: P > 095: WHILE STATUS <> SQL.NO.DATA.FOUND DO > 096: * > 097: * > 098: STATUS = SQLFetch(statement.env) > 099: * > 100: GOSUB CHECK.STATUS > 101: IF STATUS <> SQL.NO.DATA.FOUND THEN > 102: FOR NUMCOL=1 TO cols > 103: CRT SQLDATA(NUMCOL):" ": > 104: NEXT NUMCOL > 105: CRT > 106: END > 107: REPEAT > 108: * > 109: ENVTYPE = "Statement" > 110: GOSUB CHECK.STATUS > 111: > 112: > 113: * that it, discount from the server > 114: STATUS = SQLFreeStmt(statement.env, SQL.UNBIND) > 115: STATUS = SQLFreeStmt(statement.env, SQL.DROP) > 116: STATUS = SQLDisconnect(connection.env) > 117: STATUS = SQLFreeConnect(connection.env) > 118: STATUS = SQLFreeEnv(database.env) > 119: CRT "Finished" > 120: STOP > *--: > > > > Graham Forbes > Trading Systems > BT Consulting & Systems Integration > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod > Sent: 31 December 2004 06:02 > To: [email protected] > Subject: Re: [U2] BCI Connect > > Source code for the CONNECT verb is supplied. Track it down and take a look > at it. > > The steps are as follows. You should check for, and clear, errors at every > step. Seriously. ODBC simply does not function with "dirty handles". > > 1. Allocate ODBC environment (SQLAllocEnv) > 2. Allocate connection environment (SQLAllocConnect) > 3. Connect to Oracle database (SQLConnect) > (optional) discover information about connection (SQLTables, etc.) > 4. Construct SELECT statement. > 5. If SELECT statement includes parameter markers (?) bind variables to each > (SQLBindParameter). > 6. Allocate statement environment (SQLAllocStmt) > 7. Either: > (a) send SQL statement to be prepared (SQLPrepare) and executed > (SQLExecute) > (b) send SQL statement to be executed directly (SQLExecDirect) > 8. If you don't already know how many columns there are in the result set > (e.g. SELECT * FROM ...) > find out (SQLNumResultCols). > 9. Bind a variable to each result set column (SQLBindCol). > 10. Loop calling (SQLFetch), which returns one result row, until its status > is SQL.NO.DATA.FOUND. > 11. The value from each column is in the relevant bound variable. Do > whatever with these. > 12. Now that the loop's finished: > (a) Free the statement environment (SQLFreeStmt) > (b) Disconnect from the Oracle server (SQLDisconnect) > (c) Free the connection environment (SQLFreeConnect) > (d) Free the ODBC environment (SQLFreeEnv) > > Simple, innit? > > > ----- Original Message ----- > From: [EMAIL PROTECTED] > To: [email protected] > Subject: [U2] BCI Connect > Date: Thu, 30 Dec 2004 13:30:39 -0000 > > > > > Hello all > > > > I am trying to extract data from an Oracle Database from Unidata. I have > read > > through the BCI Connect manual and it looks fairly simple using the Connect > > command, but much more complicated using directly within Unibasic. Does > > anyone have a simple example of executing a SQL statement and returning the > > results into either a Unidata file or reading each row? > > > > If I do not want to update should I just datastack and use the CONNECT > > command? > > > > Thanks > > > > Graham > > > > > > > > Graham Forbes > > Trading Systems > > BT Consulting & Systems Integration > > > > > > > > > > ******************************************************************** > > > > This email may contain information which is privileged or confidential. If > you > > are not the intended recipient of this email, please notify the sender > > immediately and delete it without reading, copying, storing, forwarding or > > disclosing its contents to any other person > > Thank you > > > > Check us out at http://www.bt.com/consulting > > > > ******************************************************************** > > ------- > > u2-users mailing list > > [email protected] > > To unsubscribe please visit http://listserver.u2ug.org/ > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ > > > ******************************************************************** > > This email may contain information which is privileged or > confidential. If you are not the intended recipient of this email, > please notify the sender immediately and delete it without reading, > copying, storing, forwarding or disclosing its contents to any > other person > Thank you > > Check us out at http://www.bt.com/consulting > > ******************************************************************** > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
