open sqilite path unicode

[Anonymous Monk]

Dear monks

I've been trying for days and I'm really getting upset, because or 1) I'm a total idiot or 2) perl makes things difficult when it cames to unicode.

I just want to connect to a Sqlite database on a Windows machine. The database is in a directory containing non english characters... Teh file position is selected with a Tk Gui. I've tried different modules and different approches. No results

If there is a non english caracter in the path, it just wan't open the database (C:\folder-ü)

use strict;
use warnings;
use Tk;
use DBD::SQLite;
my $mw = new MainWindow;
    my $types = [ ['Text', '.db'], ['All Files',   '*'],];
    my $file= $mw->getOpenFile(-filetypes => $types);
    my $shortpath=Win32::GetANSIPathName($file); #for path with accent
+s    
    my $path_and_databasename=$shortpath;
my $lco;
    
    #connecting to sqlite
    my $dbh = DBI->connect("dbi:SQLite:$path_and_databasename", "", ""
+, { RaiseError => 1, AutoCommit => 1, PrintError => 0 });
    my $all = $dbh->selectall_arrayref("SELECT language    FROM metada
+ta");
    foreach my $row_db (my @all) {
         ($lco) = @$row_db;
    }    
print "Connected: $lco\n";

Any solution, I don't need to use getOpenFile. Any other solution with a Browse Tk would be fine!

[atcroft]

I do not see any indication in the code that you are processing variables as Unicode. There are a series of articles written by Tom Christiansen about working with Unicode in Perl 5 that I suspect may prove useful to you in this case.

Hope that helps.

[Anonymous Monk]

I don't see that any of the mention unicode filenames or win32 .... but I'm sure nothing there will help this situation

[kcott]

According to the DBD::SQLite documentation, the connect() syntax is (from the SYNOPSIS):

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");

You appear the missing the dbname= part.

I'd suggest you use the utf8 pragma and hard-code the connect() call, with the unicode character(s), directly into your script (or, at least, a test version); e.g.

my $dbh = DBI->connect('dbi:SQLite:dbname=C:\folder-ü', ...);

Remove all the Tk and SQL code: just attempt to connect to the database. Work on getting that happening. If you encounter difficulties, ask here but include all relevant details (see the "How do I post a question effectively?" guidelines if you're unsure about this step).

Once you have a script that allows you to successfully connect(), start adding the other parts back in a bit at a time.

Repeat the process outlined: fixing small problems as they occur or asking here when you can't. Eventually, you'll have everything fixed — and may still retain much of your original hair :-)

[Anonymous Monk]

I would first check without GetANSIPathName...

https://metacpan.org/source/ISHIGAKI/DBD-SQLite-1.40/lib/DBD/SQLite.pm

    # To avoid unicode and long file name problems on Windows,
    # convert to the shortname if the file (or parent directory) exist
+s.
    if ( $^O =~ /MSWin32/ and $real ne ':memory:' and $real ne '') {
        require Win32;
        require File::Basename;
        my ($file, $dir, $suffix) = File::Basename::fileparse($real);
        my $short = Win32::GetShortPathName($real);
        if ( $short && -f $short ) {
            # Existing files will work directly.
            $real = $short;
        } elsif ( -d $dir ) {
            # We are creating a new file.
            # Does the directory it's in at least exist?
            $real = join '', grep { defined } Win32::GetShortPathName(
+$dir), $file, $suffix;
        } else {
            # SQLite can't do mkpath anyway.
            # So let it go through as it and fail.
        }
    }

Although according to https://metacpan.org/source/ISHIGAKI/DBD-SQLite-1.40/sqlite3.c unicode filenames should work

#if SQLITE_OS_WINCE || SQLITE_OS_WINNT || SQLITE_OS_WINRT
#  define SQLITE_WIN32_HAS_WIDE
#endif

but there doesn't appear to be a test in the (perl) test suite

So maybe your version is too old, you're getting 8.3 filename interference, maybe your copy of DBD::SQLite was compiled without SQLITE_WIN32_HAS_WIDE or or something else entirely :)

These things (above) are the things I would check

I would also read read Win32::Unicode and Win32::Unicode::Native and Re: dos path accents and read Re^5: threads::shared seems to kill performance (Workaround). and beware of :memory: and Windows Alternate Data Streams

[MidLifeXis]

Are there any error messages you can provide? What are the contents of $shortpath? Is the content from getOpenFile processed correctly from unicode to perl-internal? Just some areas to look at.

[Anonymous Monk]

Hi MidLife

Error: DBI connect('C:/incriminated path with strange characters/DATABASE.db','',...) failed: unable to open database at mymodule.pm line xy

$shortpath was just a way to try to have a path working. Just skip that one and use $file

" Is the content from getOpenFile processed correctly from unicode to perl-internal" this I really do not know, sorry

[ig]

I created a SQLite database in C:\folder-ü and ran your sample program and it opened the database successfully. I tried another directory with other non-ASCII characters in the name and had no trouble opening a database in that directory either. This with 64bit Strawberry Perl 5.16.2 on 64bit Windows 7 Professional.

What version of perl are you running and what is your operating system?

[Anonymous Monk]

Hello, I'm running ActiveState 5.16

Here is the simple code I'm working on right now with some (maybe) useful error messages. The database is in a folder containing a "ü" in its name...

use strict;
use warnings;
use UTF8;
use Tk;
use DBD::SQLite;
use Data::Dumper;
$Data::Dumper::Useqq=1;


my $mw = new MainWindow;
my $types = [ ['Text', '.db'], ['All Files',   '*'],];
my $file_1= $mw->getOpenFile(-filetypes => $types);

#just looking at how the path is
print Dumper($file_1);    

#connecting to sqlite
my $dbh = DBI->connect("dbi:SQLite:$file_1", "", "", { RaiseError => 1
+, AutoCommit => 1, PrintError => 0 });

... connection error

[soonix]

What file system are you using there? NTFS or one of the FAT versions? (I know about PHP having issues with NTFS and non-ASCII characters)

And, perhaps you could diagnose what readdir returns as name of the concerned folder...

[Laurent_R]

...because or 1) I'm a total idiot or 2) perl makes things difficult when it cames to unicode.

I have not seen any indication that hypothesis 1 should be taken seriously, but Unicode is really a complicated matter. And, as far as I can say, Perl is probably the language that makes Unicode as simple as it can be. But you need to understand a number of things before mastering the subject, and that makes it quite complex (and I certainly don't master the subject as far as I am concerned).

[Anonymous Monk]

I had the same problem in Japanese but I can solve it.

First of all, what is the character code of $file? If you can get that string correctly in Perl internal encoding, then you should covert it to (non-flagged) utf8 before pass to DBI->connect function.