On Tue, Aug 15, 2006 at 11:55:58PM -0400, Jasbinder Bali wrote:
> I'm using ECPG and trying to do follwoing insert in one of the talbes.
>
> insert into raw_email ( id , raw_email ) values ( 1 , :ch);
>
> ch is an array and defined as follows:
>
> EXEC SQL BEGIN DECLARE SECTION;
> char ch[MSG_LEN];
> EXEC SQL END DECLARE SECTION;
>
> contents of array ch is actually a parsed raw email that i've attached
> herewith.
>
> I get the following error when the insert statement is hit:
>
> [2446]: ECPGexecute line 97: Error: ERROR: array value must start with "{"
> or dimension information.

What's the table definition for raw_email? Based on the error it
looks like one of the columns you're inserting is defined as an
array. I'm guessing you did something like this:

CREATE TABLE raw_email (
id integer,
raw_email char[]
);

When you should have done this:

CREATE TABLE raw_email (
id integer,
raw_email text -- or varchar
);

Is my guess correct?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

table definition of raw_email table is as follows

CREATE TABLE raw_email (
id int4 NOT NULL,
raw_email varchar[],
parsed_flag bool NOT NULL DEFAULT false,
CONSTRAINT pk_rawemail PRIMARY KEY (id)
)
WITHOUT OIDS;

i have very much declared raw_email field as varchar and not char

~Jas



On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Tue, Aug 15, 2006 at 11:55:58PM -0400, Jasbinder Bali wrote:
> > I'm using ECPG and trying to do follwoing insert in one of the talbes.
> >
> > insert into raw_email ( id , raw_email ) values ( 1 , :ch);
> >
> > ch is an array and defined as follows:
> >
> > EXEC SQL BEGIN DECLARE SECTION;
> > char ch[MSG_LEN];
> > EXEC SQL END DECLARE SECTION;
> >
> > contents of array ch is actually a parsed raw email that i've attached
> > herewith.
> >
> > I get the following error when the insert statement is hit:
> >
> > [2446]: ECPGexecute line 97: Error: ERROR: array value must start with
> "{"
> > or dimension information.
>
> What's the table definition for raw_email? Based on the error it
> looks like one of the columns you're inserting is defined as an
> array. I'm guessing you did something like this:
>
> CREATE TABLE raw_email (
> id integer,
> raw_email char[]
> );
>
> When you should have done this:
>
> CREATE TABLE raw_email (
> id integer,
> raw_email text -- or varchar
> );
>
> Is my guess correct?
>
> --
> Michael Fuhr
>

On Wed, Aug 16, 2006 at 12:38:42AM -0400, Jasbinder Bali wrote:
> table definition of raw_email table is as follows
>
> CREATE TABLE raw_email (
> id int4 NOT NULL,
> raw_email varchar[],
> parsed_flag bool NOT NULL DEFAULT false,
> CONSTRAINT pk_rawemail PRIMARY KEY (id)
> )
> WITHOUT OIDS;
>
> i have very much declared raw_email field as varchar and not char

It's not the varchar-vs-char distinction that matters; it's the
fact that raw_email is declared as array, so it expects a certain
input syntax. Example:

test=> INSERT INTO raw_email (id, raw_email) VALUES (1, 'test');
ERROR: array value must start with "{" or dimension information
test=> INSERT INTO raw_email (id, raw_email) VALUES (1, '{test}');
INSERT 0 1

Are you sure you want raw_email to be an array instead of a simple
varchar column? Are you handling the contents as array elements
(e.g., one element per line) or is the whole considered a single
piece of data?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

This is how the array is formed in my C code
-------------------------------------------------------------------------------------------
FILE *fp;

while(!feof(fp))
{ch[i]=fgetc(fp);
if(ch[i]=='\n') lines++; i++; }
ch[i-1]='\0';
fclose(fp);
------------------------------------------------------------------------------
and then am inserting ch as a whole in the varchar column in the database.

Do you want me to append a leading '{' and a trailing '}' to ch??

~Jas


