talk2chakra has asked for the wisdom of the Perl Monks concerning the following question:

Before anything, I need to tell my Software Environment as follows.

Installed Software :-
SQL Server Compact 3.5 Books Online ENU
SQL Server Compact SP2 ENU
SQL Server Compact 3.5 SP2 x64 ENU
MS Office Enterprise 2007
MS .NET Framework 4.5.2
CompactView 1.4.12.0
ActivePerl Ver 5.24.0.2400 (x64)
OS : Windows 7 Pro x64

I installed IIS 7.5.7600.16385 and configured it properly for use with ActivePerl. Apart from that, I changed Physical Path of Site to E:\PUCC. The PUCC Folder has been assigned proper security for IUSR and IIS_IUSRS as FULL CONTROL. I tested IIS for use with PERL and succeeded.

I've 2 .SDF Files (Password Protected) stored in PUCC\DB Folder. As I know the Password, I can access both the Files through CompactView. However I cannot access it through ActivePerl. Whenever, I run PERL SCRIPT from Command Prompt, following errors popped up. My ultimate goal is to furnish the Tables with specific Rows, Columns in Web Browser through IIS.

PERL Code:

01 use strict;
02 use warnings;
03 use DBI;
04
05 my $user="";
06 my $pass="myPass";
07
08 my $dbh = DBI->connect("dbi:ADO:Provider=Microsoft.SQLSERVER.CE.OLE
+DB.3.5;Data Source=E:\\PUCC\\DB\\D_stud.sdf", $user, $pass);
09
10
11 my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.TABLES WH
+ERE TABLE_TYPE = 'TABLE'");
12
13 $sth->execute();
14
15 while (my $href = $sth->fetchrow_hashref()) {
16    print $href->{TABLE_NAME} . "\n";
17 }

[download]

ERROR Details:
DBI connect('Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=E:\PUCC\DB\D_stud.sdf','',...) failed: Can't Open Connection 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=E:\PUCC\DB\D_stud.sdf'
Package : DBD::ADO::dr
Filename : C:/Perl64/site/lib/DBD/ADO.pm
Line : 158
Last error : -2147217887

OLE exception from "ADODB.Connection":

Provider could not set DATASOURCE, USERID, or PASSWORD property.

Win32::OLE(0.1712) error 0x80040e21
in METHOD/PROPERTYGET "Open" at test.pl line 8.
Can't call method "prepare" on an undefined value at test.pl line 11.


Can someone help me out of the Problem?

Replies are listed 'Best First'.
Re: ActivePerl (x64) is not working with MS SQL Server CE Database
by BrowserUk (Patriarch) on Sep 26, 2016 at 15:31 UTC

    There is something wrong with your connection string.

    Wild-arsed guess: try switching \\ to / in the path.

    Beyond that you'll probably get better help on the ActiveState commumity forum.

    With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority". I knew I was on the right track :)
    In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]

      I also apprehend that there are something wrong with my Connection String but could not figure out where.

      According to your advice, I changed all \\ to / but to no avail. Surprisingly same ERROR appears.

      I just posted the same topic on ActiveState Community Forum.

      Thanks a lot for guiding me.

      Let us see if someone appears with solutions

[reply]
Re: ActivePerl (x64) is not working with MS SQL Server CE Database
by NetWallah (Canon) on Sep 26, 2016 at 17:36 UTC
    Not sure if this will help, but it is easy enough to try --
    Connectionstrings.com and this MSDN forum seem to suggest putting the password into the connection string like this: 
    PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;ssce:database password=test;
+Data 
Source=C:\temp\testcompact3.sdf 

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myDat
+a.sdf;
SSCE:Database Password='myPassword';

    [download]

            ...it is unhealthy to remain near things that are in the process of blowing up.     man page for WARP, by Larry Wall

[reply]
[d/l]

      Success eventually came on applying following Connection String

      my $dbh = DBI->connect("dbi:ADO:PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.
+3.5;ssce:database password=$pass;Data Source=E:\\PUCC\\DB\\D_stud.sdf
+");

      [download]

      Many many thanks for the reference

[reply]
[d/l]
Re: ActivePerl (x64) is not working with MS SQL Server CE Database
by chacham (Prior) on Sep 27, 2016 at 13:16 UTC

    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'

    Side comment: I generally suggest people do not use * in lieu of an actual column list, because of added or removed columns, and possible renames or reordering. Though, that could technically happen here, it is unlikely on an information_schema table. So, i'd still suggest it, just not as strongly.

    OTOH, it is also self-documenting and creates less traffic over the network when you only ask for the actual columns you need. At the very least, you do not need TABLE_TYPE in this query since it is specified with a literal anyway.

[reply]

Back to Seekers of Perl Wisdom