PHP  
downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | my php.net 
search for in the  
<odbc_commitodbc_cursor>
view the version of this page
Last updated: Thu, 21 Aug 2003

odbc_connect

(PHP 3>= 3.0.6, PHP 4 )

odbc_connect -- Connect to a datasource

Description

resource odbc_connect ( string dsn, string user, string password [, int cursor_type])

Returns an ODBC connection id or 0 (FALSE) on error.

The connection id returned by this functions is needed by other ODBC functions. You can have multiple connections open at once. The optional fourth parameter 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.

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.

The following constants are defined for cursortype:

  • SQL_CUR_USE_IF_NEEDED

  • SQL_CUR_USE_ODBC

  • SQL_CUR_USE_DRIVER

  • SQL_CUR_DEFAULT

For persistent connections see odbc_pconnect().



add a note add a note User Contributed Notes
odbc_connect
lffranco at dco.pemex.com
28-Aug-2003 08:08
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, whatever
2. You cannot make a system DSN with a UNC (\\Server\resource), so you must map the drive

Cute isn't it?

So... I quit on ODBC and went via ADO, this is the code that works:

=== CODE ===

$db = '\\\\server\\resource\\db.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("DRIVER={Driver do Microsoft Access (*.mdb)}; DBQ=$db");

// 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.

$sql = 'SELECT username FROM tblUsuarios';
$res = $conn->Execute($sql);
while (!$res->EOF)
{
    print $res->Fields['username']->Value . "<br>";
    $res->MoveNext();
}

$res->Close();
$conn->Close();
$res = null;
$conn = null;

=== /CODE ===
eric dot ramirez at iberoonline dot com
31-May-2003 12:44
Connecting with SQL in a ODBC source

2 ways, one is if your SQL server is runign in your machine

$ser="LOCALMACHINE"; #the name of the SQL Server
$db="mydatabase"; #the name of the database
$user="myusername"; #a valid username
$pass="my pass"; #a password for the username

# one line
$conn=odbc_connect("Driver={SQL Server};Server=".$ser.";Database=".$db,$user,$pass);
# one line

the second way is if the SQL Server is runing in other machine but in the same network

$ser="LOCALMACHINE"; #the name of the SQL Server
$db="mydatabase"; #the name of the database
$user="myusername"; #a valid username
$pass="my pass"; #a password for the username

#one line
$conn=odbc_connect("DRIVER=SQL Server;SERVER=".$ser.";UID=".$user.";PWD=".$pass.";
DATABASE=".$db.";
Address=".$ser.",1433","","");
#one line
richard at lordrich dot com
02-May-2003 06:07
Because the dsn needs to be system-wide, you will also need write access to the registry to set it up.
Yvan Ecarri
22-Mar-2003 07:01
I fighted with the "Data source name not found and no
default driver specified, SQL state IM002 in SQLConnect"
error for a while trying to connect via ODBC to a SQL Server
2000. Finally I found this workaround:

$cn = odbc_connect("Driver={SQL Server};Server=MyServer;Database=MyDatabase",
"MyUser","MyPassword")

Change "MyServer", "MyDatabase", "MyUser" and "MyPassword" to the right values.

I guess that adding the "Integrated Security=YES" will work too.

Regards,

Yvan Ecarri, MCDBA, MCSD
jeremy at austin.ibm.com
01-Mar-2003 06:20
here's a quick note about using php and db2 that cost me a couple of hours and several recompiles trying to figure out why it didn't work.

put the below line in any script

     putenv("DB2INSTANCE=db2inst1");

Or, set that in your webserver environment somehow.
sambou at everyonesports dot com
17-Dec-2002 06:11
If you encounter the error:

"[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.
oottavi at netcourrier dot com
07-Nov-2002 02:36
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)

Ex : Here is a connection to a DSN

putenv("SYBASE=c:\sybase");

$conn  =  odbc_connect("DSN1","USER","PASSWORD");
echo  "conn:  $conn";
if  ($conn  <=  0)  {
        echo  "Error  in  connection";
        exit;
} else  {
        echo  "<P>Connection  successful\n";
};
mortoray at ecircle-ag dot com
31-Oct-2002 08:31
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.
osiris at rich-howard dot co dot uk
25-Jul-2002 12:34
Thought I'd add a note here on this. I'm using Apache 2.0.39 on Windows XP and PHP 4.2.2.

It helps a lot if you don't use capital letters in your dsn string.

Thought I also comment on the posts about using system dsns over file dsns. There are lots of posts saying use systems not files, but none (that I have seen) which explain why.

Essentially: File DSNs are specific to the current user, therefore the Internet Guest User Account doesn't have rights to them. Systems are available to everyone.

Regards

Osiris :)
d-m at eudoramail dot com
06-Feb-2002 01:40
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
odbc_connect ERRO at DB2
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

To Solve the problem with DB2 + PHP folow this steps!

INSTALL THE PROGRAM LIKE THIS!

-- DB2 --
Install the DataBank
Install Application Tools
-- END DB2 --

--- APACHE ---
cd ../mod_ssl-2.8.5-1.3.22/
./configure  --enable-module=so --with-apache=../apache_1.3.22/  --with-ssl=../openssl-0.9.6c/
cd ../apache_1.3.22/
make
make certificate TYPE=custom
make install
--- END APACHE ---

-- PHP --
cd ../php-4.1.1/
./configure  --with-apxs=/usr/local/apache/bin/apxs --with-pgsql --with-mysql --with-ibm-db2=/usr/IBMdb2/V7.1
make
make install
-- END PHP ---

-- LIB --
vim /etc/ld.so.conf
add line: /usr/IBMdb2/V7.1/lib
execute: ldconfig
-- END LIB --

