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/
