Re: pass date type data to PQexecparams

On Mon, Sep 25, 2006 at 10:35:17AM +0200, pr0v4 wrote:
> Ok, the format is binary, value is text and destination is date, I've put
> $8::text::date for 8th parameter,
> then I've got error message "ERROR : invalid byte sequence for encoding
> "SQL-ASCII":0x00 ".

Are you sure the value is of type text? I get this error if the
value is a date in binary format and I use $8::text::date. Have
you tried $8::date? If so and it didn't work then what happened
(error message, misbehavior, etc.)?

> Then I've read that paramLength must be in
> network-byte-order if the data is in binary format so I've put it in network
> byte order, well after that I'm geting segmentation fault?

Where did you read that? The length should be in host byte order,
which should be the order returned by PQgetlength().

> To be more precise I've declared date variable as char* date, then with
> PexecParams select date from table, and then with PQgetvalue
> put the date value into date variable. This work's fine because I've print
> it with printf.

Did you request binary format or text format? What was the last
argument to PQexecParams() for the SELECT query? What was the exact
printf() statement and what was the output?

What values did you assign to the date column's slot in paramValues,
paramLengths, and paramFormats for the INSERT?

> Before I've put the date variable in paramValues I've cast the date into
> network byte order.

If you received the date in binary format from libpq then you
shouldn't modify it if you're going to send it back to libpq.

> After all this I'm getting Segmentation fault when the code
> reached PQexecParams wich inserts the data into table ...

Please post a simple but complete program that shows what you're
doing. For example, create the following table:

CREATE TABLE test (d date);

Have the program connect to the database, issue "SELECT current_date"
to get a date value or "SELECT current_date::text" to get a text
value, then INSERT that value into the table. If the INSERT returns
a PQresultStatus() of other than PGRES_COMMAND_OK then use
PQresultErrorMessage() to get an error message and include that
error in your post.

The code you sent doesn't compile due to several problems like
undeclared variables and syntax errors. After those errors are
corrected and the program compiles, it fails at runtime for various
reasons. These difficulties prevent people from running the program
to see what's happening without spending additional time to fix the
mistakes, by which time the code might no longer resemble what
you're really doing, which reduces its usefulness as a test case
(not to mention that a person who had been willing to help might
have given up by now). When posting a test program, please compile
and run it first to verify that it "works" in the sense that it
runs and demonstrates the behavior you're seeing.

All that said, the main problem is here:

> bindate = htonl( (uint32_t) *date);
> paramValues[0] = (char*) &bindate;
> paramLengths[0] = sizeof(bindate);
> paramFormats[0] = 1;

The date variable is a char * that points to a date value in text
format. That is, date points to memory that contains the characters
in a string like "2006-09-26" followed by a NUL (\0) character.
The values in memory would look something like this (in hex):

32 30 30 36 2d 30 39 2d 32 36 00

The bindate assignment takes the first character (0x32), casts it
to uint32_t, then converts that value to network byte order. The
result has nothing to do with the original date value. Try this
instead:

paramValues[0] = date;
paramLengths[0] = strlen(date);
paramFormats[0] = 0;

Since the date value is in text format you can use strlen() to get
its length; you could also use PQgetlength() with the result pointer
since you fetched the value from a query. The format should be 0
(zero) because the value is in text format, not binary format.

Sorry because uncompiled example program, yesterday I was very busy so I've
probably forget to compile it. I thought I done that.
Well about my problem, I have done what you said to me to try, well it
isn't help.
Now I'm getting following error message:
Insert new records FAILED: ERROR: invalid input syntax for type date: ""

On 26/09/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> On Tue, Sep 26, 2006 at 10:28:08AM +0200, pr0v4 wrote:
> > On 26/09/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> > > Please post a simple but complete program that shows what you're
> > > doing.
> >
> > Ok, I've created sample code just like you said.
> > So here it is:
>
> Please copy the mailing list on replies. This gives other people
> an opportunity to respond, which can get you an answer faster if
> the person you emailed privately is unavailable. It also allows
> others who are reading the list now or in the future via the archives
> to learn from the discussion if they have similar questions.
>
> The code you sent doesn't compile due to several problems like
> undeclared variables and syntax errors. After those errors are
> corrected and the program compiles, it fails at runtime for various
> reasons. These difficulties prevent people from running the program
> to see what's happening without spending additional time to fix the
> mistakes, by which time the code might no longer resemble what
> you're really doing, which reduces its usefulness as a test case
> (not to mention that a person who had been willing to help might
> have given up by now). When posting a test program, please compile
> and run it first to verify that it "works" in the sense that it
> runs and demonstrates the behavior you're seeing.
>
> All that said, the main problem is here:
>
> > bindate = htonl( (uint32_t) *date);
> > paramValues[0] = (char*) &bindate;
> > paramLengths[0] = sizeof(bindate);
> > paramFormats[0] = 1;
>
> The date variable is a char * that points to a date value in text
> format. That is, date points to memory that contains the characters
> in a string like "2006-09-26" followed by a NUL (\0) character.
> The values in memory would look something like this (in hex):
>
> 32 30 30 36 2d 30 39 2d 32 36 00
>
> The bindate assignment takes the first character (0x32), casts it
> to uint32_t, then converts that value to network byte order. The
> result has nothing to do with the original date value. Try this
> instead:
>
> paramValues[0] = date;
> paramLengths[0] = strlen(date);
> paramFormats[0] = 0;
>
> Since the date value is in text format you can use strlen() to get
> its length; you could also use PQgetlength() with the result pointer
> since you fetched the value from a query. The format should be 0
> (zero) because the value is in text format, not binary format.
>
> --
> Michael Fuhr
>