Postgres via odbc?

[Ingela Anderton]

There is a much better solution that does not require you to change
the c-code of the Erlang odbc-application, and that is too
disable scrollable cursors when opening your connection.

odbc:connect(ConnectionString, [{scrollable_cursors, off}])

When doing that you will get the exact same effect as your workaround
below but without any hassles ;)

This is mentioned in the ODBC users guide if anybody would bother to
read it!

--------- From the users Guide ---------------------

1.4 About the Erlang ODBC application

Provides an Erlang interface to communicate with relational
SQL-databases. It is built on top of Microsofts ODBC interface and
therefore requires that you have an ODBC driver to the database that
you want to connect to. The Erlang ODBC application is designed using
the version 3.0 of the ODBC-standard, however using the option
{scrollable_cursors, off} for a connection has been known to make it
work for at least some 2.X drivers.

-----------------------------------------------------


Carlos Abalde wrote:
> 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.

-- 
/Ingela

Ericsson AB - OTP team