Thread: 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

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

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

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

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

Tried everthing but nothing seems to work.
:(

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;

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

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;

On Wed, Aug 16, 2006 at 04:11:23PM -0400, Jasbinder Bali wrote:
> I'm sending u the main() function that has the ECPG Insert statement and
> commit in it.

The program you posted isn't a simplified example as I requested.
The idea is that anybody should be able to compile the code and run
it; that's not possible with the code you posted because it's
incomplete.  It also contains many lines that aren't relevant to
the problem, which makes it harder to focus on what is relevant.

While stripping down the code I noticed a problem: it never initializes
the i variable before doing this:

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

On my system that results in a segmentation fault and core dump
because i contains garbage, causing ch[i] to point somewhere illegal.
It's possible that on your system i contains garbage but that ch[i]
points to valid memory, just not to where it should.  As a result,
the ch you insert into the database doesn't contain the data it's
supposed to.  See if initializing i = 0 fixes the problem.  Most
compilers have options to warn about uninitialized variables; I'd
recommend using them.

If that doesn't help then please post a small (10-20 line), complete
program that anybody could compile and run.

--
Michael Fuhr

On Wed, Aug 16, 2006 at 11:40:40AM -0400, Jasbinder Bali wrote:
> [2998]: ECPGexecute line 97 Ok: INSERT 0 1

This is a backend message saying that the insert command was executed error free and inserted one record.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

but i don't see any record getting inserted in the table..
Why is that happenening.

On Thu, Aug 17, 2006 at 10:19:57AM -0400, Jasbinder Bali wrote:
> but i don't see any record getting inserted in the table..
> Why is that happenening.

How exactly are you determining that the record isn't being inserted?
Are you sure you're connected to the right database and querying
tables in the right schema?

Here's a complete example for you to try:

1. Create the following table:

CREATE TABLE public.foo (
    id   integer PRIMARY KEY,
    msg  text NOT NULL
);

2. Compile the following program:

int main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char  msg[] = "This is a test.";
    EXEC SQL END DECLARE SECTION;

    ECPGdebug(1, stderr);

    /* Adjust the next line as necessary. */
    EXEC SQL CONNECT TO tcp:postgresql://192.168.0.110/xyz USER jsbali;
    EXEC SQL INSERT INTO public.foo (id, msg) VALUES (1, :msg);
    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT;

    return 0;
}

3. Run the above program and post all output it generates.

4. Connect to the database with psql or whatever client you use,
execute the following query, and post the output:

SELECT * FROM public.foo;

Also, what version of PostgreSQL are you using and what platforms
are the server and client?  I don't recall if you've said.

--
Michael Fuhr