Postgres via odbc?

[Carlos Abalde]

El 17 de May de 2004, Shawn Pearce wrote,

> Has anyone setup Erlang ODBC to use Postgres?
> 
> I'm looking at trying to setup a Postgres server on Linux and have
> Erlang connect to it from the same box via ODBC; but was wondering
> if there are any gotcha's I should be aware of before I waste a few
> hours on it.
> 
> If its really an issue, I'll just break down and write a port driver
> for it.  :)

Hi Shawn!

We are currently using the Erlang ODBC driver to access a Postgres server.
I don't know what is exactly yor problem, but i'll try to sum up our
experience (valid until R9B, but not tested yet with R9C).

1. Compile Erlang with ODBC support,

	1.1 ./configure --enable-odbc ...

	1.2 Edit <erlang-src-dir>/lib/Makefile. Search for,

	        ifeq ($(findstring sparc-sun-solaris,$(TARGET)),sparc-sun-solaris)
      			OTHER_SUB_DIRECTORIES += odbc  
		endif
	
	    and replace it with (delete the if statement),

        	OTHER_SUB_DIRECTORIES += odbc

	1.3 Build Erlang

	1.4 Unfortunately, there are some problems of compatibility
	between the Erlang ODBC port and the Postgres ODBC driver.
	We have found a *workaround* that works fine with our
	current configuration (Erlang R9B and Postgresql 7.3.4).
	Search in the file <erlang-installation-dir>/lib/odbc-<version>/c_src/odbcserver.c
	the following code,

		if(!sql_success(SQLGetInfo(connection_handle(state),
					   SQL_DYNAMIC_CURSOR_ATTRIBUTES1,
					   (SQLPOINTER)&supportMask,
					   sizeof(supportMask),
					   NULL))) {
		  exit_on_failure("SQLGetInfo failed in dbInfo");
		}

		if (supportMask & SQL_CA1_ABSOLUTE ) {
		  ei_x_encode_atom(&dynamic_buffer(state), "true");
		}
		else {
		  ei_x_encode_atom(&dynamic_buffer(state), "false");    
		}

		if (supportMask & SQL_CA1_RELATIVE) {
		  ei_x_encode_atom(&dynamic_buffer(state), "true");    
		}
		else {
		  ei_x_encode_atom(&dynamic_buffer(state), "false");
		}

		msg.buffer = (byte *)dynamic_buffer(state).buff;

	And replace it with the following,

		if(!sql_success(SQLGetInfo(connection_handle(state),
					   SQL_DYNAMIC_CURSOR_ATTRIBUTES1,
					   (SQLPOINTER)&supportMask,
					   sizeof(supportMask),
					   NULL))) {
		    /* exit_on_failure("SQLGetInfo failed in dbInfo"); */
		    ei_x_encode_atom(&dynamic_buffer(state), "false");
		    ei_x_encode_atom(&dynamic_buffer(state), "false");
		} else {

		    if (supportMask & SQL_CA1_ABSOLUTE ) {
			ei_x_encode_atom(&dynamic_buffer(state), "true");
		    }
		    else {
			ei_x_encode_atom(&dynamic_buffer(state), "false");
		    }

		    if (supportMask & SQL_CA1_RELATIVE) {
			ei_x_encode_atom(&dynamic_buffer(state), "true");
		    }
		    else {
			ei_x_encode_atom(&dynamic_buffer(state), "false");
		    }
		}

		msg.buffer = (byte *)dynamic_buffer(state).buff;

	If you inspect this code, you will notice that we are eliminating
	some error conditions in the driver setup. This is correct if
	you know what are you doing and what are the implications. You can
	find more infomation about the change in,

		http://www.erlang.org/ml-archive/erlang-questions/200211/msg00247.html

	1.5 Finally, go to <erlang-installation-dir>/lib/odbc-<version>/c_src
	and rebuild the driver (make)

2. Configure your enviroment to access via ODBC to your database
(~/.odbc.ini, odbcinst and so on). Before trying to use Erlang,
make sure that you can access it using a tool like isql.

With this two steps I believe you will be able to achieve your goal. I known
that the workaround is not the best solutions, but until now is the best...

I hope this was useful for you. Best regards,


-- Carlos.