unixODBC working but not psqlodbc...

Hi all,

I had some C code that was accessing a MySQL database through a mysql
c API and now I need to move to PostgreSQL through ODBC.
I've installed the following packages (I need to stay with this
version)
postgresql-7.2.1-5
postgresql-odbc-7.2.1-5
postgresql-libs-7.2.1-5
postgresql-server-7.2.1-5
postgresql-docs-7.2.1-5
postgresql-devel-7.2.1-5
unixODBC-2.2.0-5
unixODBC-devel-2.2.0-5

I've found some code sample (on red hat site) to try to connect to the
database through ODBC but my problem is that I can have it working
with the unixODBC driver but not with the provided PostgreSQL ODBC
driver.

I'll put the code sample at the end of the post and here is the result
I have from there

gcc -lodbcpsql -I/usr/include/unixODBC-2.2.6/include -I. ViewTable.c
-o ViewTable
# ./ViewTable
Connection Parameters: DSN='MY_DB', UID='myUser', PWD=''
Handles initialized.

Content of players:
******************

No problem there, the result is as expected

#gcc -lpsqlodbc -I/usr/include/unixODBC-2.2.6/include -I. ViewTable.c
-o ViewTable
# ./ViewTable
Connection Parameters: DSN='MY_DB', UID='myUser', PWD=''
Unable to connect to datasource (ret=-1)
Segmentation fault (core dumped)
#

Here are my /etc/odbc.ini and /etc/odbcinst.ini
odbc.ini
[ODBC Data Sources]
MY_DB = My database using psqlodbc access

[MY_DB]
Driver = /usr/lib/libpsqlodbc.so
Servername = localhost
Database = my_db
Port = 5432
ReadOnly = No

odbcint.ini
[PostgreSQL]
Description     = PostgreSQL driver
Driver          = /usr/lib/libpsqlodbc.so
Setup           = /usr/lib/libpsqlodbcS.so


What am I doing wrong ? Do both drivers do the same thing ? What is
the role of each one ? One of my requirement is to use the postgresql
driver and not the unixodbc so any help is apreciated ;-)

Steel


Initialize.h:

#include <stdio.h>
#include <stdlib.h>
//#include "iodbc/sql.h"
//#include "iodbc/isqlext.h"
#include <sql.h>
#include <sqlext.h>

SQLCHAR DSN[10] = "MY_DB";

// The following should be changed to your userid and password
// and the length of the variable should be the actual length + 1
SQLCHAR DSN_userid[9] = "myUser";
SQLCHAR DSN_password[12] = "";

int Initialize(HENV * phenv, HDBC * phdbc, HSTMT * phstmt);
void cleanup(HENV henv, HDBC hdbc, HSTMT hstmt);

// Allocate the environment, connection, statement handles
int Initialize(HENV * phenv, HDBC * phdbc, HSTMT * phstmt)
{
  int res;

  // Allocate an environment handle
  res = SQLAllocEnv(phenv);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to allocate environment handle
(ret=%d)\n", res);
    exit(1);
  }

  // Allocate a connection handle
  res = SQLAllocConnect(*phenv, phdbc);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to allocate connection handle (ret=%d)\n",
res);
    cleanup(*phenv, *phdbc, *phstmt);
    exit(1);
  }

  // The connection parameters
  printf("Connection Parameters: DSN='%s', UID='%s', PWD='%s'\n", DSN,
    DSN_userid, DSN_password);

  // Connect to the database
  res = SQLConnect(*phdbc, DSN, SQL_NTS, DSN_userid, SQL_NTS,
    DSN_password, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to connect to datasource (ret=%d)\n",
res);
    cleanup(*phenv, *phdbc, *phstmt);
    exit(1);
  }

  // Allocate a statement handle
  res = SQLAllocStmt(*phdbc, phstmt);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to allocate statement handle (ret=%d)\n",
res);
    cleanup(*phenv, *phdbc, *phstmt);
    exit(1);
  }
  printf("Handles initialized.\n");
  return 0;
}

// Cleanup before exit
void cleanup(HENV henv, HDBC hdbc, HSTMT hstmt)
{
  // Disconnect from the database and free all handles
  SQLFreeStmt(hstmt, SQL_CLOSE);
  SQLDisconnect(hdbc);
  SQLFreeConnect(hdbc);
  SQLFreeEnv(henv);
  return;
}


ViewTable.c:

#include <stdio.h>
#include <stdlib.h>
#include "Initialize.h"

int main(int argc, char *argv[])
{
  HENV henv;
  HDBC hdbc;
  HSTMT hstmt;
  SQLCHAR SelectStmt[255], cname1[25], cname2[50];
  int res;

  if (argc != 1) {
    fprintf(stderr, "Usage: ViewTable\n");
    exit(1);
  }

  // Allocate all handles
  res = Initialize(&henv, &hdbc, &hstmt);
  if (res != 0) {
    fprintf(stderr, "Unable to initialize (ret=%d)\n", res);
    exit(1);
  }

  // Create the query
  strcpy((char *) SelectStmt, "SELECT * FROM ConfigurationData");

  // Prepare and execute the SQL statement
  res = SQLExecDirect(hstmt, SelectStmt, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to execute statement directly (ret=%d)\n",
res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }

  // Bind the columns in the result data set returned to
  // application variables
  res = SQLBindCol(hstmt, 1, SQL_C_CHAR, cname1, sizeof(cname1),
NULL);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to bind column 1 (ret=%d)\n", res);
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
  res = SQLBindCol(hstmt, 2, SQL_C_CHAR, cname2, sizeof(cname2),
NULL);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to bind column 2 (ret=%d)\n", res);
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }

  printf("\nContent of players:\n");
  printf("******************\n");

  // While there are remaining rows in the result set,
  // retrieve and display them
  res = SQLFetch(hstmt);
  while (res != SQL_NO_DATA_FOUND) {
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
      fprintf(stderr, "Unable to fetch row (ret=%d)\n", res);
      cleanup(henv, hdbc, hstmt);
      exit(1);
    }
    printf("%s -- %s \n", cname1, cname2);
    res = SQLFetch(hstmt);
  }
  cleanup(henv, hdbc, hstmt);

  return 0;
}

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match