Version Info

Currently installed version: 3.50.UC8 - AIX 5.3
Hitting IBM Informix Dynamic Server Version 11.50.FC9 - AIX 5.3


I have a stored procedure:

CREATE PROCEDURE test_sp(PorderNum INT, PbatchID CHAR(20)) ...


We are attempting to use get descriptor to get the input parameters of the prepare statement:


EXEC SQL allocate descriptor 'stmt_params_in' with max 50;
EXEC SQL describe input :hv_stmt_alias using sql descriptor 'stmt_params_in';

EXEC SQL prepare :hv_stmt_alias from "execute procedure test_sp(?,?)";

....

hv_index = 1;
EXEC SQL get descriptor 'stmt_params_in' VALUE :hv_index :hv_type = TYPE, :hv_length = LENGTH, :hv_indicator = INDICATOR;
TRACE_LOG(TL_LEVEL1)("query_informix_statement: hv_index=%d hv_type=%d hv_length(%d)", hv_index, hv_type, hv_length);

hv_index = 2;
EXEC SQL get descriptor 'stmt_params_in' VALUE :hv_index :hv_type = TYPE, :hv_length = LENGTH, :hv_indicator = INDICATOR;
TRACE_LOG(TL_LEVEL1)("query_informix_statement: hv_index=%d hv_type=%d hv_length(%d)", hv_index, hv_type, hv_length);


The trace logs the following:

20110916120919139143 0,0,0 0,0,1,1 0.000000 query_informix_statement: hv_index=1 hv_type=2 hv_length(4)
20110916120919139164 0,0,0 0,0,1,1 0.000000 query_informix_statement: hv_index=2 hv_type=0 hv_length(0)

We are trying to figure out why the second argument is giving us a length of 0 as opposed to 20.

It should give us 20, correct? We have done this for standard SQL statements before just fine but this is the first time we have tried it with a storeprocedure.

Any help or directions to look?


TIA,
David

Prepare first then describe.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Fri, Sep 16, 2011 at 12:16 PM, Link, David A <DALink (AT) west (DOT) com> wrote:

Sorry. Cut and Paste got in the way here, Art. We connect, prepare, allocate descriptors, then attempt to describe the descriptor.

This should work, right?

From: Art Kagel [mailto:art.kagel (AT) gmail (DOT) com]
Sent: Friday, September 16, 2011 11:47 AM
To: Link, David A
Cc: informix-list (AT) iiug (DOT) org
Subject: Re: ESQL/C question

Prepare first then describe.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com<http://www.advancedatatools.com>)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, orby inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.


On Fri, Sep 16, 2011 at 12:16 PM, Link, David A <DALink (AT) west (DOT) com<mailtoALink (AT) west (DOT) com>> wrote:
Version Info

Currently installed version: 3.50.UC8 - AIX 5.3
Hitting IBM Informix Dynamic Server Version 11.50.FC9 - AIX 5.3


I have a stored procedure:

CREATE PROCEDURE test_sp(PorderNum INT, PbatchID CHAR(20)) ...


We are attempting to use get descriptor to get the input parameters of the prepare statement:


EXEC SQL allocate descriptor 'stmt_params_in' with max 50;
EXEC SQL describe input :hv_stmt_alias using sql descriptor 'stmt_params_in';

EXEC SQL prepare :hv_stmt_alias from "execute procedure test_sp(?,?)";

....

hv_index = 1;
EXEC SQL get descriptor 'stmt_params_in' VALUE :hv_index :hv_type = TYPE, :hv_length = LENGTH, :hv_indicator = INDICATOR;
TRACE_LOG(TL_LEVEL1)("query_informix_statement: hv_index=%d hv_type=%d hv_length(%d)", hv_index, hv_type, hv_length);

hv_index = 2;
EXEC SQL get descriptor 'stmt_params_in' VALUE :hv_index :hv_type = TYPE, :hv_length = LENGTH, :hv_indicator = INDICATOR;
TRACE_LOG(TL_LEVEL1)("query_informix_statement: hv_index=%d hv_type=%d hv_length(%d)", hv_index, hv_type, hv_length);


The trace logs the following:

20110916120919139143 0,0,0 0,0,1,1 0.000000 query_informix_statement: hv_index=1 hv_type=2 hv_length(4)
20110916120919139164 0,0,0 0,0,1,1 0.000000 query_informix_statement: hv_index=2 hv_type=0 hv_length(0)

We are trying to figure out why the second argument is giving us a length of 0 as opposed to 20.

It should give us 20, correct? We have done this for standard SQL statements before just fine but this is the first time we have tried it with a storeprocedure.

Any help or directions to look?


TIA,
David




_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org<ma...iiug (DOT) org>
http://www.iiug.org/mailman/listinfo/informix-list

Yup, that's good. I haven't used descriptors much, I prefer using the sqlda
structure ( I know, not portable), but it should be working.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Fri, Sep 16, 2011 at 2:18 PM, Link, David A <DALink (AT) west (DOT) com> wrote:

On Fri, Sep 16, 2011 at 09:16, Link, David A <DALink (AT) west (DOT) com> wrote:


I don't see that there is anything you are doing wrong, so the problem is
likely in the server. Your expectation is reasonable; there is no obvious
reason why the description should not indicate the length 20. Of course,
Informix being the cooperative DBMS it is, it will accept character strings
of lengths longer (and shorter) than 20 and truncate or pad appropriately,
but it should still tell you the expected/usable length.

I suggest taking a simple reproduction (not much more complex than what
you've shown) to IBM Tech Support.

--
Jonathan Leffler <jonathan.leffler (AT) gmail (DOT) com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."