Thread: ecpg weird behavior

On Thu, Mar 07, 2002 at 10:14:41AM +1100, Nicolas Bazin wrote:
> I'm porting an application from INFORMIX to Postgresql and have found weird behaviour of ecpg:
> - when using cursors, the fetch statement needs "into ..." which does not comply with the SQL syntax,

I'm not sure what you are talking about. FETCH with INTO does not comply
to which standard? SQL3 for instance says:

<fetch statement> ::=   FETCH [ [ <fetch orientation> ] FROM ]     <cursor name> INTO <fetch target list>

If I omit the INTO how shall it know the variables?

> - a type definition needs to be written twice: the C  version and the SQL version. It is easy to modify the preproc
toonly have the SQL definition (I have done it and can submit a patch if anyone is interested),
 

Yes, I am. Please send it to me directly at meskes@postgresql.org.

> Also I have found a bug in the way the preproc  handles preproc conditions "EXEC SQL IFDEF ...". I have submitted the
patchto fix it to hackers but it looks like it's been ignored.
 

It seems you mail got delayed as it just arrived in my mailbox. But I
take it this is the patch you already send me a few days ago (after all
this mail I'm replying to was written March 7th). So it should be in CVS
already.

Michael

-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

----- Original Message -----
From: "Michael Meskes" <meskes@postgresql.org>
To: "Nicolas Bazin" <nbazin@ingenico.com.au>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Thursday, March 14, 2002 1:16 AM
Subject: Re: [INTERFACES] ecpg weird behavior


> On Thu, Mar 07, 2002 at 10:14:41AM +1100, Nicolas Bazin wrote:
> > I'm porting an application from INFORMIX to Postgresql and have found
weird behaviour of ecpg:
> > - when using cursors, the fetch statement needs "into ..." which does
not comply with the SQL syntax,
>
> I'm not sure what you are talking about. FETCH with INTO does not comply
> to which standard? SQL3 for instance says:
>
> <fetch statement> ::=
>     FETCH [ [ <fetch orientation> ] FROM ]
>       <cursor name> INTO <fetch target list>
>
> If I omit the INTO how shall it know the variables?
It will know the variables when the cursor is declared. Here is the syntax
that we currently use with INFORMIX and it also corresponds to the syntax in
the PostgreSQL documentation:

EXEC SQL DECLARE curs_currency CURSOR FOR   SELECT DISTINCT
FT_devises.dvs_devise,pays.pys_coddevalp,pays.pys_nbrdecimal   INTO :pays.pys_coddevnum, :pays.pys_coddevalp,
:pays.pys_nbrdecimal  FROM pays, FT_devises WHERE FT_devises.dvs_code = :stpe.tpe_profdevise   AND
FT_devises.dvs_devise= pays.pys_coddevnum;
 

EXEC SQL FETCH curs_currency;


>
> > - a type definition needs to be written twice: the C  version and the
SQL version. It is easy to modify the preproc to only have the SQL
definition (I have done it and can submit a patch if anyone is interested),
>
> Yes, I am. Please send it to me directly at meskes@postgresql.org.
This patch breaks backward compatibility. The idea was to change the output
of the preproc when it parses EXEC SQL type mytpe is ... from comment to the
proper typedef definition. Then the typedef doesn't have to be added
manually. If you still want it, I can send it.

>
> > Also I have found a bug in the way the preproc  handles preproc
conditions "EXEC SQL IFDEF ...". I have submitted the patch to fix it to
hackers but it looks like it's been ignored.
>
> It seems you mail got delayed as it just arrived in my mailbox. But I
> take it this is the patch you already send me a few days ago (after all
> this mail I'm replying to was written March 7th). So it should be in CVS
> already.
Exactly.

>
> Michael
>
> --
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
>

On Thu, Mar 14, 2002 at 05:16:41PM +1100, Nicolas Bazin wrote:
> It will know the variables when the cursor is declared. Here is the syntax
> that we currently use with INFORMIX and it also corresponds to the syntax in
> the PostgreSQL documentation:

Where did you find this in the PostgreSQL docs?

It certainly is not implemented.

> EXEC SQL DECLARE curs_currency CURSOR FOR
>     SELECT DISTINCT
> FT_devises.dvs_devise,pays.pys_coddevalp,pays.pys_nbrdecimal
>     INTO :pays.pys_coddevnum, :pays.pys_coddevalp, :pays.pys_nbrdecimal
>     FROM pays, FT_devises WHERE FT_devises.dvs_code = :stpe.tpe_profdevise
>     AND FT_devises.dvs_devise = pays.pys_coddevnum;
> 
> EXEC SQL FETCH curs_currency;

Anyone out there with more knowledge about standards? I thought this was
not standard at all.

> > Yes, I am. Please send it to me directly at meskes@postgresql.org.
> This patch breaks backward compatibility. The idea was to change the output
> of the preproc when it parses EXEC SQL type mytpe is ... from comment to the
> proper typedef definition. Then the typedef doesn't have to be added
> manually. If you still want it, I can send it.

I still want it. After all I can change the behaviour with a command
line option.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

----- Original Message -----
From: "Thomas Lockhart" <thomas@fourpalms.org>
To: "Michael Meskes" <meskes@postgresql.org>
Cc: "Nicolas Bazin" <nbazin@ingenico.com.au>;
<pgsql-interfaces@postgresql.org>
Sent: Saturday, March 16, 2002 4:11 AM
Subject: Re: [INTERFACES] ecpg weird behavior


> ...
> > > EXEC SQL FETCH curs_currency;
> > Anyone out there with more knowledge about standards? I thought this was
> > not standard at all.
>
> From what I can tell from my SQL99 maybe-docs we have all shared in the
> past on this list, the INTO is required to travel with the FETCH
> statement:
>
>          <dynamic fetch statement> ::=
>               FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor
> name>
>               <output using clause>
>
>          <output using clause> ::=
>                 <into arguments>
>               | <into descriptor>
>
>          <into arguments> ::=
>               INTO <into argument> [ { <comma> <into argument> }... ]
>
>          <into argument> ::= <target specification>
>
>          <into descriptor> ::=
>               INTO [ SQL ] DESCRIPTOR <descriptor name>
>
> hth
>
>                      - Thomas
>
> btw, I was working for the first time with ecpg and was very impressed.
> A company porting from Oracle ran into very few issues. Their existing
> code *did* have an Oracle extension to use a cursor to select multiple
> rows into variables declared as arrays. Have you run into this syntax or
> thought about what it would take to implement it?
>
Yes I also have this issue. From what I read in the ecpg doc, indexed
variables are not supported by ecpg and trying to compile the code definitle
proved it is not supported. The way I work arround it is to declare a local
variable of the same type, copy the array element into this variable,
perform the FECTH or SELECT and then copy the variable back into the array
element. I guess the preprocessor could also generate the code.
Another issue I had that ecpg does not support prepared statements. But I
didn't have that many prepared statements so I just rewrote the code.

----- Original Message -----
From: "Michael Meskes" <meskes@postgresql.org>
To: "Nicolas Bazin" <nbazin@ingenico.com.au>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Friday, March 15, 2002 7:21 PM
Subject: Re: [INTERFACES] ecpg weird behavior


> On Thu, Mar 14, 2002 at 05:16:41PM +1100, Nicolas Bazin wrote:
> > It will know the variables when the cursor is declared. Here is the
syntax
> > that we currently use with INFORMIX and it also corresponds to the
syntax in
> > the PostgreSQL documentation:
>
> Where did you find this in the PostgreSQL docs?
Ooops sorry I was refering to the documentation of the SQL commands in the
reference manual, but I guess it is not fully relevent here.

>
> It certainly is not implemented.
>
> > EXEC SQL DECLARE curs_currency CURSOR FOR
> >     SELECT DISTINCT
> > FT_devises.dvs_devise,pays.pys_coddevalp,pays.pys_nbrdecimal
> >     INTO :pays.pys_coddevnum, :pays.pys_coddevalp, :pays.pys_nbrdecimal
> >     FROM pays, FT_devises WHERE FT_devises.dvs_code =
:stpe.tpe_profdevise
> >     AND FT_devises.dvs_devise = pays.pys_coddevnum;
> >
> > EXEC SQL FETCH curs_currency;
>
> Anyone out there with more knowledge about standards? I thought this was
> not standard at all.
>
> > > Yes, I am. Please send it to me directly at meskes@postgresql.org.
> > This patch breaks backward compatibility. The idea was to change the
output
> > of the preproc when it parses EXEC SQL type mytpe is ... from comment to
the
> > proper typedef definition. Then the typedef doesn't have to be added
> > manually. If you still want it, I can send it.
>
> I still want it. After all I can change the behaviour with a command
> line option.

OK this patch includes the following (I hope it's in the right order this
time):
1.
EXEC SQL type mytype is .... does not need a typedef after.
2.
EXEC SQL begin declare section;
typedef struct {   int field1;
} mytype;
mytype var1;
EXEC SQL end declare sction;

I still have a problem when the grammar is parsed by bison. I get the
following message:
conflicts: 12 shift/reduce

As I told you before I'm not very familiar with yacc or bison so if you
could help me in finding what is the problem.

Nicolas BAZIN

>
> Michael
> --
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
>

On Mon, Mar 18, 2002 at 10:41:41AM +1100, Nicolas Bazin wrote:
> Yes I also have this issue. From what I read in the ecpg doc, indexed
> variables are not supported by ecpg and trying to compile the code definitle

Yes, there's an open TODO for that.

> proved it is not supported. The way I work arround it is to declare a local
> variable of the same type, copy the array element into this variable,
> perform the FECTH or SELECT and then copy the variable back into the array
> element. I guess the preprocessor could also generate the code.

It should be even easier. I think the parser could handle that directly.

> Another issue I had that ecpg does not support prepared statements. But I
> didn't have that many prepared statements so I just rewrote the code.

ECPG just simulates a prepare statement, but that should not qualify as
"not support". So what didn't work?

Michael

-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Fri, Mar 15, 2002 at 09:11:34AM -0800, Thomas Lockhart wrote:
> btw, I was working for the first time with ecpg and was very impressed.

Thanks.

> A company porting from Oracle ran into very few issues. Their existing
> code *did* have an Oracle extension to use a cursor to select multiple
> rows into variables declared as arrays. Have you run into this syntax or
> thought about what it would take to implement it?

Do you mean like this:

exec sql begin declare section;int amount[6];char name[6][8];
exec sql end declare section;
...
exec sql select * into :name, :amount from "Test";

This example btw is taken from test1.pgc under ecpg/test and thus is
fully supported.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

----- Original Message -----
From: "Michael Meskes" <meskes@postgresql.org>
To: "Thomas Lockhart" <thomas@fourpalms.org>
Cc: "Michael Meskes" <meskes@postgresql.org>; "Nicolas Bazin"
<nbazin@ingenico.com.au>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, March 20, 2002 6:16 AM
Subject: Re: [INTERFACES] ecpg weird behavior


> On Fri, Mar 15, 2002 at 09:11:34AM -0800, Thomas Lockhart wrote:
> > btw, I was working for the first time with ecpg and was very impressed.
>
> Thanks.
>
> > A company porting from Oracle ran into very few issues. Their existing
> > code *did* have an Oracle extension to use a cursor to select multiple
> > rows into variables declared as arrays. Have you run into this syntax or
> > thought about what it would take to implement it?
>
> Do you mean like this:
>
> exec sql begin declare section;
> int amount[6];
> char name[6][8];
> exec sql end declare section;
> ...
> exec sql select * into :name, :amount from "Test";
no what would work is
exec sql select * into :var1[i].name :var1[i].amount from "Test";

>
> This example btw is taken from test1.pgc under ecpg/test and thus is
> fully supported.
>
> Michael
> --
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
>

----- Original Message -----
From: "Michael Meskes" <meskes@postgresql.org>
To: "Nicolas Bazin" <nbazin@ingenico.com.au>
Cc: "Thomas Lockhart" <thomas@fourpalms.org>; "Michael Meskes"
<meskes@postgresql.org>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, March 20, 2002 6:18 AM
Subject: Re: [INTERFACES] ecpg weird behavior


> On Mon, Mar 18, 2002 at 10:41:41AM +1100, Nicolas Bazin wrote:
> > Yes I also have this issue. From what I read in the ecpg doc, indexed
> > variables are not supported by ecpg and trying to compile the code
definitle
>
> Yes, there's an open TODO for that.
>
> > proved it is not supported. The way I work arround it is to declare a
local
> > variable of the same type, copy the array element into this variable,
> > perform the FECTH or SELECT and then copy the variable back into the
array
> > element. I guess the preprocessor could also generate the code.
>
> It should be even easier. I think the parser could handle that directly.
>
> > Another issue I had that ecpg does not support prepared statements. But
I
> > didn't have that many prepared statements so I just rewrote the code.
>
> ECPG just simulates a prepare statement, but that should not qualify as
> "not support". So what didn't work?

EXEC SQL PREPARE statement FROM :requete;
EXEC SQL DECLARE curs_cartlstnoire CURSOR FOR statement;
didn't get preprocessed.

>
> Michael
>
> --
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
>

Why is an ARRAY type not supported by SQL as a columnar definition as an
extension to each data element?

Would make life soooo much easier.

--Hal.

===========================================================
Hal Davison                 Internet Petroleum Distribution
Davison Consulting                LSE Linux V1.22
6850 Myakka Valley Tr    PostgreSQL 7.03 - Sun Forte - JAVA
Sarasota, Florida 34241         Phone: (941) 921-6578
http://www.faams.net              FAX: (941) 924-7135
===========================================================

I don't see this e-mail in the thread so I resend it with the attached file
in the text.
Beware that this is not a pacth to apply. Just that I have reach the limit
of my knowledge on bison and can't find out what's wrong (conflicts: 12
shift/reduce). If someone can give a quick look then we will have a better
type definition syntax with ecpg.
*** postgresql-7.2.cvs/src/interfaces/ecpg/preproc/preproc.y Mon Mar 18
13:52:48 2002

--- postgresql-7.2/src/interfaces/ecpg/preproc/preproc.y Mon Mar 18 14:34:31
2002

***************

*** 169,174 ****

--- 169,175 ----

S_DOTPOINT S_EQUAL S_EXTERN S_INC S_LSHIFT S_MEMPOINT

S_MEMBER S_MOD S_MUL S_NEQUAL S_OR S_REGISTER S_RSHIFT

S_STATIC S_SUB S_VOLATILE

+ S_TYPEDEF


/* I need this and don't know where it is defined inside the backend */

%token TYPECAST

***************

*** 360,365 ****

--- 361,367 ----

%type <str> enum_type civar civarind ECPGCursorStmt ECPGDeallocate

%type <str> ECPGFree ECPGDeclare ECPGVar opt_at enum_definition

%type <str> struct_type s_struct declaration declarations
variable_declarations

+ %type <str> var_declaration type_declaration

%type <str> s_union union_type ECPGSetAutocommit on_off

%type <str> ECPGAllocateDescr ECPGDeallocateDescr symbol opt_symbol

%type <str> ECPGGetDescriptorHeader ECPGColLabel

***************

*** 3672,3678 ****

| declarations declaration { $$ = cat2_str($1, $2); }

;


! declaration: storage_clause storage_modifier

{

actual_storage[struct_level] = cat2_str(mm_strdup($1), mm_strdup($2));

actual_startline[struct_level] = hashline_number();

--- 3674,3749 ----

| declarations declaration { $$ = cat2_str($1, $2); }

;


! declaration: type_declaration { $$ = $1; }

! | var_declaration { $$ = $1; };

!

! type_declaration: S_TYPEDEF

! {

! /* reset this variable so we see if there was */

! /* an initializer specified */

! initializer = 0;

! }

! type opt_type_array_bounds opt_reference ColLabel ';'

! {

! /* add entry to list */

! struct typedefs *ptr, *this;

! int dimension = $4.index1;

! int length = $4.index2;

!

! if (($3.type_enum == ECPGt_struct ||

! $3.type_enum == ECPGt_union) &&

! initializer == 1)

! {

! mmerror(PARSE_ERROR, ET_ERROR, "Initializer not allowed in EXEC SQL VAR
command");

!

! }

! else

! {

! for (ptr = types; ptr != NULL; ptr = ptr->next)

!

!

!

!

!

!

! {

! if (strcmp($6, ptr->name) == 0)

! {

! /* re-definition is a bug */

! sprintf(errortext, "Type %s already defined", $6);

! mmerror(PARSE_ERROR, ET_ERROR, errortext);

! }

! }

!

! adjust_array($3.type_enum, &dimension, &length, $3.type_dimension,
$3.type_index, *$5?1:0);

!

! this = (struct typedefs *) mm_alloc(sizeof(struct typedefs));

!

! /* initial definition */

! this->next = types;

! this->name = $6;

! this->type = (struct this_type *) mm_alloc(sizeof(struct this_type));

! this->type->type_enum = $3.type_enum;

! this->type->type_str = mm_strdup($6);

! this->type->type_dimension = dimension; /* dimension of array */

! this->type->type_index = length; /* lenght of string */

! this->struct_member_list = ($3.type_enum == ECPGt_struct || $3.type_enum
== ECPGt_union) ?

! struct_member_list[struct_level] : NULL;

!

! if ($3.type_enum != ECPGt_varchar &&

! $3.type_enum != ECPGt_char &&

! $3.type_enum != ECPGt_unsigned_char &&

! this->type->type_index >= 0)

! mmerror(PARSE_ERROR, ET_ERROR, "No multi-dimensional array support for
simple data types");

!

! types = this;

! }

!

!

! $$ = cat_str(6, make_str("typedef "), mm_strdup($3.type_str),
mm_strdup($4.str), $5, mm_strdup($6), make_str(";"));

! };

!

! var_declaration: storage_clause storage_modifier

{

actual_storage[struct_level] = cat2_str(mm_strdup($1), mm_strdup($2));

actual_startline[struct_level] = hashline_number();

***************

*** 4260,4266 ****

types = this;

}


! $$ = cat_str(7, make_str("/* exec sql type"), mm_strdup($3),
make_str("is"), mm_strdup($5.type_str), mm_strdup($6.str), $7,
make_str("*/"));

}

;


--- 4331,4338 ----

types = this;

}


! // $$ = cat_str(7, make_str("/* exec sql type"), mm_strdup($3),
make_str("is"), mm_strdup($5.type_str), mm_strdup($6.str), $7,
make_str("*/"));

! $$ = cat_str(6, make_str("typedef "), mm_strdup($5.type_str),
mm_strdup($6.str), $7, mm_strdup($3), make_str(";"));

}

;


*** postgresql-7.2.cvs/src/interfaces/ecpg/preproc/c_keywords.c Mon Mar 18
13:52:48 2002

--- postgresql-7.2/src/interfaces/ecpg/preproc/c_keywords.c Mon Mar 18
14:00:41 2002

***************

*** 36,41 ****

--- 36,42 ----

{"signed", SQL_SIGNED},

{"static", S_STATIC},

{"struct", SQL_STRUCT},

+ {"typedef", S_TYPEDEF},

{"union", UNION},

{"unsigned", SQL_UNSIGNED},

{"varchar", VARCHAR},



----- Original Message -----
From: "Nicolas Bazin" <nbazin@ingenico.com.au>
To: "Michael Meskes" <meskes@postgresql.org>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Monday, March 18, 2002 3:34 PM
Subject: Re: [INTERFACES] ecpg weird behavior


>
> ----- Original Message -----
> From: "Michael Meskes" <meskes@postgresql.org>
> To: "Nicolas Bazin" <nbazin@ingenico.com.au>
> Cc: <pgsql-interfaces@postgresql.org>
> Sent: Friday, March 15, 2002 7:21 PM
> Subject: Re: [INTERFACES] ecpg weird behavior
>
>
> > On Thu, Mar 14, 2002 at 05:16:41PM +1100, Nicolas Bazin wrote:
> > > It will know the variables when the cursor is declared. Here is the
> syntax
> > > that we currently use with INFORMIX and it also corresponds to the
> syntax in
> > > the PostgreSQL documentation:
> >
> > Where did you find this in the PostgreSQL docs?
> Ooops sorry I was refering to the documentation of the SQL commands in the
> reference manual, but I guess it is not fully relevent here.
>
> >
> > It certainly is not implemented.
> >
> > > EXEC SQL DECLARE curs_currency CURSOR FOR
> > >     SELECT DISTINCT
> > > FT_devises.dvs_devise,pays.pys_coddevalp,pays.pys_nbrdecimal
> > >     INTO :pays.pys_coddevnum, :pays.pys_coddevalp,
:pays.pys_nbrdecimal
> > >     FROM pays, FT_devises WHERE FT_devises.dvs_code =
> :stpe.tpe_profdevise
> > >     AND FT_devises.dvs_devise = pays.pys_coddevnum;
> > >
> > > EXEC SQL FETCH curs_currency;
> >
> > Anyone out there with more knowledge about standards? I thought this was
> > not standard at all.
> >
> > > > Yes, I am. Please send it to me directly at meskes@postgresql.org.
> > > This patch breaks backward compatibility. The idea was to change the
> output
> > > of the preproc when it parses EXEC SQL type mytpe is ... from comment
to
> the
> > > proper typedef definition. Then the typedef doesn't have to be added
> > > manually. If you still want it, I can send it.
> >
> > I still want it. After all I can change the behaviour with a command
> > line option.
>
> OK this patch includes the following (I hope it's in the right order this
> time):
> 1.
> EXEC SQL type mytype is .... does not need a typedef after.
> 2.
> EXEC SQL begin declare section;
> typedef struct {
>     int field1;
> } mytype;
> mytype var1;
> EXEC SQL end declare sction;
>
> I still have a problem when the grammar is parsed by bison. I get the
> following message:
> conflicts: 12 shift/reduce
>
> As I told you before I'm not very familiar with yacc or bison so if you
> could help me in finding what is the problem.
>
> Nicolas BAZIN
>
> >
> > Michael
> > --
> > Michael Meskes
> > Michael@Fam-Meskes.De
> > Go SF 49ers! Go Rhein Fire!
> > Use Debian GNU/Linux! Use PostgreSQL!
> >
>
>
>

...
> > EXEC SQL FETCH curs_currency;
> Anyone out there with more knowledge about standards? I thought this was
> not standard at all.

From what I can tell from my SQL99 maybe-docs we have all shared in the
past on this list, the INTO is required to travel with the FETCH
statement:
        <dynamic fetch statement> ::=             FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor
name>             <output using clause>
        <output using clause> ::=               <into arguments>             | <into descriptor>
        <into arguments> ::=             INTO <into argument> [ { <comma> <into argument> }... ]
        <into argument> ::= <target specification>
        <into descriptor> ::=             INTO [ SQL ] DESCRIPTOR <descriptor name>

hth
                    - Thomas

btw, I was working for the first time with ecpg and was very impressed.
A company porting from Oracle ran into very few issues. Their existing
code *did* have an Oracle extension to use a cursor to select multiple
rows into variables declared as arrays. Have you run into this syntax or
thought about what it would take to implement it?

On Mon, Mar 18, 2002 at 03:34:56PM +1100, Nicolas Bazin wrote:
> EXEC SQL type mytype is .... does not need a typedef after.

This works mostly but still has some SQL syntax in it:
typedef str reference c

I hope to find all this syntax problems.

> I still have a problem when the grammar is parsed by bison. I get the
> following message:
> conflicts: 12 shift/reduce

I will take care of this although I haven't yet found the reason.

Michael

-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Wed, Mar 20, 2002 at 11:15:14AM +1100, Nicolas Bazin wrote:
> EXEC SQL PREPARE statement FROM :requete;
> EXEC SQL DECLARE curs_cartlstnoire CURSOR FOR statement;
> didn't get preprocessed.

From my current test sources (which will be committed in a few minutes):

sprintf (command, "select * from \"Test\"");

exec sql prepare F from :command;
exec sql declare CUR cursor for F;

strcpy(msg, "open");
exec sql open CUR;

strcpy(msg, "fetch");
exec sql fetch :how_many in CUR into :name, :amount, :letter;
...

Works like a charm with 7.2. I have no idea why yours didn't get preprocessed.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Wed, Mar 20, 2002 at 10:56:39AM +1100, Nicolas Bazin wrote:
> no what would work is
> exec sql select * into :var1[i].name :var1[i].amount from "Test";

I take it you mean this does NOT work. :-)

