Функции ODBC
Be carefull. If the table you’re inserting into has a trigger that also inserts into another table that has an identity column you’ll get the key of that other table! use scope_identity() instead of @@identity
I searched for the solution of why odbc connection of a network remote drive under Windows + Apache 2.0.X, cannot give the query, but seems no one provides the solution.
In fact, it is very simple.
Go to Control Panal -> Services;
Find and double click «Apache2»;
In the page of «Log On», choose Log on as «This account» and give an account in the web server system which have the right to control the network remote drive;
Finally, restart Apache, and that’s it.
On my search for a function to retriew the NewID of an inserted row wich has an autoincrement I found this solution like the mysql_insert_id for an ODBC connection to MS-Access :
// make your connection below
$Connection = odbc_connect(. );
$Result = odbc_exec($Connection, «select @@identity»);
$NewID = odbc_result($Result, 1);
odbc_free_result($Result);
// make here all what you want with the NewID
In my mind this should also work with MS-SQL-Server and with Sybase — via ODBC and direct (mssql_. /sybase_. ).
Ever wonder why you’re experiencing really slow data retrieval times using IBM DB2 Universal Database for Linux, UNIX, and Windows? The default cursor type used by Unified ODBC is not supported by DB2, so it gets downgraded to a forward-only cursor — and that negotiation occurs with every row fetch.
One way to force your PHP applications to use forward-only cursors is to modify your DB2 client configuration with a handy CLI patch2 setting value of 6:
$ db2 UPDATE CLI CONFIGURATION FOR SECTION dbname USING patch2 6
You have to update this client setting on the same machine on which you are running the PHP application. This works on Windows operating systems as well as on Linux & UNIX operating systems.
I ran a few basic benchmarks (fetch 10,000 rows consisting of 3 INTEGER columns from a remote database server) and concluded that this setting can make a major difference to your application speed:
Without CLI patch2 setting: ~22 seconds
With CLI patch2 setting: ~ 1.75 seconds
Note that the drawback of using this patch setting (or any other method of using forward-only cursors) makes odbc_num_rows() always return «-1» for the number of rows affected by a SELECT statement.
If the bundled ODBC library stumbles on some field formats (like some REAL from Pervasive.SQL), have a look at http://odbtp.sourceforge.net/.
After many headaches, I have adoped odbtp: it’s a very solid library and best of all it’s not tied to a particular OS.
I found this to be a perfect alternative to the MaxDB special drivers of version 7.5.00. Just weren’t that easy to install on *nix. Windows seems fine. Anyway The ODBC is a perfect alternative for connecting the SAPDB/MaxDB towards PHP.
Installation guide for the odbc alternative (instead of the MAXDB-php driver) can be found here:
Windows 64 Caveats with ODBC
I’m pretty sure PHP only accesses the 32-bit ODBC stuff on Windows 64. Therefore, you will need to configure your ODBC DSNs via the Data Source Administrator found at:
If you receive an error stating «Connection is busy with results for another hstmt, SQL state S1000 in SQLExecDirect» try opening your odbc connection using the SQL_CURSOR_FORWARD_ONLY option
$db_link = odbc_connect ( $dsn , $username , $password , SQL_CURSOR_FORWARD_ONLY )
or die( ‘Error connecting to server. Server says: ‘ . htmlspecialchars ( odbc_errormsg ()));
?>
I had big performance problems retrieving data form MS SQL Server with odbc only when the query was unsing a join.
I found out, that I had to connect with the cursor-type «SQL_CUR_USE_ODBC» and everything was ok:
$conn = odbc_connect(«ShopLive», ‘shop’, ‘xxx’, SQL_CUR_USE_ODBC);
web-mastering.blogspot.com
ODBC (Open Database Connectivity, открытый механизм взаимодействия с базами данных) — это программный интерфейс (API) доступа к базам данных, разработанный фирмой Microsoft.
С помощью ODBC, программы могут подключаться к различным СУБД (MySQL, Microsoft SQL Server, SQLite, MongoDB и другие) и выполнять SQL-запросы.
Как создать имя источника базы данных (DSN)
Имя источника базы данных указывается в функции подключения ODBC.
Например, в PHP эта функция называется odbc_connect().
1. Нажимаем «Пуск»
2. Заходим в «Панель управления»
3. Ищем пункт «Администрирование»
Если вы не видите этого пункта, то переключите «Просмотр» из режима «Категория» в режим «Мелкие значки» или «Крупные значки».
4. Нажимаем на ссылку «Источник данных (ODBC)»
5. Открываем вкладку «Пользовательский DSN»
6. Нажимаем «Добавить»
8. Указываем «Имя» источника данных. Имя может быть любым, но я рекомендую использовать имя сервера, в моем случае это «mssql4.gear.host»
9. В поле «Сервер» нужно указать имя сервера, у меня это значение такое же как «Имя» — «mssql4.gear.host»
10. Указываем «проверка подлинности учетной записи SQL Server»
11. Устанавливаем флажок «Получить параметры, используемые по умолчанию, от SQL Server.»
12. Заполняем поля «Пользователь» и «Пароль».
16. Готово, мы создали имя источника базы данных (DSN) и теперь мы можем использовать его в своих программах.
Как подключаться к ODBC из PHP
// mssql4.gear.host - это и есть имя источника базы данных (DSN) $connection = odbc_connect('mssql4.gear.host', 'mssql86', 'password'); // «» - здесь ошибки нет, именно так и нужно указывать $connection = odbc_connect('Driver=;Server=mssql4.gear.host;Database=mssql86', 'mssql86', 'password'); string(3) "101" > В таблице «test» была одна колонка «id» с одной записью «101» */ ODBC and DB2 Functions (PDO_ODBC)
PDO_ODBC is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to databases through ODBC drivers or through the IBM DB2 Call Level Interface (DB2 CLI) library. PDO_ODBC currently supports three different «flavours» of database drivers: ibm-db2
Supports access to IBM DB2 Universal Database, Cloudscape, and Apache Derby servers through the free DB2 express-C client.
Supports access to database servers through the unixODBC driver manager and the database’s own ODBC drivers.
Offers a compile option for ODBC driver managers that are not explicitly supported by PDO_ODBC.
On Windows, php_pdo_odbc.dll has to be enabled as extension in php.ini . It is linked against the Windows ODBC Driver Manager so that PHP can connect to any database cataloged as a System DSN.
Installation
- PDO_ODBC is included in the PHP source. You can compile the PDO_ODBC extension as either a static or shared module using the following configure commands. ibm_db2
./configure --with-pdo-odbc=ibm-db2,/opt/IBM/db2/V8.1/
To build PDO_ODBC with the ibm-db2 flavour, you have to have previously installed the DB2 application development headers on the same machine on which you are compiling PDO_ODBC. The DB2 application development headers are an installable option in the DB2 servers, and are also available as part of the DB2 Application Development Client freely available for download from the IBM developerWorks » website. If you do not supply a location for the DB2 libraries and headers to the configure command, PDO_ODBC defaults to /home/db2inst1/sqllib . unixODBC
./configure --with-pdo-odbc=unixODBC,/usr/local
If you do not supply a location for the unixODBC libraries and headers to the configure command, PDO_ODBC defaults to /usr/local . generic
./configure --with-pdo-odbc=generic,/usr/local,libname,ldflags,cflags
Predefined Constants
The constants below are defined by this driver, and will only be available when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using this driver. Using driver-specific attributes with another driver may result in unexpected behaviour. PDO::getAttribute() may be used to obtain the PDO::ATTR_DRIVER_NAME attribute to check the driver, if your code can run against multiple drivers.
PDO::ODBC_ATTR_USE_CURSOR_LIBRARY ( int )
- PDO::ODBC_SQL_USE_IF_NEEDED (the default): use the ODBC cursor library when needed.
- PDO::ODBC_SQL_USE_DRIVER : never use the ODBC cursor library.
- PDO::ODBC_SQL_USE_ODBC : always use the ODBC cursor library.
PDO::ODBC_ATTR_ASSUME_UTF8 ( bool )
Windows only. If true , UTF-16 encoded character data ( CHAR , VARCHAR and LONGVARCHAR ) is converted to UTF-8 when reading from or writing data to the database. If false (the default), character encoding conversion may be done by the driver.
Runtime Configuration
The behaviour of these functions is affected by settings in php.ini .
| Name | Default | Changeable | Changelog |
|---|---|---|---|
| pdo_odbc.connection_pooling | «strict» | PHP_INI_ALL | |
| pdo_odbc.db2_instance_name | NULL | PHP_INI_SYSTEM | This deprecated feature will certainly be removed in the future. |
For further details and definitions of the PHP_INI_* modes, see the Where a configuration setting may be set.
Here’s a short explanation of the configuration directives.
Whether to pool ODBC connections. Can be one of «strict» , «relaxed» or «off» (equals to «» ). The parameter describes how strict the connection manager should be when matching connection parameters to existing pooled connections. strict is the recommend default, and will result in the use of cached connections only when all the connection parameters match exactly. relaxed will result in the use of cached connections when similar connection parameters are used. This can result in increased use of the cache, at the risk of bleeding connection information between (for example) virtual hosts.
This setting can only be changed from the php.ini file, and affects the entire process; any other modules loaded into the process that use the same ODBC libraries will be affected too, including the Unified ODBC extension.
relaxed matching should not be used on a shared server, for security reasons.
Leave this setting at the default strict setting unless you have good reason to change it.
If you compile PDO_ODBC using the db2 flavour, this setting sets the value of the DB2INSTANCE environment variable on Linux and UNIX operating systems to the specified name of the DB2 instance. This enables PDO_ODBC to resolve the location of the DB2 libraries and make cataloged connections to DB2 databases.
This setting can only be changed from the php.ini file, and affects the entire process; any other modules loaded into the process that use the same ODBC libraries will be affected too, including the Unified ODBC extension.
This setting has no effect on Windows.