Thread: [INTERFACES] my problems with ecpg and arrays
Hi, I'm having problems loading (insert into ..) a c array into a postgres
table.
I have tried all the variations on syntax I can think of, and searched for
the
answer... and either didn't find it or didn't recognize it..
SHORT version:
How do I replace {1,2...} with a c array,
exec sql insert into testtab (fname,fid,farr) values
(:fname,:fid,'{1,2,3,4,5,6,7,8,9}');
?
LONG,LONG version:
typos are mine (I have to retype from the system I'm running postgres :( )
I can do this in psql:
create type int2array
(input=array_in,output=array_out,internallength=variable,element=int2);
create table sampletab2 (id int4, narr int2array);
insert into sampletab2 (id,narr) values (12,'{3,2}');
and everything looks ok with both \d sampletab2 and select * from sampletab2
And this code works great:
#include <stdio.h>
#include <libpq-fe.h>
#include <math.h>
exec sql whenever sqlerror sqlprint;
exec sql include sqlca;
exec sql begin declare section; int fid; int fidarr[10]; varchar[40]; varchar sqlcom[80];
exec sql end declare section;
main(){ int ii; printf("Begin \n"); fid=5; for (ii=0;ii<=9;ii++) {fidarr[ii]=ii;printf("fidarr[%d]=%d
\n",ii,fidarr[ii]);}; fname.len=sprintf(fname.arr,"doodah day"); printf("fid equals : %d \n",fid); exec sql connect to
testdb1;exec sql create table testtab (fname varchar(40),fid int, fidarr int[10]); exec sql insert into testtab
(fname,fid,farr)values
(:fname,:fid,'{1,2,3,4,5,6,7,8,9}'); exec sql commit; exec sql disconnect; printf ("Done \n");
}
How do a do something like:
exec sql insert into testtab (fname,fid,farr) values (:fname,:fid,:fidarr);
??
Thanks (and sorry for the long post)
andrew.
On Mon, Nov 08, 1999 at 10:39:12AM -0600, Coleman, Andrew wrote: > I'm having problems loading (insert into ..) a c array into a postgres > table. > I have tried all the variations on syntax I can think of, and searched for > the > answer... and either didn't find it or didn't recognize it.. This certainly looks like a bug. I will take care of it if I find the time sometimes. Sorry, but I'm currently very busy. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On Mon, Nov 08, 1999 at 10:39:12AM -0600, Coleman, Andrew wrote: > I'm having problems loading (insert into ..) a c array into a postgres > table. > I have tried all the variations on syntax I can think of, and searched for > the > answer... and either didn't find it or didn't recognize it.. I'm afraid you find a real problem here. I just didn't think about inserting arrays. If you give ecpg an array of int it inserts every single value as one int. That means: insert into foo(bar) values(:intarray) is equal to: for (i=0;i<maxarray;i++)insert into foo(bar) values(:intarray[i]) How shall ecpg see that this time it is meant to insert the whole array? Anyone with an idea? Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On Mon, Nov 08, 1999 at 10:39:12AM -0600, Coleman, Andrew wrote: > I'm having problems loading (insert into ..) a c array into a postgres > table. > I have tried all the variations on syntax I can think of, and searched for > the > answer... and either didn't find it or didn't recognize it.. I'm afraid you find a real problem here. I just didn't think about inserting arrays. If you give ecpg an array of int it inserts every single value as one int. That means: insert into foo(bar) values(:intarray) is equal to: for (i=0;i<maxarray;i++)insert into foo(bar) values(:intarray[i]) How shall ecpg see that this time it is meant to insert the whole array? Anyone with an idea? Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
Thanks for looking into it. For the code I'm working on, it's no problem to work around (messy, but no problem). >I'm afraid you find a real problem here. I just didn't think about inserting >arrays. If you give ecpg an array of int it inserts every single value as >one int. That means: > >insert into foo(bar) values(:intarray) > >is equal to: > >for (i=0;i<maxarray;i++) > insert into foo(bar) values(:intarray[i]) >How shall ecpg see that this time it is meant to insert the whole array? > >Anyone with an idea? I'm guessing you're mostly directing that at people who are atleast vaguely familiar with the way pg and ecpg works internally ;) > >Michael Thanks again (both for looking into it and for ecpg itself), andrew.
On Mon, Nov 15, 1999 at 08:27:36AM -0600, Coleman, Andrew wrote: > Thanks for looking into it. For the code I'm working on, it's no problem to > work around (messy, but > no problem). Hopefully I find a better solution. :-) > I'm guessing you're mostly directing that at people who are atleast vaguely > familiar with the > way pg and ecpg works internally ;) Yes. :-) > Thanks again (both for looking into it and for ecpg itself), I'm glad it is actually used. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!