[unixODBC-support] Ubuntu 5.04 config

Mike Yue myue at semotus.com
Tue Aug 9 17:04:21 BST 2005


Here's my recorded tips, take your time to have a look, maybe it helps.


freetds and unixODBC

##########################################################
##########################################################
INSTALLATION:
	1. install freetds (freetds-stable.tgz)
		./configure
		make
		make install
	2. install unixODBC (unixODBC-2.2.10)
		./configure
		make
		make install
##########################################################
##########################################################
Configuration:
==========================================================
config file 1:	/usr/local/etc/odbcinst.ini
==========================================================

[TDS]
Description     = FreeTDS
Driver          = /usr/local/lib/libtdsodbc.so
Setup           = /usr/local/lib/libtdsS.so.1
UsageCount      = 1
==========================================================
config file 2:	/usr/local/etc/odbc.ini
==========================================================

[FIAT]
Driver          = TDS
Description     = ODBC connection via FreeTDS
Trace           = Yes
TraceFile       = /tmp/sql.log
ServerName      = MYFIAT
Database        = hiplink
Port            = 1433

==========================================================
config file 3:	/usr/local/etc/freetds.conf
==========================================================

[MYFIAT]
        host = 192.168.50.131
        port = 1433
        tds version = 8.0

==========================================================
config file 4:	$HOME/.odbc.ini
==========================================================
just copy 
	/usr/local/etc/odbc.ini 
to 
	$HOME/.odbc.ini

==========================================================
config file 5:	/usr/local/unixODBC/etc/odbcinst.ini
==========================================================
just copy 
	/usr/local/etc/odbcinst.ini 
to 
	/usr/local/unixODBC/etc/odbcinst.ini

==========================================================
step 6:	set env
==========================================================
in applications
	putenv("ODBCINI=/usr/local/etc/odbc.ini");
or
	setenv("ODBCINI", "/usr/local/etc/odbc.ini", 1);


##########################################################
##########################################################
TEST freetds:

]# tsql -H 192.168.50.131 -p 1433 -U hiplink -P hiplink
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1> select * from hl_daemon
2> go
1> exit
]#
]#

##########################################################
TEST unixODBC:

]# isql -v FIAT hiplink hiplink
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from hl_daemon
SQL>
]#
]#
##########################################################
Programming with unixodbc.
Sample program:

/* odbc.c
    testing unixODBC
*/

#include <stdlib.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

SQLHENV		V_OD_Env;			// Handle ODBC
environment
SQLRETURN	V_OD_erg;			// result of functions
SQLHDBC		V_OD_hdbc;			// Handle connection
SQLHDBC		V_OD_hstmt;			// Handle connection

char		V_OD_stat[10];		// Status SQL
SQLINTEGER	V_OD_err,V_OD_rowanz,V_OD_id;
SQLSMALLINT	V_OD_mlen,V_OD_colanz;
char		V_OD_msg[200],V_OD_buffer[200];
char		l_dsn[100],l_desc[100];
char		userid[100], passwd[100];
SQLCHAR		con_str[1024];
SQLSMALLINT	outlen;
SQLCHAR		buffer[255];

int OD_ListDSN(void)
{
	int	   retcode;
	int        count = 0;
	short int  l_len1,l_len2,l_next;

	l_next=SQL_FETCH_FIRST;

	while( (retcode=SQLDataSources(V_OD_Env,l_next,l_dsn,
sizeof(l_dsn),
	    &l_len1, l_desc, sizeof(l_desc), &l_len2)) == SQL_SUCCESS) {
		printf("Server=(%s) Beschreibung=(%s)\n",l_dsn,l_desc);
		l_next=SQL_FETCH_NEXT;
		count++;

		break;
	}

	if (!count)
		printf("No DSN defined. retcode=%d\n", retcode);

	return count;
}


int main(int argc,char *argv[])
{
	int	dsn_num = 0;

	strcpy(l_dsn, "FIAT");
	strcpy(userid, "hiplink");
	strcpy(passwd, "hiplink");
	//sprintf((char *)con_str, "DSN=%s;UID=%s;PWD=%s;", l_dsn,
userid, passwd);
	//sprintf((char *)con_str, "DSN={%s};UID={%s};PWD={%s};", l_dsn,
userid, passwd);

	putenv("ODBCINI=/usr/local/etc/odbc.ini");   
	//setenv("ODBCINI", "/usr/local/etc/odbc.ini", 1);   
	printf("$ODBCINI=%s\n", getenv("ODBCINI"));

	// 1. allocate Environment handle and register version 
	
V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Error AllocHandle\n");
		exit(0);
	}
	V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION,
(void*)SQL_OV_ODBC3, 0); 
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Error SetEnv\n");
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	// 2. allocate connection handle, set timeout
	V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);

	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Error AllocHDB %d\n",V_OD_erg);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}

	/*
	//test the exist DSN
	dsn_num = OD_ListDSN();
	if (!dsn_num) {
		printf("exit normally.\n");
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	*/

	SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5,
