odbc_connect

Description

The connection id returned by this functions is needed by other
ODBC functions. You can have multiple connections open at once as long as
they either use different db or different credentials.

With some ODBC drivers, executing a complex stored procedure may
fail with an error similar to: "Cannot open a cursor on a stored
procedure that has anything other than a single select statement
in it". Using SQL_CUR_USE_ODBC may avoid that error. Also, some
drivers don't support the optional row_number parameter in
odbc_fetch_row(). SQL_CUR_USE_ODBC might help
in that case, too.

Parameters

dsn

The database source name for the connection. Alternatively, a
DSN-less connection string can be used.

user

The username.

password

The password.

cursor_type

This sets the type of cursor to be used
for this connection. This parameter is not normally needed, but
can be useful for working around problems with some ODBC drivers.

User Contributed Notes 63 notes

- Map the Network Drive where the mdb is located- Setup System DSN in Control Panel with mapped Drive- Open Registry at:HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC_INI- Edit the for example "M:\" to "\\server\..."- Close Regedit- The Apache-Service must run with a Domain (network)-User!!- After that you can connect using:

Following simonr at no2sp at m dot cogapp dot com contribution (thank you), I tried to connect to a local MS SQL Server 2014 Express database by creating a DSN-less connection using ODBC. It worked, here's the code.

I wrote a script using the PHP5 CLI binary that monitors a directory for changes, then updates a Network Server SQL Anywhere 8 database when a change was detected. Idealy, my program would run indefinately, and issue odbc_connect()/odbc_close() when appropriate. However, it seems that once connected, your odbc session is limited to 30 seconds of active time, after which, the connection becomes stale, and no further queries can be executed. Instead, it returns a generic "Authentication violation" error from the odbc driver.

Additionally, it seems that odbc_close() doesn't truely close the connection (at least not using Network SQL Anywhere 8). The resource is no longer usable after the odbc_close() is issued, but as far as the server is concerned, there is still a connection present. The connection doesn't truely close until after the php script has ended, which is unfortunate, because a subsequent odbc_connect() commands appear to reuse the existing stale connection, which was supposedly closed.

My workaround was to design my script exit entirely after a the database update had completed. I then called my script whithin a batch file and put it inside an endless loop.

I'm not sure if this is a bug with PHP or what, but I thought I'd share in case someone else is pulling their hair out trying to figure this one out...

This is the typical solution with the steps to follow if someone wants to connect MS Access to PHP. it took me a couple of hours actually till i reached it. i just wanted to ease the hassle for my colleagues in order not to waste their time as i did. this is the duty of every programmer towards his/her peers :p here you are the CAKE :)

<?php

// to have this working:// 1- u have first to creat ur access database using MS // Access (i asume u know how to do this). My database // that i used in my example is called "Questionaire.mdb". // the table in it is called "Results"//// 2- then u have to add this database to ODBC in the// control panel.//// 3- the adition happens by adding "MS Access Driver" to the // "System DNS" tab in ODBC Data Source Administrator. if // u have that "MS Access Driver" in "User DNS" tab, then // u have to delete it.//// 4- click on Add in the "System DNS" tab.//// 5- choose "MS Access Driver" from the "Creat New // Database Source" window and click finish. //// 6- then the "ODBC MS Access Setup" window will pop-up.//// 7- give the driver the name that you want to use in your // PHP scripting. i used here "MSAccessDriver".

If you encounter the error"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect"

you should make sure to have the following done:

The ODBC-link must be a System-DNS and not a User-DNS. Configure your ODBC-link and then modify your configuration with regedt32. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC_INI and open your ODBC-link. The field DBQ contains the path to your database. This path must be without Drive-names (e. g. "M:") so change it to "\\Server\folder\database.mdb". This setting is changed each time you modify your ODBC-configuration using the Windows-tool, so make sure you do this afterwards.

Then you go to the Services-Section in your Systemmanagement. Select the properties of your Apache module. In the login-section you have to make sure you login with a valid User-Account for your Network-Server.

Please note that this way you still have no permission to access linked tables within the linked database

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect"

Windows with PHP, running under IIS/PWS, PHP runs under the anonymous user, INET_USR_"server" (were "server" is your servername).This user has no read access in the ODBC System DSN tree in the registry.

With regedt32 open HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC_INI and give read access to every ODBC entry you want to use with PHP.

Beware: With every change to a ODBC system DSN, the rights on that ODBC system DSN are gone again, and you have to change the rights again manualy.

Under Apache, PHP runs under the System account and you wont have this problem.

As always Microsoft is clueless... I've been trying to connect to an Access database on a W2K on the network (not a local file, but mapped on the V: drive), via ODBC.

