odbc with freetds

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

odbc with freetds

Post by grezi »

i use freetds driver to connect to microsoft sql server database. I use the php function odbc_connect and i get the following error:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [FreeTDS][SQL Server]Read from SQL server failed., SQL state 08S01 in SQLConnect in mywebsite/html/testodbc.php on line 3

Can you please help me.
Thanks in advance
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Have you tried using the mssql_* family of functions? Sometimes that works better. Or perhaps PDO?
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

Post by grezi »

I have tested the connection with the use of tsql and the connection was succesful. I also performed a query and recieved the results, but when it comes to PHP code i always recieve this error. Is it mandatory to use the mssql_ functions or i can do it with odbc_ functions. I am completelly lost. Do you know what could be the error in the code i am using. the code is
$url = "my server url";

iODBC is the name of the settings in the freetds.conf file
// connect to database server
$db_conn = odbc_connect("iODBC","username","password", SQL_CUR_USE_ODBC)
or die( "<strong>ERROR: Connection to my server failed</strong>" );

I would try the pdo but this needs some more installations i cannot do at the moment
Thank you again for your help!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Have a look at the odbc_* functions and see if there is an odbc_error() function or something that could give you some more information about the error.
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

Post by grezi »

I tried the odbc_error() and the odbc_errormsg() but i still get the same error
on the odbc_error() (Warning: odbc_connect() [function.odbc-connect]: SQL error: [FreeTDS][SQL Server]Read from SQL server failed., SQL state 08S01 in SQLConnect in....)
and
on the odbc_erromsg() (Warning: odbc_errormsg(): supplied argument is not a valid ODBC-Link resource in ...... )

The source of the problem is the installation of the iODBC manager or the FreeTDS driver?
OR
I am doing something wrong in my settings of the odbc.ini and freetds.conf?

You are the only person that gave me alot of help and support and i thank you very much for that. The support i have from my webserver (hosting company) is confusing me more than helping me. I desided to order an installation of mssql_ functions to my php in hope that this will solve the problem but i am still not sure about that. Am i doing right or no ? Should i do something like that or it will still remain a problem. Please help me i am going crazy because this story is more than one month long. Thank you again for your support.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

I think that all of that stuff depends on FreeTDS working correctly, so you may still have problems with the mssql functions or PDO. Unfortunately the number of PHP developers who use SQL server are not that common, so you may have trouble finding help.

If the mssql functions don't fix it, post back here with any details you can provide on your configuration (including contents of your .conf file sans passwords), and any steps that you have taken to troubleshoot this.

I am guessing the solution to your problem is a simple one, if the command line client works, then there is probably some minor setting somewhere that needs to be adjusted, the problem is finding it.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

When it comes to compiling the mssql_* functions on the machine, the machine needs to make sure the appropriate client libraries are installed correctly (FreeTDS in this case). There may be more involved when talking to SQL Server, and there may issues with the web server. Are you on IIS?
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

Post by grezi »

i am on fedora core 5 and i use apache. I want to connect to a windows server 2003 with MS SQL Server 2005. The microsoft server resides on an other network. Sory i didnt mention before
Hugh
Forum Newbie
Posts: 1
Joined: Wed Apr 18, 2007 3:33 am

Post by Hugh »

Hi grezi,

You mention below that iODBC is an entry in your freetds.conf file, what exactly is the entry in this file and how is this associated with the SQLServer connection to be made ? The first param in the odbc_connect function should be that of the ODBC DSN (datasource) configured for connecting to the traget SQLServer instance and typically resides in the a file called odbc.ini which is located by the setting of the $ODBCINI environment variable. I am not sure which ODBC driver you are using, but have you verified with a simple odbc test program of some form (typically most driver have a test program for verifying basic connectivity) that the ODBC Driver itself can successfully connect to the database independant of PHP, as this is always wise test to perform to determine basic operability of the ODBC driver.

Note thier an odbc PHP HOWTO on the iODBC Web site you should read if not already done so, as this details how to make a basic PHP connection using iODBC:

http://www.iodbc.org/index.php?page=lan ... hp23039346

The key point being to have a working ODBC DSN in place, the HOWTO having been written and tested using the OpenLink ODBC Drivers (which includes one for SQLServer), but any other ODBC driver can be used also.

Hope this helps

Best Regards
Hugh Williams

grezi wrote:I have tested the connection with the use of tsql and the connection was succesful. I also performed a query and recieved the results, but when it comes to PHP code i always recieve this error. Is it mandatory to use the mssql_ functions or i can do it with odbc_ functions. I am completelly lost. Do you know what could be the error in the code i am using. the code is
$url = "my server url";

iODBC is the name of the settings in the freetds.conf file
// connect to database server
$db_conn = odbc_connect("iODBC","username","password", SQL_CUR_USE_ODBC)
or die( "<strong>ERROR: Connection to my server failed</strong>" );

I would try the pdo but this needs some more installations i cannot do at the moment
Thank you again for your help!
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

Post by grezi »

Hi Hugh and first of all thank you very much for your help,

the iODBC is the name i used for defining the dsn i have created in the odbc.ini file which looks like this

[ODBC Data Sources]
iODBC = MS SQL Server
MSSQL = MAVE

[MSSQL]
Driver = /usr/local/lib/libtdsodbc.so
Description = MSSQL
Trace = No
Server = xxx.xx.xx.xx
Database = databaseName
Port = xxxx //the default is 1433
Password = my password


[iODBC]
Driver = some dirs/libtdsodbc.so
Description = Sample iODBC Server
Host = xxx.xx.xx.xx
Server = xxx.xx.xx.xx
#Address = xxx.xx.xx.xx
Port = xxxx
#TraceFile = stdout
TraceFile = o.trc
Trace = 1 ; set to 1 to enable tracing
#UID = i use here the username of the db login
#Password = password
#PWD = password
Database = the database name
ServerType = MS SQL Server
Debug = 1
DebugFile = odbcdebug.log