Yes, I know and this is on my TODO list for several years now, I just
never starting tackling it.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Tue, Mar 19, 2002 at 07:46:04PM -0800, Thomas Lockhart wrote:
> No (although I was not aware that the above would work). The example
> looks like
> 
> exec sql begin declare section;
>   char *name_arr[10];
> exec sql end declare section;
> exec sql declare names cursor for
>   select name from horses;
> strcpy(msg, "open");
> exec sql open names;
> exec sql fetch names into :name_arr;
> exec sql close names;

This would mean the fetch knows how many tuples to return without you
telling it. 

How about:

...
exec sql open names;
exec sql fetch 10 in names into :name_arr;
exec sql close names;
...

The biggest plus of this way to write the statement is that it works
with ecpg. :-)

I just added this to my test cases. However, I didn't have to change
anything in ecpg sources.

>   Each FETCH returns, at most, the number of rows in the array
> dimension.

This has to be added. In fact ECPG has to translate the simple fetch
into a fetch <how_many> ... since otherwise the backend wouldn't return
more than one tuple.

>   Fewer rows are returned in the following cases: <snip cases>
>   The cumulative number of rows returned can be found in the third
> element
>    of sqlerrd in the SQLCA, called sqlerrd[2] in this guide.

This works with ECPG as well.

