6

I'm stuck with this problem for a while and I just can't get any further, I did a lot of searches but nothing works... I'm trying to connect to a Microsoft SQL Database with php using odbc.

Everything is set up, as follows (the values between "" are correct in the file):

/etc/odbc.ini:

[CRMCONNECT]
Description = "CRMConnect"
Driver = FreeTDS
Trace = No
Servername = CRMSERVER
Database = "dbname"
UserName = "username"
Password = "password"

[Default]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

/etc/odbcinst.ini:

[FreeTDS]
Description = tdsodbc
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout       = 5
CPReuse         = 5
FileUsage       = 1

/etc/freetds/freetds.conf:

[CRMSERVER]
host = xxx.xxx.xxx.xxx 
port = 1433
tds version = 8.0

I doublechecked the host many times and it is correct. I also tried tds version 7.0, but no luck.

I can succesfully connect to the server with isql:

root@crmart-web004:/# isql -v CRMCONNECT "user" "pass"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

But with php I just can't get it working, I get the following error:

[unixODBC][Driver Manager]Data source name not found, and no default driver specified

My connectionstring:

$connection = odbc_connect("Driver={CRMCONNECT};Server=xxx.xxx.xxx.xxx;Database=dbname;","username","password");

All parameters are doublechecked and are correct.

How come I can successfully connect with isql but it fails in php?

My php version: 

PHP Version 5.4.4-14+deb7u5

odbcinst configuration:

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I hope someone has an idea what could be wrong.

Thanks in advance

Regards

UPDATE:

I changed my connectionstring to:

$connection = odbc_connect("CRMCONNECT;Database=dbname;","user","pass");

which is resulting in another error: 

[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

I'll be looking into that, thanks again vinodadhikary

Regards.

UPDATE 2:

My connection string was wrong, it should have been:

$connection = odbc_connect("CRMCONNECT","user","pass");

Thanks vinodadhikary!

Regards and happy holidays.

peird
  • 71
  • 1
  • 1
  • 7

3 Answers3

6

Since you already have CRMCONNECT DSN defined, you could use the following connection method: 

$connection = odbc_connect("CRMCONNECT","username","password");

Also in your connection string you have Driver={CRMCONNECT};. CRMCONNECT as you've defined is not a driver, it is a Data Source Name. The driver in your case would be FreeTDS

vee
  • 38,255
  • 7
  • 74
  • 78
  • Thanks for your answer, a shame that I didn't realize I was defining a Data Source instead of a driver. I changed the connectionstring and got a new error. At least I'm glad I see another error now :-) I'll be looking into that. – peird Dec 23 '13 at 08:52
  • Try removing `;Database=dbname;` from your connection string and just keep it exactly like how I have in my answer. You don't need to pass in Database name because the ODBC already has the `Database` option defined. – vee Dec 23 '13 at 08:56
1

I had the same error in Laravel 5.5 with PHP 7.1.9 on Debian 7.11. Fixed it by removing curly braces from driver's name in DSN:

Failed: "odbc:Driver={fail};Server=host;Database=db;"

Worked: "odbc:Driver=success;Server=host;Database=db;"

Another working option was moving data source configuration from this string into odbc.ini and then referencing it: "odbc:odbc_ini_data_source_name"

The funny thing is that the same data source with Driver={SQL Server} (with curly braces) worked correctly under Windows 10.

o.v
  • 835
  • 10
  • 15
  • Thanks for this. However, on one system the string with `{}` worked where as on a different system, the one without the curly braces has worked. – i_v_harish Feb 23 '18 at 11:09
  • @i_v_harish can you tell the difference between those systems? – o.v Feb 24 '18 at 21:50
0

The same applies for PDO. $conn = new PDO("odbc:CRMCONNECT"); and be sure to set your default driver in odbc.ini

[Default] Driver = IBM i Access ODBC Driver 64-bit in our case

Thomas Bennett
  • 647
  • 6
  • 10