Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    5

    Question Unanswered: insertion through INSERT command in Libpq

    Hello Experts,
    Trying to insert data into a table through LIBPQ in POSTGRESQL .
    I'm accepting data through some 'C' variables then trying to insert them as


    printf("Enter values for Farmer's Name :");
    scanf("%s",farmer_name);


    printf("Enter values for Villages Name :");
    scanf("%s",village);

    printf("Enter values for Taluka Name :");
    scanf("%s",taluka);

    printf("Enter values for Distric Name :");
    scanf("%s",district);

    res = PQexec(conn,"insert into farmer(farmer_id,farmer_name,village_town,taluka,d istrict) values (nextval('farmer_ids'),farmer_name,village,taluka, district)");

    is the above statement correct ? Can i directly use C variables directly as above ?

    I also tried to insert data into the table through a file as follows

    if ( write(fd,farmer_name,strlen(farmer_name)) == 0 )
    printf("Could not be written to the file\n");

    Even this option is not working ?

    what is the problem . What is the conventional way of accepting data from stdin and inserting the data into the table usin LIBPQ interface ?

    Plz help me out ,


    Thx in advance ,

    Swiftguy.



    ***Source Code***********

    #include<unistd.h>
    #include <stdio.h>
    #include <string.h>
    #include <sys/stat.h>
    #include <stdlib.h>
    #include <libpq-fe.h>


    main()
    {
    char farmer_name[200],village[200],taluka[200],district[200],*comma=",";


    char *pghost,
    *pgport,
    *pgoptions,
    *pgtty;
    char *dbName;
    int nFields;
    int i,j,fd;


    PGconn *conn;
    PGresult *res;

    pghost = NULL; /* host name of the backend server */
    pgport = NULL; /* port of the backend server */
    pgoptions = NULL; /* special options to start up the backend
    * server */
    pgtty = NULL; /* debugging tty for the backend server */
    dbName = "cpnr";

    fd = open("./insert.sql",O_RDWR|O_CREAT);

    /* make a connection to the database */
    conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

    /*
    * check to see that the backend connection was successfully made
    */
    if (PQstatus(conn) == CONNECTION_BAD)
    {
    fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
    fprintf(stderr, "%s", PQerrorMessage(conn));

    }

    /* debug = fopen("/tmp/trace.out","w"); */
    /* PQtrace(conn, debug); */

    /* start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {
    fprintf(stderr, "BEGIN command failed\n");
    PQclear(res);

    }

    /*
    * should PQclear PGresult whenever it is no longer needed to avoid
    * memory leaks
    */
    PQclear(res);



    printf("Enter values for Farmer's Name :");
    scanf("%s",farmer_name);


    printf("Enter values for Villages Name :");
    scanf("%s",village);

    printf("Enter values for Taluka Name :");
    scanf("%s",taluka);

    printf("Enter values for Distric Name :");
    scanf("%s",district);

    /* strcat(farmer_name,comma);
    strcat(village,comma);
    strcat(farmer_name,village);
    strcat(taluka,comma);
    strcat(taluka,district);
    strcat(farmer_name,taluka);


    if ( write(fd,farmer_name,strlen(farmer_name)) == 0 )
    printf("Could not be written to the file\n");

    */



    res = PQexec(conn,"insert into farmer(farmer_id,farmer_name,village_town,taluka,d istrict) values (nextval('farmer_ids'),farmer_name,village,taluka, district)");

    res = PQexec(conn,"copy farmer from './insert.sql' USING DELIMITERS ',' WITH NULL AS '\null' " );

    if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
    {

    fprintf(stderr, "INSERT command failed\n");
    PQclear(res);
    }


    /* commit the transaction */
    res = PQexec(conn, "COMMIT");
    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);
    close(fd);

    /* fclose(debug); */
    return 0;

    }

  2. #2
    Join Date
    Jun 2004
    Posts
    3
    No you can't use the variables directly, you will need to concatenate all the variables together along with the SQL statement as one big string, then pass that to the PQExec function.

    Something like:

    res = PQexec(conn,"insert into farmer(farmer_id,farmer_name,village_town,taluka,d istrict) values (nextval('farmer_ids')," + farmer_name + "," + village + "," + taluka + "," + district + ")");

    Also you will need to add single quotes around those variables as well (if they inserted into the database as strings, rather than numbers).

    Now, please note it is a *long* time since I wrote C code so the string concat operator is probably not a '+'. I am a bit rusty!

  3. #3
    Join Date
    May 2004
    Posts
    5

    Thz

    Thanx a lot your answer solved my problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •