| Query *.mdb file with ODBC functions [message #170281] |
Tue, 26 October 2010 15:20  |
alvaro.NOSPAMTHANX
Messages: 277
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
Sometimes I'm handled data in a Microsoft Access database and I need to
manipulate it. I only have an old copy of Access 2000, I'm not
proficient with it, I don't know the query syntax and I hate its GUI to
the bone. In the past, I used to migrate data to another DBMS, do stuff
there and then port back to the original file, but it's an error-prone
and time-consuming method.
I've found that writing a simple PHP script with the ODBC functions (I
have a Windows box) can ease the task a lot. However, I have two
important questions so far:
1.- What exact SQL dialect does the JET engine expect? I'd appreciate a
link to MSDN or something with an explanation of basic syntax and
available functions.
2. How am I supposed to escape input data? PHP ODBC functions provide
parametrized queries but I can't make them work:
$data = array(
100 => 'Foo St.',
101 => 'Bar Ave.',
);
$sql = 'UPDATE street
SET name=?
WHERE code=?';
$stmt = odbc_prepare($conexion, $sql);
if( $stmt===FALSE ){
die(odbc_errormsg());
}
foreach($data as $code => $name){
if( !odbc_execute($stmt, array($name, $code)) ){
die(odbc_errormsg()); // SQL state 07001 Wrong number of parameters
}
}
Some user comments in the PHP manual suggest that parametrized queries
are not supported by the Access driver :-?
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
|
|
|
|
| Re: Query *.mdb file with ODBC functions [message #170283 is a reply to message #170281] |
Tue, 26 October 2010 16:41  |
Robert Hairgrove
Messages: 19
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
Álvaro G. Vicario wrote:
> Sometimes I'm handled data in a Microsoft Access database and I need to
> manipulate it. I only have an old copy of Access 2000, I'm not
> proficient with it, I don't know the query syntax and I hate its GUI to
> the bone. In the past, I used to migrate data to another DBMS, do stuff
> there and then port back to the original file, but it's an error-prone
> and time-consuming method.
>
> I've found that writing a simple PHP script with the ODBC functions (I
> have a Windows box) can ease the task a lot. However, I have two
> important questions so far:
>
> 1.- What exact SQL dialect does the JET engine expect? I'd appreciate a
> link to MSDN or something with an explanation of basic syntax and
> available functions.
The best reference for ODBC is the "ODBC Programmer's Reference" which
somes with every edition of Visual Studio and can be easily located on
the Microsoft MSDN web site if you search for the term "ODBC".
MS-Access SQL is slightly different than the SQL which ODBC supports,
but if you are connecting via ODBC, then you need to stick to the ODBC
documented syntax. I believe that the MS-Access ODBC driver supports at
least the ODBC API level 3.51 (Access 2000 was able to support it, IIRC
-- there might be a higher level, but I think ODBC support was pretty
much dropped by Microsoft in favor of OLEDB many years ago, more or less
when Windows XP came out or even before that).
There are ODBC functions (SQLGetInfo, I think it is called) which can be
used to fetch a large variety of meta-data in order to find the level of
support you have at runtime. For applications using ODBC, it is a good
habit to use this ODBC API for discovering the support level at runtime,
regardless of whether you have an MS-Access "database" or some other
kind of RDBMS at the back end.
> 2. How am I supposed to escape input data? PHP ODBC functions provide
> parametrized queries but I can't make them work:
Escaping literal text strings with ODBC must be done using single quote
characters (MS-Access SQL running inside Access accepts either single or
double quotes). Date strings, however, must be formatted according to
the ODBC docs if you want to pass a literal date value to an Access
database using ODBC. Most other data can be passed normally. There are
some "gotchas" regarding Boolean data (i.e. Access interprets "true" as
a long integer value of -1).
> Some user comments in the PHP manual suggest that parametrized queries
> are not supported by the Access driver :-?
This is highly likely. Note that any ODBC application connecting to an
MS-Access database is not going to have any support from the server,
simply because there is none (except for the file server where the MDB
file is located). And every time you query an Access database located on
a remote computer, you will download the entire MDB file.
Also, you cannot run any queries stored in the Access MDB file which
contain calls to VBA functions (even built-in functions such as iif())
because they need the MS-Access executable in order to run. You will
find that many of the built-in VBA functions can be replaced by ODBC
function calls, though.
|
|
|
|
| Re: Query *.mdb file with ODBC functions [message #170303 is a reply to message #170283] |
Thu, 28 October 2010 07:28 |
alvaro.NOSPAMTHANX
Messages: 277
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
El 26/10/2010 18:41, Robert Hairgrove escribió/wrote:
> Álvaro G. Vicario wrote:
>> Sometimes I'm handled data in a Microsoft Access database and I need
>> to manipulate it. I only have an old copy of Access 2000, I'm not
>> proficient with it, I don't know the query syntax and I hate its GUI
>> to the bone. In the past, I used to migrate data to another DBMS, do
>> stuff there and then port back to the original file, but it's an
>> error-prone and time-consuming method.
>>
>> I've found that writing a simple PHP script with the ODBC functions (I
>> have a Windows box) can ease the task a lot. However, I have two
>> important questions so far:
>>
>> 1.- What exact SQL dialect does the JET engine expect? I'd appreciate
>> a link to MSDN or something with an explanation of basic syntax and
>> available functions.
>
> The best reference for ODBC is the "ODBC Programmer's Reference" which
> somes with every edition of Visual Studio and can be easily located on
> the Microsoft MSDN web site if you search for the term "ODBC".
>
> MS-Access SQL is slightly different than the SQL which ODBC supports,
> but if you are connecting via ODBC, then you need to stick to the ODBC
> documented syntax. I believe that the MS-Access ODBC driver supports at
> least the ODBC API level 3.51 (Access 2000 was able to support it, IIRC
> -- there might be a higher level, but I think ODBC support was pretty
> much dropped by Microsoft in favor of OLEDB many years ago, more or less
> when Windows XP came out or even before that).
>
> There are ODBC functions (SQLGetInfo, I think it is called) which can be
> used to fetch a large variety of meta-data in order to find the level of
> support you have at runtime. For applications using ODBC, it is a good
> habit to use this ODBC API for discovering the support level at runtime,
> regardless of whether you have an MS-Access "database" or some other
> kind of RDBMS at the back end.
>
>> 2. How am I supposed to escape input data? PHP ODBC functions provide
>> parametrized queries but I can't make them work:
>
> Escaping literal text strings with ODBC must be done using single quote
> characters (MS-Access SQL running inside Access accepts either single or
> double quotes). Date strings, however, must be formatted according to
> the ODBC docs if you want to pass a literal date value to an Access
> database using ODBC. Most other data can be passed normally. There are
> some "gotchas" regarding Boolean data (i.e. Access interprets "true" as
> a long integer value of -1).
>
>> Some user comments in the PHP manual suggest that parametrized queries
>> are not supported by the Access driver :-?
>
> This is highly likely. Note that any ODBC application connecting to an
> MS-Access database is not going to have any support from the server,
> simply because there is none (except for the file server where the MDB
> file is located). And every time you query an Access database located on
> a remote computer, you will download the entire MDB file.
>
> Also, you cannot run any queries stored in the Access MDB file which
> contain calls to VBA functions (even built-in functions such as iif())
> because they need the MS-Access executable in order to run. You will
> find that many of the built-in VBA functions can be replaced by ODBC
> function calls, though.
Thank you for all the pointers.
I presume that PHP+ODBC+Access is not the most powerful tool-set in the
world but there aren't many alternatives to handle an *.mdb file from
PHP, are there?
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
|
|
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]