Re: pass date type data to PQexecparams

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