On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Wed, Aug 16, 2006 at 12:38:42AM -0400, Jasbinder Bali wrote:
> > table definition of raw_email table is as follows
> >
> > CREATE TABLE raw_email (
> > id int4 NOT NULL,
> > raw_email varchar[],
> > parsed_flag bool NOT NULL DEFAULT false,
> > CONSTRAINT pk_rawemail PRIMARY KEY (id)
> > )
> > WITHOUT OIDS;
> >
> > i have very much declared raw_email field as varchar and not char
>
> It's not the varchar-vs-char distinction that matters; it's the
> fact that raw_email is declared as array, so it expects a certain
> input syntax. Example:
>
> test=> INSERT INTO raw_email (id, raw_email) VALUES (1, 'test');
> ERROR: array value must start with "{" or dimension information
> test=> INSERT INTO raw_email (id, raw_email) VALUES (1, '{test}');
> INSERT 0 1
>
> Are you sure you want raw_email to be an array instead of a simple
> varchar column? Are you handling the contents as array elements
> (e.g., one element per line) or is the whole considered a single
> piece of data?
>
> --
> Michael Fuhr
>

On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:
> This is how the array is formed in my C code
> -------------------------------------------------------------------------------------------
> FILE *fp;
>
> while(!feof(fp))
> {ch[i]=fgetc(fp);
> if(ch[i]=='\n') lines++; i++; }
> ch[i-1]='\0';
> fclose(fp);
> ------------------------------------------------------------------------------
> and then am inserting ch as a whole in the varchar column in the database.
>
> Do you want me to append a leading '{' and a trailing '}' to ch??

Do you intend to handle the data as an array in SQL? If not then
the raw_email column should be declared as varchar or text instead
of varchar[]. In that case your C code won't need to change.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

I changed the datatype from varchar[] to varchar
ECPGdebug(1,stderr) says
[2998]: ECPGexecute line 97 Ok: INSERT 0 1

Its not inserting any record even though sqlcode is 0.

~Jas


On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:
> > This is how the array is formed in my C code
> >
> -------------------------------------------------------------------------------------------
> > FILE *fp;
> >
> > while(!feof(fp))
> > {ch[i]=fgetc(fp);
> > if(ch[i]=='\n') lines++; i++; }
> > ch[i-1]='\0';
> > fclose(fp);
> >
> ------------------------------------------------------------------------------
> > and then am inserting ch as a whole in the varchar column in the
> database.
> >
> > Do you want me to append a leading '{' and a trailing '}' to ch??
>
> Do you intend to handle the data as an array in SQL? If not then
> the raw_email column should be declared as varchar or text instead
> of varchar[]. In that case your C code won't need to change.
>
> --
> Michael Fuhr
>

Also,
My insert statement is

EXEC SQL INSERT INTO raw_email (id,raw_email) VALUES (1,:ch);




On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:
> > This is how the array is formed in my C code
> >
> -------------------------------------------------------------------------------------------
> > FILE *fp;
> >
> > while(!feof(fp))
> > {ch[i]=fgetc(fp);
> > if(ch[i]=='\n') lines++; i++; }
> > ch[i-1]='\0';
> > fclose(fp);
> >
> ------------------------------------------------------------------------------
> > and then am inserting ch as a whole in the varchar column in the
> database.
> >
> > Do you want me to append a leading '{' and a trailing '}' to ch??
>
> Do you intend to handle the data as an array in SQL? If not then
> the raw_email column should be declared as varchar or text instead
> of varchar[]. In that case your C code won't need to change.
>
> --
> Michael Fuhr
>

On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:
> I changed the datatype from varchar[] to varchar
> ECPGdebug(1,stderr) says
> [2998]: ECPGexecute line 97 Ok: INSERT 0 1
>
> Its not inserting any record even though sqlcode is 0.

Are you committing the transaction? See the bottom of the
following page:

http://www.postgresql.org/docs/8.1/i...-commands.html

"In the default mode, statements are committed only when EXEC SQL
COMMIT is issued."

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend