ODBC and OpenBSD

[Edmund Dengler]

Howdy all!

I am trying out Erlang to get a feel for how well it would work for us. I
am having problems getting the ODBC stuff to work with Postgresql. This is
being tested on an OpenBSD 3.0 i386 system against Postgresql (version
7.4.1). If anybody has any ideas, they would be greatly appreciated!

Steps taken (hopefully this is complete, being done from memory):
(1a) Compile and install unixODBC (latest version, 2.2.7).
(1b) Configure idbcinst.ini and odbc.ini files.
(1c) Test connectivity to Postgresql database using <isql>. Everything
     works. Can connect and issue commands.

(2a) Compile and install Erlang (latest version, R9C-0).
(2b) Try to figure out how to get the ODBC stuff working. Had to modify
     the makefile in .../otp_src_R9C-0/lib to include ODBC as there
     seemed to be included only if this was a Solaris box. Note that
     following the instructions in the manual did not seem to work
     as the ODBC did not install as per the manual.
(2c) Recompile and install Erlang.
(2d) Try out sample.
         erl
         > application:start(odbc).
         > {ok, Ref} = odbc:connect("DSN=MyDatabase", []).
(2e) Get problems ("port_program_executable_not_found"). Determine that
     this is due to odbcserver not being compiled. Try out Makefile,
     attempts to compile simply as:
         cc odbcserver.c -o odbcserver
     Missing many includes and libraries. Compile by hand using:
         gcc \
           -I/opt/unixodbc/2.2.7/include \
           -I/opt/erlang/9c.0/lib/erlang/lib/erl_interface-3.4/include \
           -L/opt/unixodbc/2.2.7/lib \
           -L/opt/erlang/9c.0/lib/erlang/lib/erl_interface-3.4/lib \
	   -DMULTITHREAD_UNIX \
	   odbcserver.c \
	   -lei -lerl_interface -lodbc \
	   -pthread \
	   -o odbcserver
     Install into location:
           mkdir -p /opt/erlang/9c.0/lib/erlang/lib/odbc-1.0.8/priv/bin
           cp odbcserver /opt/erlang/9c.0/lib/erlang/lib/odbc-1.0.8/priv/bin
(2f) Rerun sample.
         erl
         > application:start(odbc).
         > {ok, Ref} = odbc:connect("DSN=MyDatabase", []).
     Get as output:
--------------------------------------------
** exited: {{badmatch,{error,connection_closed}},[{erl_eval,expr,3}]} **

=ERROR REPORT==== 7-Feb-2004::16:21:26 ===
** Generic server <0.52.0> terminating
** Last message in was {#Port<0.48>,{exit_status,139}}
** When Server state == {state,#Port<0.48>,
                               undefined,
                               <0.24.0>,
                               undefined,
                               on,
                               undefined,
                               undefined,
                               on,
                               connecting,
                               undefined,
                               0,
                               false,
                               false,
                               []}
** Reason for termination ==
** {badarg,[{erlang,port_close,[#Port<0.48>]},
            {odbc,terminate,2},
            {gen_server,terminate,6},
            {proc_lib,init_p,5}]}
--------------------------------------------

(3a) Put in debugging statements (using DBG) within odbcserver.c.
     Activate debugging. Wrap logging around the SQLDriverConnect call.
     Get message before SQLDriverConnect, but not after. I think it
     is either locking up here, or is crashing in the thread.
(3b) Create a test.c to test out the unixODBC calls. This should follow
     fairly closely how odbcserver does the calls.
--------------------------------------------
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <pthread.h>

#define MAX_CONN_STR_OUT 1024
#define TIME_OUT 10

int main() {
  unsigned char *connStrIn = "DSN=MyDatabase";
  SQLCHAR connStrOut[MAX_CONN_STR_OUT];
  SQLRETURN stringlength2ptr, result;
  SQLSMALLINT connlen;

  SQLHENV env;
  SQLHDBC connect;

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

  SQLAllocHandle(SQL_HANDLE_DBC, env, &connect);
  SQLSetConnectAttr(connect, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)TIME_OUT, 0);
  SQLSetConnectAttr(connect, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
  SQLSetConnectAttr(connect, SQL_ATTR_TRACE, (SQLPOINTER)SQL_OPT_TRACE_OFF, 0);

  connlen = (SQLSMALLINT)strlen((const char*)connStrIn);

  printf("Connecting\n");
  result = SQLDriverConnect(connect, NULL,
                            (SQLCHAR *)connStrIn,
                            connlen,
                            connStrOut, (SQLSMALLINT)MAX_CONN_STR_OUT,
                            &stringlength2ptr, SQL_DRIVER_NOPROMPT);
  printf("Done (%d)\n", result);
}
--------------------------------------------
    This works no problem (I get the Done with a code of 1).


Does anybody have any thoughts as to what might be causing this? I can't
see how SQLDriverConnect would be locking up so badly that I never get the
next debug statement from odbcserver.

Regards!
Ed