> I'm guessing that this is a relatively short hop from your existing
> array capabilities, but don't how close. What do you think?

Quite easy I guess. Needs to be added to the TODO list. :-)

Michael

-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Tue, Mar 19, 2002 at 07:39:27PM -0500, Hal Davison wrote:
> Why is an ARRAY type not supported by SQL as a columnar definition as an
> extension to each data element?

Sorry, I didn't exactly understand what you mean. Where do you want to
use an array?

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

----- Original Message -----
From: "Michael Meskes" <meskes@postgresql.org>
To: "Nicolas Bazin" <nbazin@ingenico.com.au>
Cc: "Thomas Lockhart" <thomas@fourpalms.org>;
<pgsql-interfaces@postgresql.org>
Sent: Thursday, March 21, 2002 7:17 AM
Subject: Re: [INTERFACES] ecpg weird behavior


> On Wed, Mar 20, 2002 at 10:56:39AM +1100, Nicolas Bazin wrote:
> > no what would work is
> > exec sql select * into :var1[i].name :var1[i].amount from "Test";
>
> I take it you mean this does NOT work. :-)

That's right it oesn't work.

>
> Yes, I know and this is on my TODO list for several years now, I just
> never starting tackling it.
>
> Michael
> --
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
>

----- Original Message ----- 
From: "Michael Meskes" <meskes@postgresql.org>
To: "Nicolas Bazin" <nbazin@ingenico.com.au>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Thursday, March 21, 2002 7:59 AM
Subject: Re: [INTERFACES] ecpg weird behavior


