SQL Server Compact Edition (SQL CE) on Windows 7 (64bit)

[ig]

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();
}

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";
}

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

[Corion]

You don't tell us what exactly you tried and how exactly it failed for you. This makes it much harder for us to help you efficently.

Personally, I look at http://connectionstrings.com/ whenever I'm in search of parameters to pass on using DBD::ODBC. If you don't have an ODBC driver for your target database, get one.

Also, it seems that "SQL CE" is "Microsoft SQL Server Compact Edition", so I would guess that any connection string for Microsoft SQL Server should work. Traditionally, MS SQL used the same protocol as Sybase did (TDS), so you might get some results using DBD::Sybase.

As Microsoft SQL Server Compact Edition is a Microsoft product, personally, I would look at instantiating an ADO connection using Win32::OLE or DBD::ADO, and then using that. See, again, the connection strings provided by http://connectionstrings.com.

[ig]

Thanks for the suggestions. I don't think what I tried is much worth pursuing, so didn't give details.

I usually have good success with Super Search or Google, but failed to find anything to get me going this time. Maybe I'm just having a bad search day.

I am familiar with accessing SQL Server via DBI and Win32::OLE, but there is no "server" in SQL Server Compact Edition (not that I know about, at least - but maybe that's just my ignorance - I know very little about SQL Server Compact Edition). The connections I make to SQL Server all include specification of the server, and I don't know how they might apply to a SQL CE data file - I suspect they don't but, again, that may just be my ignorance misleading me.

I thought/hoped someone might be able to point me to a working example to get me started in the right direction. If not, I'll certainly look further at Win32::OLE and DBD::ADO, with updates here if/when I get something working.

[Corion]

sql server compact edition odbc brings me to this "social.msdn.microsoft.com" post, which links, among others, to how to add data to an SDF file from Excel (etc.) via ODBC resp. ADO.

Looking at DBD::ADO, you can supply it an ODBC connection string, so you should be able to use the same connection string as the VBA code, mutatis mutandis. If it still "doesn't work", you will have to post code. Maybe you are using relative path names instead of absolute path names, or you are not doubling the backslashes where it matters, or some other things. It's hard to tell without seeing the failing code.

Update: The DBD::ADO documentation says to use

dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\test.mdb

as the connection string, so likely the following should work for the SQL CE server:

dbi:ADO:PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\North
+wind.sdf