This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: dbi-users@perl.org
From: Bart Lateur <bart.lateur@skynet.be>
Subject: Re: Postgres w/DBI
Date: Tue, 16 Oct 2001 18:03:35 +0200
On Tue, 16 Oct 2001 09:02:05 -0600, Sterin, Ilya wrote:
>It might be getting a little bit OT, but I searched and couldn't find the
>right definition of Jet SQL.
>
>Microsoft claims it's close enough to the standard (though nowhere does it
>say in conforms to any standard).
Well...
First of all, Access uses [ and ] for field name delimiters, instead of
the standard double quotes, doubles or single quotes instead of single,
around text, and DOS/glob-like "*" and "?" for the wildcard characters,
instead of the standard "%" and "_". And table and field names are case
insensitive.
SELECT * FROM [my table] WHERE [first name] LIKE "B*"
But this is for queries built an ran inside Access only. For SQL invoked
through DBI+DBD::ODBC, you'd better stick to the standard syntax:
SELECT * FROM "my table" WHERE "first name" LIKE 'B*'
In fact, You can almost use both syntaxes through DBI, except that "*"
and "?" for wildcards doesn't work. YOu have to use the standard "%" and
"_".
Furthermore, internally Access allows calling functions in Access Basic
in a query, but apparently not through DBI.
===
To: dbi-users@perl.org
From: David Wheeler <david@wheeler.net>
Subject: RE: Postgres w/DBI
Date: 16 Oct 2001 09:29:09 -0700
On Tue, 2001-10-16 at 08:02, Sterin, Ilya wrote:
<snip />
> I guess you can spin it either way, I've worked with Access years
ago,so I
> don't know if they started fully conforming to SQL standards now, but
back
> when I was working with it (Access 97, and a little Access 2000) a lot
of
> standards were not followed. I can't recall what exactly at the
moment, but
> if needed it definitelly wouldn't be hard to find:-)
Last I heard (several years ago, with the Release of Office 2000), you
could select to use the SQL Server engine for Access instead of Jet.
That would make it a lot more compliant with the SQL standards.
David
===
To: "Rozengurtel, Daniel"
<Daniel.Rozengurtel@bofasecurities.com>,
From: "Richard Chiswell" <richard.chiswell@cradleygp.co.uk>
Subject: RE: Access/Excell DBI
Date: Wed, 17 Oct 2001 15:31:25 +0100
On October 17th, Daniel Rozengurtel asked:
> My friend is planing to write an application which will need to
> have a small
> DataBase access. I suppose its going to be either Access or Excell. Can
> anybody suggest of any DBI/DBD modules/pre-installs that he could use for
> this purpose.
Use will need DBI and DBD::ODBC to access Access databases. Save your Access
.mdb on your hard drive somewhere, go:
Start>Settings>Control Panel>Administative Tools>Data Sources (ODBC)
[on a Windows2000 machine]
and set up your database as:
System DNS>Add>Microsoft Access Drive (*.mdb)
set the Data Source Name (DSN) as the name you want to use in your Perl
script, and 'select' the Access mdb. Then use the following in your Perl
code:
use DBI;
$dbh=DBI->connect("dbi:ODBC:DNS_Name","","",{RaiseError=>1, PrintError=>1,
AutoCommit=>1}) || die ("Unable to access database: ".$DBI::errstr."\n");
and you should be to use the Access database as a standard DBI database.
I've
never tried Excel myself, but the setup (if it is supported by Excel) should
be familar.
Richy C.
[speaking personally]
===
To: "'dbi-users@perl.org'" <dbi-users@perl.org>
From: Javier Dussaillant <ashkar@vrweb.cl>
Subject: Re: Access/Excell DBI
Date: Wed, 17 Oct 2001 11:32:25 -0300
Hi,
He should use DBI and DBD::ODBC to make use of an Access DB. Both can be
installed with the PPM utility that comes with ActiveState Perl.
===