Hi all,
I am working with BCI from Unidata to connect to a MSSQL database.
Everything seems to be fine except, when I insert a row into a table
with identity column, I am not able to retrieve the new id. I tried
using the Transact SQL function 'Select scope_identity() as lastaID'
for instance, but the result set brings back no data.

Any help would be greatly appreciated. This insert statement is
working, I can see the new data there no problem, just can get the new
id back to my Unidata Basic program.

Here's what the code says:

SQLSTMT = "INSERT INTO contact (salutation, first_name,
middle_name, last_name, name_qualifier, prof_suffix, title,
contact_function, customer_id) values ('":CON.SALUTATION:"',
'":CON.FIRSTNAME:"', '":CON.MIDDLENAME:"', '":CON.LASTNAME:"',
'":CON.QUALIFIER:"', '":CON.SUFFIX:"', '":CON.TITLE:"',
'":CON.FUNCTION:"',' ":CON.CUSTOMERID:"')"
STATUS = SQLExecDirect(statement.env,SQLSTMT)
Fn = "SQLExecDirect"
ENVTYPE = "Statement"
GOSUB CHECK.STATUS
SQLSTMT = "SELECT scope_identity() as lastID"
STATUS = SQLExecDirect(statement.env,SQLSTMT)
Fn = "SQLExecDirect"
ENVTYPE = "Statement"
GOSUB CHECK.STATUS
STATUS = SQLBindCol(statement.env, 1, SQL.B.NUMBER, CON.ID)
STATUS = SQLFetch(statement.env)
STATUS = SQLFreeStmt(statement.env, SQL.CLOSE)

Thanks in advance
Baker

I apologize that I have no answer to the BCI problem.

If:
- you wish to remove SQL syntax from your MV DBMS _or_
- you find there are issues with BCI _or_
- you'd like more platform-independent code so that you can move your
SQL queries between MV environments
Then:
I highly recommend you have a look at mv.NET.

Basic info is here:
http://
removethisNebula-RnD.com/products/mvdotnet/mvdotnet.htm

I can provide code samples for exchanging data between MV and SQL
Server, MySQL, Oracle, and MV environments as well.

HTH
Tony Gravagno
Nebula Research and Development
TG@ wedon'tlikespamNebula-RnD.com

"Baker" wrote:

Baker wrote:


Hi Baker - have you tried doing this command set outside of BCI - i.e.
is it necessarily BCI or U2 at fault here. As far as I am aware any SQL
commands executed via BCI are just executed as is...

Baker wrote:
Symeon has a good question. Have you cut/paste the statement you are
trying to pass through into command line sql to see the results?

Also, it has been almost 2 years since working with bci but .. i seem
to remember that there was a method in the manual/sample code to
perform this. I could be very wrong about this as i work in many
environments and sometimes the munge together. Have you tried this
question on the u2 list?