Re: [U2] BCI
Symeon, The data type is 'date'. I have not tried any other data type. Maybe I should change the sql data type to 'datetime'? Rudy Message: 9 Date: Sat, 5 Nov 2011 11:31:29 - From: Symeon Breen syme...@gmail.com To: 'U2 Users List' u2-users@listserver.u2ug.org Subject: Re: [U2] BCI Message-ID: 022c01cc9bae$7784abe0$668e03a0$@gmail.com Content-Type: text/plain; charset=us-ascii 1st problem - - the data type - is a date or datetime - have you tried the other - or even a timespan. Learn more about SAGE Research Methods Online (SRMO) - the essential tool for researchers http://www.srmo.sagepub.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Cooper, Rudy Sent: 04 November 2011 22:02 To: 'u2-users@listserver.u2ug.org' Subject: [U2] BCI Need some help on using a bci function with sql server. We are running UV 10.3.3 on windows 2003 server sp2. We've been using bci to run sprocs in sql server. A couple of things came up that we're having problems with which we have never used before and thought this list might be of help to us. ... ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] BCI
I cant remember exactly but I thought the date data type only came in on sql server 2005, and that it was just datetime before that . -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Cooper, Rudy Sent: 09 November 2011 17:37 To: 'u2-users@listserver.u2ug.org' Subject: Re: [U2] BCI ?Symeon, The data type is 'date'. I have not tried any other data type. Maybe I should change the sql data type to 'datetime'? Rudy Message: 9 Date: Sat, 5 Nov 2011 11:31:29 - From: Symeon Breen syme...@gmail.com To: 'U2 Users List' u2-users@listserver.u2ug.org Subject: Re: [U2] BCI Message-ID: 022c01cc9bae$7784abe0$668e03a0$@gmail.com Content-Type: text/plain; charset=us-ascii 1st problem - - the data type - is a date or datetime - have you tried the other - or even a timespan. Learn more about SAGE Research Methods Online (SRMO) - the essential tool for researchers http://www.srmo.sagepub.com -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Cooper, Rudy Sent: 04 November 2011 22:02 To: 'u2-users@listserver.u2ug.org' Subject: [U2] BCI Need some help on using a bci function with sql server. We are running UV 10.3.3 on windows 2003 server sp2. We've been using bci to run sprocs in sql server. A couple of things came up that we're having problems with which we have never used before and thought this list might be of help to us. ... ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1411 / Virus Database: 2092/4005 - Release Date: 11/08/11 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] BCI
1st problem - - the data type - is a date or datetime - have you tried the other - or even a timespan. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Cooper, Rudy Sent: 04 November 2011 22:02 To: 'u2-users@listserver.u2ug.org' Subject: [U2] BCI Need some help on using a bci function with sql server. We are running UV 10.3.3 on windows 2003 server sp2. We've been using bci to run sprocs in sql server. A couple of things came up that we're having problems with which we have never used before and thought this list might be of help to us. 1st problem: We're passing an internal uv date to a sproc whose data type is date. Here are the parameters: STATUS = SQLBindParameter(STMTENV, 4, SQL.B.INTDATE, SQL.DATE, 8, 0, STARTDATE, SQL.PARAM.INPUT) We've never used SQL.B.INTDATE and SQL.DATE before, but according to the bci documentation that is what your are supposed to use when sending a date to a sql date data type. After the sqlexecute this is the error we get: SQLExecute_SqlExecute Error, state S1C00 Natcode 0 Errtxt [IBM][SQL Client][ODBC ][Microsoft][ODBC SQL Server Driver]Optional feature not implemented What the heck does that mean? Could it be that we have an old driver? 2nd problem: How do I retrieve an output value from a stored procedure? There is a conditional that sets a variable in a sql stored procedure and sets it to an output variable, like so: @ReturnValue int OUTPUT If (select isdate(@StartDataScopeDate)+isdate(@EndDataScopeDate)) 2 return 1 @ReturnValue = @ReturnValue OUT How can I get the value of 'OUT'? For retrieving the values of a dataset we'd use sqlbindcol and sqlfetch, but I don't think that will work in this case. The sqlbindparameter looks like this: STATUS = SQLBindParameter(STMTENV, 6, SQL.B.BASIC, SQL.VARCHAR, 8, 0, NUL3, SQL.PARAM.OUTPUT) I'm just assuming I should use SQL.PARAM.OUTPUT instead of SQL.PARAM.INPUT. Any help would be greatly appreciated. Thx, Rudy Learn more about SAGE Research Methods Online (SRMO) - the essential tool for researchers http://www.srmo.sagepub.com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1411 / Virus Database: 2092/3996 - Release Date: 11/04/11 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] BCI on UniData
Hello everyone, I am trying to use this and I am coming up with the error when I try to run the SQLConnect command. The error is sqlstate = 08003, which I have looked up and found it means the connection is not open. What I don't know is how to go about opening the connection. I have not been able to find anything on opening the connection. Am I correct that a connection needs to be opened? If so, how do I do it? Thank you. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] BCI Interface
I have not found any way to force SQL type compatibility, but I have worked around it by using CAST/CONVERT in the SQL statement to make UniData see the data as a type it did recognize. In my case it was a matter of BigInt not being handled (which is ironic, since U2 doesn't usually fuss about whether something is a string or a number). I don't know if that is going to work for your situation, though. HTH, David Beahm Date: Fri, 13 Jul 2007 05:43:44 -0600 From: Trey Miller [EMAIL PROTECTED] Subject: [U2] BCI Interface Hello, I'm on a system running UniData 6.0. I'm attempting to use the BCI to return an SQL table from a 2003 MsSql server. I've established a connection and can execute the SQLExecDirect with a select * from tablename against several tables with no issue, but any table which includes a binary column fails with an unsupported data type error. The documentation shows that there are provisions for binding SQL.B.BINARY data types when pushing data out of UniData and onto the data source. I'm wondering if there are any provisions for binding to a binary data column when attempting to bring an entire table into UniData through the BCI. I can not seem to find any. Thanks in advance! --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] BCI Interface
Hello, I'm on a system running UniData 6.0. I'm attempting to use the BCI to return an SQL table from a 2003 MsSql server. I've established a connection and can execute the SQLExecDirect with a select * from tablename against several tables with no issue, but any table which includes a binary column fails with an unsupported data type error. The documentation shows that there are provisions for binding SQL.B.BINARY data types when pushing data out of UniData and onto the data source. I'm wondering if there are any provisions for binding to a binary data column when attempting to bring an entire table into UniData through the BCI. I can not seem to find any. Thanks in advance! --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI Interface
Thank you David. I suspected I might be reduced to trying a cast on the server side. I should also mention that I am using the Easysoft driver and I've posited the question to them, just to see their take. Kind regards! -- Original message -- From: David Beahm [EMAIL PROTECTED] I have not found any way to force SQL type compatibility, but I have worked around it by using CAST/CONVERT in the SQL statement to make UniData see the data as a type it did recognize. In my case it was a matter of BigInt not being handled (which is ironic, since U2 doesn't usually fuss about whether something is a string or a number). I don't know if that is going to work for your situation, though. HTH, David Beahm Date: Fri, 13 Jul 2007 05:43:44 -0600 From: Trey Miller Subject: [U2] BCI Interface Hello, I'm on a system running UniData 6.0. I'm attempting to use the BCI to return an SQL table from a 2003 MsSql server. I've established a connection and can execute the SQLExecDirect with a select * from against several tables with no issue, but any table which includes a binary column fails with an unsupported data type error. The documentation shows that there are provisions for binding SQL.B.BINARY data types when pushing data out of UniData and onto the data source. I'm wondering if there are any provisions for binding to a binary data column when attempting to bring an entire table into UniData through the BCI. I can not seem to find any. Thanks in advance! --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI Connect
Does anyone know if I can call a SQL stored procedure using the SQLExecDirect command? Thanks Graham Graham Forbes| Trading Systems | BT Global Services | Tel:+44 (0)20 7176| Mob:+44 (0)77407 38550 | Fax:+44 (0)20 7177 | E: [EMAIL PROTECTED] | www.bt.com/globalservices -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: Sunday, January 02, 2005 11:01 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect 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: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect Date: Fri, 31 Dec 2004 07:55:00 - 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: u2-users@listserver.u2ug.org 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
RE: [U2] BCI Connect
Yes, you can call a stored procedure using the SQLExecDirect function, provided everything has been set up right (variables bound to the arguments and result columns, etc.). Note that there are two syntaxes, one with curly braces, the other with CALL. Your database may expect one or the other or either. - Original Message - From: [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect Date: Wed, 28 Jun 2006 07:38:35 +0100 Does anyone know if I can call a SQL stored procedure using the SQLExecDirect command? Thanks Graham --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI Connect
Angelo No, Unidata on Windows to SQL 2005. I haven't tried it yet, but when I do I will post an example here. Regards Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Angelo Collazo Sent: Wednesday, June 28, 2006 2:50 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect Graham, By any chance are you doing BCI via UNIX? Cheers, Angelo Collazo System Administrator Silver Line Building Products Corporation One Silver Line Drive North Brunswick NJ, 08902 [EMAIL PROTECTED] Ph: 732.435.1000 X4495 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 2:39 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect Does anyone know if I can call a SQL stored procedure using the SQLExecDirect command? Thanks Graham Graham Forbes| Trading Systems | BT Global Services | Tel:+44 (0)20 7176| Mob:+44 (0)77407 38550 | Fax:+44 (0)20 7177 | E: [EMAIL PROTECTED] | www.bt.com/globalservices -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Wurlod Sent: Sunday, January 02, 2005 11:01 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect 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: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect Date: Fri, 31 Dec 2004 07:55:00 - 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
RE: [U2] BCI
We had problems with it too, when I did a BCI app - the way I found it worked was to use VAR char for everything, on the SQL side, and it worked!!! -Original Message- From: Cooper, Rudy [mailto:[EMAIL PROTECTED] Sent: Friday, May 26, 2006 3:45 PM To: u2-users@listserver.u2ug.org Subject: [U2] BCI Hello List, I have a problem with the bci inserting into sql server. I'm running UV 10.1.10, W2K Sp2. I've already ran it thru IBM and they tell me it is a problem on the sql server side. Here is my problem: I have a uv program that inserts into a table in sql server via a stored procedure. I get the following error: lExecute Error, state 37000 Natcode 201 Errtxt [IBM][SQLClient][ODBC][Microsoft][ODBC SQL Server Driver][SQLserver] Procedure 'sp_insert_person' expects parameter '@homephone', which was not supplied. The parameter @homephone is being passed. If I remove a few of the fields that are being passed I don't get the error, so it seems like there is a limit to the number of fields that can be passed. I put the fields that I removed back in and reran, the error comes back. I reran once again with all the fields and with the sql profiler turned on (trace), and it shows all but the last two fields reaching my stored procedure, which explains why my parameter @homephone never reached sql server. I'm sending 33 fields, each row of data is about 2K bytes. Does anyone know anything about this problem and if there is a fix for it ? Thx, Rudy Rudy Cooper Technical Project Lead Sage Publications Information Technology Development (805) 410-7724 [EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI
Rudy .. I've already ran it thru IBM and they tell me it is a problem on the sql server side. they would .. wouldn't they I have a uv program that inserts into a table in sql server via a stored procedure. I get the following error: lExecute Error, state 37000 Natcode 201 Errtxt [IBM][SQLClient][ODBC][Microsoft][ODBC SQL Server Driver][SQLserver] Procedure 'sp_insert_person' expects parameter '@homephone', which was not supplied. since you are running a stored procedure have you tried running it from the server side? If that works have you tried running form anything else .. say excell or MSQuery? The parameter @homephone is being passed. If I remove a few of the fields that are being passed I don't get the error, so it seems like there is a limit to the number of fields that can be passed. I put the fields that I removed back in and reran, the error comes back. I don't remember there being a limit and am on the road right now so don't have my docs. I don't see anywhere where you say what 'sql' server you are using and what version. DSig David Tod Sigafoos SigsSolutions, Inc. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] BCI
Hello List, I have a problem with the bci inserting into sql server. I'm running UV 10.1.10, W2K Sp2. I've already ran it thru IBM and they tell me it is a problem on the sql server side. Here is my problem: I have a uv program that inserts into a table in sql server via a stored procedure. I get the following error: lExecute Error, state 37000 Natcode 201 Errtxt [IBM][SQLClient][ODBC][Microsoft][ODBC SQL Server Driver][SQLserver] Procedure 'sp_insert_person' expects parameter '@homephone', which was not supplied. The parameter @homephone is being passed. If I remove a few of the fields that are being passed I don't get the error, so it seems like there is a limit to the number of fields that can be passed. I put the fields that I removed back in and reran, the error comes back. I reran once again with all the fields and with the sql profiler turned on (trace), and it shows all but the last two fields reaching my stored procedure, which explains why my parameter @homephone never reached sql server. I'm sending 33 fields, each row of data is about 2K bytes. Does anyone know anything about this problem and if there is a fix for it ? Thx, Rudy Rudy Cooper Technical Project Lead Sage Publications Information Technology Development (805) 410-7724 [EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] bci
Rudy What does your code look like? BTW...You should use SQLExecDirect when calling stored procedures, when it doesn't need to be called repeatedly (as it is supposedly more efficient). Check out the online help or BCI manuals for more info. Here's a snippet of source code from a real sample of calling a stored procedure...from BCI under UV... STORED.PROC = call *data_io_handler (?, ?, ?, ?, ?, ?, ?, ?) SQL.STATUS = SQLBindParameter(STMTENV, 1, SQL.B.BASIC, SQL.INTEGER, 0, 0, ERROR.CODE, SQL.PARAM.OUTPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 2, SQL.B.BASIC, SQL.INTEGER, 0, 0, SEVERITY.CODE, SQL.PARAM.OUTPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 3, SQL.B.BASIC, SQL.CHAR, 0, 0, ERROR.TEXT, SQL.PARAM.OUTPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 4, SQL.B.BASIC, SQL.CHAR, 0, 0, TABLE.NAME, SQL.PARAM.INPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 5, SQL.B.BASIC, SQL.CHAR, 0, 0, PRIMARY.KEY, SQL.PARAM.INPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 6, SQL.B.BASIC, SQL.CHAR, 0, 0, INPUT.LIST, SQL.PARAM.INPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 7, SQL.B.BASIC, SQL.CHAR, 0, 0, OUTPUT.LIST, SQL.PARAM.OUTPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLBindParameter(STMTENV, 8, SQL.B.BASIC, SQL.CHAR, 0, 0, QUERY, SQL.PARAM.INPUT) gosub CHECK.STMT.ENV SQL.STATUS = SQLExecDirect(STMTENV, STORED.PROC) if SQL.STATUS then crt Error: SQLExecDirect failed, SQL.STATUS is :SQL.STATUS gosub CHECK.STMT.ENV SQL.STATUS = SQLRowCount(STMTENV, ROWS) if ROWS then crt ROWS: rows returned end Regards, David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cooper, Rudy Sent: Saturday, 11 March 2006 9:48 AM To: u2-users@listserver.u2ug.org Subject: [U2] bci Can someone familiar with bci assist me with one of the functions. I'm running bci from w2k UV 10.1.10. I'm trying to get a varchar output parameter from a sql server stored procedure. I made sure the sproc worked in sql analyzer before attempting to use bci. In UV I can execute the sproc just fine, but I can't seem to get the output returned. I'm using SQLBindParameter to get the output from my sproc. I execute immediately after SqlExecute. I don't seem to be getting any output back from SQLBindParameter. No error is generated and the return status is 0. In which argument is my output supposed to be in ? Any hints would be greatly appreciated. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] RE: [u2] bci
David,
Here is my test code. This run's fine without the output parameter from
my stored procedure.
**Source code:**
OPTIONS INFORMATION
*
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
UCMD = ''
*
STATUS = SQLAllocEnv(DBENV)
STATUS = SQLAllocConnect(DBENV,CONENV)
STATUS = SQLConnect(CONENV,'sagefin_old_odbc','webuser','webuser')
STATUS = SQLAllocStmt(CONENV,STMTENV)
UCMD = {call sp_insert_cmtest ('1*40888','GARY','WILLIAMS','1
SPRING RD.','MOORPARK','CA','93021','USA')}
STATUS = SQLPrepare(STMTENV,UCMD)
STATUS = SQLExecute(STMTENV)
CRT 'Status SqlExecute ':STATUS
CRT 'SQL.SUCCESS ':SQL.SUCCESS
STATUS = SQLFreeStmt(STMTENV,SQL.DROP)
STATUS = SQLDisconnect(CONENV)
STATUS = SQLFreeConnect(CONENV)
STATUS = SQLFreeEnv(DBENV)
END
**Output:**
Status SqlExecute 0
SQL.SUCCESS 0
**My sproc with the output parameter commented out:**
CREATE PROCEDURE [dbo].[sp_insert_cmtest]
@cmid as varchar(50),
@fnameas varchar(50),
@lname as varchar(50),
@addr1 as varchar(50),
@city as varchar(50),
@state as varchar(50),
@zip as varchar(50),
@country as varchar(10)
[EMAIL PROTECTED] as varchar(50) OUTPUT
AS
SET NOCOUNT ON
INSERT INTO
cmtest (id,firstname,lastname,address1,city,state,zip,country)
VALUES
(@cmid,@fname,@lname,@addr1,@city,@state,@zip,@country)
--SELECT @id_out = @cmid
GO
My test source:
OPTIONS INFORMATION
*
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
UCMD = ''
DBENV = '' ; STMTENV = '' ; CONENV = ''
TESTCOL = ''
*
STATUS = SQLAllocEnv(DBENV)
STATUS = SQLAllocConnect(DBENV,CONENV)
STATUS = SQLConnect(CONENV,'sagefin_old_odbc','webuser','webuser')
STATUS = SQLAllocStmt(CONENV,STMTENV)
UCMD = {call sp_insert_cmtest ('1*40999','GARY','WILLIAMS','1
SPRING RD.','MOORPARK','CA','93021','USA')}
STATUS = SQLPrepare(STMTENV,UCMD)
STATUS = SQLExecute(STMTENV)
CRT 'Status SqlExecute ':STATUS
CRT 'SQL.SUCCESS ':SQL.SUCCESS
STATUS = SQLFetch(STMTENV)
STATUS = SQLBindCol(STMTENV,1,SQL.B.CHAR,TESTCOL)
STATUS = SQLFreeStmt(STMTENV,SQL.DROP)
STATUS = SQLDisconnect(CONENV)
STATUS = SQLFreeConnect(CONENV)
STATUS = SQLFreeEnv(DBENV)
END
Output:
Status SqlExecute -1
SQL.SUCCESS 0
So, on the 2nd test with the output parameter enabled in the sproc, the
insert fails.
So I guess my question is, how do I get the output from the sproc into
my UV program from a stored procedure.
Any suggestions would be greatly appreciated.
Thx,
Rudy
Date: Thu, 16 Mar 2006 23:14:44 +1100
From: Hona, David S [EMAIL PROTECTED]
Subject: RE: [U2] bci
Rudy
What does your code look like?
BTW...You should use SQLExecDirect when calling stored procedures, when
it doesn't need to be called repeatedly (as it is supposedly more
efficient). Check out the online help or BCI manuals for more info.
Here's a snippet of source code from a real sample of calling a stored
procedure...from BCI under UV...
Rudy Cooper
Information Technology
Project Technical Lead
Sage Publications
2455 Teller Road
Thousand Oaks, California 91320
Direct (805) 410-7724
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] RE: [u2] bci
Rudy,
Looking at your UV BASIC BCI code and stored procedure, I suggest the
following:
A) I noticed that your stored procedure has 9 arguments, not 8 as you've
defined in your BCI code. You've ommitted the most important one - the
output argument. Isn't that what you're asking about?
B) I recommend you to use SQLExecDirect, instead of SQLExecute (see my
example)
C) When using SQLExecDirect, you will first need to define/declare your
stored procedure and argument list using parameter mark characters (?
characters).
That is, do not put the values in the declaration.
For example: MYSPROC = {sptestproc (?,?,?,?)}.
Secondly, you will need to define all the Input and Output arguments
with SQLBindParameter (see example I posted). Ensure the in correct
sequential order as the stored procedure inputs and outputs them.
Again, look at the example I posted and amend your own source code
appropriately.
Hope this helps.
Regards,
David
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] bci
SQLBindParameter is for binding a variable to a parameter marker in the query to be executed. What you really need is SQLBindCol, which binds a variable to a column in the result set. If the number of return columns is N, you need N invocations of SQLBindCol (that is, you must consume all the columns in the result set, even if the application does nothing with some of them). You can dynamically determine the number of result columns with SQLNumResultCols. HTH --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] bci
Can someone familiar with bci assist me with one of the functions. I'm running bci from w2k UV 10.1.10. I'm trying to get a varchar output parameter from a sql server stored procedure. I made sure the sproc worked in sql analyzer before attempting to use bci. In UV I can execute the sproc just fine, but I can't seem to get the output returned. I'm using SQLBindParameter to get the output from my sproc. I execute immediately after SqlExecute. I don't seem to be getting any output back from SQLBindParameter. No error is generated and the return status is 0. In which argument is my output supposed to be in ? Any hints would be greatly appreciated. Thx, Rudy Rudy Cooper Information Technology Project Technical Lead Sage Publications 2455 Teller Road Thousand Oaks, California 91320 Direct (805) 410-7724 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] BCI with two statement environments
I'm able to use BCI (UniBasic SQL Client Interface) with one statement environment, but when I try to add a second one it stops working. The manual says You allocate the SQL statement environment with the SQLAllocStmt function. This function allocates memory for an SQL statement environment and returns its address, or handle, in a variable. You can establish more than one SQL statement environment for the same connection environment. If I remove the query, then the update works. Both the query and the update work fine if I paste them into a SQL query tool. Is there something else I need to do to get multiple statement environments to work? Code: STATUS = '' STATUS-1 = SQLAllocEnv(database.env) STATUS-1 = SQLAllocConnect(database.env,connection.env) STATUS-1 = SQLConnect(connection.env, DATASOURCE, USERNAME, PASSWD) STATUS-1 = SQLAllocStmt(connection.env,query.stmt.env) STATUS-1 = SQLAllocStmt(connection.env,update.stmt.env) GOSUB CHECK.STATUS ;* Try querying before updating... STATEMENT = 'select * from FoundationFamilyDataCollection' CRT STATEMENT STATUS-1 = SQLExecDirect(query.stmt.env,STATEMENT) MSG = 'Executing query' GOSUB CHECK.STATUS DATESTAMP = OCONV(@DATE,D4/MDY) STATEMENT = UPDATE tPAMemberInfo SET updatedDatetime = ':DATESTAMP:' WHERE rowId = '1' CRT STATEMENT STATUS-1 = SQLExecDirect(update.stmt.env,STATEMENT) MSG = 'Executing update' GOSUB CHECK.STATUS Output: : XWDS.TEST.BCI.WRITE select * from foundationfamilydatacollection UPDATE tPAMemberInfo SET updatedDatetime = '02/08/2006' WHERE rowId = '1' STATUS IS -1 MSG IS Executing update ERROR, STOPPING. Thanks, Wendy Smoak --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI with two statement environments
Hi Wendy I'm able to use BCI (UniBasic SQL Client Interface) with one statement environment, but when I try to add a second one it stops working. It has been while since I worked with BCI, but I am sure that you have to reset the sqlallocstmt for each query. Regards David Jordan --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI with two statement environments
On 2/8/06, David Jordan [EMAIL PROTECTED] wrote: I'm able to use BCI (UniBasic SQL Client Interface) with one statement environment, but when I try to add a second one it stops working. It has been while since I worked with BCI, but I am sure that you have to reset the sqlallocstmt for each query. Thanks for your help. As far as I can tell, I allocating a new statement for each one: STATEMENT = 'update ...' STATUS-1 = SQLAllocStmt(connection.env,update.stmt.env) STATUS-1 = SQLExecDirect(update.stmt.env,STATEMENT) STATEMENT = 'select * from ...' STATUS-1 = SQLAllocStmt(connection.env,query.stmt.env) STATUS-1 = SQLExecDirect(query.stmt.env,STATEMENT) In that order, it works fine. If I put the query first, (which I need to do!) I get a status of -1 when I try to execute the update. Does a query do something else to the overall environment that I need to deal with in order to be able to update a table while I'm cursoring through a selection from a view? -- Wendy --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI with two statement environments
Thanks for your help. As far as I can tell, I allocating a new statement for each one: STATEMENT = 'update ...' STATUS-1 = SQLAllocStmt(connection.env,update.stmt.env) STATUS-1 = SQLExecDirect(update.stmt.env,STATEMENT) STATEMENT = 'select * from ...' STATUS-1 = SQLAllocStmt(connection.env,query.stmt.env) STATUS-1 = SQLExecDirect(query.stmt.env,STATEMENT) In that order, it works fine. If I put the query first, (which I need to do!) I get a status of -1 when I try to execute the update. Wendy - I know this may sound dumb but is the update section returning a value which you need for the query section? Is something running after the update section before the query that is needed for the query section? dan -- This communication may contain information that is legally privileged, confidential or exempt from disclosure. If you are not the intended recipient, please note that any dissemination, distribution, or copying of this communication is strictly prohibited. Anyone who receives this message in error should notify the sender immediately by telephone or by return e-mail and delete it from his or her computer. -- Daniel Plocinik [EMAIL PROTECTED] Sr. Programmer Analyst Arnold Porter LLP Telephone: 202-942-6392 555 Twelfth Street, NW Fax:202-942-5999 Washington, DC 20004-1206 For more information about Arnold Porter LLP, click here: http://www.arnoldporter.com --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI with two statement environments
On 2/8/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: is the update section returning a value which you need for the query section? Is something running after the update section before the query that is needed for the query section? No. That was lifted straight from a little test program I'm using to try to figure out what's wrong. In fact I need to run the query (and the associated column bindings, etc.) first in order to get the rowId I am supposed to update. Still no idea why the update-then-query works, but not the other way around. :/ Thanks... keep the ideas coming. I know other people are using two statement environments, so clearly I'm doing something wrong. :) -- Wendy --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI with two statement environments
Hi Wendy Could you not do that as one SQL statement ie as below UPDATE .. WHERE ID = (SELECT id FROM table WHERE ...) Regards David Jordan Managing Consultant --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI with two statement environments
For UniVerse at least you can have as many statement environments as you like in a connection environment. Of course each must have its own management space allocated (SQLAllocStmt). --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI with two statement environments
On 2/8/06, David Jordan [EMAIL PROTECTED] wrote: Could you not do that as one SQL statement ie as below UPDATE .. WHERE ID = (SELECT id FROM table WHERE ...) No. That's stripped down from the actual code until I can figure out what I'm doing wrong. The original program selects a bunch of rows from a view, cursors through them, and updates a different table as it processes each row. I can't write back to the view, and I don't want to write at all until I've evaluated the data in the row and done my processing. (And since once again I've forgotten to mention it: UniData 6.1.) -- Wendy --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: Re[2]: [U2] BCI and Dates to SqlServer
I noticed the difference between this example and the previous is that your
nbr and tdate varaibles were set being null string values just before
the SQLBindParameter. Yet this example (with no parameter markers) is force
feeding the values and it is working. This suggests that you are passing
empty strings/values - perhaps? Just speculating here!
I suggest to you change populate the variables prior to the bind and see
what happens. I've experienced similar issues with SQLBindParameter and
SQLExecDirect in older BCI releases.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Tod Sigafoos
Sent: Tuesday, 29 March 2005 2:49 AM
To: Ray Wurlod
Cc: u2-users@listserver.u2ug.org
Subject: Re[2]: [U2] BCI and Dates to SqlServer
Ray,
sorry to do this again but i forgot to mention ..
if i change the 'prepare' section as noted here .. it does work well.
Now I can modify the read, write shells to force the conversion ..
but now am really interested in why i am not getting it right to
begin with g
*if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 0, date)
SQL.SUCCESS) then stop 'date'
if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.BASIC, SQL.DATE, 30, 0, date)
SQL.SUCCESS) then stop 'date'
nbr = 1
tdate = 13598
tdate = oconv(tdate,'D4-')
date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
sqlStatement = 'insert into testDate values (' : nbr : ',' : squote(date) :
')'
*sqlStatement = 'insert into testDate values (?,?)'
IF (SQLPrepare(sqlStmtEnv, sqlStatement) SQL.SUCCESS) then stop 'prepare'
*nbr = 1
*tdate = 13598
*tdate = oconv(tdate,'D4-')
*date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI and Dates to SqlServer
The command HELP BCI SQLBindParameter refers to DATEFORM and SQLSetConnectOptions, and to Appendix A (of the BCI manual). Looking at all of those you may get a clue, though as far as I can make out, DATEFORM is not supported for ODBC dbms types in 10.0. Anyway take a look; I'll take a look at your code on the weekend. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: Re[4]: [U2] BCI and Dates to SqlServer
Hi David, I'm well, thanks. How you are going? I've checked out our BCI applications, we've always populated the variables prior to SQLBindParameter. Probably as a result of normal Pick/U2 BASIC convention than what the manual says. Hopefully, this 'tip' may work for you. I believe some C-code we have done using UCI, does work, as per the manual, as you pointed out correctly (albeit, the UCI manual). Regards, David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Tod Sigafoos Sent: Wednesday, 30 March 2005 7:50 AM To: Hona, David S Subject: Re[4]: [U2] BCI and Dates to SqlServer David, How is it going? I have done that .. been moving stuff around just to see what might correct the problem. According to a note from IBM .. the binding really only connects position with var name. Actual data doesn't take effect until the execute. Seemed odd to me G. But will try it again .. can't hurt --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re[2]: [U2] BCI and Dates to SqlServer
Ray, if you get a chance could you please look at the sample code I sent. I can not find any errors in it and yet the dates do not work. According to the manual .. If you use SQL.B.INTDATE, the SQL Client Interface assumes the program variable holds a date in UniVerse internal date format and uses the DATEFORM conversion string to convert the internal date to an external format as required by the data source. As you can see from the sample code I sent last week I am using SQL.B.INTDATE with SQL.DATE which should force the internal date to the correct format. My test code sets 'date = 13598' which should be converted to 24Mar05. I have tried to mix and match this .. but still have no success. Any help will be appreciated. thanks Thursday, March 24, 2005, 1:52:39 PM, you wrote: RW BCI mimics the ODBC API, so dates are expected to be in ODBC format (-MM-DD). RW Using the constant SQL.B.DATE in SQLBindParameter() or RW SQLBindColumn() causes an implicit conversion to occur, so that RW you get internal format out of a SELECT, and must supply internal RW format in bound variables to an INSERT or UPDATE. -- DSig ` David Tod Sigafoos ( O O ) ___oOOo__( )__oOOo___ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re[2]: [U2] BCI and Dates to SqlServer
Ray,
sorry to do this again but i forgot to mention ..
if i change the 'prepare' section as noted here .. it does work well.
Now I can modify the read, write shells to force the conversion ..
but now am really interested in why i am not getting it right to
begin with g
*if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 0, date)
SQL.SUCCESS) then stop 'date'
if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.BASIC, SQL.DATE, 30, 0, date)
SQL.SUCCESS) then stop 'date'
nbr = 1
tdate = 13598
tdate = oconv(tdate,'D4-')
date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
sqlStatement = 'insert into testDate values (' : nbr : ',' : squote(date) : ')'
*sqlStatement = 'insert into testDate values (?,?)'
IF (SQLPrepare(sqlStmtEnv, sqlStatement) SQL.SUCCESS) then stop 'prepare'
*nbr = 1
*tdate = 13598
*tdate = oconv(tdate,'D4-')
*date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
Thanks again ..
Thursday, March 24, 2005, 1:52:39 PM, you wrote:
RW BCI mimics the ODBC API, so dates are expected to be in ODBC format
(-MM-DD).
RW Using the constant SQL.B.DATE in SQLBindParameter() or
RW SQLBindColumn() causes an implicit conversion to occur, so that
RW you get internal format out of a SELECT, and must supply internal
RW format in bound variables to an INSERT or UPDATE.
--
DSig `
David Tod Sigafoos ( O O )
___oOOo__( )__oOOo___
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI and Dates to SqlServer
David,
I'm on the road (as usual), doing non-UniVerse stuff. The earliest I will get
to take a look at this is next weekend. Since you have a workaround, I guess
the urgency's gone. Have you contacted IBM support? (And what version of SQL
Server are you using?)
Regards,
Ray
- Original Message -
From: David Tod Sigafoos [EMAIL PROTECTED]
To: Ray Wurlod [EMAIL PROTECTED]
Subject: Re[2]: [U2] BCI and Dates to SqlServer
Date: Mon, 28 Mar 2005 08:49:27 -0800
Ray,
sorry to do this again but i forgot to mention ..
if i change the 'prepare' section as noted here .. it does work well.
Now I can modify the read, write shells to force the conversion ..
but now am really interested in why i am not getting it right to
begin with g
*if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30,
0, date) SQL.SUCCESS) then stop 'date'
if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.BASIC, SQL.DATE, 30, 0,
date) SQL.SUCCESS) then stop 'date'
nbr = 1
tdate = 13598
tdate = oconv(tdate,'D4-')
date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
sqlStatement = 'insert into testDate values (' : nbr : ',' :
squote(date) : ')'
*sqlStatement = 'insert into testDate values (?,?)'
IF (SQLPrepare(sqlStmtEnv, sqlStatement) SQL.SUCCESS) then stop 'prepare'
*nbr = 1
*tdate = 13598
*tdate = oconv(tdate,'D4-')
*date = field(tdate,'-',3) :'-': field(tdate,'-',1) :'-': field(tdate,'-',2)
Thanks again ..
Thursday, March 24, 2005, 1:52:39 PM, you wrote:
RW BCI mimics the ODBC API, so dates are expected to be in ODBC
format (-MM-DD).
RW Using the constant SQL.B.DATE in SQLBindParameter() or
RW SQLBindColumn() causes an implicit conversion to occur, so that
RW you get internal format out of a SELECT, and must supply internal
RW format in bound variables to an INSERT or UPDATE.
--
DSig `
David Tod Sigafoos ( O O )
___oOOo__( )__oOOo___
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: Re[2]: [U2] BCI and Dates to SqlServer
Can you re-send the sample code? I seem to have deleted it already. Thanks, Ray --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: Re[2]: [U2] BCI and Dates to SqlServer
What happens if you replace SQL.B.INTDATE with SQL.B.BASIC in the SQLBindParameter call? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] BCI and Dates to SqlServer
BCI mimics the ODBC API, so dates are expected to be in ODBC format (-MM-DD). Using the constant SQL.B.DATE in SQLBindParameter() or SQLBindColumn() causes an implicit conversion to occur, so that you get internal format out of a SELECT, and must supply internal format in bound variables to an INSERT or UPDATE. HTH --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re[2]: [U2] BCI and Dates to SqlServer
Ray, Thursday, March 24, 2005, 1:52:39 PM, you wrote: RW BCI mimics the ODBC API, so dates are expected to be in ODBC format (-MM-DD). yes .. RW Using the constant SQL.B.DATE in SQLBindParameter() or RW SQLBindColumn() causes an implicit conversion to occur, so that RW you get internal format out of a SELECT, and must supply internal RW format in bound variables to an INSERT or UPDATE. I do use the SQL.B.INTDATE and SQL.DATE. MsSql creates as datetime (using Enterprise Manager) .. As soon as i try to write to this column i get the error. If i change the column to 'char' in all places then it writes out correctly. It is only DATE that i have any problems with (haven't tried time). INTs chars varchars (which map also to text columns) etc work well. It is only date .. it must be something on my end as I would find it odd that there is some ambiguity in the uv - odbc interface -- DSig ` David Tod Sigafoos ( O O ) ___oOOo__( )__oOOo___ Cannot open file C:\DOCUMENTS AND SETTINGS\DAVID TOD SIGAFOOS\APPLICATION DATA\BATMAIL\COOKIES.TXT --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re[2]: [U2] BCI and Dates to SqlServer
Ray, Thursday, March 24, 2005, 1:52:39 PM, you wrote: snip as further follow up here is a sample routine and info. Note that that the only problem shows up after the sqlExecute .. and it is caused by the write .. RUN DAVES.BP DATE_TEST.MV Program: DATE_TEST Status for sqlExecute call is: -1 SQLSTATE,NATCOD are:S1C00 0 Error text is [IBM][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Optional feature n ot implemented Program DATE_TEST.MV: Line 69, Cannot resolve ERRMSG item EXITING CKSTMTENV. sql definition: tableName = testDate columns nbr int testDatedatetime --- program = 'DATE_TEST' include UNIVERSE.INCLUDE ODBC.H equ TRUE$ to 1 equ FALSE$ to 0 equ NULL$ to '' if (SQLAllocEnv(comBaseEnv) SQL.SUCCESS) SQL.SUCCESS then stop 'allocEnv' if (SQLAllocConnect(comBaseEnv, comConnEnv) SQL.SUCCESS) then stop 'allocConnect' if (SQLConnect(comConnEnv, 'Universe','Universe','universe') SQL.SUCCESS) then stop 'Connect' if (SQLAllocStmt(comConnEnv, sqlStmtEnv) SQL.SUCCESS) SQL.SUCCESS then stop 'allocStmt' nbr = NULL$ date = NULL$ if (SQLBindParameter(sqlStmtEnv, 1, SQL.B.BASIC, SQL.INTEGER, 4, 0, nbr) SQL.SUCCESS) then stop 'BP nbr' if (SQLBindParameter(sqlStmtEnv, 2, SQL.B.INTDATE, SQL.DATE, 30, 0, date) SQL.SUCCESS) then stop 'date' sqlStatement = 'insert into testDate values (?,?)' IF (SQLPrepare(sqlStmtEnv, sqlStatement) SQL.SUCCESS) then stop 'prepare' nbr = 1 date = 13598 status = sqlExecute(sqlStmtEnv) if status SQL.SUCCESS then sqlRoutine = 'sqlExecute' gosub CKSTMTENV end STOP CKSTMTENV: STATUS = status Fn = sqlRoutine Expect = '' IF STATUS EQ -2 THEN status = SQLFreeStmt(sqlStmtEnv, SQL.DROP) STOP INVALID STATEMENT HANDLE end IF STATUS EQ 100 THEN RETURN IF STATUS NE 0 THEN 301* ST = SQLERROR(-1,-1,sqlStmtEnv,STATE,NATCODE,ERRTXT) IF ST SQL.NO.DATA.FOUND THEN PRINT COUNT = 1 IF Expect NE 0 AND STATE = Expect AND ST SQL.NO.DATA.FOUND THEN PRINT Allowed error of :STATE: returned for func :Fn GOTO 399 END ELSE PRINT 'Program: ': program PRINT Status for :Fn: call is: :STATUS PRINT SQLSTATE,NATCOD are: : STATE: :NATCODE PRINT Error text is PRINT : ERRTXT END IF ST = 0 THEN GOTO 301 END IF STATUS = -1 AND COUNT = 1 THEN status = SQLFreeStmt(sqlStmtEnv, SQL.DROP) STOP EXITING CKSTMTENV end END 399* IF STATUS 0 THEN PRINT RETURN -- DSig ` David Tod Sigafoos ( O O ) ___oOOo__( )__oOOo___ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] BCI Connect
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: u2-users@listserver.u2ug.org Subject: RE: [U2] BCI Connect Date: Fri, 31 Dec 2004 07:55:00 - 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: u2-users@listserver.u2ug.org 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
RE: [U2] BCI Connect
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: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org Subject: [U2] BCI Connect Date: Thu, 30 Dec 2004 13:30:39 - 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
[U2] BCI Connect
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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
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: u2-users@listserver.u2ug.org Subject: [U2] BCI Connect Date: Thu, 30 Dec 2004 13:30:39 - 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] BCI problems
We are trying to congigure and use BCI on Universe 10 on a solaris 8. We have configured Datadirects ODBC drivers properly and are able to connect to our Oracle databases. However when we log into Universe and use the CONNECT command we are able to connect but we get a core dump when we try to perform a select or any other operation. Has anyone else had any problems using BCI. Daniel Buster Nimmons System Analyst/Developer SB Engineers Constructors email: [EMAIL PROTECTED] phone: 713-845-4343 fax: 713-640-8717 --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