[Default]
Driver = some dirs /libtdsodbc.so


i have performed the test you recomended that is in the iodbc openlink website (http://www.iodbc.org/index.php?page=lan ... hp23039346)

and the result is the following

---------------------------------------------
Connecting...
Warning: odbc_connect() [function.odbc-connect]: SQL error: [FreeTDS][SQL Server]Read from SQL server failed., SQL state 08S01 in SQLConnect in ..... my website /odb.php on line 64

Uh-oh! Failure to connect to DSN [iODBC]:

--------------------------------------------------
for the error 08S01 microsoft says

The communication link between the driver and the data source to which the driver was attempting to connect failed before the function completed processing.

i say ?????????

I use iODBC driver manager and the actual driver is the FreeTDS driver.
The test tsql on command line is connecting without problems and i can also perform queries etc.... The problem comes when i try to use the function odbc_connect(). it is returning the error above. When i change the name of the DSN to something else i get the error

[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQL state IM002 in SQLConnect in .....

I used the trace file for iODBC and the result is the following:

** iODBC Trace file
** Trace started on Tue Apr 17 14:21:39 2007
** Driver Manager: 03.52.0507.0105


[000000.184459]
php 00000000 EXIT SQLConnect with return code -1 (SQL_ERROR)
SQLHDBC 0xa255090
SQLCHAR * 0xa25940c
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0xa2596ec
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x575258
SQLSMALLINT -3 (SQL_NTS)

[000000.184627]
php 00000000 ENTER SQLError
SQLHENV 0xa259988
SQLHDBC 0xa255090
SQLHSTMT 0x0 (SQL_NULL_HANDLE)
SQLCHAR * 0xbfd2364e
SQLINTEGER * 0xbfd23654
SQLCHAR * 0xbfd2344e
SQLINTEGER 511
SQLSMALLINT * 0xbfd2365a

[000000.184711]
php 00000000 EXIT SQLError with return code 0 (SQL_SUCCESS)
SQLHENV 0xa259988
SQLHDBC 0xa255090
SQLHSTMT 0x0 (SQL_NULL_HANDLE)
SQLCHAR * 0xbfd2364e
| 08S01 |
SQLINTEGER * 0xbfd23654 (20004)
SQLCHAR * 0xbfd2344e
| [FreeTDS][SQL Server]Read from SQL serve |
| r failed. |
SQLINTEGER 511
SQLSMALLINT * 0xbfd2365a (49)

[000000.184914]
php 00000000 ENTER SQLFreeConnect
SQLHDBC 0xa255090

[000000.184965]
php 00000000 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS)
SQLHDBC 0xa255090


i dont understand a thing. Thank you again for your help and if these details about my problem helped you understand anything PLEASE HELP.





Hugh wrote:Hi grezi,

You mention below that iODBC is an entry in your freetds.conf file, what exactly is the entry in this file and how is this associated with the SQLServer connection to be made ? The first param in the odbc_connect function should be that of the ODBC DSN (datasource) configured for connecting to the traget SQLServer instance and typically resides in the a file called odbc.ini which is located by the setting of the $ODBCINI environment variable. I am not sure which ODBC driver you are using, but have you verified with a simple odbc test program of some form (typically most driver have a test program for verifying basic connectivity) that the ODBC Driver itself can successfully connect to the database independant of PHP, as this is always wise test to perform to determine basic operability of the ODBC driver.

Note thier an odbc PHP HOWTO on the iODBC Web site you should read if not already done so, as this details how to make a basic PHP connection using iODBC:

http://www.iodbc.org/index.php?page=lan ... hp23039346

The key point being to have a working ODBC DSN in place, the HOWTO having been written and tested using the OpenLink ODBC Drivers (which includes one for SQLServer), but any other ODBC driver can be used also.

Hope this helps

Best Regards
Hugh Williams

grezi wrote:I have tested the connection with the use of tsql and the connection was succesful. I also performed a query and recieved the results, but when it comes to PHP code i always recieve this error. Is it mandatory to use the mssql_ functions or i can do it with odbc_ functions. I am completelly lost. Do you know what could be the error in the code i am using. the code is
$url = "my server url";

iODBC is the name of the settings in the freetds.conf file
// connect to database server
$db_conn = odbc_connect("iODBC","username","password", SQL_CUR_USE_ODBC)
or die( "<strong>ERROR: Connection to my server failed</strong>" );

I would try the pdo but this needs some more installations i cannot do at the moment
Thank you again for your help!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So this is getting a little clearer now. You can connect using the client on your machine at the command line. However, you cannot connect using the client through PHP?
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

Post by grezi »

Everah wrote:So this is getting a little clearer now. You can connect using the client on your machine at the command line. However, you cannot connect using the client through PHP?
Yes this is the problem : ) Do you think the installation of mssql_* functions would help the situation? I am not quite sure about that although i am at the moment expecting from the server admin to do this for me. I dont know if the php function would react like the tsql testing or it will give me the same error.
it is a little confusing situation.
grezi
Forum Newbie
Posts: 15
Joined: Tue Apr 03, 2007 5:25 am

Finally Problem solved!

Post by grezi »

For people who are reading this posts the solution to my problem was recompiling php with mssql_* functions support and FreeTDS with msdblib.
I didnt manage to make odbc_* functions to work but this doesnt mean that it doesnt actually work. If sombody is going to do something like this i recomend the use of the mssql_* functions support to be enabled. Thank you all for your support!
Post Reply