> On Mon, Mar 18, 2002 at 03:34:56PM +1100, Nicolas Bazin wrote:
> > EXEC SQL type mytype is .... does not need a typedef after.
> 
> This works mostly but still has some SQL syntax in it:
> typedef str reference c
> 
> I hope to find all this syntax problems.
> 
> > I still have a problem when the grammar is parsed by bison. I get the
> > following message:
> > conflicts: 12 shift/reduce
> 
> I will take care of this although I haven't yet found the reason.
> 
Thanks. Can you keep me updated when you commit the modifications?

> Michael
> 
> -- 
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
> 

On Thu, Mar 21, 2002 at 10:21:08AM +1100, Nicolas Bazin wrote:
> Thanks. Can you keep me updated when you commit the modifications?

Just committed.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

When designing a database structure for example MTD_sales. It would be
nice to have this column designated as a decimal with an extension of 13
elements effectivally creating:  MTD_sales[1]...MTD_sales[13]


--Hal.


===========================================================
Hal Davison                 Internet Petroleum Distribution
Davison Consulting                LSE Linux V1.22
6850 Myakka Valley Tr    PostgreSQL 7.03 - Sun Forte - JAVA
Sarasota, Florida 34241         Phone: (941) 921-6578
http://www.faams.net              FAX: (941) 924-7135
===========================================================

