Re: pass date type data to PQexecparams - Mailing list pgsql-novice

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