0);


	// 3. Connect to the defined datasource
	printf("DSN=%s UID=%s PWD=%s\n", l_dsn, userid, passwd);
/*
	V_OD_erg = SQLConnect(V_OD_hdbc,
		//(SQLCHAR*)l_dsn, (SQLSMALLINT)strlen(l_dsn),
		//(SQLCHAR*)userid, (SQLSMALLINT)strlen(userid),
		//(SQLCHAR*)passwd, (SQLSMALLINT)strlen(passwd));
		(SQLCHAR*)l_dsn, SQL_NTS,
		(SQLCHAR*)userid, SQL_NTS,
		(SQLCHAR*)passwd, SQL_NTS);
*/
	V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "FIAT", SQL_NTS,
                                     (SQLCHAR*) "hiplink", SQL_NTS,
                                     (SQLCHAR*) "hiplink", SQL_NTS);
printf("return %d\n", V_OD_erg);

	if ( V_OD_erg == SQL_SUCCESS || V_OD_erg ==
SQL_SUCCESS_WITH_INFO )
		printf("Connected !\n");
	else {
		printf("Error SQLConnect %d\n",V_OD_erg);
		SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, 
		              V_OD_stat,
&V_OD_err,V_OD_msg,100,&V_OD_mlen);
		printf("%s (%d)\n",V_OD_msg,V_OD_err);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}

	/*
	printf("connect string is: [%s]\n", con_str);
	V_OD_erg = SQLDriverConnect(V_OD_hdbc,
		(SQLHWND) NULL,
		con_str,
		SQL_NTS,
		buffer,
		sizeof(buffer)-1,
		&outlen,
		//SQL_DRIVER_NOPROMPT);
		SQL_DRIVER_COMPLETE);

	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Error SQLDriverConnect %d\n",V_OD_erg);
		SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, 
		              V_OD_stat,
&V_OD_err,V_OD_msg,100,&V_OD_mlen);
		printf("%s (%d)\n",V_OD_msg,V_OD_err);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	*/

	V_OD_erg=SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc,
&V_OD_hstmt);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Fehler im AllocStatement %d\n",V_OD_erg);
		SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1,
V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
		printf("%s (%d)\n",V_OD_msg,V_OD_err);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err);
	SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,150,&V_OD_err);
	
	//V_OD_erg=SQLExecDirect(V_OD_hstmt,"SELECT dtname,iduser FROM
web order by iduser",SQL_NTS);   
	V_OD_erg=SQLExecDirect(V_OD_hstmt,"select daemonid,name from
hl_daemon order by daemonid",SQL_NTS);   
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Error in Select %d\n",V_OD_erg);
		SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1,
V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
		printf("%s (%d)\n",V_OD_msg,V_OD_err);
		SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
		SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}
	V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
		SQLDisconnect(V_OD_hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}

	printf("Number of Columns %d\n",V_OD_colanz);
	V_OD_erg=SQLRowCount(V_OD_hstmt,&V_OD_rowanz);
	if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("Number of RowCount %d\n",V_OD_erg);
		SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
		SQLDisconnect(V_OD_hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
		SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
		exit(0);
	}

	printf("Number of Rows %d\n",V_OD_rowanz);
	V_OD_erg=SQLFetch(V_OD_hstmt);  
	while(V_OD_erg != SQL_NO_DATA)
	{
		printf("Result: %d %s\n",V_OD_id,V_OD_buffer);
		V_OD_erg=SQLFetch(V_OD_hstmt);  
	};

	SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
	SQLDisconnect(V_OD_hdbc);
	SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
	SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);

	return(0);
}
##########################################################


-----Original Message-----
From: unixodbc-support-bounces at easysoft.com
[mailto:unixodbc-support-bounces at easysoft.com] On Behalf Of Derek
Harding
Sent: August 9, 2005 2:47 AM
To: unixodbc-support at easysoft.com
Subject: [unixODBC-support] Ubuntu 5.04 config

Hi,

I am trying to get odbc connexion to another machine on the network.
Here are my config files:
odbc.ini
[acd]
Description	= ACD
Driver		= MySQL
Server		= bayonne
Database	= acd
Port		= 
Socket		= 
Option		= 3
Stmt		= 

odbcinst.ini
[ODBC]
Trace		= Yes
TraceFile	= /tmp/sql.log
ForceTrace	= Yes
Pooling		= No

[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/odbc/libmyodbc.so
Setup		= /usr/lib/odbc/libodbcmyS.so
FileUsage	= 1

When I try isql it tells me "could not SQLConnect" yet the details above
are correct. I have tried with and without user= and password=

I can make odbc connexions to bayonne from a windows machine but not
this Ubuntu box although I can ping it :(

I used apt-get to load everything and no errors were reported.

Anyone any ideas please?

-- 
Best wishes,
Derek

-- 
Best wishes,
Derek


_______________________________________________
unixODBC-support mailing list
unixODBC-support at easysoft.com
http://mail.easysoft.com/mailman/listinfo/unixodbc-support




More information about the unixODBC-support mailing list