All I got is this message:Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides., SQL state S1009 in SQLConnect in d:\apache\cm\creaart.php on line 13

So... I started looking al around and looks like the ODBC driver has some severe problems:

1. It cannot access a Access database via a mapped drive. And this is for ANY application, name it PHP, Coldfusion, whatever2. You cannot make a system DSN with a UNC (\\Server\resource), so you must map the drive

// Driver do Microsoft Access (*.mdb)// must be the name in your odbc drivers, the one you get// from the Data Sources (ODBC).// In this case, I'm in Mexico but the driver name is in portuguese, thanks Microsoft.

I fighted with the "Data source name not found and nodefault driver specified, SQL state IM002 in SQLConnect"error for a while trying to connect via ODBC to a SQL Server2000. Finally I found this workaround:

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect"

make sure you have the correct permission to your database file (e.g. if using Win2k, might want to set the "Everyone" group to "Full Control"). For Windows, I find that I have to sometimes use the registry editor (e.g. RegEdt32.exe) to set the database file's permission because for some unknown reason, setting the permission from the file's "Properties" option does not work.

If you have problem to connect to sybase with an ODBC driver, try to set up your SYBASE environment variable to the correct directory. ([ODBC SQL Server driver]Allocation of a Sybase Open Client Context failed)

If you have switched to a new Version of PHP (from 4.1 to 4.3) and at the same time have upgraded your Apache server (from 1.x to 2.x) and suddenly get the error:

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect"

It may be because you have your ODBC connections listed (Control Panel | ODBC) as User DSN rather than System DSN. They need to be System DSN in order for the PHP in the Apache service to access to them.

