Run multiple statements from VB6 ?
A VB6 developer asked how to get back the serial/primary key value generated by an INSERT, guessing at "select SQLCA.SQLERRD[2] ... go". Replies pointed out that GO is SQL Server syntax (Informix uses a semicolon), and that SQLCA is a global record read via a single-row select, e.g. SELECT SQLCA.SQLERRD[2] FROM systables WHERE tabid=1 — but outside ESQL/C the correct form is SELECT DBINFO('sqlca.sqlerrd1') FROM systables WHERE tabid=1, which the original responder agreed was right. An unrelated question piggybacked on the thread (an SPL function failing in an ACE report) turned out to be a typo in the caller.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am contracting at a new site and I need to return the PK from an
insert that I am making.
I have seen quite a few statements but not sure if this is correct?
select SQLCA.SQLERRD[2] from informix.myTable
What I want to do is :
insert into informix.myTable(crs_recur_setup_id , bla_bla_bal ...)
values ( 0, fo, fo, fo)go
select SQLCA.SQLERRD[2] from informix.myTable
go
Then receive back the value of the array for the PK of that table.
I have no docs and google is limited having a "follow this code
syntax" for this issue.
Is this right or am I pretty close?
TIA
__Stephen
Note that:
1) The Informix statement separator is a semi-colon ("GO" is SQL Server).
2) SQLCA is a global record: get a member using any single-row SELECT.
Revised solution:
INSERT INTO myTable (...) VALUES (...);SELECT SQLCA.SQLERRD[2] FROM systables WHERE tabid = 1;
--
Regards,
Doug Lawry
www.douglawry.webhop.org
> values ( 0, fo, fo, fo)
> go
> select SQLCA.SQLERRD[2] from informix.myTable
"srussell705" <srussell@lotmate.com> wrote in message
news:1180103985.913599.240800@p47g2000hsd.googlegroups.com...
>I am contracting at a new site and I need to return the PK from an
> insert that I am making.
>
> I have seen quite a few statements but not sure if this is correct?
>
> select SQLCA.SQLERRD[2] from informix.myTable
>
> What I want to do is :
>
> insert into informix.myTable(crs_recur_setup_id , bla_bla_bal ...)
> values ( 0, fo, fo, fo)> go
> select SQLCA.SQLERRD[2] from informix.myTable
> go
>
> Then receive back the value of the array for the PK of that table.
>
> I have no docs and google is limited having a "follow this code
> syntax" for this issue.
>
> Is this right or am I pretty close?
>
> TIA
>
> __Stephen
>
Greetings... INFORMIX 7.2x (pre-Y2K) ISQL 7.21 HPUX 11.x I have a stored procedure language that has run fine for years when called from an SQL. I tried it for the first time in an ACE report. It isn't recognized in ACE! The error I get shows that it expects a FUNCTION statement. That's for C language library calls, though. Any ideas on how to make an ACE report use an SPL? Rob Konikoff
If he's not using EsqlC, he may have to say"
SELECT DBINFO('sqlca.sqlerrd1') FROM systables WHERE tabid = 1;
----- Original Message -----
From: Doug Lawry
Newsgroups: comp.databases.informix
To: informix-list@iiug.org
Sent: Friday, May 25, 2007 10:00 AM
Subject: Re: Run multiple statements from VB6 ?
Note that:
1) The Informix statement separator is a semi-colon ("GO" is SQL Server).
2) SQLCA is a global record: get a member using any single-row SELECT.
Revised solution:
INSERT INTO myTable (...) VALUES (...); SELECT SQLCA.SQLERRD[2] FROM systables WHERE tabid = 1;
--
Regards,
Doug Lawry
www.douglawry.webhop.org
> values ( 0, fo, fo, fo)
> go
> select SQLCA.SQLERRD[2] from informix.myTable
"srussell705" <srussell@lotmate.com> wrote in message
news:1180103985.913599.240800@p47g2000hsd.googlegroups.com...
>I am contracting at a new site and I need to return the PK from an
> insert that I am making.
>
> I have seen quite a few statements but not sure if this is correct?
>
> select SQLCA.SQLERRD[2] from informix.myTable
>
> What I want to do is :
>
> insert into informix.myTable(crs_recur_setup_id , bla_bla_bal ...)
> values ( 0, fo, fo, fo) > go
> select SQLCA.SQLERRD[2] from informix.myTable
> go
>
> Then receive back the value of the array for the PK of that table.
>
> I have no docs and google is limited having a "follow this code
> syntax" for this issue.
>
> Is this right or am I pretty close?
>
> TIA
>
> __Stephen
>
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Rob Konikoff wrote: > Greetings... > > INFORMIX 7.2x (pre-Y2K) > ISQL 7.21 > HPUX 11.x > > I have a stored procedure language that has run fine for years when called > from an SQL. I tried it for the first time in an ACE report. It isn't > recognized in ACE! > > The error I get shows that it expects a FUNCTION statement. That's for C > language library calls, though. > > Any ideas on how to make an ACE report use an SPL? If you are trying to do 'EXECUTE PROCEDURE your_spl_code()', the answer is "You can't". If you are trying to do 'SELECT your_spl_code(), extra_info FROM Somewhere', the question is "What's your problem?". Of course, if you can't use the function in the SELECT statement from DB-Access, you won't be able to do it in ACE either. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/
Bill is quite right, Stephen. It must have been too early in the morning!
"Bill64bits" <garage_dba@hotmail.com> wrote in message
news:mailman.22.1180126735.13675.informix-list@iiug.org...
If he's not using EsqlC, he may have to say"
SELECT DBINFO('sqlca.sqlerrd1') FROM systables WHERE tabid = 1;
----- Original Message -----
From: Doug Lawry
Newsgroups: comp.databases.informix
To: informix-list@iiug.org
Sent: Friday, May 25, 2007 10:00 AM
Subject: Re: Run multiple statements from VB6 ?
Note that:
1) The Informix statement separator is a semi-colon ("GO" is SQL Server).
2) SQLCA is a global record: get a member using any single-row SELECT.
Revised solution:
INSERT INTO myTable (...) VALUES (...);SELECT SQLCA.SQLERRD[2] FROM systables WHERE tabid = 1;
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"srussell705" <srussell@lotmate.com> wrote in message
news:1180103985.913599.240800@p47g2000hsd.googlegroups.com...
> I am contracting at a new site and I need to return the PK from an
> insert that I am making.
>
> I have seen quite a few statements but not sure if this is correct?
>
> select SQLCA.SQLERRD[2] from informix.myTable
>
> What I want to do is :
>
> insert into informix.myTable(crs_recur_setup_id , bla_bla_bal ...)
> values ( 0, fo, fo, fo)> go
> select SQLCA.SQLERRD[2] from informix.myTable
> go
>
> Then receive back the value of the array for the PK of that table.
>
> I have no docs and google is limited having a "follow this code
> syntax" for this issue.
>
> Is this right or am I pretty close?
>
> TIA
>
> __Stephen
Jonathan: Ok, you were right... It was the way I was calling it (i.e., it doesn't need to be "called"). I hate to admit this, but the FUNCTION statement error was caused by a typo. Thanks... Rob Konikoff -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Jonathan Leffler Sent: Monday, May 28, 2007 12:41 AM To: informix-list@iiug.org Subject: Re: SPL not available in ACE but is in SQL? Rob Konikoff wrote: > Greetings... > > INFORMIX 7.2x (pre-Y2K) > ISQL 7.21 > HPUX 11.x > > I have a stored procedure language that has run fine for years when called > from an SQL. I tried it for the first time in an ACE report. It isn't > recognized in ACE! > > The error I get shows that it expects a FUNCTION statement. That's for C > language library calls, though. > > Any ideas on how to make an ACE report use an SPL? If you are trying to do 'EXECUTE PROCEDURE your_spl_code()', the answer is "You can't". If you are trying to do 'SELECT your_spl_code(), extra_info FROM Somewhere', the question is "What's your problem?". Of course, if you can't use the function in the SELECT statement from DB-Access, you won't be able to do it in ACE either. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/ _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list