PHP TOP

odbc_connect

(PHP 4, PHP 5, PHP 7, PHP 8)

odbc_connect — データソースに接続する

説明

odbc_connect(
    string $dsn,
    string $user,
    string $password,
    int $cursor_option = SQL_CUR_USE_DRIVER
): resource|false

他の ODBC 関数を使用するには、この関数が返す接続 ID が必要となります。異なる db や異なる権限を使用する限り、複数の接続を同時にオープンすることができます。

いくつかの ODBC ドライバでは、複雑なストアド・プロシージャの実行時に次のようなエラーにより失敗する可能性があります。 "Cannot open a cursor on a stored procedure that has anything other than a single select statement in it" SQL_CUR_USE_ODBC を使用することにより、このようなエラーを回避できる可能性があります。また、いくつかのドライバは odbc_fetch_row() においてオプションの row_number パラメータをサポートしません。この場合でも、SQL_CUR_USE_ODBC により解決できる可能性があります。

パラメータ

dsn

接続に使用するデータベースソース名。あるいは、DSNではない接続文字列を使用することもできます。

user

ユーザー名。

password

パスワード。

cursor_option

この接続で使用するカーソルの型。通常はこのパラメータは必要ありませんが、いくつかの ODBC ドライバの問題に対処する際には有用です。

次のような定数がカーソル型として定義されています。

SQL_CUR_USE_IF_NEEDED
SQL_CUR_USE_ODBC
SQL_CUR_USE_DRIVER

戻り値

ODBC 接続を返します。失敗した場合に false を返します

例

例1 DSN なしの接続

<?php
// Microsoft SQL Server using the SQL Native Client 10.0 ODBC Driver - allows connection to SQL 7, 2000, 2005 and 2008
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);

// Microsoft Access
$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdbFilename", $user, $password);

// Microsoft Excel
$excelFile = realpath('C:/ExcelData.xls');
$excelDir = dirname($excelFile);
$connection = odbc_connect("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=$excelFile;DefaultDir=$excelDir" , '', '');
?>

参考

持続的な接続の場合: odbc_pconnect() - 持続的なデータベース接続を開く

Found A Problem?

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 30 notes

down

fr at freedom2ct dot com ¶

5 years ago

Under Windows odbc_connect (and PDO ODBC) apparently uses the Windows locale to handle input and output from e.g. MS access and MS SQL Server databases.

This causes lots of headaches if one is using other than ASCII characters.

Work-round solutions like utf8_encode and mb_convert both fails.

The solution I fund working is to perform the following changes to Windows

Control Panel > Region > Administrative > Change system locale
>Check '<i>Use Unicode UTF-8 for worldwide language support.</i>'

down

nunya at damn dot biz ¶

4 years ago

One additional note regarding odbc_pconnect  and possibly other variations of pconnect:

If the connection encounters an error (bad SQL, incorrect request, etc), that error will return with  be present in odbc_errormsg for every subsequent action on that connection, even if subsequent actions don't cause another error.

For example:

A script connects with odbc_pconnect.
The connection is created on it's first use.
The script calls a query "Select * FROM Table1".
Table1 doesn't exist and odbc_errormsg contains that error.

Later(days, perhaps), a different script is called using the same parameters to odbc_pconnect.
The connection already exists, so it is reused.
The script calls a query "Select * FROM Table0".
The query runs fine, but odbc_errormsg still returns the error about Table1 not existing.

I'm not seeing a way to clear that error using odbc_ functions, so keep your eyes open for this gotcha or use odbc_connect instead.

down

webmaster at dzconnexion dot com ¶

19 years ago

Concerning the note posted by Grisu on the 23-Dec-2003 11:51: Connect to an MS-Access Database on the Network via ODBC,

PLEASE dont forget to put double slashes as follows:

"\\\\Server\\folder\\database.mdb" 

when setting up the registry key as indicated...

down

Mahmoud at iastate dot edu ¶

23 years ago

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.

down

lffranco at dco.pemex.com ¶

21 years ago

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 ===

down

Shyam Hazari ¶

15 years ago

Here is my successful odbc_connect with mysql on Ubuntu. It took me a while to figure this out. 

Installed following packages using apt-get.

apache2
apache2-mpm-prefork
apache2-utils
apache2.2-common
libapache2-mod-php5
libdbd-mysql-perl
libmyodbc
libmysqlclient15off
mysql-client-5.0
mysql-common
mysql-server-5.0
mysql-server-core-5.0
odbcinst1debian1
php5
php5-cli
php5-common
php5-odbc
unixodbc

/etc/odbc.ini
------------
myodbc3      = MySQL ODBC 3.51 Driver

[myodbc3]
Driver         = /usr/lib/odbc/libmyodbc.so
Description  = MySQL ODBC 3.51 Driver
Server        = localhost
Port           = 3306
User           = shyam
Password    = mypass
Database    = mysql
Option        = 3   
Socket       = /var/run/mysqld/mysqld.sock

