| 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:
|
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
]