[sqlite] NFS SQLITE problem
Hello, I am using a Beowulf Linux compute cluster to run my application. I store my data in SQLITE databases. My application needs to read various SQLITE files over a shared network over NFS. I have read a lot of NFS problems and SQLITE. I also know it is because NFS is broken somewhat. But, I am wondering why this affects my application since I don't read files at the same time (even over the network). Each process in each machine will load a different SQLITE file. And the files are read-only. What shared memory is having this contention? I open my database using the following command: sqlite3_open_v2(line, , SQLITE_OPEN_READONLY, NULL); The application simply hangs during the sqlite3_exec() function call. I also tried using the sqlite3 command line application. After typing '.schema' the application simply hangs too. I also tried doing a Ctrl-C but it just says "error: database is locked". I tried to kill the sqlite3 process and entire Linux machine dies! I am using CentOS 4.4, nfs-utils 1.0.6-70.EL4 and Linux Kernel 2.6.9-42.0.2.ELsmp. I am using sqlite3 3.5.6. I compiled sqlite3 from source. Thanks. -- --- William Emmanuel S. Yu (杨怀义) Department of Information Systems and Computer Science Ateneo de Manila University email : wyu at ateneo dot edu blog : http://hip2b2.yutivo.org/ web: http://CNG.ateneo.edu/cng/wyu/ phone : +63(2)4266001 loc. 4186 GPG: http://CNG.ateneo.net/cng/wyu/wyy.pgp Confidentiality Issue: This message is intended only for the use of the addressee and may contain information that is privileged and confidential. If you are not the intended recipient, you are hereby notified that any use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by reply and delete this message from your system. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A few question using sqlite
On Wed, Mar 12, 2008 at 11:50:57PM +0500, Zoltan Bencik scratched on the wall: > Hello all, > > I'm trying to enhance startup load and sorting in my application. In > order to speed up sorting I'm thinking of using sqlite. Since the data > I'm using gets sometimes in 10,000 rows, and using some list control > for GUI, sorting by list columns gets a bit nasty and slow. > I would like to get some useful comments if I the way I use sqlite > will really help me. > > I've a single table: > > CREATE TABLE tblObjectInfo ( > filenamechar(10) PRIMARY KEY NOT NULL, > priorityinteger NOT NULL DEFAULT 0, > signedboolean NOT NULL DEFAULT false, > crypted boolean NOT NULL DEFAULT false, > mdate timestamp, > cdate timestamp, > "owner" text, > "group"text > ); > > Then, I create index for each column, except the primary key: > > CREATE INDEX tblObjectInfo_Idx1 > ON tblObjectInfo > (priority); > CREATE INDEX tblObjectInfo_Idx2 > ON tblObjectInfo > (signed); > ... > and so on. > Then, depending on what column user hits to be sorted, I just query > the table using: > SELECT filename FROM tblObjectInfo ORDER BY [selected column name] > > and re-order my list view. > My questions are: > -- Will be this way of indexing speed up my ordered retrieval of data? Yes, although if you have an index on every column your inserts will be pretty slow. If you can, it would be best to batch them in a transaction to help with that. > -- Is there anyway, I can get the information where new inserted row > will be located, so I could correctly insert this item visually into > list view? I suppose it will be at the end of the table, but the point > is, I would like to know where in ordered result set I've just queried > it would be inserted. Or, will I have to query the table once again to > get the visual update with correct ordered list of items? I think you're missing a conceptual point: Tables have no inherent ordering. Tables are just a group collection of rows, with no explicit order or structure. If you do a SELECT without a ORDER BY, there is no guarantee about the order of the records returned. Technically, you could run the exact same SELECT statement two times in a row and get a different ordering of rows-- although this doesn't happen often in practice. Because tables have no inherent ordering, it doesn't really make sense to speak of "where new inserted row will be located." It will just be in the table, like a record thrown into a bucket. If you know what column(s) you're sorting over with ORDER BY, you should be able to figure out where a new row might go in a set of sorted output (output != table), although be sure you're using the *exact* same sorting conditions as the database (easy with numbers, not always so easy with text). This does bring up another issue, however... since the row input order is unsorted, if you ORDER BY a single non-unique column, your rows can still shift around. To get around this, you can put a unique column (like filename) as the last expression in the ORDER BY clause. Also consider that most GUIs that provide "click to sort" columns give the user a stable-sort experience. For example, in a music player application, if I click on "track number", and then "album" and then "band name," I expect to see things ordered by band name, followed by album, followed by track number. If you're pulling this out of a database and the application only provides ORDER BY with the *last* column the user clicked on, I'd get band names in order, but everything beyond that would random. Things work a bit differently if you're sorting an in-memory data structure with a stable sort, but that's not the case if you keep going back to the database for you records. Depending on how many of your columns allow duplicate entries (looks like most), your application might need to build some pretty interesting ORDER BY clauses to get the experience the user expects. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very large database
Is there any reason why a database of >20GiB would have problems being opened with sqlite3_open_v2 or have issues with sqlite3_prepare_v2. I am running on a RHEL 5 x86_64 server. Accessing the database via the legacy open and prepare functions works fine as well as the exec function. The database contains a single table with 4 integer keys and a 1300 byte string column. Thanks, Severin Swensen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
Kees, Thanks for the interest in this and replied off-list. Bart -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt Sent: 12 March 2008 22:06 To: General Discussion of SQLite Database Subject: Re: [sqlite] PHP Code That Can Store and Retrieve Images On Wed, 12 Mar 2008 18:44:36 -, Bart wrote: >Kees, > >Would you be interested to do a project for me for a fee? [..] >Regards, Bart Smissaert Answered in private mail. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP 4 and SQLite3
Hi Giovanni, On Wed, 12 Mar 2008 20:50:13 +0100, you wrote: >Hello >I must use SQLite functions of PHP 4, but it seems >that these functions doesn't works with SQLite 3. Indeed the sqlite_ functions in PHP 4 are SQLite 2 only. >What can I do? Use SQLite 2. Or find out if there is a SQLite3 capable PDO_sqlite for PHP4 (I doubt it, PHP4 practically reached end of life). >best regards > >Giovanni Rossati -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
On Wed, 12 Mar 2008 18:44:36 -, Bart wrote: >Kees, > >Would you be interested to do a project for me for a fee? [..] >Regards, Bart Smissaert Answered in private mail. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inconsistency of Returned Field Names?
On Wed, 12 Mar 2008 12:53:33 -0600 (MDT), you wrote: >I have noticed what appears to be an inconsistency of returned field >names. > >Two queries accessing the same field in the same table are returning >slightly different field names. I don't know if this is by design or by >accident and I'm simply reporting what I see. > > > >QUERY1: >--- >"SELECT [categoryIndex] FROM CategoriesContent WHERE [contentIndex] = %i >ORDER BY [categoryIndex] ASC;"; > >--> returns a field name of "categoryIndex". > > > >QUERY2: >--- >"SELECT DISTINCT [categoryIndex] FROM CategoriesContent WHERE [new] = 1 >ORDER BY [categoryIndex] ASC;"; > >--> returns a field name of "[categoryIndex]". > > > >I am using SQLite3 version 3.5.4. > >Lee Crain Either don't use [] around column names or add an alias to explicitly name the resultcolumn: SELECT DISTINCT categoryIndex FROM CategoriesContent WHERE new = 1 ORDER BY categoryIndex ASC; SELECT DISTINCT [categoryIndex] AS catindex FROM CategoriesContent WHERE [new] = 1 ORDER BY [categoryIndex] ASC; I agree it seems slightly inconsistent behaviour. [] around identifiers are not standard SQL. You would only need them (or double quotes, which is standard SQL) if you choose reserved words as identifiers. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limiting the number of results from a query per group
Paul Hilton <[EMAIL PROTECTED]> wrote: > In the sqlite documentation under > http://www.sqlite.org/lang_select.html > it says: > "The limit is applied to the entire query not to the individual SELECT > statement to which it is attached." > > Why does that not apply to the 'Select *' in this query? The phrase you quote talks about a compound query - one like this: select * from table1 union all select * from table2 limit 100; It says limit is applied to the whole union, not just to the last query in the union. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limiting the number of results from a query per group
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Thursday, March 06, 2008 7:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Limiting the number of results from a query per group "Paul Hilton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >> I have a query where I wish to limit the number of rows in the result >> per group, but not limit them overall. >> >> So for example if I have a table Intable >> >> Create Table Intable (ID Integer Primary Key, Grp Integer, .other >> stuff.); >> >> I would like to select up to a limited number of records for each >> value of grp, say 10. >This seems to work: >select * from >(select distinct grp from Intable) t1, Intable t2 >where t2.ID in (select ID from Intable t3 where t3.grp=t1.grp limit 10); >Performance will probably be abysmal. At least create an index on Grp. >Igor Tandetnik Igor, Thanks for your reply, In the sqlite documentation under http://www.sqlite.org/lang_select.html it says: "The limit is applied to the entire query not to the individual SELECT statement to which it is attached." Why does that not apply to the 'Select *' in this query? I have done it at the moment with an insert to a table Dummygrp of each value of grp, And then a triggered query on insert into Dummygrp that writes up to 10 values to the original result table for each insert. Paul Hilton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A few question using sqlite
Zoltan Bencik wrote: > Hello all, > > I'm trying to enhance startup load and sorting in my application. In > order to speed up sorting I'm thinking of using sqlite. Since the data > I'm using gets sometimes in 10,000 rows, and using some list control > for GUI, sorting by list columns gets a bit nasty and slow. > I would like to get some useful comments if I the way I use sqlite > will really help me. > > I've a single table: > > CREATE TABLE tblObjectInfo ( > filenamechar(10) PRIMARY KEY NOT NULL, > priorityinteger NOT NULL DEFAULT 0, > signedboolean NOT NULL DEFAULT false, > crypted boolean NOT NULL DEFAULT false, > mdate timestamp, > cdate timestamp, > "owner" text, > "group"text > ); > > Then, I create index for each column, except the primary key: > > CREATE INDEX tblObjectInfo_Idx1 > ON tblObjectInfo > (priority); > CREATE INDEX tblObjectInfo_Idx2 > ON tblObjectInfo > (signed); > ... > and so on. > Then, depending on what column user hits to be sorted, I just query > the table using: > SELECT filename FROM tblObjectInfo ORDER BY [selected column name] > > and re-order my list view. > > My questions are: > -- Will be this way of indexing speed up my ordered retrieval of data? Yes, but it will also increase your database size, and slow your inserts and updates, since each index will also have to be updated. You may want to create a temp index on the one column that the user has selected for sorting as needed. This index would be dropped and recreated as the sort column changes. There would be a delay as the index is created, but that only happens once when the sort column changes. This index would not be stored in your database file so its size will not be affected. For a table with 10,000 rows creating the index should be quite fast. > -- Is there anyway, I can get the information where new inserted row > will be located, so I could correctly insert this item visually into > list view? I suppose it will be at the end of the table, but the point > is, I would like to know where in ordered resultset I've just queried > it would be inserted. Or, will I have to query the table once again to > get the visual update with correct ordered list of items? No. But it probably doesn't matter after you read about selecting a subset of the list below. > -- What's the best way to approach scrolling of the list view, which > backended with this table data? In order not to query all the rows, > but just the viewable are of the list view. The list view can give all > information about viewing area(top position, bottom position). See http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for additional info. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] binding to parameters in PRAGMA statements doesn't work
Although it is certainly not a critical thing, it would be nice if binding a table-name parameter in a PRAGMA statement worked. executing "PRAGMA table_info (?)" produces a SYNTAX error. William Bardwell [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
Robert L Cochran <[EMAIL PROTECTED]> writes:
>
> Is there open source PHP code (PHP 5.x compatible) that can store and
> retrieve images from an SQLite 3.5.6 database?
>
Here's a quick example I came up with... it's using sqlite2 (I guess I have an
old version of php or something like that :P), but it should work with an
sqlite 3 database / driver once you change the string that opens the database...
import.php
getMessage());
}
// create page view database table
$db->exec('DROP TABLE images;');
$db->exec('CREATE TABLE images(id, image)');
// insert page visit in database with a prepared statement
$insert_sql = 'INSERT INTO images (image) VALUES (:image)';
$images = array('1.jpg', '2.jpg');
try {
$i = 1;
foreach( $images as $image ) {
echo 'INSERT INTO images (id,image) VALUES (' . $i . ', "' .
encodeImage($image) . '");';
echo '[' . $db->exec('INSERT INTO images (id,image) VALUES (' . $i++ . ', "'
. encodeImage($image) . '");') . ']';
}
} catch( PDOException $exception ){
die('Error: ' . $exception->getMessage());
}
?>
List.php
getMessage());
}
foreach ($db->query('SELECT * FROM images;') as $image) {
echo 'View Image ' . $image['id']
. '';
echo $image['image'] . '';
}
?>
view.php
getMessage());
}
define( 'ID', intval( $_REQUEST['id'] ) );
$image = $db->query('SELECT * FROM images;')->fetch();
header( 'Content-type: image/jpg' );
echo decodeImage($image['image']);
?>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
Good point Kees. I forgot about the serialize(). I don't used it because I use my sqlite db with PHP, .NET and VB6. But if the project is going to PHP only serialize() is the way to go. On Wed, Mar 12, 2008 at 2:38 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote: > On Wed, 12 Mar 2008 09:10:44 -0400, you wrote: > > >Here is a link to the PHP code to generate the base64 string and to > convert > >the string back to an image. > > > >http://fundisom.com/phparadise/php/image_handling/base64_image_encode > > > > In PHP it might be better to serialize(). > That works for every value type. > Just my 0.02 > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corruption
On Wed, 12 Mar 2008 20:39:06 +0530, you wrote: >Hi, > >I am using the Sqlite for my server database application purpose. >I want to make sure that the database does not get corrupted. > >If by chance if i corrupts the database by what means can i restore it back? >From a backup. Sometimes the commandline tool can recover a part: sqlite3 corrupted.db .dump | sqlite3 recovered.db >What are the various possibilities of database corrupting methods >in sqlite? so that i can try to avoid all those. http://www.sqlite.org/lockingv3.html#how_to_corrupt Of course, incorrect SQL INSERTs, DELETEs, DROPs and UPDATEs can damage your data as well. >Best Regards, >A. Sreedhar. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
hey i wanted to thank Igor, Ron, Dennis and Matthew (sorry if i forgot anyone else) with helping me with this blob stuff. -cheers c.s. --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > C S <[EMAIL PROTECTED]> wrote: > > -so it seems that despite my byte order being > wrong?, > > i get the output back out exactly how i stored it > in > > the original array. > > There is nothing wrong with the byte order. It > matches the native order > of your CPU. > > > is there a problem here i should > > be concerned about in the long run or why i should > > change the order? > > If you ever need to have the database file portable > between machines > with different architecture (e.g. Intel Mac vs > PowerPC Mac) you will > have to think about this endianness problem. > > > i am running on a Linux Ubuntu machine. > > The operating system is largely irrelevant. What's > important is what > hardware you are running on. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Inconsistency of Returned Field Names?
I have noticed what appears to be an inconsistency of returned field names. Two queries accessing the same field in the same table are returning slightly different field names. I don't know if this is by design or by accident and I'm simply reporting what I see. QUERY1: --- "SELECT [categoryIndex] FROM CategoriesContent WHERE [contentIndex] = %i ORDER BY [categoryIndex] ASC;"; --> returns a field name of "categoryIndex". QUERY2: --- "SELECT DISTINCT [categoryIndex] FROM CategoriesContent WHERE [new] = 1 ORDER BY [categoryIndex] ASC;"; --> returns a field name of "[categoryIndex]". I am using SQLite3 version 3.5.4. Lee Crain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A few question using sqlite
Hello all, I'm trying to enhance startup load and sorting in my application. In order to speed up sorting I'm thinking of using sqlite. Since the data I'm using gets sometimes in 10,000 rows, and using some list control for GUI, sorting by list columns gets a bit nasty and slow. I would like to get some useful comments if I the way I use sqlite will really help me. I've a single table: CREATE TABLE tblObjectInfo ( filenamechar(10) PRIMARY KEY NOT NULL, priorityinteger NOT NULL DEFAULT 0, signedboolean NOT NULL DEFAULT false, crypted boolean NOT NULL DEFAULT false, mdate timestamp, cdate timestamp, "owner" text, "group"text ); Then, I create index for each column, except the primary key: CREATE INDEX tblObjectInfo_Idx1 ON tblObjectInfo (priority); CREATE INDEX tblObjectInfo_Idx2 ON tblObjectInfo (signed); ... and so on. Then, depending on what column user hits to be sorted, I just query the table using: SELECT filename FROM tblObjectInfo ORDER BY [selected column name] and re-order my list view. My questions are: -- Will be this way of indexing speed up my ordered retrieval of data? -- Is there anyway, I can get the information where new inserted row will be located, so I could correctly insert this item visually into list view? I suppose it will be at the end of the table, but the point is, I would like to know where in ordered resultset I've just queried it would be inserted. Or, will I have to query the table once again to get the visual update with correct ordered list of items? -- What's the best way to approach scrolling of the list view, which backended with this table data? In order not to query all the rows, but just the viewable are of the list view. The list view can give all information about viewing area(top position, bottom position). That's about I guess, for now. Any help and hints are highly appreciated. Thanks. -zoltan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
Kees, Would you be interested to do a project for me for a fee? I need to upload/download data to/from a hosted SQLite 3 DB. This has to be done from VBA or from a VB6 AX dll. I have posted this to RAC, but there seems little interest/progress. If interested then could you contact me off-list? I could correspond in Dutch. Regards, Bart Smissaert -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt Sent: 12 March 2008 18:39 To: General Discussion of SQLite Database Subject: Re: [sqlite] PHP Code That Can Store and Retrieve Images On Wed, 12 Mar 2008 09:10:44 -0400, you wrote: >Here is a link to the PHP code to generate the base64 string and to convert >the string back to an image. > >http://fundisom.com/phparadise/php/image_handling/base64_image_encode > In PHP it might be better to serialize(). That works for every value type. Just my 0.02 -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
On Wed, 12 Mar 2008 09:10:44 -0400, you wrote: >Here is a link to the PHP code to generate the base64 string and to convert >the string back to an image. > >http://fundisom.com/phparadise/php/image_handling/base64_image_encode > In PHP it might be better to serialize(). That works for every value type. Just my 0.02 -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Param Binding Problem
Thanks to Dan and Dennis for the heads up, all works great now
Mike
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: 12 March 2008 17:12
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Param Binding Problem
Mike Marshall wrote:
>
> acQuery = sqlite3_mprintf("SELECT rowid FROM fulltext_%s WHERE contents
> MATCH '? '",sTempTableRoot.c_str());
You need to remove he quotes around the ? following MATCH. With the
quotes it is a literal string containing a question mark. Without them
it is a parameter inthe query that you can bind some string value to later.
>
> nError from the prepare is SQLITE_OK but from the bind its SQLITE_RANGE
>
This is because you don't have any parameters to be bound.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Param Binding Problem
Mike Marshall wrote:
>
> acQuery = sqlite3_mprintf("SELECT rowid FROM fulltext_%s WHERE contents
> MATCH '? '",sTempTableRoot.c_str());
You need to remove he quotes around the ? following MATCH. With the
quotes it is a literal string containing a question mark. Without them
it is a parameter inthe query that you can bind some string value to later.
>
> nError from the prepare is SQLITE_OK but from the bind its SQLITE_RANGE
>
This is because you don't have any parameters to be bound.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
C S <[EMAIL PROTECTED]> wrote: > -so it seems that despite my byte order being wrong?, > i get the output back out exactly how i stored it in > the original array. There is nothing wrong with the byte order. It matches the native order of your CPU. > is there a problem here i should > be concerned about in the long run or why i should > change the order? If you ever need to have the database file portable between machines with different architecture (e.g. Intel Mac vs PowerPC Mac) you will have to think about this endianness problem. > i am running on a Linux Ubuntu machine. The operating system is largely irrelevant. What's important is what hardware you are running on. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Param Binding Problem
On Mar 12, 2008, at 11:40 PM, Mike Marshall wrote:
> Hi all
>
>
>
> I'm trying to get param binding working and I'm obviously doing
> something
> wrong
>
>
>
> My code is
>
>
>
> acQuery = sqlite3_mprintf("SELECT rowid FROM fulltext_%s WHERE
> contents
> MATCH '? '",sTempTableRoot.c_str());
Don't put quotes around the ? character. ('? ') is a literal string. You
probably want (? || ' ') - an SQL variable with a single space appended
to it.
Dan.
>
> nError = sqlite3_prepare_v2(m_pHandle,acQuery,-1,,NULL);
>
> nError =
> sqlite3_bind_text(pStatement,1,m_sQuery.c_str(),-1,SQLITE_TRANSIENT);
>
>
>
> nError from the prepare is SQLITE_OK but from the bind its
> SQLITE_RANGE
>
>
>
> Anyone got any ideas where I'm going wrong, or is it the fact that I'm
> trying to do a bind on a MATCH (fts) that is giving me the problem.
>
>
>
> Any and all help gratefully received
>
>
>
> Mike
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
Ron -
this is my select statement:
sqlite> select imageID, hex(imageData) from Images;
1|0300060009000C000F001200150018001B00
2|010002000300040005000600070008000900
3|010002000300040005000600070008000900
-sorry for the confusion i was just copying and
pasting wrong. looks like everything looks fine if you
were to space these out.
--- "Wilson, Ron P"
<[EMAIL PROTECTED]> wrote:
> you are delimiting your dwords wrong. reverse the
> byte order. this
> resolves your leading and trailing 00 problem.
>
> sqlite> select imageID, hex(imageData) from Images;
> 1| 0300 0600 0900 0C00 0F00 1200 1500 1800 1B00
> 2| 0100 0200 0300 0400 0500 0600 0700 0800 0900
> 3| 0100 0200 0300 0400 0500 0600 0700 0800 0900
> ***
>
> RW
>
> Ron Wilson, S/W Systems Engineer III, Tyco
> Electronics, 434.455.6453
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf
> Of C S
> Sent: Wednesday, March 12, 2008 12:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQL Blobs - what am i doing
> wrong here?(code
> inside)
>
> Igor,
>
> thanks for all your help too man. here is what is
> in
> the table images so far when i do a select. its 3
> blobs:
>
> sqlite> select imageID, hex(imageData) from Images;
> 1|00 0003 0006 0009 000C 000F 0012 0015 0018 001B 00
> 2|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
> 3|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
>
*
>
> here is what i have now:
>
> const unsigned short *image;
> int i = 0;
>
> myString2 = "select imageID, imageData from Images";
>
> int status = sqlite3_prepare_v2(db, update.c_str(),
> -1, , NULL);
>
> while ((status = sqlite3_step(statement)) ==
> SQLITE_ROW)
>{
> i++
>
> if(i == 1)
> std::cout << "first row" << std::endl;
>
> else std::cout << "another row" << std::endl;
>
> num_bytes = sqlite3_column_bytes(statement, i);
> num_bytes = num_bytes / sizeof(unsigned short);
> image = new unsigned short[num_bytes];
> image = (const unsigned
> short*)sqlite3_column_blob(statement, i);
>
> for(int i = 0; i < num_bytes; i++)
>std::cout << "image " << i << ": " <<
> image[i]
> << std::endl;
>
> //delete [] image; //causes a crash so far.
>}
>
> status = sqlite3_finalize(statement);
> if (status != SQLITE_OK)
> std::cerr << "Error deleting prepared SQL
> statement" << std::endl;
>
> else std::cout << "finalized statement
> successfully"
> << std::endl;
>
> ***
>
> Output:
>
> first row
> image 0: 0
> image 1: 3
> image 2: 6
> image 3: 9
> image 4: 12
> image 5: 15
> image 6: 18
> image 7: 21
> image 8: 24
> image 9: 27
> another row
> another row
> finalized statement successfully
>
> --so for some reason i get the first
> blob(successfully, yes!) but it never increments
> after
> that. the statement is also finalized successfully.
> any reason why the other two blobs are not printing
> out?
>
> as a side note i commented out the deleting of the
> image because i got a major abort crash, no doubt
> memory leaks/forbidden access issues.
>
> thanks!!
>
>
>
>
>
>
> --- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> > C S <[EMAIL PROTECTED]> wrote:
> > > thanks for the tip on the hex. now i can see my
> > > entry of the blob. my original loop to fill the
> > > dynamic array with unsigned shorts was this:
> > >
> > > imageArray = new unsigned short[10];
> > >
> > > for(int i = 0; i < 10; i++)
> > > imageArray[i] = i;
> > >
> > > so now if i run the program then do a:
> > >
> > > select imageID, hex(imageData) from Images;
> > >
> > > i get the result:
> > >
> > > 1|010002000300040005000600070008000900
> > >
> > > i am not sure where the first 2 leading zeros
> are
> > > coming from
> >
> > The first value (the one at index 0) in imageArray
> > is zero, represented
> > by two zero bytes.
> >
> > > now i need to be able to extract the data back
> out
> > of
> > > the blob with sqlite3_column_blob and
> > > sqlite3_column_bytes so that i can repopulate an
> > > unsigned short array.
> > >
> > > if i have multiple blobs in the table Images how
> > would
> > > i go about retrieving back all this information?
> >
> > You prepare a select statement, e.g.
> >
> > select imageID, imageData from Images;
> >
> > Then you loop over all rows in the resultset with
> > sqlite3_step call
> > (each call advances to the next row), and for each
> > row call
> > sqlite3_column_* once for each column you are
> > interested in.
> >
> > Igor Tandetnik
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> >
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
>
>
>
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
Igor,
haha i JUST changed this before i checked your email
to:
while ((status = sqlite3_step(statement)) ==
SQLITE_ROW)
{
std::cout << "status is: " << status <<
std::endl;
i++;
if(i == 1)
std::cout << "first row" << std::endl;
else
std::cout << "another row" << std::endl;
num_bytes = sqlite3_column_bytes(statement,
1);
num_bytes = num_bytes / sizeof(unsigned
short);
image = new unsigned short[num_bytes];
image = (const unsigned
short*)sqlite3_column_blob(statement, 1);
for(int j = 0; j < num_bytes; j++)
std::cout << "image " << j << ": " << image[j]
<< std::endl;
**
so i get the output correct back out now:
Output --
first row
image 0: 0
image 1: 3
image 2: 6
image 3: 9
image 4: 12
image 5: 15
image 6: 18
image 7: 21
image 8: 24
image 9: 27
another row
image 0: 0
image 1: 1
image 2: 2
image 3: 3
image 4: 4
image 5: 5
image 6: 6
image 7: 7
image 8: 8
image 9: 9
another row
image 0: 0
image 1: 1
image 2: 2
image 3: 3
image 4: 4
image 5: 5
image 6: 6
image 7: 7
image 8: 8
image 9: 9
***
-so it seems that despite my byte order being wrong?,
i get the output back out exactly how i stored it in
the original array. is there a problem here i should
be concerned about in the long run or why i should
change the order?
would like your response -- thanks!
oh yeah -
i am running on a Linux Ubuntu machine. the only big
endian machines now a days that i can think of are old
SGI's(didnt they switch to intel) and old mac's(which
now use intel(little endian)).
*
--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> C S <[EMAIL PROTECTED]> wrote:
> > thanks for all your help too man. here is what is
> in
> > the table images so far when i do a select. its 3
> > blobs:
> >
> > sqlite> select imageID, hex(imageData) from
> Images;
> > 1|00 0003 0006 0009 000C 000F 0012 0015 0018 001B
> 00
> > 2|00 0001 0002 0003 0004 0005 0006 0007 0008 0009
> 00
> > 3|00 0001 0002 0003 0004 0005 0006 0007 0008 0009
> 00
> >
>
*
>
> I bet you are running on a little-endian machine. On
> such a machine, the
> low-order byte comes first. So the correct breakdown
> is
>
> 1| 0300 0600 0900 0C00 0F00 1200 1500 1800 1B00
>
> For example, two bytes 0300 represent a two-byte
> integer 3 in
> little-endian (least-significant byte first,
> most-significant last).
>
> > while ((status = sqlite3_step(statement)) ==
> > SQLITE_ROW)
> > {
> >i++
> >
> >num_bytes = sqlite3_column_bytes(statement, i);
>
> 'i' refers to the row index, and is incremented with
> each step. The last
> parameter to sqlite3_column_bytes is a _column_
> index: it doesn't change
> as you scan through rows. You always pass 0 to
> retrieve the first column
> (from the current row, whatever it happens to be), 1
> to retrieve the
> second column and so on.
>
> Your code just accidentally happens to pass a
> correct column index for
> the first row, but wrong index for all subsequent
> rows.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
C S <[EMAIL PROTECTED]> wrote:
> thanks for all your help too man. here is what is in
> the table images so far when i do a select. its 3
> blobs:
>
> sqlite> select imageID, hex(imageData) from Images;
> 1|00 0003 0006 0009 000C 000F 0012 0015 0018 001B 00
> 2|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
> 3|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
> *
I bet you are running on a little-endian machine. On such a machine, the
low-order byte comes first. So the correct breakdown is
1| 0300 0600 0900 0C00 0F00 1200 1500 1800 1B00
For example, two bytes 0300 represent a two-byte integer 3 in
little-endian (least-significant byte first, most-significant last).
> while ((status = sqlite3_step(statement)) ==
> SQLITE_ROW)
> {
>i++
>
>num_bytes = sqlite3_column_bytes(statement, i);
'i' refers to the row index, and is incremented with each step. The last
parameter to sqlite3_column_bytes is a _column_ index: it doesn't change
as you scan through rows. You always pass 0 to retrieve the first column
(from the current row, whatever it happens to be), 1 to retrieve the
second column and so on.
Your code just accidentally happens to pass a correct column index for
the first row, but wrong index for all subsequent rows.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Param Binding Problem
Hi all
I'm trying to get param binding working and I'm obviously doing something
wrong
My code is
acQuery = sqlite3_mprintf("SELECT rowid FROM fulltext_%s WHERE contents
MATCH '? '",sTempTableRoot.c_str());
nError = sqlite3_prepare_v2(m_pHandle,acQuery,-1,,NULL);
nError =
sqlite3_bind_text(pStatement,1,m_sQuery.c_str(),-1,SQLITE_TRANSIENT);
nError from the prepare is SQLITE_OK but from the bind its SQLITE_RANGE
Anyone got any ideas where I'm going wrong, or is it the fact that I'm
trying to do a bind on a MATCH (fts) that is giving me the problem.
Any and all help gratefully received
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
you are delimiting your dwords wrong. reverse the byte order. this
resolves your leading and trailing 00 problem.
sqlite> select imageID, hex(imageData) from Images;
1| 0300 0600 0900 0C00 0F00 1200 1500 1800 1B00
2| 0100 0200 0300 0400 0500 0600 0700 0800 0900
3| 0100 0200 0300 0400 0500 0600 0700 0800 0900
***
RW
Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C S
Sent: Wednesday, March 12, 2008 12:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Blobs - what am i doing wrong here?(code
inside)
Igor,
thanks for all your help too man. here is what is in
the table images so far when i do a select. its 3
blobs:
sqlite> select imageID, hex(imageData) from Images;
1|00 0003 0006 0009 000C 000F 0012 0015 0018 001B 00
2|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
3|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
*
here is what i have now:
const unsigned short *image;
int i = 0;
myString2 = "select imageID, imageData from Images";
int status = sqlite3_prepare_v2(db, update.c_str(),
-1, , NULL);
while ((status = sqlite3_step(statement)) ==
SQLITE_ROW)
{
i++
if(i == 1)
std::cout << "first row" << std::endl;
else std::cout << "another row" << std::endl;
num_bytes = sqlite3_column_bytes(statement, i);
num_bytes = num_bytes / sizeof(unsigned short);
image = new unsigned short[num_bytes];
image = (const unsigned
short*)sqlite3_column_blob(statement, i);
for(int i = 0; i < num_bytes; i++)
std::cout << "image " << i << ": " << image[i]
<< std::endl;
//delete [] image; //causes a crash so far.
}
status = sqlite3_finalize(statement);
if (status != SQLITE_OK)
std::cerr << "Error deleting prepared SQL
statement" << std::endl;
else std::cout << "finalized statement successfully"
<< std::endl;
***
Output:
first row
image 0: 0
image 1: 3
image 2: 6
image 3: 9
image 4: 12
image 5: 15
image 6: 18
image 7: 21
image 8: 24
image 9: 27
another row
another row
finalized statement successfully
--so for some reason i get the first
blob(successfully, yes!) but it never increments after
that. the statement is also finalized successfully.
any reason why the other two blobs are not printing
out?
as a side note i commented out the deleting of the
image because i got a major abort crash, no doubt
memory leaks/forbidden access issues.
thanks!!
--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> C S <[EMAIL PROTECTED]> wrote:
> > thanks for the tip on the hex. now i can see my
> > entry of the blob. my original loop to fill the
> > dynamic array with unsigned shorts was this:
> >
> > imageArray = new unsigned short[10];
> >
> > for(int i = 0; i < 10; i++)
> > imageArray[i] = i;
> >
> > so now if i run the program then do a:
> >
> > select imageID, hex(imageData) from Images;
> >
> > i get the result:
> >
> > 1|010002000300040005000600070008000900
> >
> > i am not sure where the first 2 leading zeros are
> > coming from
>
> The first value (the one at index 0) in imageArray
> is zero, represented
> by two zero bytes.
>
> > now i need to be able to extract the data back out
> of
> > the blob with sqlite3_column_blob and
> > sqlite3_column_bytes so that i can repopulate an
> > unsigned short array.
> >
> > if i have multiple blobs in the table Images how
> would
> > i go about retrieving back all this information?
>
> You prepare a select statement, e.g.
>
> select imageID, imageData from Images;
>
> Then you loop over all rows in the resultset with
> sqlite3_step call
> (each call advances to the next row), and for each
> row call
> sqlite3_column_* once for each column you are
> interested in.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
replying to my own message -
update should my myString2 - that is not an actual
error, just copied and pasted it wrong. also i noticed
a problem with i in my final for loop. i changed it to
j since i was being incremented. i still get the same
output however. just to clarify.
--- C S <[EMAIL PROTECTED]> wrote:
> Igor,
>
> thanks for all your help too man. here is what is
> in
> the table images so far when i do a select. its 3
> blobs:
>
> sqlite> select imageID, hex(imageData) from Images;
> 1|00 0003 0006 0009 000C 000F 0012 0015 0018 001B 00
> 2|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
> 3|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
>
*
>
> here is what i have now:
>
> const unsigned short *image;
> int i = 0;
>
> myString2 = "select imageID, imageData from Images";
>
> int status = sqlite3_prepare_v2(db, update.c_str(),
> -1, , NULL);
>
> while ((status = sqlite3_step(statement)) ==
> SQLITE_ROW)
>{
> i++
>
> if(i == 1)
> std::cout << "first row" << std::endl;
>
> else std::cout << "another row" << std::endl;
>
> num_bytes = sqlite3_column_bytes(statement, i);
> num_bytes = num_bytes / sizeof(unsigned short);
> image = new unsigned short[num_bytes];
> image = (const unsigned
> short*)sqlite3_column_blob(statement, i);
>
> for(int i = 0; i < num_bytes; i++)
>std::cout << "image " << i << ": " <<
> image[i]
> << std::endl;
>
> //delete [] image; //causes a crash so far.
>}
>
> status = sqlite3_finalize(statement);
> if (status != SQLITE_OK)
> std::cerr << "Error deleting prepared SQL
> statement" << std::endl;
>
> else std::cout << "finalized statement
> successfully"
> << std::endl;
>
> ***
>
> Output:
>
> first row
> image 0: 0
> image 1: 3
> image 2: 6
> image 3: 9
> image 4: 12
> image 5: 15
> image 6: 18
> image 7: 21
> image 8: 24
> image 9: 27
> another row
> another row
> finalized statement successfully
>
> --so for some reason i get the first
> blob(successfully, yes!) but it never increments
> after
> that. the statement is also finalized successfully.
> any reason why the other two blobs are not printing
> out?
>
> as a side note i commented out the deleting of the
> image because i got a major abort crash, no doubt
> memory leaks/forbidden access issues.
>
> thanks!!
>
>
>
>
>
>
> --- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> > C S <[EMAIL PROTECTED]> wrote:
> > > thanks for the tip on the hex. now i can see my
> > > entry of the blob. my original loop to fill the
> > > dynamic array with unsigned shorts was this:
> > >
> > > imageArray = new unsigned short[10];
> > >
> > > for(int i = 0; i < 10; i++)
> > > imageArray[i] = i;
> > >
> > > so now if i run the program then do a:
> > >
> > > select imageID, hex(imageData) from Images;
> > >
> > > i get the result:
> > >
> > > 1|010002000300040005000600070008000900
> > >
> > > i am not sure where the first 2 leading zeros
> are
> > > coming from
> >
> > The first value (the one at index 0) in imageArray
> > is zero, represented
> > by two zero bytes.
> >
> > > now i need to be able to extract the data back
> out
> > of
> > > the blob with sqlite3_column_blob and
> > > sqlite3_column_bytes so that i can repopulate an
> > > unsigned short array.
> > >
> > > if i have multiple blobs in the table Images how
> > would
> > > i go about retrieving back all this information?
> >
> > You prepare a select statement, e.g.
> >
> > select imageID, imageData from Images;
> >
> > Then you loop over all rows in the resultset with
> > sqlite3_step call
> > (each call advances to the next row), and for each
> > row call
> > sqlite3_column_* once for each column you are
> > interested in.
> >
> > Igor Tandetnik
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> >
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
>
>
> Never miss a thing. Make Yahoo your home page.
> http://www.yahoo.com/r/hs
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
Igor,
thanks for all your help too man. here is what is in
the table images so far when i do a select. its 3
blobs:
sqlite> select imageID, hex(imageData) from Images;
1|00 0003 0006 0009 000C 000F 0012 0015 0018 001B 00
2|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
3|00 0001 0002 0003 0004 0005 0006 0007 0008 0009 00
*
here is what i have now:
const unsigned short *image;
int i = 0;
myString2 = "select imageID, imageData from Images";
int status = sqlite3_prepare_v2(db, update.c_str(),
-1, , NULL);
while ((status = sqlite3_step(statement)) ==
SQLITE_ROW)
{
i++
if(i == 1)
std::cout << "first row" << std::endl;
else std::cout << "another row" << std::endl;
num_bytes = sqlite3_column_bytes(statement, i);
num_bytes = num_bytes / sizeof(unsigned short);
image = new unsigned short[num_bytes];
image = (const unsigned
short*)sqlite3_column_blob(statement, i);
for(int i = 0; i < num_bytes; i++)
std::cout << "image " << i << ": " << image[i]
<< std::endl;
//delete [] image; //causes a crash so far.
}
status = sqlite3_finalize(statement);
if (status != SQLITE_OK)
std::cerr << "Error deleting prepared SQL
statement" << std::endl;
else std::cout << "finalized statement successfully"
<< std::endl;
***
Output:
first row
image 0: 0
image 1: 3
image 2: 6
image 3: 9
image 4: 12
image 5: 15
image 6: 18
image 7: 21
image 8: 24
image 9: 27
another row
another row
finalized statement successfully
--so for some reason i get the first
blob(successfully, yes!) but it never increments after
that. the statement is also finalized successfully.
any reason why the other two blobs are not printing
out?
as a side note i commented out the deleting of the
image because i got a major abort crash, no doubt
memory leaks/forbidden access issues.
thanks!!
--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> C S <[EMAIL PROTECTED]> wrote:
> > thanks for the tip on the hex. now i can see my
> > entry of the blob. my original loop to fill the
> > dynamic array with unsigned shorts was this:
> >
> > imageArray = new unsigned short[10];
> >
> > for(int i = 0; i < 10; i++)
> > imageArray[i] = i;
> >
> > so now if i run the program then do a:
> >
> > select imageID, hex(imageData) from Images;
> >
> > i get the result:
> >
> > 1|010002000300040005000600070008000900
> >
> > i am not sure where the first 2 leading zeros are
> > coming from
>
> The first value (the one at index 0) in imageArray
> is zero, represented
> by two zero bytes.
>
> > now i need to be able to extract the data back out
> of
> > the blob with sqlite3_column_blob and
> > sqlite3_column_bytes so that i can repopulate an
> > unsigned short array.
> >
> > if i have multiple blobs in the table Images how
> would
> > i go about retrieving back all this information?
>
> You prepare a select statement, e.g.
>
> select imageID, imageData from Images;
>
> Then you loop over all rows in the resultset with
> sqlite3_step call
> (each call advances to the next row), and for each
> row call
> sqlite3_column_* once for each column you are
> interested in.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Two Phase commit using sqlite (Ken)
Shailesh, Sqlite does not have a 2 phase commit mechanism across hosts. In fact there is no way in sqlite currently to perform a transaction against two db's on two seperate hosts (unless of course the db's are on a shared filesystem). 1. Put the db's on a shared filesystem. In that case you'll need to be carefull of locking. There are known issues with NFS file systems that have incorrect locking mechanisms. 2. Implement your own 2 phase commit logic. Use a logging table where you would put the undo for the transaction. Then commit both db's and validate that once the commit succeeds on both systems you may purge the undo data. Or if it fails you'll need to apply the undo data back on the db's tables. You could base the undo/redo on: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Good luck. Ken Shailesh Madhukar Birari <[EMAIL PROTECTED]> wrote: Hello, Ken you are partially correct on what I am trying to implement. There are two hosts A & B, each having its on copy of databases dbA and dbB. A commit of a transaction means that it committed on both dbA and dbB. At any given point of time, both the databases should be exact replica of each other. If it cannot be done, is there a way that I can rollback committed operation in sqlite? Since in my case, it might happen that a commit on B succeeded but a commit of A failed and then I want to rollback the committed operation on B. Or is there a better way to achieve this functionality using sqlite. Kindly let me know, Regards Shailesh Date: Mon, 10 Mar 2008 08:15:30 -0700 (PDT) From: Ken Subject: Re: [sqlite] Two Phase commit using sqlite To: General Discussion of SQLite Database Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=iso-8859-1 Hello Shailesh, Seems like you may be trying to put a square peg into a round hole. Sqlite is an embedded database. Meaning it is not "distributed" as such unless you build the distribution and 2 phase commit logic yourself. Let me just clarify for a moment, by distributed you mean. a. 2 databases and 2 host platforms b. A trasnsaction that originates on host A but uses host b c. A transaction that upon commit on host a implies a commit on host b. d. A transaction that upon error of commit logic, host B will enter its transaction as "in doubt". e. A transaction that is rolled back on host A will also rollback on host b. However, Sqlite does have the capability to have two databases. You can attach a database and run a transaction/rolllback. But I'm not certain that it could recover from a failure when the databases reside on seperate filesystems (nfs/san/nas etc) and the DB is utilized by Host B. Consider this: host A houses A.db and host b houses B.db. B.db is accessible to both host a and host b (stored on a shared file system, but host A.db is stored locally ) A process on Host A opens A.db and Attaches B.db, opens a transaction and modifies data such that writes occur to A.db and B.db (ie journal files are created and modified data is written out). Then Lets say Host B becomes unavailble. Then a Process on Host B attempts to utilize B.db I think you'll get a failure of corruption of some form at this stage since B.db is involved in a transaction, but the journal file is not available for a recovery. Hope that helps. Ken Shailesh Madhukar Birari wrote: Hello, I want to implement a distributed database using sqlite. Can some one give me information of whether SQLite supports two phase commit? Thanks, Shailesh. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why doesn't "where =" work for text field
[EMAIL PROTECTED] wrote: >> What about this: > >> select name, length(name), hex(name) >> from PerfTest1 >> where name like '%key5000%'; > >> hex(name) should dump raw bytes, in hexadecimal, stored in name >> column. See if there's anything unusual (perhaps a trailing NUL >> byte?) > > Returns: 'key5000'|9|276B65793530303027 > > Nothing weird there. The text stored in your table contains single quotes. In other words, the string you store is not 'key5000' but '''key5000''' . Note how the length is 9, not the expected 7. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why doesn't "where =" work for text field
>Does the value of the field really contain the single quotation marks, as >opposed to being just a delimiter? That would explain it. > >Brad It appears I am stupid (hopefully not contagious)... Select * from PerfTable1 where name = '''key5000'''; Works. I thought "'" were delimiters. I expected to see them in the hex call because they are part of what is returned by the query... Oh well, mystery solved. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why doesn't "where =" work for text field
[EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> select name, length(name), hex(name) >> from PerfTest1 >> where name like '%key5000%'; > Returns: 'key5000'|9|276B65793530303027 Does the value of the field really contain the single quotation marks, as opposed to being just a delimiter? That would explain it. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
spaces added for clarification:
1| 0100 0200 0300 0400 0500 0600 0700 0800 0900
Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C S
Sent: Wednesday, March 12, 2008 10:48 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Blobs - what am i doing wrong here?(code
inside)
Dennis,
thanks for the tip on the hex. now i can see my
entry of the blob. my original loop to fill the
dynamic array with unsigned shorts was this:
imageArray = new unsigned short[10];
for(int i = 0; i < 10; i++)
imageArray[i] = i;
so now if i run the program then do a:
select imageID, hex(imageData) from Images;
i get the result:
1|010002000300040005000600070008000900
i am not sure where the first 2 leading zeros are
coming from but that looks to be ok.
now i need to be able to extract the data back out of
the blob with sqlite3_column_blob and
sqlite3_column_bytes so that i can repopulate an
unsigned short array.
if i have multiple blobs in the table Images how would
i go about retrieving back all this information?
somehow though i would think that i would need the
number of blob entries in the database to make this
work.
for(int i = 0; i < blob_entries; i++)
{
numBytes = sqlite3_column_bytes(statement, i);
sqlite3_column_blob(statement, i);
}
--what do you think? thanks all for the help. its
appreciated.
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> C S wrote:
> >
> > i get nothing. i have a printout statement to make
> > sure an imageID was created and it was
> successfully.
> > the array is indeed dynamic and has to be. to echo
> > this is what i have:
> >
> > myString = "insert into Images(imageID, imageData)
> > values(?, ?);
> >
> > status = sqlite3_prepare_v2(db, myString.c_str(),
> -1,
> > , NULL);
> >
> > void *blob = reinterpretcast(imageArray);
> >
>
> This should be:
>
> void *blob = reinterpretcast(imageArray);
>
> > status = sqlite3_bind_blob(statement, 2, blob, 10
> *
> > sizeof(unsigned short), SQLITE_STATIC);
> >
> > statusu = sqlite3_finalize(statement);
> >
>
> You need to execute the insert statement before you
> finalize it. You
> have created and destroyed the statement, but have
> not executed it. Add
> the following between the two statements above:
>
> status = sqlite3_step(statement);
> if (status != SQLITE_OK) {
> //process error
> }
>
>
> >
> > however when i do:
> >
> > select * from Images;
> >
>
> To dump the blob data in a human readable format you
> could use the hex()
> SQL function. It will display each byte of the blob
> as two ASCII
> characters that correspond to the hexadecimal value
> of the byte.
>
> select imageID, hex(imageData) from Images;
>
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database corruption
Hi, I am using the Sqlite for my server database application purpose. I want to make sure that the database does not get corrupted. If by chance if i corrupts the database by what means can i restore it back? What are the various possibilities of database corrupting methods in sqlite? so that i can try to avoid all those. Best Regards, A. Sreedhar. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why doesn't "where =" work for text field
>What about this: >select name, length(name), hex(name) >from PerfTest1 >where name like '%key5000%'; >hex(name) should dump raw bytes, in hexadecimal, stored in name column. >See if there's anything unusual (perhaps a trailing NUL byte?) Returns: 'key5000'|9|276B65793530303027 Nothing weird there. I feel stupid (and contagious) but I sure don't see why this doesn't work! Table structure is: sqlite3 test.db 'create table PerfTest1 (name TEXT , value1 INTEGER, value2 INTEGER, value3 INTEGER, value4 INTEGER, value5 INTEGER, value6 INTEGER, value7 INTEGER, value8 INTEGER, value9 INTEGER, value10 INTEGER, value11 INTEGER, value12 INTEGER, value13 INTEGER, value14 INTEGER, value15 INTEGER, value16 INTEGER, value17 INTEGER, value18 INTEGER, value19 INTEGER, value20 INTEGER, value21 INTEGER, value22 INTEGER, value23 INTEGER, value24 INTEGER, value25 INTEGER, value26 INTEGER, value27 INTEGER, value28 INTEGER, value29 INTEGER, value30 INTEGER, value31 INTEGER, value32 INTEGER, value33 INTEGER, value34 INTEGER, value35 INTEGER, value36 INTEGER, value37 INTEGER, value38 INTEGER, value39 INTEGER, PRIMARY KEY(name, value1))' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
C S <[EMAIL PROTECTED]> wrote: > thanks for the tip on the hex. now i can see my > entry of the blob. my original loop to fill the > dynamic array with unsigned shorts was this: > > imageArray = new unsigned short[10]; > > for(int i = 0; i < 10; i++) > imageArray[i] = i; > > so now if i run the program then do a: > > select imageID, hex(imageData) from Images; > > i get the result: > > 1|010002000300040005000600070008000900 > > i am not sure where the first 2 leading zeros are > coming from The first value (the one at index 0) in imageArray is zero, represented by two zero bytes. > now i need to be able to extract the data back out of > the blob with sqlite3_column_blob and > sqlite3_column_bytes so that i can repopulate an > unsigned short array. > > if i have multiple blobs in the table Images how would > i go about retrieving back all this information? You prepare a select statement, e.g. select imageID, imageData from Images; Then you loop over all rows in the resultset with sqlite3_step call (each call advances to the next row), and for each row call sqlite3_column_* once for each column you are interested in. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why doesn't "where =" work for text field
[EMAIL PROTECTED] wrote: >> See if this statement returns any rows. Any whitespace should be >> clearly visible: > >> select '!' || name || '!' from PerfTest1 >> where name != trim(name); > > 0 rows so it's not spaces. What about this: select name, length(name), hex(name) from PerfTest1 where name like '%key5000%'; hex(name) should dump raw bytes, in hexadecimal, stored in name column. See if there's anything unusual (perhaps a trailing NUL byte?) Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3.def and sqlite3 functions
Hello, I have a one question. While sqlite3.def file doesn't have the next sqlite3 function: sqlite3_column_database_name sqlite3_column_database_name16 sqlite3_column_table_name sqlite3_column_table_name16 sqlite3_column_origin_name sqlite3_column_origin_name16 I have downloaded sqlite3.def from download section http://www.sqlite.org/download.html Predrag P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
Dennis,
thanks for the tip on the hex. now i can see my
entry of the blob. my original loop to fill the
dynamic array with unsigned shorts was this:
imageArray = new unsigned short[10];
for(int i = 0; i < 10; i++)
imageArray[i] = i;
so now if i run the program then do a:
select imageID, hex(imageData) from Images;
i get the result:
1|010002000300040005000600070008000900
i am not sure where the first 2 leading zeros are
coming from but that looks to be ok.
now i need to be able to extract the data back out of
the blob with sqlite3_column_blob and
sqlite3_column_bytes so that i can repopulate an
unsigned short array.
if i have multiple blobs in the table Images how would
i go about retrieving back all this information?
somehow though i would think that i would need the
number of blob entries in the database to make this
work.
for(int i = 0; i < blob_entries; i++)
{
numBytes = sqlite3_column_bytes(statement, i);
sqlite3_column_blob(statement, i);
}
--what do you think? thanks all for the help. its
appreciated.
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> C S wrote:
> >
> > i get nothing. i have a printout statement to make
> > sure an imageID was created and it was
> successfully.
> > the array is indeed dynamic and has to be. to echo
> > this is what i have:
> >
> > myString = "insert into Images(imageID, imageData)
> > values(?, ?);
> >
> > status = sqlite3_prepare_v2(db, myString.c_str(),
> -1,
> > , NULL);
> >
> > void *blob = reinterpretcast(imageArray);
> >
>
> This should be:
>
> void *blob = reinterpretcast(imageArray);
>
> > status = sqlite3_bind_blob(statement, 2, blob, 10
> *
> > sizeof(unsigned short), SQLITE_STATIC);
> >
> > statusu = sqlite3_finalize(statement);
> >
>
> You need to execute the insert statement before you
> finalize it. You
> have created and destroyed the statement, but have
> not executed it. Add
> the following between the two statements above:
>
> status = sqlite3_step(statement);
> if (status != SQLITE_OK) {
> //process error
> }
>
>
> >
> > however when i do:
> >
> > select * from Images;
> >
>
> To dump the blob data in a human readable format you
> could use the hex()
> SQL function. It will display each byte of the blob
> as two ASCII
> characters that correspond to the hexadecimal value
> of the byte.
>
> select imageID, hex(imageData) from Images;
>
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why doesn't "where =" work for text field
[EMAIL PROTECTED] wrote:
>
> Simple select and I cannot make it work:
>
> Select name from PerfTest1 where name = trim('key5000');
>
Since the string literal 'key5000' doesn't contain leading or trailing
spaces, there is nothing for the trim function to remove. The result of
the trim function will be the same as the argument.
Select name from PerfTest1 where name = 'key5000';
If this doesn't work then you don't have any columns that match that
string value. This is probably due to trailing (or possibly leading)
spaces in the values in the name column. You should try trimming those
values instead.
Select name from PerfTest1 where trim(name) = 'key5000';
> This works:
>
> Select name from PerfTest1 where name like '%key5000%';
>
That is because this query matches names that have trailing and/or
leading spaces.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why doesn't "where =" work for text field
[EMAIL PROTECTED] wrote:
> Simple select and I cannot make it work:
>
>Select name from PerfTest1 where name = trim('key5000');
Since 'key5000' doesn't have any leading or trailing spaces, applying
trim() to it is pointless. Did you perhaps mean trim(name) = 'key5000' ?
> This works:
>
>Select name from PerfTest1 where name like '%key5000%';
This does suggest the value of "name" column has extraneous characters
(possibly but not necessarily whitespace) surrounding the text
'key5000'.
See if this statement returns any rows. Any whitespace should be clearly
visible:
select '!' || name || '!' from PerfTest1
where name != trim(name);
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
replying to my own message - i added the step function that i needed...so: sqlite_step(statement), in between the bind and finalize.. i deleted all the entries from the table so i could start over, so when i do a select on Images i get: 1| so either somehow i cant see the contents of the blob or there is nothing there at all. any suggestions? --- C S <[EMAIL PROTECTED]> wrote: > Dennis mentioned before that it was not necessary to > do a bind on something that was defined as an > integer > primary key. > > when i do: > > select * from Images; > > 1| > 2| > > thats pretty much it. there are two entries because > i > tried to pure void cast as the previous message > suggest vs the char * way..and it "seems" to make no > difference. > > if i am supposed to bind the imageID what would that > bind look like? which bind statement am i looking > for? > thanks!! > --- "Matthew L. Creech" <[EMAIL PROTECTED]> wrote: > > > On Wed, Mar 12, 2008 at 9:37 AM, C S > > <[EMAIL PROTECTED]> wrote: > > > > > > myString = "insert into Images(imageID, > > imageData) > > > values(?, ?); > > > > > > > > > status = sqlite3_prepare_v2(db, > myString.c_str(), > > -1, > > > , NULL); > > > > > > void *blob = reinterpretcast *>(imageArray); > > > > > > > > > status = sqlite3_bind_blob(statement, 2, blob, > 10 > > * > > > sizeof(unsigned short), SQLITE_STATIC); > > > > > > statusu = sqlite3_finalize(statement); > > > > > > return sqlite3_last_insert_row(db); > > > > > > > > > > > > however when i do: > > > > > > select * from Images; > > > > > > i get no results returned to me. i just get > > returned > > > to the prompt. is there anything that i missed? > > thanks > > > again!!! > > > > > > > You're missing a bind for the first column (the > > imageID), and more > > importantly, a call to sqlite3_step() - you do > need > > one, otherwise the > > insert never executes. :) > > > > -- > > Matthew L. Creech > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
C S wrote:
>
> i get nothing. i have a printout statement to make
> sure an imageID was created and it was successfully.
> the array is indeed dynamic and has to be. to echo
> this is what i have:
>
> myString = "insert into Images(imageID, imageData)
> values(?, ?);
>
> status = sqlite3_prepare_v2(db, myString.c_str(), -1,
> , NULL);
>
> void *blob = reinterpretcast(imageArray);
>
This should be:
void *blob = reinterpretcast(imageArray);
> status = sqlite3_bind_blob(statement, 2, blob, 10 *
> sizeof(unsigned short), SQLITE_STATIC);
>
> statusu = sqlite3_finalize(statement);
>
You need to execute the insert statement before you finalize it. You
have created and destroyed the statement, but have not executed it. Add
the following between the two statements above:
status = sqlite3_step(statement);
if (status != SQLITE_OK) {
//process error
}
>
> however when i do:
>
> select * from Images;
>
To dump the blob data in a human readable format you could use the hex()
SQL function. It will display each byte of the blob as two ASCII
characters that correspond to the hexadecimal value of the byte.
select imageID, hex(imageData) from Images;
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
C S <[EMAIL PROTECTED]> wrote: > myString = "insert into Images(imageID, imageData) > values(?, ?); > > status = sqlite3_prepare_v2(db, myString.c_str(), -1, > , NULL); > > void *blob = reinterpretcast(imageArray); > > status = sqlite3_bind_blob(statement, 2, blob, 10 * > sizeof(unsigned short), SQLITE_STATIC); > > statusu = sqlite3_finalize(statement); You haven't actually _run_ the statement. This is done with sqlite3_step (between bind and finalize). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
Dennis mentioned before that it was not necessary to do a bind on something that was defined as an integer primary key. when i do: select * from Images; 1| 2| thats pretty much it. there are two entries because i tried to pure void cast as the previous message suggest vs the char * way..and it "seems" to make no difference. if i am supposed to bind the imageID what would that bind look like? which bind statement am i looking for? thanks!! --- "Matthew L. Creech" <[EMAIL PROTECTED]> wrote: > On Wed, Mar 12, 2008 at 9:37 AM, C S > <[EMAIL PROTECTED]> wrote: > > > > myString = "insert into Images(imageID, > imageData) > > values(?, ?); > > > > > > status = sqlite3_prepare_v2(db, myString.c_str(), > -1, > > , NULL); > > > > void *blob = reinterpretcast(imageArray); > > > > > > status = sqlite3_bind_blob(statement, 2, blob, 10 > * > > sizeof(unsigned short), SQLITE_STATIC); > > > > statusu = sqlite3_finalize(statement); > > > > return sqlite3_last_insert_row(db); > > > > > > > > however when i do: > > > > select * from Images; > > > > i get no results returned to me. i just get > returned > > to the prompt. is there anything that i missed? > thanks > > again!!! > > > > You're missing a bind for the first column (the > imageID), and more > importantly, a call to sqlite3_step() - you do need > one, otherwise the > insert never executes. :) > > -- > Matthew L. Creech > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
On Wed, Mar 12, 2008 at 9:37 AM, C S <[EMAIL PROTECTED]> wrote: > > myString = "insert into Images(imageID, imageData) > values(?, ?); > > > status = sqlite3_prepare_v2(db, myString.c_str(), -1, > , NULL); > > void *blob = reinterpretcast(imageArray); > > > status = sqlite3_bind_blob(statement, 2, blob, 10 * > sizeof(unsigned short), SQLITE_STATIC); > > statusu = sqlite3_finalize(statement); > > return sqlite3_last_insert_row(db); > > > > however when i do: > > select * from Images; > > i get no results returned to me. i just get returned > to the prompt. is there anything that i missed? thanks > again!!! > You're missing a bind for the first column (the imageID), and more importantly, a call to sqlite3_step() - you do need one, otherwise the insert never executes. :) -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
void *blob = reinterpretcast(imageArray);
void *blob = reinterpretcast(imageArray);
Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C S
Sent: Wednesday, March 12, 2008 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Blobs - what am i doing wrong here?(code
inside)
Dennis,
thanks for your and everyone elses help with this
problem. i actually did everything you said here and
the program executed without any errors.
i re-created the table to what you said as now so it
is blob now instead of text.
i get nothing. i have a printout statement to make
sure an imageID was created and it was successfully.
the array is indeed dynamic and has to be. to echo
this is what i have:
myString = "insert into Images(imageID, imageData)
values(?, ?);
status = sqlite3_prepare_v2(db, myString.c_str(), -1,
, NULL);
void *blob = reinterpretcast(imageArray);
status = sqlite3_bind_blob(statement, 2, blob, 10 *
sizeof(unsigned short), SQLITE_STATIC);
statusu = sqlite3_finalize(statement);
return sqlite3_last_insert_row(db);
however when i do:
select * from Images;
i get no results returned to me. i just get returned
to the prompt. is there anything that i missed? thanks
again!!!
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> C S wrote:
> > hi all - i am trying once again to insert an
> image(an
> > array of shorts) into a blob.
> >
> > i created a table that is described as such:
> >
> > CREATE TABLE Images{
> > imageID integer primary key,
> > imageData text not null);
> >
> > #1 - is text ok here for a blob? maybe not - this
> may
> > be my entire problem.
> >
>
> I would recommend using a column type of BLOB for
> blob data just to
> avoid any confusion.
>
> CREATE TABLE Images (
> imageID integer primary key,
> imageData blob not null);
>
>
> > anyway instead of using data in an image i just
> made
> > an array of unsigned shorts and filled it. a size
> of
> > 10.
> >
> > so here is what i am doing in the code. imageArray
> > holds the unsigned shorts(there are 10 of them)
> >
> > char* blob = reinterpret_cast(imageArray);
> >
>
> Generally you should use a void* instead of a char*
> for blobs.
>
> void* blob = reinterpret_cast(imageArray);
>
> This pointer isn't needed if your imageArray is a
> true array of unsigned
> shorts rather than a pointer to some dynamically
> allocated memory.
>
> unsigned short imageArray[10] =
> {1,2,3,4,5,6,7,8,9,10};
>
>
> > string myString = "insert into Images(ImageID,
> > imageData) values(?, 'blob')";
>
> Note, this 'blob' is a string literal and has no
> relation at all to the
> blob variable you defined above. You need to use a ?
> for the second
> parameter as well so that you can bind a value to it
> later.
>
> string myString = "insert into Images(ImageID,
> imageData) values(?, ?)";
>
> >
> > //then i want to try to prepare the statement:
> >
> > int status = sqlite3_prepare_v2(db,
> myString.c_str(),
> > -1, , NULL);
> > if( (status != SQLITE_OK) || (statement == NULL))
> > cout << "Error preparing SQL Statement" << endl;
> >
>
> There is no need to check statement here. SQLite
> will return an error
> code other than SQLITE_OK if it fails.
>
> >
> > //now i would like to bind the blob:
> >
> > status = sqlite3_bind_blob(statement, 1,
> imageArray,
> > 10 * sizeof(unsigned short), SQLITE_TRANSIENT);
> >
>
> The blob will be the second parameter to the
> statement. This parameter
> can be static as long as the image data will be
> stable until the
> statement is executed by sqlite3_step().
>
> If you want to use the blob pointer above you should
> do this:
>
> status = sqlite3_bind_blob(statement, 2, blob,
> 10 * sizeof(unsigned short), SQLITE_STATIC);
>
> If imageArray is a true array as shown above you can
> instead do this:
>
> status = sqlite3_bind_blob(statement, 2, imageArray,
> sizeof(imageArray), SQLITE_STATIC);
>
> You have not bound a value to the first parameter,
> the imageId column,
> so it will have a null value when the statement
> executes. This is OK
> since the column is declared as "integer primary
> key" and SQLite will
> assign a unique ID value.
>
> >
> > //execute statement for each row??
> > while( (status = sqlite3_step(statement)) ==
> > SQLITE_ROW);
> >
>
> There is no need for a while loop here. An insert
> statement can only
> step once, and sqlite3_step() will return
> SQLITE_DONE or some other
> error code.
>
> > //free the prepared statement
> > status = sqlite3_finalize(statement);
> > if(status != SQLITE_OK)
> > cout << "Error deleting prepared SQL statement"
> <<
> > endl;
> >
>
=
> >
> > i actually get the last status check output,
> saying
> > that there was an error deleting the statement.
> when i
> > looked that up it
Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)
Dennis,
thanks for your and everyone elses help with this
problem. i actually did everything you said here and
the program executed without any errors.
i re-created the table to what you said as now so it
is blob now instead of text.
i get nothing. i have a printout statement to make
sure an imageID was created and it was successfully.
the array is indeed dynamic and has to be. to echo
this is what i have:
myString = "insert into Images(imageID, imageData)
values(?, ?);
status = sqlite3_prepare_v2(db, myString.c_str(), -1,
, NULL);
void *blob = reinterpretcast(imageArray);
status = sqlite3_bind_blob(statement, 2, blob, 10 *
sizeof(unsigned short), SQLITE_STATIC);
statusu = sqlite3_finalize(statement);
return sqlite3_last_insert_row(db);
however when i do:
select * from Images;
i get no results returned to me. i just get returned
to the prompt. is there anything that i missed? thanks
again!!!
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> C S wrote:
> > hi all - i am trying once again to insert an
> image(an
> > array of shorts) into a blob.
> >
> > i created a table that is described as such:
> >
> > CREATE TABLE Images{
> > imageID integer primary key,
> > imageData text not null);
> >
> > #1 - is text ok here for a blob? maybe not - this
> may
> > be my entire problem.
> >
>
> I would recommend using a column type of BLOB for
> blob data just to
> avoid any confusion.
>
> CREATE TABLE Images (
> imageID integer primary key,
> imageData blob not null);
>
>
> > anyway instead of using data in an image i just
> made
> > an array of unsigned shorts and filled it. a size
> of
> > 10.
> >
> > so here is what i am doing in the code. imageArray
> > holds the unsigned shorts(there are 10 of them)
> >
> > char* blob = reinterpret_cast(imageArray);
> >
>
> Generally you should use a void* instead of a char*
> for blobs.
>
> void* blob = reinterpret_cast(imageArray);
>
> This pointer isn't needed if your imageArray is a
> true array of unsigned
> shorts rather than a pointer to some dynamically
> allocated memory.
>
> unsigned short imageArray[10] =
> {1,2,3,4,5,6,7,8,9,10};
>
>
> > string myString = "insert into Images(ImageID,
> > imageData) values(?, 'blob')";
>
> Note, this 'blob' is a string literal and has no
> relation at all to the
> blob variable you defined above. You need to use a ?
> for the second
> parameter as well so that you can bind a value to it
> later.
>
> string myString = "insert into Images(ImageID,
> imageData) values(?, ?)";
>
> >
> > //then i want to try to prepare the statement:
> >
> > int status = sqlite3_prepare_v2(db,
> myString.c_str(),
> > -1, , NULL);
> > if( (status != SQLITE_OK) || (statement == NULL))
> > cout << "Error preparing SQL Statement" << endl;
> >
>
> There is no need to check statement here. SQLite
> will return an error
> code other than SQLITE_OK if it fails.
>
> >
> > //now i would like to bind the blob:
> >
> > status = sqlite3_bind_blob(statement, 1,
> imageArray,
> > 10 * sizeof(unsigned short), SQLITE_TRANSIENT);
> >
>
> The blob will be the second parameter to the
> statement. This parameter
> can be static as long as the image data will be
> stable until the
> statement is executed by sqlite3_step().
>
> If you want to use the blob pointer above you should
> do this:
>
> status = sqlite3_bind_blob(statement, 2, blob,
> 10 * sizeof(unsigned short), SQLITE_STATIC);
>
> If imageArray is a true array as shown above you can
> instead do this:
>
> status = sqlite3_bind_blob(statement, 2, imageArray,
> sizeof(imageArray), SQLITE_STATIC);
>
> You have not bound a value to the first parameter,
> the imageId column,
> so it will have a null value when the statement
> executes. This is OK
> since the column is declared as "integer primary
> key" and SQLite will
> assign a unique ID value.
>
> >
> > //execute statement for each row??
> > while( (status = sqlite3_step(statement)) ==
> > SQLITE_ROW);
> >
>
> There is no need for a while loop here. An insert
> statement can only
> step once, and sqlite3_step() will return
> SQLITE_DONE or some other
> error code.
>
> > //free the prepared statement
> > status = sqlite3_finalize(statement);
> > if(status != SQLITE_OK)
> > cout << "Error deleting prepared SQL statement"
> <<
> > endl;
> >
>
=
> >
> > i actually get the last status check output,
> saying
> > that there was an error deleting the statement.
> when i
> > looked that up it says there was a problem with
> the
> > prepare statement being successful or nothing
> happens
> > at all when clearly i did and clearly the status
> was
> > ok too since i didnt get an error message there.
> >
>
> I'm not sure why the finalize would fail, unless
> perhaps your prepare
> also failed, and you didn't have a valid statement
> pointer to pass to
> sqlite3_finalize().
>
> > can anyone
Re: [sqlite] PHP Code That Can Store and Retrieve Images
Here is a link to the PHP code to generate the base64 string and to convert the string back to an image. http://fundisom.com/phparadise/php/image_handling/base64_image_encode On Wed, Mar 12, 2008 at 8:56 AM, <[EMAIL PROTECTED]> wrote: > I am not actually dealing with images, but normal > text and integer data. > Got one reply now from RAC, so will see what comes from it. > > RBS > > > > > The easiest way to store an image would be to first convert the image to > > base64 and store the base64 text to the database. Converting data to > > base64 > > takes up about 33% more space than the original data. > > > > On Tue, Mar 11, 2008 at 6:22 PM, RB Smissaert < > > [EMAIL PROTECTED]> wrote: > > > >> Funny you ask that as just 2 days ago I posted a little project > >> on RAC to do exactly this. In my case it has to be called from > >> VBA or VB. Unfortunately and surprisingly no takers yet. > >> > >> RBS > >> > >> > >> -Original Message- > >> From: [EMAIL PROTECTED] > >> [mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran > >> Sent: 11 March 2008 21:15 > >> To: General Discussion of SQLite Database > >> Subject: [sqlite] PHP Code That Can Store and Retrieve Images > >> > >> Is there open source PHP code (PHP 5.x compatible) that can store and > >> retrieve images from an SQLite 3.5.6 database? > >> > >> For SQLite version 3.5.x, I need to use the PHP PDO functions if I am > >> using PHP 5.2.5, right? > >> > >> I want to show a group of people about 45 photos which I would like to > >> store on an SQLite database and then retrieve. > >> > >> Thanks > >> > >> Bob Cochran > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why doesn't "where =" work for text field
Simple select and I cannot make it work:
Select name from PerfTest1 where name = trim('key5000');
This works:
Select name from PerfTest1 where name like '%key5000%';
I don't see any spaces in the text field. Do you ALWAYS have to use a LIKE
to get a match for TEXT strings? If not, what am I missing here?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP Code That Can Store and Retrieve Images
The easiest way to store an image would be to first convert the image to base64 and store the base64 text to the database. Converting data to base64 takes up about 33% more space than the original data. On Tue, Mar 11, 2008 at 6:22 PM, RB Smissaert < [EMAIL PROTECTED]> wrote: > Funny you ask that as just 2 days ago I posted a little project > on RAC to do exactly this. In my case it has to be called from > VBA or VB. Unfortunately and surprisingly no takers yet. > > RBS > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran > Sent: 11 March 2008 21:15 > To: General Discussion of SQLite Database > Subject: [sqlite] PHP Code That Can Store and Retrieve Images > > Is there open source PHP code (PHP 5.x compatible) that can store and > retrieve images from an SQLite 3.5.6 database? > > For SQLite version 3.5.x, I need to use the PHP PDO functions if I am > using PHP 5.2.5, right? > > I want to show a group of people about 45 photos which I would like to > store on an SQLite database and then retrieve. > > Thanks > > Bob Cochran > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [C] Problem getting int results
Hello
Thanks for ypur help. I achieved what i wanted. I found out that didn't have to
bind the result. I just userd result = sqlite3_column_int to get my result.
I also had to drop the quotes to make it work properly.
Thanks a lot :-)
Original-Nachricht
> Datum: Wed, 12 Mar 2008 08:27:47 -0400
> Von: "Igor Tandetnik" <[EMAIL PROTECTED]>
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] [C] Problem getting int results
> ""Severin Müller"" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
> >> ""Severin Müller"" <[EMAIL PROTECTED]>
> >> wrote in message
> >> news:[EMAIL PROTECTED]
> >>> int func(void)
> >>> {
> >>> // database is open and the select statement is executed
> >>> // this is the query: char *sql = "SELECT 'protect' FROM
> >>> 'test_db');";
> >>
> >> You should get an error here. If you don't, the statement you
> >> actually run differs from the one you show.
> >
> > Neither. No Error, and it's axactly the statement i'm passing
>
> You sure? Complete with unmatched closing parenthesis?
>
> Yes, I see that SQLite manages to accept a string literal in the FROM
> clause, and interpret it as a table name. This is not valid SQL, SQLite
> seems to accept it as an extension.
>
> In any case, the statement likely doesn't do what you think it's doing.
> It returns a resultset with the literal string 'protect' repeated for
> each row in the table test_db. I guess you wanted to select a column
> named protect: in this case, just drop the quotes:
>
> select protect from test_db;
>
> Or, if you insist, use double quotes (though they are unnecessary in
> this case):
>
> select "protect" from "test_db";
>
> >>> {
> >>> if((rc=sqlite3_bind_int(oStmt,0,result))==SQLITE_OK)
> >>
> >> You don't have any parameter placeholders in your statement. What
> >> exactly are you binding here?
> >
> > That's what i'm asking. I don't understand what todo with this call
> > at all.
>
> That rather depends on what you are trying to achieve, which I believe
> you've never explained.
>
> For description of SQL parameters, see
>
> http://sqlite.org/c3ref/bind_blob.html
>
> To retrieve individual fields from the resultset, use sqlite3_column_*
> API:
>
> http://sqlite.org/c3ref/column_blob.html
>
> >> The variable 'result' is never modified in your code. What do you
> >> expect to see in it, other than its initial value of zero?
> >
> > i thought the variable result was changed to it's value in the
> > sqlite3_bind_in function.
>
> sqlite3_bind_int takes its 'int' parameter by value. It can't possibly
> modify it.
>
> Again, sqlite3_bind_* is for setting statement parameters. To retrieve
> values from resultset, use sqlite3_column_*
>
> Igor Tandetnik
>
>
>
--
Freundliche Grüsse // Kind regards
IBM Global Technology Services Switzerland
Severin A. Mueller
Assistant eWP Teamleader
Mythenquai 2
CH-8022 Zurich
Office: +41 44 625 30 41
Mobile: +41 76 249 85 38
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [C] Problem getting int results
""Severin Müller"" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
>> ""Severin Müller"" <[EMAIL PROTECTED]>
>> wrote in message
>> news:[EMAIL PROTECTED]
>>> int func(void)
>>> {
>>> // database is open and the select statement is executed
>>> // this is the query: char *sql = "SELECT 'protect' FROM
>>> 'test_db');";
>>
>> You should get an error here. If you don't, the statement you
>> actually run differs from the one you show.
>
> Neither. No Error, and it's axactly the statement i'm passing
You sure? Complete with unmatched closing parenthesis?
Yes, I see that SQLite manages to accept a string literal in the FROM
clause, and interpret it as a table name. This is not valid SQL, SQLite
seems to accept it as an extension.
In any case, the statement likely doesn't do what you think it's doing.
It returns a resultset with the literal string 'protect' repeated for
each row in the table test_db. I guess you wanted to select a column
named protect: in this case, just drop the quotes:
select protect from test_db;
Or, if you insist, use double quotes (though they are unnecessary in
this case):
select "protect" from "test_db";
>>> {
>>> if((rc=sqlite3_bind_int(oStmt,0,result))==SQLITE_OK)
>>
>> You don't have any parameter placeholders in your statement. What
>> exactly are you binding here?
>
> That's what i'm asking. I don't understand what todo with this call
> at all.
That rather depends on what you are trying to achieve, which I believe
you've never explained.
For description of SQL parameters, see
http://sqlite.org/c3ref/bind_blob.html
To retrieve individual fields from the resultset, use sqlite3_column_*
API:
http://sqlite.org/c3ref/column_blob.html
>> The variable 'result' is never modified in your code. What do you
>> expect to see in it, other than its initial value of zero?
>
> i thought the variable result was changed to it's value in the
> sqlite3_bind_in function.
sqlite3_bind_int takes its 'int' parameter by value. It can't possibly
modify it.
Again, sqlite3_bind_* is for setting statement parameters. To retrieve
values from resultset, use sqlite3_column_*
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corrupt db vacuums clean on 3.2.7 but not 3.5.4 or 3.5.6
En/na Josh Gibbs ha escrit: > Hi there, > > We are having some problem with DB corruption occurring > using 3.5.4. I don't know the source of the corruption, however > after extensive testing and updating to 3.5.6 in the hope of getting > some resolution to our problems I accidentally ran an older build > of the command line tool and found that it was able to vacuum a > corrupt DB back to a working state. I reported the same problem http://thread.gmane.org/gmane.comp.db.sqlite.general/36071 but in another thread I was told that vacuum isn't supposed to recover a database. It's a pity, because with 3.3.8 (and, as you say, 3.2.7) it appeared to work. Bye -- Luca Olivetti Wetron Automatización S.A. http://www.wetron.es/ Tel. +34 93 5883004 Fax +34 93 5883007 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [C] Problem getting int results
> ""Severin Müller"" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
> > int func(void)
> > {
> > // database is open and the select statement is executed
> > // this is the query: char *sql = "SELECT 'protect' FROM
> > 'test_db');";
>
> This is not valid SQL. FROM clause expects a table name, not a string
> literal.
The query is executed properly. It'd return if there was an error, i checked
for it.
>
> > struct sqlite3_stmt *oStmt;
> > int rc;
> > int result = 0;
> >
> > if((rc=sqlite3_prepare_v2(db,sql,strlen(sql),,NULL))==SQLITE_OK)
>
> You should get an error here. If you don't, the statement you actually
> run differs from the one you show.
Neither. No Error, and it's axactly the statement i'm passing
>
> > {
> > if((rc=sqlite3_bind_int(oStmt,0,result))==SQLITE_OK)
>
> You don't have any parameter placeholders in your statement. What
> exactly are you binding here?
That's what i'm asking. I don't understand what todo with this call at all.
>
> > {
> > if((rc=sqlite3_step(oStmt))==SQLITE_ROW)
> > {
> > printf("result: %i\n",result);
>
> The variable 'result' is never modified in your code. What do you expect
> to see in it, other than its initial value of zero?
i thought the variable result was changed to it's value in the sqlite3_bind_in
function.
>
> > But i always get the following error message:
> >
> > "Bind or column index out of range"
> >
> > What did i do wrong?
>
> You try to bind a parameter that doesn't exist in the statement.
>
> Igor Tandetnik
>
>
>
--
Freundliche Grüsse // Kind regards
IBM Global Technology Services Switzerland
Severin A. Mueller
Assistant eWP Teamleader
Mythenquai 2
CH-8022 Zurich
Office: +41 44 625 30 41
Mobile: +41 76 249 85 38
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [C] Problem getting int results
""Severin Müller"" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> int func(void)
> {
> // database is open and the select statement is executed
> // this is the query: char *sql = "SELECT 'protect' FROM
> 'test_db');";
This is not valid SQL. FROM clause expects a table name, not a string
literal.
> struct sqlite3_stmt *oStmt;
> int rc;
> int result = 0;
>
> if((rc=sqlite3_prepare_v2(db,sql,strlen(sql),,NULL))==SQLITE_OK)
You should get an error here. If you don't, the statement you actually
run differs from the one you show.
> {
> if((rc=sqlite3_bind_int(oStmt,0,result))==SQLITE_OK)
You don't have any parameter placeholders in your statement. What
exactly are you binding here?
> {
> if((rc=sqlite3_step(oStmt))==SQLITE_ROW)
> {
> printf("result: %i\n",result);
The variable 'result' is never modified in your code. What do you expect
to see in it, other than its initial value of zero?
> But i always get the following error message:
>
> "Bind or column index out of range"
>
> What did i do wrong?
You try to bind a parameter that doesn't exist in the statement.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Why doesn't filesize change?
On Wed, 12 Mar 2008 03:32:06 -0700, "Cory Nelson" <[EMAIL PROTECTED]> wrote: >SQLite allocates space in pages. If an insert doesn't require >allocating a new page, the file size won't grow. Good to know. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Why doesn't filesize change?
On Wed, Mar 12, 2008 at 3:03 AM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > Hello > > Out of curiosity, is it normal that the size of an SQLite file doesn't > change even when adding stuff to it, as reported by "ls" in Linux? > > Does SQLite use some kind of clusters, ie. pre-reserved space, which > would explain this? SQLite allocates space in pages. If an insert doesn't require allocating a new page, the file size won't grow. -- Cory Nelson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [3.5.4] Why doesn't filesize change?
Hello Out of curiosity, is it normal that the size of an SQLite file doesn't change even when adding stuff to it, as reported by "ls" in Linux? Does SQLite use some kind of clusters, ie. pre-reserved space, which would explain this? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and (high) concurrency
Ken & John, thank you very much indeed! That did the trick - so we could perhaps say that IGNORE safely can be ignored... Thanks also for heading me in the right direction regarding transactions, and guess what - all this cut loose several kilos of code. But perhaps the most importent effect - it restored my confidence in SQLite! Thanks again! cheers, Tore ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [C] Problem getting int results
Hello Folks
I have some problem retrieving a result from a sqlite database in C.
I have the following statement:
int func(void)
{
// database is open and the select statement is executed
// this is the query: char *sql = "SELECT 'protect' FROM 'test_db');";
struct sqlite3_stmt *oStmt;
int rc;
int result = 0;
if((rc=sqlite3_prepare_v2(db,sql,strlen(sql),,NULL))==SQLITE_OK)
{
if((rc=sqlite3_bind_int(oStmt,0,result))==SQLITE_OK)
{
if((rc=sqlite3_step(oStmt))==SQLITE_ROW)
{
printf("result: %i\n",result);
return result;
}
}
}
else
{
printf("SQL Error: %s\n",sqlite3_errmsg());
return -1;
}
}
}
But i always get the following error message:
"Bind or column index out of range"
What did i do wrong?
Thanks for your help
--
Freundliche Grüsse // Kind regards
Severin A. Mueller
Mythenquai 2
CH-8022 Zurich
Office: +41 44 278 85 03
Mobile: +41 76 249 85 38
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
