FW: [sqlite] Reading Blob data using Perl
Magic Kishor
I'm learning a lot as I study Perl and now SQLite and your invaluable
assistance did the trick.
The key lines now read:
my $newdata = $ref->{icon};
binmode OUTPUT;
print OUTPUT $newdata;
Many thanks.
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 17 October 2007 3:07 p.m.
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Reading Blob data using Perl
can't help you all the way, but below are a few comments --
On 10/16/07, Brian Rowlands (Greymouth High School)
<[EMAIL PROTECTED]> wrote:
> Hi
> I'm hoping someone can help me explain how to fix a problem I have
> with reading a Blob field from a SQLite DB using Perl. My long term
> aim is to not save it to a file but use it within my application and
> store it as a label bitmap. However, first things first.
>
> This code reads the Blob but when I view the bmp file created it is
> distorted - as though the bits have been wrapped around and ones I'd
> expect on the right are on the left of the image in the prevw. Looking
> at the icon in the SQLite DB all is well.
>
> #!/usr/bin/perl -w
> use strict;
> use DBI;
> use Cwd;
> use Win32::GUI::BitmapInline ();
> our $datafile = getdcwd()."\\mydatabase.db";
>
> # suck icon from SQLite database,
> my @array;
> my $dbh = DBI-> connect("dbi:SQLite:dbname=$datafile","","",{});#
> database handle
> my $sql = "SELECT icon FROM icons WHERE name = 'logo'";
> my $sth = $dbh-> prepare($sql); # statement handle
>
> # must have this next line but DONT KNOW WHY my $numrows =
> $sth->execute;
The line above is where you actually make your program do some work.
After all those declarations, you have to execute the statement, and
that is precisely what you are doing above. That is why you need the
above line, and without it nothing will work.
>
> open OUTPUT, ">output.bmp";
> my $ref = $sth->fetchrow_hashref;
> my $newdata = $$ref{'icon'};
I would write the above line as
my $newdata = $ref->{icon};
now, somewhere here, don't you require some binmode magic like so,
particularly on Windows
binmode OUTPUT;
> print OUTPUT $newdata;
> close OUTPUT;
>
> $sth->finish;
> undef $sth;
> $dbh->disconnect();
>
>
> Q1. Why is the line my $numrows = $sth->execute; essential? If it is
> not present I can't view the image file at all and I get a message
> that preview is not available.
> Q2. How do I fix it so the bmp file displays perfectly?
>
>
> Thanks
> Brian Rowlands
> We must accept finite disappointment, but we must never lose infinite
> hope.
> Martin Luther King Jr.
>
>
>
>
--
Puneet Kishor
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Compact statement for table to table copy
Well, you don't have to list the columns if you're not changing the ordering. INSERT INTO table1 SELECT * FROM table2; Rich Rattanni wrote: I have two tables in a database with exactly the same schema (idNum PRIMARY KEY, data1, data2) I want to copy all the records in table 2 to table 1, currently I am using the following statement: INSERT INTO table1 (data1, data2) SELECT data1, data2 FROM table2. Now this is just a simplified illustration, in my case I am copying about 10 columns over. I was wondering if there was a compact way to write the SQL statement, that copied the data over from one table to the other ignoring the primary key fields. I suspect there is not, but I figured it wouldnt hurt to ask. -- Thanks, Richard Rattanni - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
My question is how you're measuring the memory useage? Are you accounting for the space overhead of the various bookkeeping sqlite needs (i.e. master table)? The way you're creating you're table implies you're not using autoincrement for the integer field - are you accounting for the extra internal row id column? Joe Wilson wrote: It could be malloc fragmentation. Which sqlite version, operating system, and malloc implementation are you using? --- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote: Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, If ROWID is manually inserted and made descending for 1 records from 1 to 1, (or even if random number for ROWID - both these cases), the memory occupied is more. Why is this so? Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading Blob data using Perl
can't help you all the way, but below are a few comments --
On 10/16/07, Brian Rowlands (Greymouth High School)
<[EMAIL PROTECTED]> wrote:
> Hi
> I'm hoping someone can help me explain how to fix a problem I have with
> reading a Blob field from a SQLite DB using Perl. My long term aim is to
> not save it to a file but use it within my application and store it as a
> label bitmap. However, first things first.
>
> This code reads the Blob but when I view the bmp file created it is
> distorted - as though the bits have been wrapped around and ones I'd
> expect on the right are on the left of the image in the prevw. Looking
> at the icon in the SQLite DB all is well.
>
> #!/usr/bin/perl -w
> use strict;
> use DBI;
> use Cwd;
> use Win32::GUI::BitmapInline ();
> our $datafile = getdcwd()."\\mydatabase.db";
>
> # suck icon from SQLite database,
> my @array;
> my $dbh = DBI-> connect("dbi:SQLite:dbname=$datafile","","",{});#
> database handle
> my $sql = "SELECT icon FROM icons WHERE name = 'logo'";
> my $sth = $dbh-> prepare($sql); # statement handle
>
> # must have this next line but DONT KNOW WHY
> my $numrows = $sth->execute;
The line above is where you actually make your program do some work.
After all those declarations, you have to execute the statement, and
that is precisely what you are doing above. That is why you need the
above line, and without it nothing will work.
>
> open OUTPUT, ">output.bmp";
> my $ref = $sth->fetchrow_hashref;
> my $newdata = $$ref{'icon'};
I would write the above line as
my $newdata = $ref->{icon};
now, somewhere here, don't you require some binmode magic like so,
particularly on Windows
binmode OUTPUT;
> print OUTPUT $newdata;
> close OUTPUT;
>
> $sth->finish;
> undef $sth;
> $dbh->disconnect();
>
>
> Q1. Why is the line my $numrows = $sth->execute; essential? If it is not
> present I can't view the image file at all and I get a message that
> preview is not available.
> Q2. How do I fix it so the bmp file displays perfectly?
>
>
> Thanks
> Brian Rowlands
> We must accept finite disappointment, but we must never lose infinite
> hope.
> Martin Luther King Jr.
>
>
>
>
--
Puneet Kishor
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Reading Blob data using Perl
Hi
I'm hoping someone can help me explain how to fix a problem I have with
reading a Blob field from a SQLite DB using Perl. My long term aim is to
not save it to a file but use it within my application and store it as a
label bitmap. However, first things first.
This code reads the Blob but when I view the bmp file created it is
distorted - as though the bits have been wrapped around and ones I'd
expect on the right are on the left of the image in the prevw. Looking
at the icon in the SQLite DB all is well.
#!/usr/bin/perl -w
use strict;
use DBI;
use Cwd;
use Win32::GUI::BitmapInline ();
our $datafile = getdcwd()."\\mydatabase.db";
# suck icon from SQLite database,
my @array;
my $dbh = DBI-> connect("dbi:SQLite:dbname=$datafile","","",{});#
database handle
my $sql = "SELECT icon FROM icons WHERE name = 'logo'";
my $sth = $dbh-> prepare($sql); #
statement handle
# must have this next line but DONT KNOW WHY
my $numrows = $sth->execute;
open OUTPUT, ">output.bmp";
my $ref = $sth->fetchrow_hashref;
my $newdata = $$ref{'icon'};
print OUTPUT $newdata;
close OUTPUT;
$sth->finish;
undef $sth;
$dbh->disconnect();
Q1. Why is the line my $numrows = $sth->execute; essential? If it is not
present I can't view the image file at all and I get a message that
preview is not available.
Q2. How do I fix it so the bmp file displays perfectly?
Thanks
Brian Rowlands
We must accept finite disappointment, but we must never lose infinite
hope.
Martin Luther King Jr.
Re: [sqlite] sqlite 3.x lock states
Here is my current understanding (by "process" I mean a process *or* thread that has opened the database): >> *snip* >> Correct. On the other hand, our hope as always been that you do not need to understand any of this in order to use SQLite. It should all just work. The only time you might to know this stuff is when you are trying to squeeze every last ounce of concurrency out of the system. -- D. Richard Hipp <[EMAIL PROTECTED]> ... or when you're porting sqlite 3 to an embedded OS, as I am, and you need to figure out how to implement xLock, xUnlock, and xCheckReservedLock!;-) Thanks, Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite 3.x lock states
Richard Klein <[EMAIL PROTECTED]> wrote: > Hello all, > > I'm upgrading from sqlite 2 to 3, and am trying to get a handle > on the new lock states. > > Here is my current understanding (by "process" I mean a process > *or* thread that has opened the database): Or two separate database connections (obtained by separate calls to sqlite3_open_v2()), unless you have shared cache enabled using sqlite3_enable_shared_cache(). > > > > A database is in the UNLOCKED state if every process holds *no* > lock of any kind on the database. > > A database is in the SHARED state if every process holds either > no lock or a SHARED lock. Processes holding a SHARED lock may > read, but not write, the database. > > A database is in the RESERVED state if one of the processes has > a RESERVED lock. Only *one* process at a time can hold a RESERVED > lock. The process holding the RESERVED lock intends to write to > the database file by the time its current transaction ends. Every > other process must hold either no lock or a SHARED lock. A process > holding no lock can acquire a SHARED lock, but nothing stronger > than that. Processes holding SHARED locks can continue to read. > > A database is in the PENDING state if one of the processes has > a PENDING lock. Only *one* process at a time can hold a PENDING > lock. The process holding the PENDING lock intends to write to > the database file as soon as all other processes drop their SHARED > locks. Every other process must hold either no lock or a SHARED > lock. A process holding no lock cannot acquire a lock of any kind. > Processes holding SHARED locks can continue to read. > > A database is in the EXCLUSIVE state if one of the processes has > an EXCLUSIVE lock. Only *one* process at a time can hold an EX- > CLUSIVE lock. The process holding the EXCLUSIVE lock is currently > writing to the database file. Every other process must hold *no* > lock. No other process can acquire a lock of any kind. > > > > Do I have this right? > Correct. On the other hand, our hope as always been that you do not need to understand any of this in order to use SQLite. It should all just work. The only time you might to know this stuff is when you are trying to squeeze every last ounce of concurrency out of the system. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite 3.x lock states
Hello all, I'm upgrading from sqlite 2 to 3, and am trying to get a handle on the new lock states. Here is my current understanding (by "process" I mean a process *or* thread that has opened the database): A database is in the UNLOCKED state if every process holds *no* lock of any kind on the database. A database is in the SHARED state if every process holds either no lock or a SHARED lock. Processes holding a SHARED lock may read, but not write, the database. A database is in the RESERVED state if one of the processes has a RESERVED lock. Only *one* process at a time can hold a RESERVED lock. The process holding the RESERVED lock intends to write to the database file by the time its current transaction ends. Every other process must hold either no lock or a SHARED lock. A process holding no lock can acquire a SHARED lock, but nothing stronger than that. Processes holding SHARED locks can continue to read. A database is in the PENDING state if one of the processes has a PENDING lock. Only *one* process at a time can hold a PENDING lock. The process holding the PENDING lock intends to write to the database file as soon as all other processes drop their SHARED locks. Every other process must hold either no lock or a SHARED lock. A process holding no lock cannot acquire a lock of any kind. Processes holding SHARED locks can continue to read. A database is in the EXCLUSIVE state if one of the processes has an EXCLUSIVE lock. Only *one* process at a time can hold an EX- CLUSIVE lock. The process holding the EXCLUSIVE lock is currently writing to the database file. Every other process must hold *no* lock. No other process can acquire a lock of any kind. Do I have this right? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Compact statement for table to table copy
I have two tables in a database with exactly the same schema (idNum PRIMARY KEY, data1, data2) I want to copy all the records in table 2 to table 1, currently I am using the following statement: INSERT INTO table1 (data1, data2) SELECT data1, data2 FROM table2. Now this is just a simplified illustration, in my case I am copying about 10 columns over. I was wondering if there was a compact way to write the SQL statement, that copied the data over from one table to the other ignoring the primary key fields. I suspect there is not, but I figured it wouldnt hurt to ask. -- Thanks, Richard Rattanni - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
>> Brickl Roland [mailto:[EMAIL PROTECTED] wrote: >> Integer PrimaryKeys are always autoincrementing. When you don't >> specify it it uses after (2^63)-1 a random free positiv value. >> "Odekirk, Shawn" <[EMAIL PROTECTED]> wrote: >> I will compile this for Windows and see what my results are using a >> compiler that supports 64 bit integers. > "Odekirk, Shawn" <[EMAIL PROTECTED]> wrote: > Indeed, compiled using Microsoft Visual Studio 2005 it works as > described. > So, now to dive into the source and figure out how to make it work > using my old SCO tools. Well, that was easy! A little poking around and I found the SQLITE_32BIT_ROWID preprocessor symbol. Simply defining that symbol and recompiling fixed my problem. I have to say that I am very impressed with how easy it has been to compile SQLite on different platforms and with how well it seems to work. I'm looking around trying to find other projects where I can use it. Shawn - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Moreover, is it typical to have an implementation like VDBE in other databases as well? This is a common approach and has been used for a very long time. For example we used it in products produced during the 1980s because producing a virtual machine and a compiler for its application-specific instruction set was a far better solution than masses of procedural logic. At that time it was a time honored technique and not at all innovative. Look at how PostgreSQL compiles and stores SQL statements for background information on the concept. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
--- Uma Krishnan <[EMAIL PROTECTED]> wrote: > I'm a student trying to understand SQLite for my DB project. There are a > couple of aspects that > I don't quite understand: > 1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once > the code is > generated, I don't see when it's executed. Moreover, is it typical to have an > implementation > like VDBE in other databases as well? Search for sqlite3VdbeExec. It is called by sqlite3Step. The best way to understand the code is to step through sqlite3 example SQL statements with a debugger. Then follow up by reading the source files involved. I've heard that FoxPro used to JIT its queries in x86 prior to executing them, but I don't know if it's true. > 2) VIRTUAL TABLES. Why should the shared_cache be disabled when using > VIRTUAL TABLES? No idea. I've asked the same question myself. I would think it would be desirable to use FTS[123] with shared cache in a multi-threaded web server scenario. I suppose you could uncomment the code that prevents shared cache running with virtual tables, run it, and see what breaks. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Hello, I'm a student trying to understand SQLite for my DB project. There are a couple of aspects that I don't quite understand: 1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once the code is generated, I don't see when it's executed. Moreover, is it typical to have an implementation like VDBE in other databases as well? 2) VIRTUAL TABLES. Why should the shared_cache be disabled when using VIRTUAL TABLES? Thanks Uma
[sqlite] Full column names in join command: table name is used in a result set instead of alias (libversion 3.4.2)
Hi all, I am upgrading an application from version 2.8.x to 3.4.2 and I have troubles with full column names. I've preset pragmas short_column_names=OFF full_column_names=ON Then various forms of the same SQL command: 1. SELECT frg.*, ses.saCOMIName,ses.saXXX,... FROM TxOTG_Fragments frg, TxOTG_Sessions ses WHERE (frg.pkFragmentGUID='...') and (frg.fkSessionGUID=ses.pkSessionGUID) 2. SELECT frg.*, ses.saCOMIName,ses.saXXX,... FROM TxOTG_Fragments frg INNER JOIN TxOTG_Sessions ses ON (frg.fkSessionGUID=ses.pkSessionGUID) WHERE (frg.pkFragmentGUID='...') 3. SELECT frg.*, ses.saCOMIName,ses.saXXX,... FROM TxOTG_Fragments AS frg INNER JOIN TxOTG_Sessions AS ses ON (frg.fkSessionGUID=ses.pkSessionGUID) WHERE (frg.pkFragmentGUID='...') produce result set having correct frg. column names, but columns from TxOTG_sessions table have are prefixed by the table name i.e. TxOTG_Sessions. instead of alias. Please, does anybody knows what I'm missing? Thanks, pf - To unsubscribe, send email to [EMAIL PROTECTED] -
