|
|
 |
LXIX. ODBC FunktionenEinf�hrung
Zus�tzlich zur normalen ODBC-Unterst�tzung bieten die
ODBC-Funktionen in PHP auch Schnittstellen zu einigen
Datenbanken, die Ihre API der Semantik der ODBC-API
nachempfunden haben. Statt vielen nahezu identischen
Datenbanktreibern wurden diese Treiber zu einer einzigen
Gruppe von ODBC-Funktionen zusammengefasst.
Die folgenden Datenbanken werden von den ODBC-Funktionen
unterst�tzt: ,
,
,
und
.
Anmerkung:
Es ist kein echtes ODBC in die Kommunikation mit oben
genannten
Datenbanken involviert. Es handelt sich um echte native
Datenbankschnittstellen, die den Funktionsnamen
und den Funktionssyntax der ODBC-Funktionen nutzen.
Die Ausnahme ist iODBC. Wenn PHP mit iODBC-Unterst�tzung
kompiliert wird, k�nnen alle ODBC-kompatiblen Treiber mit
PHP genutzt werden. iODBC wird von
betreut. Mehr Informationen zu iODBC, unter anderem
eine HOWTO, finden Sie auf .
Anforderungen
Um die Datenbanken mit PHP nutzen zu k�nnen, sollten
die daf�r ben�tigten Bibliotheken installiert sein.
Installation
Bitte schauen Sie sich den Abschnitt
Installationsoptionen f�r Datenbanken an,
dort finden Sie zus�tzliche Informationen wie
PHP f�r diese Datenbanken konfiguriert werden muss.
Laufzeit Konfiguration
Das Verhalten der ODBC-Funktionen wird von den Einstellungen
in der globalen Konfigurationsdatei
php.ini bestimmt.
Tabelle 1. ODBC-Konfigurationsoptionen | Name | Standard | Einstellbar in |
|---|
| odbc.default_db * | NULL | PHP_INI_ALL | | odbc.default_user * | NULL | PHP_INI_ALL | | odbc.default_pw * | NULL | PHP_INI_ALL | | odbc.allow_persistent | "1" | PHP_INI_SYSTEM | | odbc.check_persistent | "1" | PHP_INI_SYSTEM | | odbc.max_persistent | "-1" | PHP_INI_SYSTEM | | odbc.max_links | "-1" | PHP_INI_SYSTEM | | odbc.defaultlrl | "4096" | PHP_INI_ALL | | odbc.defaultbinmode | "1" | PHP_INI_ALL |
Anmerkung:
Mit * markierte Eintr�ge sind noch nicht implementiert.
Weitere Informationen und Definitionen der PHP_INI_*-
Einstellungen finden Sie bei
ini_set().
Hier eine kurze Erkl�rung der Konfigurationsoptionen:
- odbc.default_db
string
Standard-ODBC-Datenquelle, wenn in
odbc_connect() oder
odbc_pconnect() keine definiert wird.
- odbc.default_user
string
Nutzername, wenn in
odbc_connect() oder
odbc_pconnect() keiner angegeben wird.
- odbc.default_pw
string
Passwort, wenn keines in
odbc_connect() oder
odbc_pconnect() angegeben wird.
- odbc.allow_persistent
boolean
Einstellung, ob persistente ODBC-Verbindungen
genutzt werden sollen.
- odbc.check_persistent
boolean
Aktiviert eine �berpr�fung, ob eine persistente
Datenbankverbindung noch aktiv ist, bevor
sie weitergenutzt wird.
- odbc.max_persistent
integer
Die maximale Anzahl von persistenten ODBC-Verbindungen
pro Prozess.
- odbc.max_links
integer
Die maximale Anzahl von ODBC-Verbindungen pro Prozess,
inklusive persistenten Verbindungen.
- odbc.defaultlrl
integer
Bestimmt die Anzahl Bytes, mit denen ein
LONG-Feld an PHP �bergeben wird.
- odbc.defaultbinmode
integer
Definiert die Behandlung von Bin�rdaten.
Resource TypenDiese Erweiterung definiert keine Resource-Typen. Vordefinierte Konstanten
Folgende Konstanten werden von dieser Erweiterung definiert und
stehen nur zur Verf�gung, wenn die Erweiterung entweder statisch
in PHP kompiliert oder dynamisch zur Laufzeit geladen wurde.
User Contributed Notes
ODBC Funktionen |
 |