MSaccess DSN(Microsoft Jet engine couldn't open the database 'Unknow'.Another user is using it exclusively, or you dont have permission to useit).

Make sure your access *.mdb file is not on a network drive. Put it on C: or D: disable all security first so you can test the connection. Once you can verify that you can connect add appropriate passwords, group access, etc.

odbc connect to Oracle 8.0.xxx / NT4 / IIS4 / php.exe (4.1.0)
had a lot of trouble connecting kept receiving the 12154 TNS error.
Found a really useful hint in a mail msg on phpbuilder. http://www.phpbuilder.com/mail/php-db/2001051/0192.phpHad to strip the <cr>'s out of both sqlnet.ora and tnsnames.ora to get a connection established. Also had trouble in php.ini need to
fully qualify extension_dir on NT if you leave the last \ on the dir
name it is replaced with a /

odbc_connect() kept giving me weird errors when trying to connect to a MSaccess DSN(Microsoft Jet engine couldn't open the database 'Unknow'. Another user is using it exclusively, or you dont have permission to use it).

After going nuts for a while, I realized that my database name had a space in it (course surveys.mdb), I shortened the name to eliminate the space .. and everything worked fine.

We've tried hard to connect from php to our IBM DB2 RS/6000 Server. It worked after we compiled with --ibm-db2= option, but it was unbelievable
slow.

No, just testing some options, we found out that it went from very slow (getting 100 records lasts 1 till 10 seconds) to fast access (almost same speed as with using JDBC from Servlets) to 0.2 till 0.3 seconds.

We simply added the optional parameter Cursortype to odbc_connect, and with the cursortype SQL_CUR_USE_ODBC it changed in that way!

To connect to a PROGRESS database using ODBC you must have SQL_CUR_USE_ODBC as the 4th parameter eg odbc_connect(DSN,uname,password,SQL_CUR_USE_ODBC ) otherwise you can pass queries but no results are ever returned .

After much testing, and I think supported by a comment I found in the code, I have come to a disturbing conclusion: odbc_connect() in PHP4.04pl1 is really an odbc_pconnect(), with all the implications for transaction scoping. Specifically, each time you call odbc_connect( "X", "" "" ), you will get the same physical ODBC Connection, and odbc_commit() and odbc_rollback() will affect all copies. The only solution I could find was to use several different DSNs to access the database.

If like me you are using openlink from unix to access an MS Access database on an NT/Win2k machine and find out that your INSERT queries don't do anything and don't report any errors, use odbc_pconnect().

I couldn't understand what was going on and after a bit of research I found out that with MySQL they recommended using mysql_pconnect() for INSERT queries. I tried the same thing with odbc and it worked.

If using Openlink to connect to a Microsoft Access database, you will most likely fine tha odbd_connect() works fine, but discover that ANY query will produce odd results; with SELECT queries failing with "[OpenLink][ODBC][Driver]Driver not capable, SQL state
S1C00 in SQLExecDirect in xxxx.php on line xx" and INSERT / DELETE queries warning "No tuples available at this result index".

In this case, use the SQL_CUR_USE_ODBC cursor!

This had me stumped for quite some time; because it was the odbc_exec() which was seemingly at fault... :)

Alot of people share the same kind of problems getting this setup on linux. I was assigned this problem 2 days ago and I was successful. My combination was PHP4 RC2, Easysoft OOB, and unixODBC. These three products work very well together and are real easy to install. More info http://www.easysoft.com/products/oob/main.phtml. ps also works good with Perl's DBI.

After doing "harald dot angel at gmail dot com" suggestion, you may still receive this error:

"Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data., SQL state S1000 in SQLConnect in... "

You may need to include the <computer name> of the machine where the ODBC is, to the <local group> of the machine where the *.mdb is stored. And make sure that the <local group> has enough permission to access the *.mdb.

I also had a problem with this message: ([ODBC SQL Server driver]Allocation of a Sybase Open Client Context failed). The message only appeared after the server had run for a few hours, so I expected resource starvation. However it was a settings problem and I thought this might benefit others too.

In addition to putting a <?php putenv ("SYBASE=c:\sybase"); ?> as described by oottavi above, I also had so specify a locale by setting <?php putenv ("LC_ALL=default"); ?>.

Even though my locale was already set to a valid one (en_US) I had to set this environment variable to make it work anyhow.

For three days I fought to be able to connect our Linux intranet server to our AS400 database through ODBC and PHP on Mandrake. I installed everything I thought would work but I still got a: odbc_connect(): SQL error: Missing server name, port, or database name in call to CC_connect., SQL state IM002 in SQLConnect

Note that isql was working great but php was failing to connect.

The solution:I located and found a PHP module called php-unixODBC (to oppose with php-odbc). Once installed (even though it wasn't for the right version of PHP), I realised it didn't place the file properly. The ini file was in /etc/php/ instead of /etc/php.d/, so I moved it there and renamed the old /etc/php.d/36_odbc.ini to /etc/php.d/36_odbc.ini.sav, so that I now had /etc/php.d/36_unixodbc.ini. I restarted the httpd server and now I was able to access the as400.

If you have questions, email _artaxerxes2_at_iname_dot_com (sans the underscore).

Connecting to ADS (Advantage Database Server) using Windows. When you setup data source in ODBC Manager (PHP_SERVER) don't use a mapped drive in Database or Data Dictionary Path, or you cannot connect:

Lets suppose that you share C:\ADS_SERVER\ADS as ADS.Mapping to a drive X: in PHP_SERVER

Instead of:

X:\APP\DATA\APP.ADD

use UNC:

\\ADS_SERVER\ADS\APP\DATA\APP.ADD

if the ADS ODBC dialog don´t let you to browse a Network Drive type it manually

A VERY IMPORTANT NOTE OF CAUTION FOR WINDOWS USERS DEVELOPING ON NON-NETWORKED SYSTEMS

If like me you are developing on a stand-alone system (Windows XP professional running IIS). Make sure that the the folder your database resides in is shared, otherwise you will get the following type of message:

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.'

and you will spend 2 days (as I did) looking for the right combination of settings to write the record properly.

I was having trouble connecting to a MSSQL database using FreeTDS and a DSN string with the error message: "Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect"

The problem was with specifying the driver. Many examples show the driver name surrounded by { }

The solution was to remove the braces.

Example:<?php$user = "username";$pass = "password";// Some examples show "Driver={FreeTDS};" but this will not work$dsn = "Driver=FreeTDS;Server=some.server.com;Port=1433;Database=mydatabase;";$cx = odbc_connect($dsn,$user,$pass);// Get the error messageif($cx === false) { throw new ErrorExcpetion(odbc_errormsg());}?>

I found that on Windows I am able to connect to a FoxPro free table directory on a password-protected mapped network drive without having a domain controller (as posted in the solution below from harald).Trying to connect to the mapped drive returned the error "tablename does not exist." The same code worked correctly if the ODBC resource was located locally on my machine.

As a workaround, I specified the datasource explicitly in the connection, but pointed the source at a shortcut to the same mapped directory:<?php$dsn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\\shortcut;Exclusive=NO;collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";$conn=odbc_connect($dsn,"","");?>and the connection completed. My guess is that the credentials needed to access the drive weren't accessible to apache (I tried changing the user apache ran as to no avail), and the shortcut put that responsibility on Windows.

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

If you keep running into this on the 64 bit versions of windows, ie server 2008, and none of the other solutions helped.

In a 64 bit windows server operating system, there are TWO odbc managers. When you pull up the usual menu for the odbc / dsn system, it is for the 64 bit odbc manager, and 32 bit applications (vb 6.0, PHP 5) will not work using these dsn's.