DB insert Error

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

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

Show quoted text

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

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

Show quoted text

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

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

Show quoted text

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

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

Show quoted text

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);

Show quoted text

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/interactive/ecpg-commands.html

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

--
Michael Fuhr

Its actually something like the trigger should start a C function after
insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing
a stored procedure, as in , executing a fucntion in C file using stored
procedure/ function.

Harpreet

Show quoted text

On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:

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/interactive/ecpg-commands.html

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

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Forgot to write that that was my question.
I mean can we call a stored procedure as an action of a trigger?

Show quoted text

On 8/16/06, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:

Its actually something like the trigger should start a C function after
insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while
executing a stored procedure, as in , executing a fucntion in C file using
stored procedure/ function.

Harpreet

On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:

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/interactive/ecpg-commands.html

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

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

I did commit the transaction by writing
EXEC SQL COMMIT; right after the insert statement.
Then also its not inserting the record and says:
[2998]: ECPGexecute line 97 Ok: INSERT 0 1 and SQLCODE is still 0
and SQLCODE is still 0

~Jas

Show quoted text

On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:

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/interactive/ecpg-commands.html

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

--
Michael Fuhr

Also, i tried to mannualy run the insert query in the query tool

------------------------------------------------------------

insert into raw_email ( id , raw_email ) values ( 1 , 'From
simon@simon-cozens.org Tue Apr 15 20:24:47 2003
X-MultiHeader: one
X-MultiHeader: two
X-MultiHeader: three
From: Simon Cozens <simon@simon-cozens.org>
To: test
Bcc: simon@twingle.net
Subject: foo
Mime-Version: 1.0
Content-Type: image/gif
Content-Disposition: attachment; filename="1.gif"
Content-Transfer-Encoding: base64
X-Operating-System: Linux deep-dark-truthful-mirror 2.4.9
X-POM: The Moon is Waxing Gibbous (98% of Full)
X-Addresses: The simon@cozens.net address is deprecated due to being broken.
simon@brecon.co.uk still works, but simon-cozens.org or netthink.co.uk are
preferred.
X-Mutt-Fcc: =outbox-200304
Status: RO
Content-Length: 1205
Lines: 17

R0lGODlhDAAMAPcAAAAAAAgICBAQEBgYGCkpKTExMTk5OUpKSoyMjJSUlJycnKWlpbW1tc7O
zufn5+/v7/f39///////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
/////////////////////////////////ywAAAAADAAMAAAIXwAjRICQwIAAAQYUQBAYwUEB
AAACEIBYwMHAhxARNIAIoAAEBBAPOICwkSMCjBAXlKQYgCMABSsjtuQI02UAlC9jFgBJMyYC
CCgRMODoseFElx0tCvxYIEAAAwkWRggIADs=

' )

------------------------------------------------------------

But it doesn't store the full raw_email field contents

raw_email field has just the follwing stored in it

-----------------------------------------------
"From simon@simon-cozens.org Tue Apr 15 20:24:47 2003
X-MultiHeader: one
X-MultiHeader: two
X-MultiHeader: three
From: Simon Cozens <simon@simon-cozens.org>
To: test
Bcc: simon@twingle.net
Subject: foo
Mime-Version: 1.0
Content-Type: image/gif
Content-Dispo (..)"
-----------------------------------------------

Also, as i've already told, even after i commit my transaction, it doesn't
insert the record.

Thanks,
~Jas

Show quoted text

On 8/16/06, Jasbinder Bali <jsbali@gmail.com> wrote:

I did commit the transaction by writing
EXEC SQL COMMIT; right after the insert statement.

Then also its not inserting the record and says:
[2998]: ECPGexecute line 97 Ok: INSERT 0 1
and SQLCODE is still 0

~Jas

On 8/1/06, Michael Fuhr <mike@fuhr.org> wrote:

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/interactive/ecpg-commands.html

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

--
Michael Fuhr

Harpreet Dhaliwal wrote:

Forgot to write that that was my question.
I mean can we call a stored procedure as an action of a trigger?

Sure, here's a working example from a running application:

CREATE OR REPLACE FUNCTION assign_detail_device_type_seq() RETURNS
trigger AS $$
BEGIN
IF NEW.det_device_type_id is NULL THEN
NEW.det_device_type_id := (SELECT
NEXTVAL('rumba.det_device_type_seq'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tDetDevType
BEFORE INSERT
ON rumba.DETAIL_DEVICE_TYPE
FOR EACH ROW
EXECUTE PROCEDURE assign_detail_device_type_seq();

On 8/16/06, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
Its actually something like the trigger should start a C function
after insert and the C function has the ECPG code for some more
inserts.
Its similar to the way we dynamically load a shared library while
executing a stored procedure, as in , executing a fucntion in C file
using stored procedure/ function.

Harpreet

On 8/16/06, Michael Fuhr <mike@fuhr.org > wrote:
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/interactive/ecpg-commands.html

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

--
Guy Rouillier

On Wed, Aug 16, 2006 at 02:59:23AM -0400, Harpreet Dhaliwal wrote:

I mean can we call a stored procedure as an action of a trigger?

You'll need to write a trigger function that calls the non-trigger
function (what you're referring to as "stored procedure"). Example:

CREATE FUNCTION trigger_function() RETURNS trigger AS $$
BEGIN
PERFORM other_function();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE ON table_name
FOR EACH ROW EXECUTE PROCEDURE trigger_function();

Its actually something like the trigger should start a C function after
insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while
executing a stored procedure, as in , executing a fucntion in C file using
stored procedure/ function.

You have a server-side C function that uses ECPG? Is there a reason
you're not using SPI? Are you connecting to a different server?
Or by "function" do you really mean "program," meaning a separate
executable?

http://www.postgresql.org/docs/8.1/interactive/spi.html

--
Michael Fuhr

Tried everthing but nothing seems to work.
:(

Show quoted text

On 8/16/06, Jasbinder Bali <jsbali@gmail.com> wrote:

Also, i tried to mannualy run the insert query in the query tool

------------------------------------------------------------

insert into raw_email ( id , raw_email ) values ( 1 , 'From
simon@simon-cozens.org Tue Apr 15 20:24:47 2003
X-MultiHeader: one
X-MultiHeader: two
X-MultiHeader: three
From: Simon Cozens <simon@simon-cozens.org>
To: test
Bcc: simon@twingle.net
Subject: foo
Mime-Version: 1.0
Content-Type: image/gif
Content-Disposition: attachment; filename="1.gif"
Content-Transfer-Encoding: base64
X-Operating-System: Linux deep-dark-truthful-mirror 2.4.9
X-POM: The Moon is Waxing Gibbous (98% of Full)
X-Addresses: The simon@cozens.net address is deprecated due to being
broken. simon@brecon.co.uk still works, but simon-cozens.org or
netthink.co.uk are preferred.
X-Mutt-Fcc: =outbox-200304
Status: RO
Content-Length: 1205
Lines: 17

R0lGODlhDAAMAPcAAAAAAAgICBAQEBgYGCkpKTExMTk5OUpKSoyMjJSUlJycnKWlpbW1tc7O
zufn5+/v7/f39///////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
/////////////////////////////////ywAAAAADAAMAAAIXwAjRICQwIAAAQYUQBAYwUEB
AAACEIBYwMHAhxARNIAIoAAEBBAPOICwkSMCjBAXlKQYgCMABSsjtuQI02UAlC9jFgBJMyYC
CCgRMODoseFElx0tCvxYIEAAAwkWRggIADs=

' )

------------------------------------------------------------

But it doesn't store the full raw_email field contents

raw_email field has just the follwing stored in it

-----------------------------------------------
"From simon@simon-cozens.org Tue Apr 15 20:24:47 2003
X-MultiHeader: one
X-MultiHeader: two
X-MultiHeader: three
From: Simon Cozens <simon@simon-cozens.org>
To: test
Bcc: simon@twingle.net
Subject: foo
Mime-Version: 1.0
Content-Type: image/gif
Content-Dispo (..)"
-----------------------------------------------

Also, as i've already told, even after i commit my transaction, it doesn't
insert the record.

Thanks,
~Jas

On 8/16/06, Jasbinder Bali <jsbali@gmail.com> wrote:

I did commit the transaction by writing

EXEC SQL COMMIT; right after the insert statement.

Then also its not inserting the record and says:
[2998]: ECPGexecute line 97 Ok: INSERT 0 1
and SQLCODE is still 0

~Jas

On 8/1/06, Michael Fuhr < mike@fuhr.org> wrote:

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/interactive/ecpg-commands.html

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

--
Michael Fuhr

On Wed, Aug 16, 2006 at 02:42:46PM -0400, Jasbinder Bali wrote:

Tried everthing but nothing seems to work.
:(

Could you provide a simplified but complete example that shows what
you're doing? That is, all SQL statements and C code necessary to
create a table and whatever functions you're using. I've done a
little testing and haven't been able to reproduce your results, so
apparently my tests don't match what you're doing.

--
Michael Fuhr

I'm sending u the main() function that has the ECPG Insert statement and
commit in it.

-------------------------------------------START-----------------------------------------------------------
#include <stdio.h>
#include <ctype.h>
#include <string.h>
#include <stdlib.h>

EXEC SQL INCLUDE sqlca;

#define MP 10
#define MSG_LEN 1024000
#define FLD_LEN 512

char *msg_parts[MP], *msg_header[MP], *msg_body[MP];
char headers[MSG_LEN]="", body[MSG_LEN]="";
char mime_version[FLD_LEN]="", content_type[FLD_LEN]="",
content_id[FLD_LEN]="";
char content_description[FLD_LEN]="", content_transfer_encoding[FLD_LEN]="";
char content_length[FLD_LEN]="",content_disposition[FLD_LEN]="";
int part_num=0;
int pnum=0;

char *dst;

int main(int argc, char *argv[])
{

ECPGdebug (1, stderr);

EXEC SQL CONNECT TO tcp:postgresql://192.168.0.110/xyz USER jsbali;

printf("sql code is %d \n",SQLCODE);
void split_head_from_body(char *ch, char *o_headers, char *o_body);
void limit_fields(char *instr, char *outstr, char *fieldname);
void unlimit_fields(char *instr, char *outstr, char *fieldname);
void parts(char *intype, char *bdy, char *inbody, int pcount);

FILE *fp;
EXEC SQL BEGIN DECLARE SECTION;
char ch[MSG_LEN];
EXEC SQL END DECLARE SECTION;

int i,j;
int lines;
unsigned int len;
char from[FLD_LEN]="", to[FLD_LEN]="", subject[FLD_LEN]="",
date[FLD_LEN]="";
char mesid[FLD_LEN]="", sender[FLD_LEN]="", cc[FLD_LEN]="",
bcc[FLD_LEN]="";
char replyto[FLD_LEN]="", inreplyto[FLD_LEN]="", ref[FLD_LEN]="";
char rec[FLD_LEN]="", comments[FLD_LEN]="", keywords[FLD_LEN]="",
return_path[FLD_LEN]="";
char xapp[FLD_LEN]="";
char resent_date[FLD_LEN]="", resent_from[FLD_LEN]="",
resent_sender[FLD_LEN]="", resent_to[FLD_LEN]="", resent_cc[FLD_LEN]="",
resent_bcc[FLD_LEN]="", resent_mid[FLD_LEN]="";
char boundary[FLD_LEN]="--";
char *str, *pstr;

if(argc!=2)
{printf("You forgot to enter a filename\n");
exit(0);
}

if((fp=fopen(argv[1],"r"))==NULL)
{printf("cannot open the file\n");
exit(0);
}

/* read the email from a file */

lines=0;
while(!feof(fp))
{ch[i]=fgetc(fp);
if(ch[i]=='\n') lines++;
i++;
}
ch[i-1]='\0';
fclose(fp);
len=strlen(ch);

//815 printf("--------------------- Content of the Raw
email---------------\n");
//815 printf("%s\n",ch);

//815 printf("--------------------- the end of the content
----------------\n");

//EXEC SQL SELECT MAX(case_no) INTO :caseno FROM raw_email;

EXEC SQL INSERT INTO raw_email (id,raw_email) VALUES (1,:ch);
EXEC SQL COMMIT;
printf("sql code is %d \n",SQLCODE);

// printf("-----There are %d lines in the email.-----\n", lines);
// printf("-----The character length is %d -----\n", len);

/* seperate the header and the message body */

split_head_from_body(ch,headers,body);

// printf("-----------------------------------------\n");
// printf("------The header of the email:--------\n");
// printf("%s\n",headers);
// printf("-----------------------------------------\n");

// printf("-----------------------------------------\n");
// printf("------The body of the email:--------\n");
// printf("%s\n",body);
// printf("-----------------------------------------\n");

/* all the fields in an email header */
/* Bcc, Cc, Comments, Content-Type, */
/* Content-Transfer-Encoding, Content-Disposition, */
/* Date, From, Keywords, Message-ID, MIME-Version, */
/* Organizaion, Received, Reference, Reply-To, */
/* Resent-From, Resent-To, Return-Path, Sender, */
/* Subject, To, X-*** */

/* Now, get the fields in the headers */
/* for limited fields */
/* from, sender, reply-to, to, cc, bcc, */
/* message-id, in-reply-to, reference, */
/* subject */

limit_fields(headers,from,"From: ");
limit_fields(headers,to,"To: ");
limit_fields(headers,subject,"Subject: ");
limit_fields(headers,date,"Date: ");
limit_fields(headers,mesid,"Message-ID: ");
limit_fields(headers,sender,"Sender: ");
limit_fields(headers,cc,"Cc: ");
limit_fields(headers,bcc,"Bcc: ");
limit_fields(headers,replyto,"Reply-To: ");
limit_fields(headers,inreplyto,"In-Reply-To: ");
limit_fields(headers,ref,"Reference: ");

/* new for MIME */
/* Mime-Version, Content-Type, */
/* Content-ID, Content-Description, */
/* Content-Transfer-Encoding, Content-Length, */
/* Content-Disposition */

limit_fields(headers,mime_version,"MIME-Version: ");
limit_fields(headers,content_type,"Content-Type: ");

limit_fields(headers,content_transfer_encoding,"Content-Transfer-Encoding:
");
limit_fields(headers,content_id,"Content-ID: ");
limit_fields(headers,content_description,"Content-Description: ");
limit_fields(headers,content_length,"Content-Length: ");
limit_fields(headers,content_disposition,"Content-Disposition: ");

/* for unlimited fields */
/* (unlimited) */
/* comments, keywords, */
/* Return-Path, Received, */
/* Resent-Date, Resent-From, */
/* Resent-Sender, Resent-To, */
/* Resent-Cc, Resent-Bcc, */
/* Resent_Message-ID, X-*** */

unlimit_fields(headers,rec,"Received: ");
unlimit_fields(headers,comments,"Comments: ");
unlimit_fields(headers,keywords,"Keywords: ");

unlimit_fields(headers,return_path,"Return-Path: ");

unlimit_fields(headers,resent_date,"Resent-Date: ");
unlimit_fields(headers,resent_from,"Resent-From: ");
unlimit_fields(headers,resent_to,"Resent-To: ");
unlimit_fields(headers,resent_sender,"Resent-Sender: ");
unlimit_fields(headers,resent_cc,"Resent-Cc: ");
unlimit_fields(headers,resent_bcc,"Resent-Bcc: ");
unlimit_fields(headers,resent_mid,"Resent-Msg-Id: ");
unlimit_fields(headers,xapp,"X-");

/* find out the boundary */
parts(content_type, boundary, body, part_num);

return 0;

}
---------------------------------------------------------------END--------------------------------------------------------------------------------

Show quoted text

On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:

On Wed, Aug 16, 2006 at 02:42:46PM -0400, Jasbinder Bali wrote:

Tried everthing but nothing seems to work.
:(

Could you provide a simplified but complete example that shows what
you're doing? That is, all SQL statements and C code necessary to
create a table and whatever functions you're using. I've done a
little testing and haven't been able to reproduce your results, so
apparently my tests don't match what you're doing.

--
Michael Fuhr

This is the create table statement..

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;
ALTER TABLE raw_email OWNER TO postgres;

Show quoted text

On 8/16/06, Michael Fuhr <mike@fuhr.org> wrote:

On Wed, Aug 16, 2006 at 02:42:46PM -0400, Jasbinder Bali wrote:

Tried everthing but nothing seems to work.
:(

Could you provide a simplified but complete example that shows what
you're doing? That is, all SQL statements and C code necessary to
create a table and whatever functions you're using. I've done a
little testing and haven't been able to reproduce your results, so
apparently my tests don't match what you're doing.

--
Michael Fuhr