Re: pass date type data to PQexecparams

I need to pass date type data from one table to another using the libpq
library wich meens that I'm using the C programming language...
PQexecparams look's like:

res = PQexecParams(conn,
"INSERT INTO orders
(userid,productid,qty,nomprice,discount,totprice,delivered,orderdate) VALUES
($1::int4,$2::int4,$3::int4,$4::int4,$5::int4,$6::int4,$7::int4,$8::QUESTION)",
8,
NULL,
paramValues,
paramLengths,
paramFormats,
1)

You can see that last 8th argument is of type QUESTION, well this is my
question wich datatype must be declared to pass date into ?

On Fri, Sep 22, 2006 at 10:26:12AM +0200, pr0v4 wrote:
> I need to pass date type data from one table to another using the libpq
> library wich meens that I'm using the C programming language...
> PQexecparams look's like:
>
> res = PQexecParams(conn,
> "INSERT INTO orders
> (userid,productid,qty,nomprice,discount,totprice,delivered,orderdate) VALUES
> ($1::int4,$2::int4,$3::int4,$4::int4,$5::int4,$6::int4,$7::int4,$8::QUESTION)",
> 8,
> NULL,
> paramValues,
> paramLengths,
> paramFormats,
> 1)
>
> You can see that last 8th argument is of type QUESTION, well this is my
> question wich datatype must be declared to pass date into ?

The obvious answer is that the type should be date but I can think
of situations that need something more. If the parameter format
is binary and the parameter value is a type other than date then
you might need two casts: one to specify the parameter type and
another to cast that type to the destination column's type. For
example, if the format is binary, the value is text, and the
destination column is date, then "$8::text::date" is probably what
you need. But if the value is date then "$8::date" should work.

Is the input value in text or binary format? If binary then what
type is it? What have you tried and with what results? If you're
getting an error then please post the error message.

--
Michael Fuhr

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

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.

--
Michael Fuhr

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

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
>

On Wed, Sep 27, 2006 at 12:41:21PM +0200, pr0v4 wrote:
> 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: ""

Have you used PQgetisnull() to check if the value you retrieved is
NULL? If that's not the problem then please post a minimal but
complete program that compiles and runs and demonstrates the behavior
you're seeing. Include any SQL statements necessary to set up the
test (CREATE TABLE, INSERT, etc.). We need to see exactly what
you're doing to be able to say why it doesn't work.

--
Michael Fuhr

On 27/09/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> On Wed, Sep 27, 2006 at 12:41:21PM +0200, pr0v4 wrote:
> > 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:
> ""
>
> Have you used PQgetisnull() to check if the value you retrieved is
> NULL? If that's not the problem then please post a minimal but
> complete program that compiles and runs and demonstrates the behavior
> you're seeing. Include any SQL statements necessary to set up the
> test (CREATE TABLE, INSERT, etc.). We need to see exactly what
> you're doing to be able to say why it doesn't work.
>
> --
> Michael Fuhr
>

Well Michael I've
resolve my problem. Well instead of date type I'm using timestamp,
here is the sample code wich doing what I need:

#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>

#include <netinet/in.h>
#include <arpa/inet.h>

#include "libpq-fe.h"

static void exit_easey(PGconn *conn)
{
PQfinish(conn);
exit(1);
}

int main(int argc, char* argv[])
{
const char* conninfo;
const char* paramValues[1];
int paramLengths[1];
int paramFormats[1];
const char* params[1];
int paramlength[7];
int paramformat[7];
char date[20];
PGresult *res;
PGconn* conn;
uint32_t bindate;

if(argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = dbtest"; //set default database name

system("clear");
conn = PQsetdbLogin("localhost","5432","","","dbtest","testuser","test");
//make connection to the database
if(PQstatus(conn) != CONNECTION_OK) //error handler if connection isn't
successfull
{
fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn)
);
exit_easey(conn);
}
else
printf("Connection to database successfully started!\n");

res = PQexec(conn, "BEGIN"); //execute begin command
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Begin command failed: %s\n", PQerrorMessage(conn));
PQclear(res);
exit_easey(conn);
}
else
printf("Begin command successfully executed\n");
PQclear(res); //clear result, free up memory

res = PQexec(conn, "DECLARE pgdate CURSOR FOR SELECT datum from testing");
if(PQresultStatus(res) != PGRES_COMMAND_OK) //check for the query execution
{
fprintf(stderr, "Declaring cursor date failed: %s!!\n", PQerrorMessage(conn)
);
PQclear(res);
exit_easey(conn);
}

/*Fetch records*/
res = PQexec(conn, "FETCH ALL IN pgdate");
if(PQresultStatus(res) != PGRES_TUPLES_OK) //Check is data fetched
{
fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn) );
PQclear(res);
exit_easey(conn);
}

// date = PQgetvalue(res, 0, 0); //get date to input in orders table
strcpy(date, "2006-09-23");
printf("This is date: %-15s\n", date);
//res = PQexec(conn, "CLOSE pgdate");
PQclear(res);

// bindate = htonl( (uint32_t) *date);
paramValues[0] = date;
paramLengths[0] = strlen(date);
paramFormats[0] = 0;

res = PQexecParams(conn,
"INSERT INTO testing (datum) VALUES ($1::timestamp)",
1,
NULL,
paramValues,
paramLengths,
paramFormats,
1); //insert into table
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Insert new records FAILED: %s", PQerrorMessage(conn));
PQclear(res);
exit_easey(conn);
}
PQexec(conn, "COMMIT");
res = PQexec(conn, "CLOSE price"); //close cursor price
PQclear(res);
res = PQexec(conn, "CLOSE pgdate"); //close cursor pgdate
PQclear(res); //clear res

res = PQexec(conn, "END");
PQclear(res);
return 0;
}