Re: [sqlite] The correct way to use the utf encoding?
If I understand what you mean by 'ansi', UTF8 is a superset of it. So just go ahead and use the UTF8 interface with your strings as they are, things will probably work fine. --- hubdog <[EMAIL PROTECTED]> wrote: > Now I want to use store and read utf string in sqlite3. > Now , I need encode my text in utf8 and pass them as insert sql to a > utf8 encoding sqlite database and > then I query the database to get the string inserted previously and > manully decode the string from utf8 to ansi to display them. > Now it looks like the encoding and decoding work all done by myself. So > I confused that what is meaning with > the utf8 encoding sqlite database, what is role of sqlite database in > processing the utf string? > __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
Re: [sqlite] problem with blobs (perl code)
On Thu, Dec 01, 2005 at 09:52:25PM -0500, [EMAIL PROTECTED] wrote: > Suppose you do this: > >sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); > > If this is part of an INSERT, say, then you will insert a 10-character > string that happens to contain a couple of extra \000 characters. > The statement: This is indeed exactly how DBD::SQLite handles an execute statement with a blob argument: it's bound as text but with the length set according to the blob length. So the retrievals happen exactly as you describe (although I had not thought to try 'cast as blob'). > So as far as I can tell, both SQLite and Perl are doing exactly what > they ought to be. That certainly could be argued, although it does seem to trap the unwary (like me) with fair regularity. One option for 'improving' it might be to make 'BLOB' become a bona fide column affinity, so that a TEXT type inserted into a BLOB column would be stored as a blob. Would there be a downside to do this? --nate
Re: [sqlite] The correct way to use the utf encoding?
On Dec 1, 2005, at 7:03 PM, hubdog wrote: Now , I need encode my text in utf8 and pass them as insert sql to a utf8 encoding sqlite database and then I query the database to get the string inserted previously and manully decode the string from utf8 to ansi to display them. I don't believe "ansi" is a text encoding. Now it looks like the encoding and decoding work all done by myself. So I confused that what is meaning with the utf8 encoding sqlite database, what is role of sqlite database in processing the utf string? Why encode and decode? Why don't you just leave your strings in UTF8?
Re: [sqlite] problem with blobs (perl code)
A very interesting discussion thread! Thanks to everyone who posted for adding to my knowledge. Bob Cochran [EMAIL PROTECTED] wrote: Matt Sergeant <[EMAIL PROTECTED]> wrote: Perl has no concept of blobs. A scalar variable can be one of: IV (integer) UV (unsigned integer) NV (double) PV (string) so a blob is just a string - but perl carries a length around with it so you can have binary data in there. SQLite does has a separate BLOB type. But for TEXT types, SQLite still works like Perl and carries around a length so that the string can have embedded '\000' characters. I just added a test to the test suite to verify that this works. Suppose you do this: sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); If this is part of an INSERT, say, then you will insert a 10-character string that happens to contain a couple of extra \000 characters. The statement: SELECT length(x) FROM table; will return 3 because the length() function is counting UTF-8 characters, not bytes, and it will stop at the first '\000'. But if you say this: SELECT length(cast(x AS blob)) FROM table; you will get 10, because length() returns the number of bytes in a blob. Similarly, if you say: SELECT quote(x) FROM table; you will get 'abc' as a reply. But if you say: SELECT quote(cast(x AS blob)) FROM table; then you will get X'6162630078797A007071' as the result. So you see, all 10 bytes of the original string are still there. So as far as I can tell, both SQLite and Perl are doing exactly what they ought to be. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] The correct way to use the utf encoding?
Now I want to use store and read utf string in sqlite3. Now , I need encode my text in utf8 and pass them as insert sql to a utf8 encoding sqlite database and then I query the database to get the string inserted previously and manully decode the string from utf8 to ansi to display them. Now it looks like the encoding and decoding work all done by myself. So I confused that what is meaning with the utf8 encoding sqlite database, what is role of sqlite database in processing the utf string?
Re: [sqlite] problem with blobs (perl code)
Matt Sergeant <[EMAIL PROTECTED]> wrote: > Perl has no concept of blobs. A scalar variable can be one of: > > IV (integer) > UV (unsigned integer) > NV (double) > PV (string) > > so a blob is just a string - but perl carries a length around with it > so you can have binary data in there. > SQLite does has a separate BLOB type. But for TEXT types, SQLite still works like Perl and carries around a length so that the string can have embedded '\000' characters. I just added a test to the test suite to verify that this works. Suppose you do this: sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); If this is part of an INSERT, say, then you will insert a 10-character string that happens to contain a couple of extra \000 characters. The statement: SELECT length(x) FROM table; will return 3 because the length() function is counting UTF-8 characters, not bytes, and it will stop at the first '\000'. But if you say this: SELECT length(cast(x AS blob)) FROM table; you will get 10, because length() returns the number of bytes in a blob. Similarly, if you say: SELECT quote(x) FROM table; you will get 'abc' as a reply. But if you say: SELECT quote(cast(x AS blob)) FROM table; then you will get X'6162630078797A007071' as the result. So you see, all 10 bytes of the original string are still there. So as far as I can tell, both SQLite and Perl are doing exactly what they ought to be. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] creating pivot queries
Is there a way to generate a query that producesa pivot table, much like MS's Jet does with TRANSFORM PIVOT BY? I did a google search, and the best I saw was to build up a bunch of case statements for each pivot value. But that would only work if you know ahead of time how many there are going to be and what values will be in there. I seem to recall seeing something a few years back where you create a temp table with all the pivot values, and by doing a cross product of the original data with the temp table, then filtering it out, you'd get the results you want, but I can't find that anymore. So is there a way to do it directly in SQLite in its supported SQL? Thanks, David
Re: [sqlite] What happens to the data obtained from sqlite3_column_*?
Henrik Ronellenfitsch <[EMAIL PROTECTED]> wrote: > What happens to the data obtained from the sqlite3_column_*-functions > after the db is closed? The data is automatically freed by SQLite the next time you call sqlite3_step(), sqlite3_reset(), or sqlite3_finalize(). If you need the data to last longer you will need to make your own copy. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On 1 Dec 2005, at 17:19, Jim Dodgen wrote: Perl is mostly typeless, or more correctly has late dynamic binding. No way to tell between a scalar used as a string, or a blob. I see no down side in having to specify the SQL_BLOB type when access a BLOB field, I just would help to have know that. 1.10 is on CPAN now with docs that cover this :-) __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
On 1 Dec 2005, at 15:10, [EMAIL PROTECTED] wrote: So in the example of $sth->execute($blob), if $blob contains an integer, use sqlite3_bind_int64(), or if $blob contains a string use sqlite3_bind_text(), or if $blob contains a blob, then use sqlite3_bind_blob(), and so forth. Is there something about perl internals that prevents the above from working? Yes. Perl has no concept of blobs. A scalar variable can be one of: IV (integer) UV (unsigned integer) NV (double) PV (string) so a blob is just a string - but perl carries a length around with it so you can have binary data in there. You'd have to check something like strlen(data) != len to determine if it contained NULs (or just scan for the NUL - same difference). Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
Jim Dodgen <[EMAIL PROTECTED]> wrote: > Perl is mostly typeless, or more correctly has late dynamic binding. No way > to > tell between a scalar used as a string, or a blob. I see no down side in > having > to specify the SQL_BLOB type when access a BLOB field, I just would help to > have know that. > > JIm Dodgen > > I think you have misunderstood my suggestion. I'm talking about having the DBD::SQLite code check the type of $blob at runtime, then invoking the appropriate sqlite3_bind api **at runtime** when the type of $blob is well known. The code that does this is inside the DBD::SQLite and is never seen by the person using DBD::SQLite. > > Quoting [EMAIL PROTECTED]: > > > So in the example of $sth->execute($blob), if $blob contains an > > integer, use sqlite3_bind_int64(), or if $blob contains a string > > use sqlite3_bind_text(), or if $blob contains a blob, then use > > sqlite3_bind_blob(), and so forth. > > > > Is there something about perl internals that prevents the above > > from working? > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > > > > > > .
Re: [sqlite] problem with blobs (perl code)
Perl is mostly typeless, or more correctly has late dynamic binding. No way to tell between a scalar used as a string, or a blob. I see no down side in having to specify the SQL_BLOB type when access a BLOB field, I just would help to have know that. JIm Dodgen Quoting [EMAIL PROTECTED]: > So in the example of $sth->execute($blob), if $blob contains an > integer, use sqlite3_bind_int64(), or if $blob contains a string > use sqlite3_bind_text(), or if $blob contains a blob, then use > sqlite3_bind_blob(), and so forth. > > Is there something about perl internals that prevents the above > from working? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
Re: [sqlite] problem with blobs (perl code)
Nathan Kurz <[EMAIL PROTECTED]> wrote: > > Inserting blobs works fine if one uses "$sth->bind_param(1, $blob, > SQL_BLOB)" as in the test. It does not work if one merely uses > "$sth->execute($blob)". While I can see that this might be behaviour > as designed, it temporarily stumped me when I first starting using it, > and searching the web I found several others stumped by it as well. > The poster to the list presumably was having the same problem. > > Looking now at the DBI documentation, I see that values bound using > execute are 'usually treated as "SQL_VARCHAR" types unless the driver > can determine the correct type (which is rare)'. Because it is simple > to scan the string for NUL's, I guess I consider this one of those > rare cases where the driver can just 'do the right thing'. > I know nothing about Perl internals and precious little about the language itself, so I'm guessing here. But surely the sqlite driver must be able to query a perl variable to figure out what datatype it is currently holding, no? And with that information, it can use whichever sqlite3_bind function is appropriate for that datatype. So in the example of $sth->execute($blob), if $blob contains an integer, use sqlite3_bind_int64(), or if $blob contains a string use sqlite3_bind_text(), or if $blob contains a blob, then use sqlite3_bind_blob(), and so forth. Is there something about perl internals that prevents the above from working? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On Thu, 1 Dec 2005, Matt Sergeant wrote: > > Looking now at the DBI documentation, I see that values bound using > > execute are 'usually treated as "SQL_VARCHAR" types unless the driver > > can determine the correct type (which is rare)'. Because it is simple > > to scan the string for NUL's, I guess I consider this one of those > > rare cases where the driver can just 'do the right thing'. > > It's pointless though to do that, because then when they try and get data > out the same way they won't understand why it's truncated. At least this > way they have to bind properly on both in and out. Oh, I forgot to answer the issue - if I did a scan for NUL bytes on every string going into the system it would really hurt those doing performance sensitive apps. Unfortunately for you that includes me :-) Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
On Thu, 1 Dec 2005, Nathan Kurz wrote: > On Thu, Dec 01, 2005 at 10:13:24AM -0500, Matt Sergeant wrote: > > On 30 Nov 2005, at 17:59, Nathan Kurz wrote: > > > > >The Perl interface through DBD-SQLite-1.09 is broken with regard to > > >blobs. It binds the result as text, thus doesn't handle NUL's. > > > > This is nonsense. The perl interface is just fine with regard to blobs > > - there's even a test suite for it. > > I apologize if I've offended you, but I think your response is a bit > harsh. I'll try to be more specific as to how I might consider it to > be broken, even if this isn't the word you would choose. While it > certainly works in some cases (including those in the test suite) I > don't think it works in all the cases it could or should. > > Inserting blobs works fine if one uses "$sth->bind_param(1, $blob, > SQL_BLOB)" as in the test. It does not work if one merely uses > "$sth->execute($blob)". While I can see that this might be behaviour > as designed, it temporarily stumped me when I first starting using it, > and searching the web I found several others stumped by it as well. > The poster to the list presumably was having the same problem. > > Looking now at the DBI documentation, I see that values bound using > execute are 'usually treated as "SQL_VARCHAR" types unless the driver > can determine the correct type (which is rare)'. Because it is simple > to scan the string for NUL's, I guess I consider this one of those > rare cases where the driver can just 'do the right thing'. It's pointless though to do that, because then when they try and get data out the same way they won't understand why it's truncated. At least this way they have to bind properly on both in and out. It's just a weakness of the weak type system that sqlite uses that means it has to be this way. I'm sorry if that's undesirable but that's the way it is. If I went the other route, and treated everything as blobs (unless explicitly bound as VARCHAR) then you would get weird results trying to do things in SQL that assume strings, particularly with unicode data. > For the second patch I offered, I think I'm on firmer ground. I don't > think it is currently possible to return NUL containing data from a > user defined function. Attached is a patch to 08create_function.t to > test for this. It fails with the released version, but works with my > patched version. While I think my patch is 'safe', I would happily > agree that there might be a more elegant solution. I'll have to look again. My time is currently under great strain, and my mac just pretty much wiped itself so I've lost 2 days to a restore. > Thanks for your work in creating this interface! I don't mean to > sound ungrateful for it. No problem. For the first issue I'd be really happy to accept a documentation patch - that's where I feel the blob support is weak :-) __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] What happens to the data obtained from sqlite3_column_*?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello! I could not find anything on this topic in the docs, so I'll ask here: What happens to the data obtained from the sqlite3_column_*-functions after the db is closed? Is it freed automatically or do I have to free it manually? I believe the docs are not very clear on that topic Regards, Henrik -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDjz9u9vAfrm0sescRAnQfAKCLIRIgpBqTUAmSM+WPpSDbKDp2xQCgiVdg /jdrgjCbIVGQ9TuBb5onR6g= =QGJk -END PGP SIGNATURE-
Re: [sqlite] problem with blobs (perl code)
On Thu, Dec 01, 2005 at 10:13:24AM -0500, Matt Sergeant wrote:
> On 30 Nov 2005, at 17:59, Nathan Kurz wrote:
>
> >The Perl interface through DBD-SQLite-1.09 is broken with regard to
> >blobs. It binds the result as text, thus doesn't handle NUL's.
>
> This is nonsense. The perl interface is just fine with regard to blobs
> - there's even a test suite for it.
I apologize if I've offended you, but I think your response is a bit
harsh. I'll try to be more specific as to how I might consider it to
be broken, even if this isn't the word you would choose. While it
certainly works in some cases (including those in the test suite) I
don't think it works in all the cases it could or should.
Inserting blobs works fine if one uses "$sth->bind_param(1, $blob,
SQL_BLOB)" as in the test. It does not work if one merely uses
"$sth->execute($blob)". While I can see that this might be behaviour
as designed, it temporarily stumped me when I first starting using it,
and searching the web I found several others stumped by it as well.
The poster to the list presumably was having the same problem.
Looking now at the DBI documentation, I see that values bound using
execute are 'usually treated as "SQL_VARCHAR" types unless the driver
can determine the correct type (which is rare)'. Because it is simple
to scan the string for NUL's, I guess I consider this one of those
rare cases where the driver can just 'do the right thing'.
For the second patch I offered, I think I'm on firmer ground. I don't
think it is currently possible to return NUL containing data from a
user defined function. Attached is a patch to 08create_function.t to
test for this. It fails with the released version, but works with my
patched version. While I think my patch is 'safe', I would happily
agree that there might be a more elegant solution.
Thanks for your work in creating this interface! I don't mean to
sound ungrateful for it.
--nate
--- t/08create_function.t.orig 2005-12-01 10:09:04.0 -0700
+++ t/08create_function.t 2005-12-01 10:28:38.0 -0700
@@ -1,5 +1,5 @@
use Test;
-BEGIN { plan tests => 18 }
+BEGIN { plan tests => 19 }
use DBI;
sub now {
@@ -44,6 +44,10 @@
return $_[0];
}
+sub return_blob {
+return "bl" . "\x00" . "ob";
+}
+
my $dbh = DBI->connect("dbi:SQLite:dbname=foo", "", "", { PrintError => 0 } );
ok($dbh);
@@ -111,4 +115,8 @@
$result = $dbh->selectrow_arrayref( "SELECT noop(1.1)" );
ok( $result && $result->[0] == 1.1 );
+$dbh->func( "return_blob", 0, \&return_blob, "create_function" );
+$result = $dbh->selectrow_arrayref( "SELECT return_blob()" );
+ok( $result && $result->[0] eq return_blob() );
+
$dbh->disconnect;
Re: [sqlite] problem with blobs (perl code)
does this mean my "workaround" is not a "workaround" but insted is the correct way to handle blobs? or am I missing something? thanks Jim Dodgen Quoting Matt Sergeant <[EMAIL PROTECTED]>: > On 30 Nov 2005, at 17:59, Nathan Kurz wrote: > > > The Perl interface through DBD-SQLite-1.09 is broken with regard to > > blobs. It binds the result as text, thus doesn't handle NUL's. > > This is nonsense. The perl interface is just fine with regard to blobs > - there's even a test suite for it. > > See t/40blobs.t in the distribution. > > Matt. > > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ >
RE: [sqlite] Feature request
I took a look at the code. My perception is that, while the new design *can* imnplement what I want, it cannot do so without writing code at the C level. since I am using sqlite via the dll "wrapper", I do not have this option, I would need an "api" level capability for this, which is not the same thing as the new virtual OsFile. > -Original Message- > From: Will Leshner [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 01, 2005 2:46 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Feature request > > > On Nov 30, 2005, at 4:18 PM, Cariotoglou Mike wrote: > > > The concept of in-memory database would be greatly enhanced > if there > > was a mechanism to serialize the internal cache to/from a > stream (not > > necessarily a disk stream). In-memory databases are > extremely useful > > for a number of applications, but ar a PITA to initalize/persist. > > With the very exciting virtual OsFile API that is being > developed, I'm wondering if we couldn't serialize a SQLite > database ourselves. I haven't looked at the entire API, so > maybe this isn't possible, but if you can create multiple > "subclasses" of OsFile in the same application, then you > could have an on-disk subclass for your file system, and an > in-memory one as well, and theoretically SQLite wouldn't know > the difference. Then you could attach one to another and copy > tables back and forth. > > >
Re: [sqlite] problem with blobs (perl code)
On 30 Nov 2005, at 17:59, Nathan Kurz wrote: The Perl interface through DBD-SQLite-1.09 is broken with regard to blobs. It binds the result as text, thus doesn't handle NUL's. This is nonsense. The perl interface is just fine with regard to blobs - there's even a test suite for it. See t/40blobs.t in the distribution. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
RE: [sqlite] Feature request
I would be in favour of this, specifically to allow shared memory database access. -Original Message- From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] Sent: 01 December 2005 00:18 To: sqlite-users@sqlite.org Subject: [sqlite] Feature request Hi all. I would like to propose an enchancement in sqlite. I am not sure whether the issue has come up before, if it has, and has been shot down, pls let me know. The concept of in-memory database would be greatly enhanced if there was a mechanism to serialize the internal cache to/from a stream (not necessarily a disk stream). In-memory databases are extremely useful for a number of applications, but ar a PITA to initalize/persist. one might argue why such a mechanism would be useful. If I need a persistent database, you could say, then I would make it a disk-based one. Not so. Consider the case where you would like to store the contents of an entire "database" as part of another storage structure. consider the case where an entire sqlite database was a field in another RDBMS. consider OO databases. Consider case tool implementation. The applications would be endless. I took a look at the ":memory:" implementation, and it seems to me that this enchancement would be trivial, consisting of maybe 10-20 c code lines, for somebody who understands the internal structures involved. unfortunately, I do not do C, so I cannot do this myself. OTOH, given the extreme usefuleness of what I propose (IMHO), could you consider this as a permament addition in sqlite? the design I have in mind would be something like this: int sqlite3_loadMemDb(sqlite3 * db,reader) int sqlite3_saveMemDb(sqlite3 * db,writer) where "reader" and "writer" are function pointers with a signature like : int reader(void mem, int size) (excuse my attempt at C syntax, this is meant only as a tip) I suspect that the "load" function might need to know the number of pages beforehand, so some kind of overloaded definiton of "reader" would be required, that would return this information. if we agree that the idea has merit, the details can be worked out easily. what does the community, and especially DRH, think about this ?
Re: [sqlite] sluggish performance in MacOS 10.4
On 25/11/2005, at 7:14 PM, Jens Miltner wrote: Hi Jens These are some really good questions. I had to reinstall system 10.2 (with a little foresight, probably should have had installed the systems on different partitions in the first place). I rebuilt the files with project builder in 10.2. The app worked fine. This build also worked fine when rebooting in system 10.4. I then rebuilt the app in system 10.2 linking to the sqlite build I did under 10.4. This also worked fine. Magically, when rebooting into 10.4 and converting this PB project to xcode and rebuilding, the build worked! In summary, I have no idea what the original problem was, but its working fine now. Thanks for the tips and feedback all. Peter Peter, no hot resolution tip here, but a couple of points you may want to investigate: - Are you sure it's related to your move to Mac OS X 10.4? - Did you try the same query with the same database in your 10.2 build? - If the updated app is still running on 10.2 - what are the results if running on 10.2? - If it's related to 10.4 - are you sure no other background service is doing disk i/o (spotlight's comes to mind)? - Did you try the same query/queries with the sqlite3 commandline executable for comparison? - Since apparently you're not using make to build sqlite: when you build with deployment build style, did you define NDEBUG=1 ? BTW: we're using sqlite 3.2.7 on 10.4 and I don't see this kind of sluggishness, but anyway sqlite performance certainly also depends on the data & schema of the database being used, so results can't be compared unless the same database is used...