fjohansson at bigfoot dot com
20-May-1999 10:07 |
|
A common problem with many ODBC drivers (especially thoose from Microsoft)
is that it is impossible to eg returning an ordernumber after inserting
the row into the table. The solution is simple,
make one field in the
db to a counter, and one to a temp field(text50). While inserting the row
set the temp field to microtime() (or any other uniqe number). Then do a
SELECT for this row (where temp=$microtimeinserted...). / Fredrik
Johansson
|
|
Pascal dot Guimier at wanadoo dot fr
09-Mar-2000 04:08 |
|
This is a usefull fetch_array function, inspired from SAL.phl ()
Thanks to Gianugo Rabellino ()
function odbc_fetch_array($res) {
$row = array();
$result = array();
if ($result =
odbc_fetch_row($res)) {
$nf = odbc_num_fields($res)+1;
for($count=1; $count < $nf; $count++) {
$field_name =
odbc_field_name($res, $count);
$field_value = odbc_result($res,
$count);
$row[$count] = $field_value;
/* or
$row[$field_name] = $field_value */
}
return $row;
}
}
------------------------
use it like this
:
$QCateg="SELECT * FROM Table";
$ret =
odbc_exec($conn, $QCateg) or die("Invalid query :
$QCateg");
print "My select : <select
name=myselect>";
While ($row = fetch_array($ret,
2))
print "<option
value=$row[1]>$row[2]\n";
print
"</select>\n
";
|
|
cliff at cape dot com
20-Apr-2000 07:36 |
|
When using the Microsoft Jet Database Engine with Access remeber that you
cannot use reserved names like Value or Add or Date within your
tables(unless I am mistaken and I hope That I am) so watch out when you
are designing tables in access!
|
|
esk525 at hotmail dot com
15-Aug-2000 09:52 |
|
For those having trouble with apostrophies in their ODBC queries: If the
database you are connecting to supports CHAR(39), which prints the
character associated with this ASCII number, or something like it, try
this:
$myInput =
ereg_replace("'","'+CHAR(39)+'",$myInput);
$query
= "Select * From Table Where
(Field='$myInput')";
$connection =
odbc_connect("mydb","myuserid","mypass");
$result
= odbc($connection,$query);
p.s. Don't forget the 's around the
variable in the query line... good luck
|
|
cgi at harrison dot org
16-Aug-2000 12:50 |
|
Troubles with (')apostrophies in general seems to be solved simply by
disabling MAGIC_QUOTES in the php.ini
(ex: magic_quotes_gpc = Off)
|
|
jlim at natsoft dot com dot my
11-Sep-2000 01:08 |
|
For examples of how to create a Data Source Name for ODBC and examples of
ODBC connections, see:
|
|
view at gmx dot de
02-Nov-2000 10:05 |
|
A (in my opinion) good database-abstraction-framework can be found
on
|
|
vpil at retico dot com
06-Nov-2000 01:20 |
|
Additional links to ODBC_exec:
How to actually write the SQL
commands:
Demystifying
SQL
BIG REF MANUAL:
Introduction
to Structured Query Language
Covers read, add, modify &
delete of data.
Hope it helps others as well.
|
|
sundogcurt at netscape dot net
31-Jan-2001 09:49 |
|
I agree, I find that a little bit confusing. I am setting up with a host
now that has Access support and I was sure that this was supported. But
these lines stopped me in my tracks for a bit.
The following
databases are supported by the Unified ODBC functions: Adabas D, IBM DB2,
iODBC, Solid, and Sybase SQL Anywhere.
Note: There is no ODBC
involved when connecting to the above databases. The functions that you
use to speak natively to them just happen to share the same names and
syntax as the ODBC functions.
|
|
dan dot polansky at seznam dot cz
02-Mar-2001 05:37 |
|
First I just repeat that examples are here:
Second:
Despite confusing remarks above, using unified ODBC functions you can
really connect to any database for which you create DSN (Data Source
Name). That is: using unified ODBC functions you can connect to _any_
database for which you have ODBC driver. Drivers exists or example
Interbase, VisualFoxPro, DBase III,IV,V etc, many drivers are part of
Win98 installation, other can be obtained from companies like EasySoft and
Merant. ODBC is one of the most flexible ways for connecting to databases.
This is because ODBC driver exist for almost every database there is. The
question is, whether the driver is free. At least there are many 30 days
trial versions of drivers.
|
|
dan dot polansky at seznam dot cz
02-Mar-2001 05:58 |
|
One nice thing about these unified ODBC functions is that you don't have to
care about blobs. You just obtain the contents of the blob in the result
of a select statement, not just blob identifier.
For you who do
not know what a blob is my message is that with these functions you
perhaps do not need to know.
|
|
dan dot polansky at seznam dot cz
02-Mar-2001 06:02 |
|
Don't forget to read user comments for odbc_connect :-)
|
|
pgodel at lticom dot com
02-May-2001 05:57 |
|
This sounds stupid, but took me a good bunch of time to figure it
out..
If you want to do a select on an excel file through ODBC,
"SELECT * from [Sheet1$]"
Where Sheet1 is the name of
the sheet inside the .xls file.
Hope this helps.
|
|
dseidel at sylphen dot com
23-Jul-2001 01:59 |
|
I have wrote an odbc_fetch_array-function. It works like the
mysql_fetch_array-function.
The source:
/** the same like
mysql_fetch_array for ODBC
* params : resultset
* return :
assoziative array of actual row
* or false if no more entries
*
author : Dierk Seidel ([email protected]), Sylphen.com KG,
www.sylphen.com
* last modified : 23.07.2001
*/
function
odbc_fetch_array($res) {
if(odbc_fetch_into($res, '', $ret))
{
while (list($key,$value)=@each($ret))
{
$arret[odbc_field_name($res, $key+1)] = $value;
}
return
$arret;
} else return false;
}
|
|
nick at innousa dot com
16-Nov-2001 04:38 |
|
Microsoft Access Heads up:
I used the odbc drivers to connect to a
DSN which pointed to an access database. Well it was on a test server so I
didn't bother assigning a user/pass combo to the DSN. Well I could select,
alter tables, drop tables, and create tables but I couldn't run an update
or insert until I assigned a user/pass to the DSN. STUPID! Anyways save
yourself some headaches when using access.
|
|
mr dot soetjianto at mail dot tju dot edu
17-Jan-2002 08:03 |
|
Adding to the previous post about "trouble with apostrophies",
simply converting each single apostrophie into two apostrophies works for
me.
Use something like:
//if magic_quotes_gpc = Off
$myInput
= ereg_replace("'","''",$myInput);
//if
magic_quotes_gpc = On
$myInput =
ereg_replace("\'","''",$myInput);
|
|
NOSPAM-bkeyes at minnetronix dot com
08-May-2002 10:17 |
|
If you are accessing a Microsoft Access based query that contains a where
clause with LIKE '*XYZ' you may find that you don't get any results
returned.
If this is the case you should change the asterisk in the
clause to a percent sign:
LIKE '%XYZ'
The query should now
return results to PHP.
However, the query will NOT return results
if run from within Access. If you need to use the query both from within
Access and from PHP you will need to create 2 queries.
|
|
tomking2030 at hotmail dot com
21-Jun-2002 12:40 |
|
I had problems with access and reserved words. I could select data but
couldn't insert data. It was a real headache but you can get around it by
using [] in your insert command.
$sql66 = "INSERT INTO
$tablename
([level], [ADMIN])
Values
('$level',
'$State')";
Smebody mentioned it above but heres another way
to use it. Those [] also help if your having problems with "white
spaces" between data.
|
|
steven at webdesignsolutions dot net
12-Aug-2002 03:59 |
|
another fetch array acts the same as mysql_fetch_array, but make a numeric
array
ODBCDATA[0];
ODBCDATA[1];
ODBCDATA[2];
and an
associative
array
ODBCDATA["field1"];
ODBCDATA["field2"];
ODBCDATA["field3"];
I
am sure people could inprive on this:
function
odbc_fetch_array($R)
{
$FR = odbc_fetch_row($R);
for ($i = 1;$i
<= odbc_num_fields($R);$i++)
{
$FN =
odbc_field_name($R,$i);
$FV = odbc_result($R,$i);
$RA[$FN] =
$FV;
$RA[$i-1] = $FV;
}
if ($FR == false)return
false;
return $RA;
}
|
|
b dot parish at no_spam dot linst dot ac dot uk
14-Aug-2002 03:46 |
|
Accessing a Microsoft SQL Server database from PHP running under
Linux:
|
|
LaLista (lalista.fly.to)
16-Mar-2003 11:43 |
|
FOR ASP-COMERS
--------------
You can use the ADODB interface
instead. It's the same code than ASP, but it's written in
PHP:
<HTML>
<HEAD>
<TITLE>ADODB Test
in PHP</TITLE>
</HEAD>
<BODY>
<?php
$Connection = new COM("ADODB.Connection");
$RecordSet = new COM("ADODB.RecordSet");
$Connection -> Open("Provider=SQLOLEDB; DBQ=MyServer; UID=MyUser;
PWD=MyPassword");
$RecordSet = $Connection ->
Execute("SELECT * FROM MyTable");
while(!$RecordSet
-> EOF)
{
echo "\"" . $RecordSet ->
Fields(0) . "\"";
echo "costs $" .
$RecordSet -> Fields(1) . "< BR>";
$RecordSet
-> MoveNext() #Don't forget this
}
$Connection ->
Close();
$Connection -> Release();
$RecordSet ->
Release();
$Connection = null;
$RecordSet = null;
?>
</BODY>
</HTML>
Supposing this is the
table:
Article(0) Price(1)
Wine 19.95
CD-RW
1.49
CD-R 0.49
Pen 0.45
This will be the
output:
<HTML>
<HEAD>
<TITLE>ADODB Test in
PHP</TITLE>
</HEAD>
<BODY>
"Wine" costs $19.95< BR>
"CD-RW" costs
$1.49< BR>
"CD-R" costs $0.49< BR>
"Pen" costs $0.45< BR>
</BODY>
</HTML>
|
|
mitchind at telusplanet dot net
06-May-2003 06:58 |
|
Thanks to the great documentation here, I was able to combine several
examples and figure out the rest to create a generic routine to export a
SQL query from an ODBC database to Excel format. It should work on most
platforms and browsers. Usage is simple and described in code - but
basically you just need to format the SQL query and supply it as a hidden
input in another page's form and then use this script as the
"ACTION" parameter.
Ideally I'd like to add a pop up
message if the number of records exceeds the MAX_RECS value - tell the
user and get a confirmation to continue. Maybe someone else can add that
for me - my knowledge of PHP has been stretched with this
module.
<?php
// exportExcel.php
// PHP Script to
Export Results of ODBC SQL Query directly into Excel
// Get SQL
Statement from Form variable
// No caching of script
results
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// Date in the past
header("Last-Modified: " .
gmdate("D, d M Y H:i:s") . " GMT");
// always
modified
header("Cache-Control: no-store, no-cache,
must-revalidate"); // HTTP/1.1
header("Cache-Control:
post-check=0, pre-check=0", false);
header("Pragma:
no-cache"); // HTTP/1.0
// Variables
dependent on application and input form
$DSN = "YOUR DSN
NAME"; // Database ODBC DSN Name
$DB_USER = "";
// Database ODBC Username
$DB_PWD = "";
// Database ODBC Password
$MAX_RECS = "100"; //
Maximum number of records to export (ignored if
empty)
$SQL_FORM_FIELD = "SQL"; // Input Form
Field with SQL Query String
$NUM_RECS_FORM_FIELD = "nRecs"
// Input Form Field with Number of Records
$ALLOW_REFER_FROM =
"www.yourwebsitename.com"; // Only Allow usage from this
website
if (empty($_SERVER['HTTP_REFERER']))
die("No
Referal Website");
$refer = $_SERVER['HTTP_REFERER'];
$pos
= strpos("$refer", "$ALLOW_REFER_FROM") or
die("Not Coming From Correct Website");
// Read in SQL
statment from form (regardless of GET or POST
method)
reset($_GET);
reset($_POST);
if
(empty($_REQUEST["$SQL_FORM_FIELD"]))
die("No Input
Form Field Defined");
$sqlQuery =
$_REQUEST["$SQL_FORM_FIELD"];
// Get rid of the slashes
that PHP inserts and will screw up ODBC SQL queries
$sqlQuery =
stripslashes("$sqlQuery");
// Connect to data
source
$db =
odbc_connect("$DSN","$DB_USER","$DB_PWD");
//
Get Number of Records in query if already known
if
(!empty($_REQUEST["$NUM_RECS_FORM_FIELD"])) {
$numrecs =
$_REQUEST["$NUM_RECS_FORM_FIELD"]; // Check this value against
Max
}
else {
$numrecs = 0; // Unknown .. better set upper
limit
}
// Attach MaxRecs limitation if defined and less than
number of records in query
// or if Number of Records is unknown
if
(!empty($MAX_RECS)) {
if ($numrecs == 0) || ($MAX_RECS < $numrecs)
{
$sqlQuery = str_replace("SELECT ", "SELECT TOP
$MAX_RECS ", "$sqlQuery");
}
}
// Fetch query
results
$result = odbc_exec($db, "$sqlQuery") or
die("Query failed");
// Build page of results
// Open
as Excel file in browser
header('Content-type:
application/vnd.ms-excel');
?>
<style
type="text/css">
<!--
body {font: 10pt/12pt Tahoma,
Verdana, Helvetica, sans-serif; color: indigo; margin: .25in .5in }
table {FONT-FAMILY:Tahoma; font-size:8pt; color:Navy; border-color:Black;
border-style:Solid; border-width:1px;}
td
{background-color:AntiqueWhite;}
//-->
</style>
<?php
//
returns table with basic formatting
odbc_result_all($result,
"border=\"1\"
class=\"def\"");
//disconnect from
database
odbc_close($db);
?>
|
|
|
|
| |
PHP Handbuch
Funktionsreferenz
Apache
notes_version