| 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:
|
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.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]