/etc/odbcinst.ini
----------------
[MySQL ODBC 3.51 Driver]
Description    = MySQL driver
Driver        = /usr/lib/odbc/libmyodbc.so
Setup        = /usr/lib/odbc/libodbcmyS.so
CPTimeout    = 
CPReuse    = 
UsageCount    = 1

my php script
------------

<html>
<body>
<?
$conn = odbc_connect("DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=mysql", "shyam", "mypass");

$sql = "SELECT user from user";
$rs = odbc_exec($conn,$sql);
echo "<table><tr>";
echo "<th>User Name</th></tr>";
while (odbc_fetch_row($rs))
 {
 $user = odbc_result($rs,"user");
 echo "<tr><td>$user</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>

</body>
</html>

down

-2

simonr at no2sp at m dot cogapp dot com ¶

20 years ago

To make a DSN-less connection using ODBC to MS-SQL:



<?php



$connection_string = 'DRIVER={SQL Server};SERVER=<servername>;DATABASE=<databasename>';



$user = 'username';

$pass = 'password';



$connection = odbc_connect( $connection_string, $user, $pass );



?>



servername is the name of the database server



databasename is the name of the database



Note, I've only tried this from a windows box using the Microsoft ODBC drivers.

down

-2

drew at pixelburn dot net ¶

15 years ago

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

This is where the 32 bit odbc manager is:

C:\Windows\SysWOW64\odbcad32.exe

down

-1

cs at coolspot dot de ¶

23 years ago

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

down

-2

Anonymous ¶

16 years ago

This might be obvious to some, but here is a quick tidbit that might save you some time if you're using FreeTDS in Linux:

Be sure that you have these two lines in freetds.conf:
dump file = /tmp/freetds.log
dump file append = yes

so you can tail -f it in the background of debugging the problem.  This helped me find my issue on on CentOS Linux:  
 
1) tsql test works
2) isql test works
3) odbc connection in php also works WHEN RUN FROM THE SHELL
4) running PHP through apache does NOT work.

my /tmp/freetds.log file told me: 
net.c:168:Connecting to MYDBSERVER port MYDBPORT
net.c:237:tds_open_socket: MYDBSERVER:MYDBPORT: Permission denied

and the answer was my firewall/SELinux was denying the Apache processes access to connect to the remote MSSQL DB port, but my shell accounts were fine.

down

-2

lomaky at yahoo dot com ¶

23 years ago

// 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>";

down

-3

aamaral at 0kbps dot net ¶

19 years ago

Two additional notes regarding ODBC connections to a Network Sybase SQL Anywhere 8 Server..

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.

Here's an example:

<?php
  $conn=odbc_connect($connect_string,'','');
  $result=odbc_exec($qry,$conn);  //returns data
  sleep(31);
  $result=odbc_exec($qry,$conn);  //"Authentication Violation"
?>

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

down

-2

Ceeclipse ¶

16 years ago

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

Keep in mind that the following code in PHP5 will not work properly:

<?php

if( odbc_connect("test", "test", "test") === false ) {
    // Your error reporting/handling here..
}

?>

odbc_connect() returns an integer, and not a PHP5 boolean!

down

-5

Abhinav ¶

12 years ago

Pls ensure that the MSAccess database format is ".mdb".
If it is ".accdb" it will not work!

down

-5

Grisu ¶

21 years ago

Connect to an MS-Access Database on the Network via ODBC

Apache 2.0.47 with PHP 4.3.4 running on Windows XP Pro

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

Funny enough all this is not necessary on Win98.

down

-5

aamaral at 0kbps dot net ¶

19 years ago

To connect to Sybase SQL Server Anywhere 8.0 on Windows use the following:

<?php
//================================================================

  // Configure connection parameters
  $db_host        = "server.mynetwork";
  $db_server_name = "Dev_Server";
  $db_name        = "Dev_Data";
  $db_file        = 'c:\dbstorage\dev.db';
  $db_conn_name   = "php_script";
  $db_user        = "dbuser";
  $db_pass        = "dbpass";

//================================================================
  $connect_string = "Driver={Adaptive Server Anywhere 8.0};".
                    "CommLinks=tcpip(Host=$db_host);".
                    "ServerName=$db_server_name;".
                    "DatabaseName=$db_name;".
                    "DatabaseFile=$db_file;".
                    "ConnectionName=$db_conn_name;".
                    "uid=$db_user;pwd=$db_pass";

  // Connect to DB
  $conn = odbc_connect($connect_string,'','');

  // Query
  $qry = "SELECT * FROM my_table";

  // Get Result
  $result = odbc_exec($conn,$qry);

  // Get Data From Result
  while ($data[] = odbc_fetch_array($result));

  // Free Result
  odbc_free_result($result);

  // Close Connection
  odbc_close($conn);

  // Show data
  print_r($data);

//================================================================
?>

down

-3

phobo_AT_paradise.net.nz ¶

24 years ago

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

Atelier ”慶”

WEBクリエイター歴２０年、最先端技術と安心をお届けします。

PHPのお勉強！

odbc_connect

説明

パラメータ

戻り値

例

参考

Found A Problem?

User Contributed Notes 30 notes