I would like to access a SQL CE database from Perl on Windows 7 and would prefer something that works on Strawberry Perl. I have tried a few ADO connection strings with DBI and DBD::ADO without success. Has anyone done this? Any suggestions? It doesn't have to be DBI, though that would be nice.

update: changed the title for clarity.

update: It looks like ODBC isn't the way to go. See, for example, http://social.msdn.microsoft.com/Forums/en/sqlce/thread/320df64a-60d8-4865-97f2-cc42fef628b4 or http://www.sqlservercompactodbc.com/.

update: Another suggestion of DBD::ADO: http://stackoverflow.com/questions/379765/is-there-a-perl-dbi-driver-for-sql-server-compact-3-5

update: It may be relevant that I am running 64bit Windows 7.

update: It is possible!!

Thanks to Corion for suggestions and encouragement to keep trying.

This script lists all the tables in the sample Northwinds database:

#!/usr/bin/perl
#
use strict;
use warnings;
use Win32::OLE qw(in);

my $objConn = Win32::OLE->new("ADODB.Connection");
die Win32::OLE->LastError() if(Win32::OLE->LastError());
die "No connection object" unless($objConn);

$objConn->Open("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=
+C:\\temp\\Northwind.sdf");
die Win32::OLE->LastError() if(Win32::OLE->LastError());

my $objRS = $objConn->Execute("SELECT * FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_TYPE = 'TABLE'");

while(! $objRS->{EOF}) {
    print $objRS->Fields('TABLE_NAME')->{value} . "\n";
    $objRS->MoveNext();
}
[download]

I ran this with Strawberry Perl on Windows 7 64bit with both 32bit and 64bit versions of SQL Server Compact Edition 3.5 SP2 installed. I also had SQL Server 2008 Express, Visual Studio and Microsoft Office 2010 installed, any of which may or may not have been relevant - I never have understood the way Microsoft chooses to bundle their products.

This is perl, v5.10.1 (*) built for MSWin32-x86-multi-thread

Copyright 1987-2009, Larry Wall

update: I must have been having a very bad day yesterday: not only did I fail with Super Search and Google, but what I tried to try first now works as expected. The only software I installed in the mean time is Books On Line for SQL Server Compact Edition - I don't think that would have installed oledb provider. Anyway, this also is working this morning:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:ADO:Provider=Microsoft.SQLSERVER.CE.OLEDB.
+3.5;Data Source=C:\\temp\\Northwind.sdf", undef, undef);


my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
+ TABLE_TYPE = 'TABLE'");

$sth->execute();

while (my $href = $sth->fetchrow_hashref()) {
    print $href->{TABLE_NAME} . "\n";
}
[download]

update: I'm an idiot! I had an extra 'D' in my connection string yesterday: Provider=Microsoft.SQLSERVER.CE.OLDEDB.3.5;

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.