To Solve the error ODBC_CONNECT exec the db2profile at the apachectl!!! Like this!

-- APACHE EDIT TO RUN DB2 --
vim /usr/local/apache/bin/apachectl
add line: . /usr/home/db2inst1/sqllib/db2profile
-- END APACHE EDIT TO RUN DB2 --

NOW run /usr/local/apache/bin/apachectl startssl

DONE !!!!!

You have a DB2 + APACHE + SSL + PHP + MYSQL + POSTGRES .

Enjoy

[]愀
Helio Ferenhof
whuang at cadvision dot com
19-Jan-2002 10:32
If you (still) get that annoying error like and you're using Access:

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).

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.

-=WH=-
bill at ergoitsolutions dot com
15-Dec-2001 02:36
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.php
Had 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 /
Mahmoud at iastate dot edu
30-Nov-2001 04:35
WINNT 4 Workstation, PHP4

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.
lomaky at yahoo dot com
24-Nov-2001 06:30
// simple conection


$cnx = odbc_connect('cliente','Administrador',''); 
//query
$SQL_Exec_String =  "select * from Clientes";   
//ejecucion query
$cur= odbc_exec( $cnx, $SQL_Exec_String ); 
echo  "<table border=1><tr><th>Dni</th><th>Nombre</th>". 
         "<th>codigo</th><th>ciudad</th></tr>\n"; 
    while( odbc_fetch_row( $cur ) ) { 
       $Dni= odbc_result( $cur, 1 ); 
       $Nombre= odbc_result( $cur, 2 ); 
       $codigo= odbc_result( $cur, 3 );
       $ciudad= odbc_result( $cur, 4 );
        echo  "<tr><td>$Dni</td><td>$Nombre</td>". 
             "<td>$codigo</td><td>$ciudad</td></tr>\n"; 
    } 
    echo  "</table>";
root at mediamonks dot net
19-Nov-2001 08:03
Due to multiple requests, more for DSN-less connections:

$db_connection = new COM("ADODB.Connection");

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("../databases/database.mdb") ." ;DefaultDir=". realpath("../databases");
$db_connection->open($db_connstr);
$rs = $db_connection->execute("SELECT * FROM Table");
$rs_fld0 = $rs->Fields(0);
$rs_fld1 = $rs->Fields(1);
while (!$rs->EOF) {
  print "$rs_fld0->value $rs_fld1->value\n";
  $rs->MoveNext(); /* updates fields! */
}
$rs->Close();
$db_connection->Close();

(Prints first 2 columns for each row.)
root at mediamonks dot net
11-Oct-2001 02:40
To open a DSN-less connection on Win32, you can use this:

$db_connection = new COM("ADODB.Connection");

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . realpath("../databases/database.mdb") . " ;DefaultDir=" . realpath("../databases");

$db_connection->open($db_connstr);

$rs = $db_connection->execute("SELECT * FROM Table");
cs at coolspot dot de
11-Jul-2001 03:01
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!

Hope this helps anybody who must connect to db2 ;)
ckelly at powerup dot com dot au
13-Jun-2001 11:42
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 .
cpoirier at shelluser dot net
10-Mar-2001 05:26
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.
fc99 at smm dot de
27-Jan-2001 10:23
If you don't want to specify your login credentials on your web server, you can leave the login fields blank to use the integrated windows security like here:

odbc_connect("DSN=DataSource","","");

Make sure you have switched your system dsn to integrated security, too !

(works on windows machines only, of course)

flo.
SilencerX at optidynamic dot com
27-Jan-2001 07:31
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.
garretg at otable dot com
03-Jan-2001 06:03
If you're connecting to a SQL server database through ODBC, you must set the default database of the ODBC DSN to the database you want to use.

There is no way to specify the database name in odbc_connect or odbc_pconnect, just the DSN name, username, and password.
phobo_AT_paradise.net.nz
02-Nov-2000 10:01
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... :)

Siggy
mario at bntrc dot com
02-Jul-2000 01:00
i'm using Apache/1.3.12 (Win32) with php4 module to get data from sql server7.0 via odbc.. and it's works ! Thanks very much.. Hope this script help the others as i were helped =)
<pre>
&lt;?php
define (NL,"\n");
$cx=odbc_pconnect("php","sa","","");
$cur=odbc_exec($cx,"select id,nama from php");
echo "DATA FROM SQL SERVER WITH PHP4".NL;
echo "<table border=1 align=center>".NL;
echo "<tr><td>ID</td>".NL."<td>Pass</td></tr>";
while(odbc_fetch_row($cur)){
echo "<tr>".NL;
$id=odbc_result($cur,1);
$pass=odbc_result($cur,2);
echo "<td>".NL;
echo "$id".NL;
echo "</td>".NL;
echo "<td>".NL;
echo "$pass".NL;
echo "</td>".NL;
echo "</tr>".NL;
echo "</td>".NL;
}
echo "</table>".NL;
?>
</pre>
侵O?/span>
cnewbill at onewest dot net
18-May-2000 07:55
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.
dipakp at atinet dot com
09-Mar-2000 12:43
OpenRDA ODBC from http://www.odbcsdk.com allows you to access MS Access, SQL Server and other PC databases from LINUX and many other UNIX platforms.

<odbc_commitodbc_cursor>
 Last updated: Thu, 21 Aug 2003
show source | credits | sitemap | mirror sites 
Copyright © 2001-2003 The PHP Group
All rights reserved.
This mirror generously provided by: http://php.mirrors.ilisys.com.au/
Last updated: Sat 01 Nov 2003 04:13:36 EST EST