On Wed, 20 Mar 2002, Michael Meskes wrote:

> On Tue, Mar 19, 2002 at 07:39:27PM -0500, Hal Davison wrote:
> > Why is an ARRAY type not supported by SQL as a columnar definition as an
> > extension to each data element?
> 
> Sorry, I didn't exactly understand what you mean. Where do you want to
> use an array?
> 
> Michael
> -- 
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
> 

On Thu, Mar 21, 2002 at 10:34:46AM -0500, Hal Davison wrote:
> 
> When designing a database structure for example MTD_sales. It would be
> nice to have this column designated as a decimal with an extension of 13
> elements effectivally creating:

Not sure if this is my fault, but I still do not understand what you
want to do. Sorry.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

> > ... have an Oracle extension to use a cursor to select multiple
> > rows into variables declared as arrays. Have you run into this syntax or
> > thought about what it would take to implement it?
> Do you mean like this:
> exec sql begin declare section;
>         int amount[6];
>         char name[6][8];
> exec sql end declare section;
> ...
> exec sql select * into :name, :amount from "Test";

No (although I was not aware that the above would work). The example
looks like

exec sql begin declare section; char *name_arr[10];
exec sql end declare section;
exec sql declare names cursor for select name from horses;
strcpy(msg, "open");
exec sql open names;
exec sql fetch names into :name_arr;
exec sql close names;

So the syntax uses a cursor fetching into an array, rather than a
"select into". A couple of details on behavior from the Oracle docs:
 Each FETCH returns, at most, the number of rows in the array
dimension. Fewer rows are returned in the following cases: <snip cases> The cumulative number of rows returned can be
foundin the third
 
element  of sqlerrd in the SQLCA, called sqlerrd[2] in this guide.

The Oracle docs at the following URL are consistant with the examples I
was seeing:
 http://www-rohan.sdsu.edu/doc/oracle/server803/A54661_01/arr.htm#512

I'm guessing that this is a relatively short hop from your existing
array capabilities, but don't how close. What do you think?
                    - Thomas