Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

open sqilite path unicode

by Anonymous Monk
on Nov 01, 2013 at 17:36 UTC ( #1060799=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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!

Replies are listed 'Best First'.
Re: open sqilite path unicode
by atcroft (Abbot) on Nov 01, 2013 at 18:10 UTC

    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.

      I don't see that any of the mention unicode filenames or win32 .... but I'm sure nothing there will help this situation
Re: open sqilite path unicode
by kcott (Chancellor) on Nov 02, 2013 at 09:24 UTC

    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 :-)

    -- Ken

Re: open sqilite path unicode (SQLITE_WIN32_HAS_WIDE means unicode)
by Anonymous Monk on Nov 01, 2013 at 23:42 UTC

    I would first check without GetANSIPathName...

    # 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 unicode filenames should work


    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

Re: open sqilite path unicode
by MidLifeXis (Monsignor) on Nov 01, 2013 at 17:58 UTC

    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.


      Hi MidLife

      Error: DBI connect('C:/incriminated path with strange characters/DATABASE.db','',...) failed: unable to open database at 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

Re: open sqilite path unicode
by ig (Vicar) on Nov 02, 2013 at 21:17 UTC

    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?

      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

        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...
Re: open sqilite path unicode
by Laurent_R (Canon) on Nov 02, 2013 at 00:08 UTC

    ...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).

Re: open sqilite path unicode
by Anonymous Monk on Feb 24, 2015 at 23:48 UTC

    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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1060799]
Front-paged by Arunbear
and the radiator hisses contentedly...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2018-04-25 19:28 GMT
Find Nodes?
    Voting Booth?