[sqlite] Error #3115: SQL Error
Hi!
I have tried to create a database and reteived data from it in SQLite for
Adobe AIR application using Flex Builder3 .But I am unable to run the
code.The code for this is as follows:
?xml version=1.0 encoding=utf-8?
mx:WindowedApplication xmlns:mx=http://www.adobe.com/2006/mxml;
creationComplete=init()
layout=vertical
mx:Script
![CDATA[
/* Import classes */
import flash.data.SQLConnection;
import flash.filesystem.File;
import flash.data.SQLResult;
import flash.data.SQLStatement;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
/* Declare properties */
private var dbConn:SQLConnection;
private var dbFile:File;
private static const CONNECT:Number = 0;
private static const CREATE_TABLE:Number = 1;
[Bindable]
private var customers:Array;
/**
* @private
* Load a local image that will be put into the
embedded database
*/
private function init():void
{
dbFile = new File(app-storage:/Customers2.db);
if(!dbFile.exists)
connect(CREATE_TABLE);
else
connect(CONNECT);
}
/**
* @private
* Create connection to embedded database and add
listeners
* @param type Number The type of connection necessary
*/
private function connect(type:Number):void
{
dbConn = new SQLConnection();
if (type)
{
dbConn.addEventListener(SQLEvent.OPEN,
createTable);
}
else
{
dbConn.addEventListener(SQLEvent.OPEN,
getCustomers);
}
dbConn.addEventListener(SQLErrorEvent.ERROR,
errorHandler);
dbConn.openAsync(dbFile);
}
/**
* @private
* Create the tables for the embedded database
* @param eventObj SQLEvent
*/
private function createTable(eventObj:SQLEvent):void
{
var dbStatement:SQLStatement = new SQLStatement();
dbStatement.sqlConnection = dbConn;
dbStatement.addEventListener(SQLEvent.RESULT,
insertCustomer);
dbStatement.addEventListener(SQLErrorEvent.ERROR,
errorHandler);
dbStatement.text = CREATE TABLE customer (id INTEGER
PRIMARY KEY, name
TEXT, title TEXT);
dbStatement.execute();
}
/**
* @private
* Insert records into the embedded database
* @param eventObj SQLEvent
*/
private function insertCustomer(eventObj:SQLEvent):void
{
var dbStatement:SQLStatement = new
SQLStatement();
dbStatement.sqlConnection = dbConn;
dbStatement.addEventListener(SQLEvent.RESULT,
getCustomers);
dbStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
dbStatement.text = INSERT INTO customer (name,
title) VALUES (’Adobe
RSS’,’Agent’);
dbStatement.execute();
}
/**
* @private
* Get the customers list from the database
* @param eventObj SQLEvent
*/
private function getCustomers(eventObj:SQLEvent):void
{
var dbStatement:SQLStatement = new
SQLStatement();
dbStatement.sqlConnection =
Re: [sqlite] How do bitwise operators work? - Example of schema and request
Can you provide examples of your schema, data, and the types of queries you want to run? This would make it easier to offer suggestions. Rich Of course Richard, In my test I was simply doind something like that : $sqlite3.exe test.db $CREATE TABLE item(id INTEGER PRIMARY KEY AUTOINCREMENT, bits BLOB); $INSERT INTO item(id,bits) VALUES (NULL, RANDOMBLOB(25000)); $INSERT INTO item(id,bits) VALUES (NULL, RANDOMBLOB(25000)); $INSERT INTO item(id,bits) VALUES (NULL, RANDOMBLOB(25000)); ... My wish is to be able to select an item like that : $SELECT id FROM item WHERE bits 1'X'; (where X is a random value from 0 to 20...) I know this is a bit tricky and not a good practice in Database, but we have a such amount of data, we need to pack it in bit arrays, and we need to keep the solution very simple... so sqlite should be a good candidate. You cannot provide your own operations, but you can provide your own functions: http://sqlite.org/c3ref/create_function.html Then you can write something like select blob_OR(blob1, blob2) from mytable; where blob_OR is your custom function. Igor Tandetnik Thanks, Igor, I will take a look on that. Bruno. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Use of attach database
On Jul 21, 2009, at 1:11 AM, Sharma, Gaurav wrote:
Hi All,
Can anybody look in to my query below and suggest me something
helpful!
http://www.sqlite.org/cvstrac/chngview?cn=6908
With Best Regards
Gaurav Sharma
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org
] On Behalf Of Sharma, Gaurav
Sent: Monday, July 20, 2009 11:57 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Use of attach database
Hi All,
I assume, it's the right place to send this mail regarding some
issues that I have notice while using sqlite latest version 3.6.16.
I have recently moved from sqlite version 3.6.1 to 3.6.16 and while
executing the command attach database ':memory:' as db2 I
experience the crash in my application. db2 is an alias for my in
memory database. The crash occures inside the function
attachFunc( sqlite3_context *context, int NotUsed, sqlite3_value
**argv ) in the below code snippet at condition
if( sqlite3StrICmp(z, zName)==0 ).
for(i=0; idb-nDb; i++){
char *z = db-aDb[i].zName;
assert( z zName );
if( sqlite3StrICmp(z, zName)==0 ){
zErrDyn = sqlite3MPrintf(db, database %s is already in use,
zName);
goto attach_error;
}
}
When i is 0, z = main and zName = db2 so its ok but when i is 1,
z = NULL and zName = db2 so sqlite3StrICmp crashes. Same situation
occure while I use version 3.6.1 code but there the crash has been
avoided by applying the condition if( z zName
sqlite3StrICmp(z, zName)==0 ). In the new code condition for ( z
zName ) has been removed.
I am not sure, whether I am not using the current code properly or
there is a bug in sqlite code. If, anyone can help me out of this
situation, will be great.
Thanks in advance
Gaurav Sharma
The information contained in this electronic mail transmission
may be privileged and confidential, and therefore, protected
from disclosure. If you have received this communication in
error, please notify us immediately by replying to this
message and deleting it from your computer without copying
or disclosing it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this electronic mail transmission
may be privileged and confidential, and therefore, protected
from disclosure. If you have received this communication in
error, please notify us immediately by replying to this
message and deleting it from your computer without copying
or disclosing it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Subqueries
Hi, I am doing an assignment using SQLite and was wondering if someone could tell me why this doesn't work and maybe offer some help please? select *, count(distinct au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(distinct au.acNum) = (select Max(int.acNumCount) from (select count(int.acNum) as int.AcNumCount from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having auNum = 0)) -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24587437.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Although your query doesn't make sense without any explanation of what did you mean and how it is supposed too work I can provide you a couple of observations: 1) Do you realize that select * doesn't make any sense in this query? The only meaningful field will be ac.AcNum, all others will be essentially trashed? 2) Looking at your query I can assume that none of your tables contain column auNum. But nonetheless you're having having auNum = 0 at the most inner query. I guess it's not what you supposed to write there. If these are not your problem then you better explain what do you want to obtain from this query and what does it return to you. Pavel On Tue, Jul 21, 2009 at 9:14 AM, Hubbooshan...@msn.com wrote: Hi, I am doing an assignment using SQLite and was wondering if someone could tell me why this doesn't work and maybe offer some help please? select *, count(distinct au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(distinct au.acNum) = (select Max(int.acNumCount) from (select count(int.acNum) as int.AcNumCount from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having auNum = 0)) -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24587437.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Subqueries
Thanks for replying OK we have several tables for our assignment and for this particular question we are asked Q. Among the academics who have no papers, who has the greatest number of interests.. I used the * just return all attributes to start with. When I use SELECT * , count( Au.AcNum ) AS num, count( i.AcNum ) AS num2 FROM academic a LEFT OUTER JOIN author Au ON a.AcNum = Au.AcNum LEFT OUTER JOIN interest i ON i.AcNUm = a.AcNum GROUP BY A.AcNum HAVING num =0 This gives me the academics with 0, that part seems to be working OK, I am struggling on how to count the second part of the question.. Database looks like Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) Paper(PaNum, Title) Author(PaNum, AcNum) Field(FieldNum, ID, Title) Interest(FieldNum, AcNum, Descrip) -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24588040.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Books
My copy of The SQL Guide to SQLite arrived yesterday and I spent some of the evening with it. Because I've read Rick's Introduction to SQL, 4th Ed. and Mike Owens' The Definitive Guide to SQLite I'll be presumptuous enough to think someone here would appreciate my thoughts on the new book. Overall, there are two important points: 1) The SQL Guide to SQLite reflects the organization and approach of Rick's generic SQL book and 2) it does not replace Mike's book. The SQL Guide to SQLite is an in-depth tutorial on the SQL language using the SQLite dialect. There is a separate chapter for each clause in the SELECT statement which is a different approach from other SQL language books I've seen in the past. The detail on each clause is valuable, and it is all presented clearly using the same basic database for all examples throughout the book. The index is extensive and appears complete. The Definitive Guide to SQLite is an in-depth tutorial and reference on SQLite as a database engine. It covers the SQL language, but in a comparatively shallow way. It is a valuable asset for all of us who use SQLite and need (or want) information on proper use and an introduction to the many language APIs. The two books have different purposes and both are worth having. Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run genfkey on temp db from app
On Mon, Jul 20, 2009 at 10:57:33PM +0200, Kees Nuyt scratched on the wall: On Mon, 20 Jul 2009 08:34:52 -0500, Jay A. Kreibich j...@kreibi.ch wrote: On Mon, Jul 20, 2009 at 12:41:59PM +0200, Jan scratched on the wall: thank you roger. Seems it's not an easy task. Guess I should go through some c tutorials. Would be really nice to have this included in sqlite itself. If someone is going to take the time to refactor the genfkey code into a stand-alone piece of code, I would like to suggest it is made into it a loadable module, so that it can be used pretty much anywhere. Also, if the code is written carefully, it is possible to use the same code to compile a module (.dll, .so, .dylib, etc.) or to compile directly into an SQLite build with very minimal changes (e.g. using #defines). That would be provide the most flexibility to the most people. -j Think Lite. I'm trying. That's why I didn't suggest just rolling it into the main code. By having it as a module you can load it (or not) and use it (or not) as needed. Those that feel they have a strong need to have the code in the engine can have it, but those of use that think it is overkill can leave it out. Heck, having it as a module would let you remove it from sqlite3 Personally I don't think this is worth it. I'm not raising my hand to take this one on. The .genfkey command does a nice job of spitting out the required SQL, making it easy to cut-and-paste, even if your database and application are running on an embedded system that can't otherwise use the sqlite3 CLI. That said, I'll say this again: *IF* someone is going to take the time to refactor the code, I would suggest it is made into a loadable module. I think the current system to generate the trigger functions is quite acceptable, but if someone is going to spend time, they might as well move in a direction that is likely to have the biggest impact and help the largest number of people. After a dynamic development phase, most databases have a static schema. At-run-time schema changes are usually a result of bad design. Run-time schema changes on referential constraints are very unlikely. Assuming the schema is static, there is no need to run .genfkey very time. There is no need to run .genfkey, but many applications need to create their own databases from scratch. There is definitely a need to be able to issue the proper CREATE TRIGGER commands, even if there isn't specifically a need to run the genfkey command. As you point out, the only need for the command is if the schema is unknown and/or dynamic. For known schemas, it is trivial to run .genfkey on an empty, stub database (even on a different platform) and extract the required SQL. This does a great job of servicing all those applications with static schema which, I agree, should be the vast majority of them. That said, there are some legit needs for dynamic use of the .genfkey code. Database managers are the first thing that come to mind. Systems that programmatically generate their tables are another**. And while it is true that this type of application is only a small part of what SQLite services, a simple and easy module is something I bet they would like. Others may have a use for it as well. ** for example, the logging system I'm working on that uses a common event definition to generate SQL, C structs, C code to pack/unpack those structs into streams, as well as C code to read/write structs to the database. * * * * * * * * As the conversation shifts from the current state of .genfkey to the bigger picture of SQLite and referential constraints, I want to be sure we all remember one very important point: The current referential constraint work-around is incomplete and can lead to silent failure. I specifically use the term work-around because I don't feel it is a solution. It is a great low-cost system that works most of the time. It also fails to live up to a true referential constraint system and can fail silently without any type of user notification that they've done something dangerous. Remember that the current solution is based off triggers, and SQLite trigger support is also incomplete-- a trigger that triggers itself will not run. From that, a constraint check that requires a constraint check on the same table will also fail to run. No error is given, the trigger is simply not called, the constraints fail to do their job, and the database becomes inconsistent: i.e. logically corrupt. This is trivial to demonstrate with a self-referencing tree table that is more than two levels deep and has a CASCADE ON DELETE constraint. If you delete a node, the database will delete the node and it's children, but the rest of the sub-tree remains,
Re: [sqlite] Subqueries
Then I guess your initial query was almost correct. Try to change it like this: select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as int.AcNumCount from interest int group by int.AcNum)) I assumed that table interest has at least 1 row for at least 1 academic. Pavel On Tue, Jul 21, 2009 at 9:49 AM, Hubbooshan...@msn.com wrote: Thanks for replying OK we have several tables for our assignment and for this particular question we are asked Q. Among the academics who have no papers, who has the greatest number of interests.. I used the * just return all attributes to start with. When I use SELECT * , count( Au.AcNum ) AS num, count( i.AcNum ) AS num2 FROM academic a LEFT OUTER JOIN author Au ON a.AcNum = Au.AcNum LEFT OUTER JOIN interest i ON i.AcNUm = a.AcNum GROUP BY A.AcNum HAVING num =0 This gives me the academics with 0, that part seems to be working OK, I am struggling on how to count the second part of the question.. Database looks like Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) Paper(PaNum, Title) Author(PaNum, AcNum) Field(FieldNum, ID, Title) Interest(FieldNum, AcNum, Descrip) -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24588040.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Subqueries
Thanks. Returns an error Likely SQL syntax error: select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as int.AcNumCount from interest int group by int.AcNum)) [ near : syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24588626.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Sorry, mis-looked: as int.AcNumCount should be as AcNumCount. Don't see other syntax errors. Pavel On Tue, Jul 21, 2009 at 10:21 AM, Hubbooshan...@msn.com wrote: Thanks. Returns an error Likely SQL syntax error: select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as int.AcNumCount from interest int group by int.AcNum)) [ near : syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24588626.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Subqueries
Hubboo shan...@msn.com wrote: Q. Among the academics who have no papers, who has the greatest number of interests.. Database looks like Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) Paper(PaNum, Title) Author(PaNum, AcNum) Field(FieldNum, ID, Title) Interest(FieldNum, AcNum, Descrip) Try this: select * from Academic where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Nah still get this error Likely SQL syntax error: select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as acNumCount from interest int group by int.AcNum)) [ near : syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] Pavel Ivanov-2 wrote: Sorry, mis-looked: as int.AcNumCount should be as AcNumCount. Don't see other syntax errors. Pavel On Tue, Jul 21, 2009 at 10:21 AM, Hubbooshan...@msn.com wrote: Thanks. Returns an error Likely SQL syntax error: select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as int.AcNumCount from interest int group by int.AcNum)) [ near : syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24588626.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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 -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24589179.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Thanks for your reply igor but i get this error Likely SQL syntax error: select * from Academic where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); [ near AcNum: syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] Igor Tandetnik wrote: Hubboo shan...@msn.com wrote: Q. Among the academics who have no papers, who has the greatest number of interests.. Database looks like Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) Paper(PaNum, Title) Author(PaNum, AcNum) Field(FieldNum, ID, Title) Interest(FieldNum, AcNum, Descrip) Try this: select * from Academic where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24589275.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
I recommend starting with a smaller query and adding to it. For example, can you do a select count from the table? Then can you do a select * from the table? Then can you do a select * with an order by? And so forth, building up the query one piece at a time until it does what you want. I'm not smart enough to write complex (or even pretty simple) queries in one go. Iterative development works well for me. - Original Message - From: Hubboo shan...@msn.com To: sqlite-users@sqlite.org Sent: Tuesday, July 21, 2009 7:55 AM Subject: Re: [sqlite] Subqueries Thanks for your reply igor but i get this error Likely SQL syntax error: select * from Academic where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); [ near AcNum: syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] Igor Tandetnik wrote: Hubboo shan...@msn.com wrote: Q. Among the academics who have no papers, who has the greatest number of interests.. Database looks like Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) Paper(PaNum, Title) Author(PaNum, AcNum) Field(FieldNum, ID, Title) Interest(FieldNum, AcNum, Descrip) Try this: select * from Academic where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24589275.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] How do bitwise operators work? - Example of schema and request
Le Hyaric Bruno bruno.le-hya...@fr.thalesgroup.com wrote: My wish is to be able to select an item like that : $SELECT id FROM item WHERE bits 1'X'; (where X is a random value from 0 to 20...) If you go custom function route for this, you may benefit from incremental blob API - see http://sqlite.org/c3ref/blob_open.html http://sqlite.org/c3ref/blob_read.html This would allow you to check individual bits (well, bytes) without having to read the whole blob into memory. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Hubboo shan...@msn.com wrote: Thanks for your reply igor but i get this error Likely SQL syntax error: select * from Academic ?where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); [ near AcNum: syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] I've actually tested this statement using the database schema you provided, and it worked for me. The statement is syntactically correct. So, either you described your database schema incorrectly, or there's a problem with whatever wrapper you are using to access SQLite. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
AH maybe its something to do with portable firefox Igor Tandetnik wrote: Hubboo shan...@msn.com wrote: Thanks for your reply igor but i get this error Likely SQL syntax error: select * from Academic ?where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); [ near AcNum: syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] I've actually tested this statement using the database schema you provided, and it worked for me. The statement is syntactically correct. So, either you described your database schema incorrectly, or there's a problem with whatever wrapper you are using to access SQLite. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24589915.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Ah it worked actually when i typed it and not pasted. Hubboo wrote: AH maybe its something to do with portable firefox Igor Tandetnik wrote: Hubboo shan...@msn.com wrote: Thanks for your reply igor but i get this error Likely SQL syntax error: select * from Academic ?where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); [ near AcNum: syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] I've actually tested this statement using the database schema you provided, and it worked for me. The statement is syntactically correct. So, either you described your database schema incorrectly, or there's a problem with whatever wrapper you are using to access SQLite. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24590028.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to :memory: database
Is it possible to have multiple connections to a purely in-memory database and if so how can one go by doing that? Would it be safe if the connections were on separate threads? -Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to :memory: database
Shaun Seckman (Firaxis) shaun.seck...@firaxis.com wrote: Is it possible to have multiple connections to a purely in-memory database No. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run genfkey on temp db from app
On Tue, 21 Jul 2009 08:57:25 -0500, Jay A. Kreibich j...@kreibi.ch wrote: On Mon, Jul 20, 2009 at 10:57:33PM +0200, Kees Nuyt scratched on the wall: Think Lite. I'm trying. That's why I didn't suggest just rolling it into the main code. By having it as a module you can load it (or not) and use it (or not) as needed. Those that feel they have a strong need to have the code in the engine can have it, but those of use that think it is overkill can leave it out. Heck, having it as a module would let you remove it from sqlite3 Yes, that's a nice solution. Actually it wasn't so smart of me to react to your previous posting, because I mostly agree with you on your balanced opinions and proposed solutions. I rather meant to oppose to the ever threatening featuritis and sometimes misplaced expectations of some overly enthousiastic SQLiters. [cut for brevity, man, you can write, I'm jealous] Regards, -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes on the table
Hi All, I need to create the indexes on the tables and these indexes have 4 columns. Let say the table definition as below: CREATE TABLE myTable( startTime INTEGER ... appId INTEGER myId INTEGER ... trafficType INTEGER .. ) StartTime can be from 1...59 appId can be from 1...256 myId can be from 1...5000 trafficType can be from 1..3 I would like to create index for this table on these columns StartTime ,appId, myId, trafficType as : create unique index myTableIndex on myTable(appId, myId, trafficType, startTime). Is the order of the columns in the create index statement importance? If yes then what is rule of thumb here? Thanks JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on the table
On 21 Jul 2009, at 11:12pm, Joanne Pham wrote: CREATE TABLE myTable( startTime INTEGER ... appId INTEGER myId INTEGER ... trafficType INTEGER .. ) StartTime can be from 1...59 appId can be from 1...256 myId can be from 1...5000 trafficType can be from 1..3 I would like to create index for this table on these columns StartTime ,appId, myId, trafficType as : create unique index myTableIndex on myTable(appId, myId, trafficType, startTime). Is the order of the columns in the create index statement importance? If yes then what is rule of thumb here? You choose what indexes to create depending on what SELECT commands you're going to use. So if none of your SELECT instructions use trafficType in the WHERE or ORDER BY clause there is no need for it in any index. Once you know which fields you want in an index, the principle is to reject as many rows as you can as soon as you can. This leaves the software fewer records to worry about at the next step, which means it needs less memory and has less processing to do. Suppose you have a thousand records and want something like SELECT * FROM myTable WHERE appId = 40 AND trafficType = 2 Suppose 1/3rd of your records have each traffic type, but 1/256th of your records have each appId. Then selecting on trafficType first would reject 2 records out of every 3, meaning that the next step has to process just 333 records, which is good. But selecting on appId first instead would reject 255 records out of every 256, meaning that the next step has to process just 4 records which is much better. So in this case an index on (appId, trafficType) would be research in a faster SELECT than (trafficType, appId). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory leak on inserting from sqlite c/c++ interfaces
Hello, I'm a bit new to sqlite, I wonder if someone can advise me here.
I'm using the Sqlite C/C++ interfaces, and I'm trying to do some very
basic things. Currently I'm just creating a database with 1 table,
and this table has 1 column of type blob. I then read some data out
of a file and insert it into the database, where each row will contain
some number N of bytes from the file, where N may not necessarily be
the same in each row.
I'm doing this using the following pseudocode:
---Initialization---
1) db = sqlite3_open_v2(C:\\foo.db)
2) sqlite3_exec(db, CREATE TABLE DummyTable (DummyColumn BLOB))
3) insert_query = sqlite3_prepare_v2(db, INSERT INTO DummyTable
(DummyColumn) VALUES (?1))
4) commit_query = sqlite3_prepare_v2(db, commit)
5) begin_query = sqlite3_prepare_v2(db, begin)
---When I want to write a chunk of the file into the database---
if (!active_transaction_)
{
//begin a new transaction
sqlite3_step(begin_query)
active_transaction_ = true;
}
//bind the data to the query and execute the query
sqlite3_bind_blob(insert_query, data, length)
sqlite3_step(insert_query) // *
sqlite3_clear_bindings(insert_query)// *
sqlite3_reset(insert_query) // *
//128 is a made up number, just for the sake of illustration
if (++count = 128)
{
//commit the transaction
sqlite3_step(commit_query)
active_transaction_ = false;
}
When I run this code for a while my memory usage grows extremely
quickly, and I don't understand why. If I remove or comment out the
three lines with //* in the code above, I get no memory leaks. If it
makes a difference when I call sqlite3_bind_blob I'm using
SQLITE_TRANSIENT for the final parameter, but my understanding is that
this is supposed to automatically free the memory when it's no longer
needed. Furthermore, the bind itself isn't what's causing the
problem, because if I leave the bind in and only comment out the
insert, I don't get the leak anymore.
Am I using the interfaces incorrectly or is perhaps something else
going on that I need to be aware of?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces
If I remove or comment out the
three lines with //* in the code above, I get no memory leaks.
So basically you're saying that if you don't insert any data into your
database and thus effectively don't do with your database anything and
thus SQLite don't have to cache anything from database then you
observe no memory leaks, right? Is it by any chance a growing database
cache is what you see? What if you execute at the very beginning of
your application this:
pragma cache_size = 1;
Pavel
On Tue, Jul 21, 2009 at 7:01 PM, Zachary Turnerdivisorthe...@gmail.com wrote:
Hello, I'm a bit new to sqlite, I wonder if someone can advise me here.
I'm using the Sqlite C/C++ interfaces, and I'm trying to do some very
basic things. Currently I'm just creating a database with 1 table,
and this table has 1 column of type blob. I then read some data out
of a file and insert it into the database, where each row will contain
some number N of bytes from the file, where N may not necessarily be
the same in each row.
I'm doing this using the following pseudocode:
---Initialization---
1) db = sqlite3_open_v2(C:\\foo.db)
2) sqlite3_exec(db, CREATE TABLE DummyTable (DummyColumn BLOB))
3) insert_query = sqlite3_prepare_v2(db, INSERT INTO DummyTable
(DummyColumn) VALUES (?1))
4) commit_query = sqlite3_prepare_v2(db, commit)
5) begin_query = sqlite3_prepare_v2(db, begin)
---When I want to write a chunk of the file into the database---
if (!active_transaction_)
{
//begin a new transaction
sqlite3_step(begin_query)
active_transaction_ = true;
}
//bind the data to the query and execute the query
sqlite3_bind_blob(insert_query, data, length)
sqlite3_step(insert_query) // *
sqlite3_clear_bindings(insert_query) // *
sqlite3_reset(insert_query) // *
//128 is a made up number, just for the sake of illustration
if (++count = 128)
{
//commit the transaction
sqlite3_step(commit_query)
active_transaction_ = false;
}
When I run this code for a while my memory usage grows extremely
quickly, and I don't understand why. If I remove or comment out the
three lines with //* in the code above, I get no memory leaks. If it
makes a difference when I call sqlite3_bind_blob I'm using
SQLITE_TRANSIENT for the final parameter, but my understanding is that
this is supposed to automatically free the memory when it's no longer
needed. Furthermore, the bind itself isn't what's causing the
problem, because if I leave the bind in and only comment out the
insert, I don't get the leak anymore.
Am I using the interfaces incorrectly or is perhaps something else
going on that I need to be aware of?
Thanks
___
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] Determine if UPDATE has worked.
Hi List,
I am new to sqlite and having trouble determining if an update has worked.
I am using the C/C++ API.
For an update, when sqlite3_step is executed it returns SQLITE_DONE when
a record is updated or if a record does not exist. I have not found an
answer by reading and searching the documentation and searching the
internet
This is the same for the command line tool.
Using code based on http://www.sqlite.org/cvstrac/wiki?p=SimpleCode;
$ fred testdb create table bob(p1 text PRIMARY KEY, p2 text)
SQLITE_DONE: 101 : unknown error
$ fred testdb insert into bob (p1, p2) values('foo', 'sue')
SQLITE_DONE: 101 : unknown error
$ fred testdb insert into bob (p1, p2) values('foo', 'sue')
Error: 19 : constraint failed
$ fred testdb update bob set p2 = 'fred' where p1 = 'foo'
SQLITE_DONE: 101 : unknown error -- update ok
$ fred testdb update bob set p2 = 'fred' where p1 = 'fo1'
SQLITE_DONE: 101 : unknown error -- update fail
$ fred testdb select count(*) from bob
count(*) = 1
SQLITE_DONE: 101 : unknown error
$ fred testdb select * from bob
p1 = foo
p2 = fred
SQLITE_DONE: 101 : unknown error
$
When using the command line tool.
SQLite version 3.6.16
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table bob(p1 text PRIMARY KEY, p2 text);
sqlite .header on
sqlite .show
echo: off
explain: off
headers: on
mode: list
nullvalue:
output: stdout
separator: |
width:
sqlite update bob set p2 = 'fred' where p1 = 'foo';
sqlite insert into bob (p1, p2, p3) values('foo', 'sue', 'todd');
SQL error: table bob has no column named p3
sqlite insert into bob (p1, p2) values('foo', 'sue');
sqlite select * from bob;
p1|p2
foo|sue
sqlite update bob set p2 = 'fred' where p1 = 'foo';
sqlite select * from bob;
p1|p2
foo|fred
sqlite update bob set p2 = 'fred' where p1 = 'fo1';
sqlite
Thanks
Steve
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determine if UPDATE has worked.
Did you look into this: http://www.sqlite.org/c3ref/changes.html ?
Pavel
On Tue, Jul 21, 2009 at 7:57 PM, Steve Martinsteve.mar...@nec.co.nz wrote:
Hi List,
I am new to sqlite and having trouble determining if an update has worked.
I am using the C/C++ API.
For an update, when sqlite3_step is executed it returns SQLITE_DONE when
a record is updated or if a record does not exist. I have not found an
answer by reading and searching the documentation and searching the
internet
This is the same for the command line tool.
Using code based on http://www.sqlite.org/cvstrac/wiki?p=SimpleCode;
$ fred testdb create table bob(p1 text PRIMARY KEY, p2 text)
SQLITE_DONE: 101 : unknown error
$ fred testdb insert into bob (p1, p2) values('foo', 'sue')
SQLITE_DONE: 101 : unknown error
$ fred testdb insert into bob (p1, p2) values('foo', 'sue')
Error: 19 : constraint failed
$ fred testdb update bob set p2 = 'fred' where p1 = 'foo'
SQLITE_DONE: 101 : unknown error -- update ok
$ fred testdb update bob set p2 = 'fred' where p1 = 'fo1'
SQLITE_DONE: 101 : unknown error -- update fail
$ fred testdb select count(*) from bob
count(*) = 1
SQLITE_DONE: 101 : unknown error
$ fred testdb select * from bob
p1 = foo
p2 = fred
SQLITE_DONE: 101 : unknown error
$
When using the command line tool.
SQLite version 3.6.16
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table bob(p1 text PRIMARY KEY, p2 text);
sqlite .header on
sqlite .show
echo: off
explain: off
headers: on
mode: list
nullvalue:
output: stdout
separator: |
width:
sqlite update bob set p2 = 'fred' where p1 = 'foo';
sqlite insert into bob (p1, p2, p3) values('foo', 'sue', 'todd');
SQL error: table bob has no column named p3
sqlite insert into bob (p1, p2) values('foo', 'sue');
sqlite select * from bob;
p1|p2
foo|sue
sqlite update bob set p2 = 'fred' where p1 = 'foo';
sqlite select * from bob;
p1|p2
foo|fred
sqlite update bob set p2 = 'fred' where p1 = 'fo1';
sqlite
Thanks
Steve
___
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] Determine if UPDATE has worked.
Pavel Ivanov wrote:
Did you look into this: http://www.sqlite.org/c3ref/changes.html ?
Pavel
On Tue, Jul 21, 2009 at 7:57 PM, Steve Martinsteve.mar...@nec.co.nz wrote:
Hi List,
I am new to sqlite and having trouble determining if an update has worked.
I am using the C/C++ API.
For an update, when sqlite3_step is executed it returns SQLITE_DONE when
a record is updated or if a record does not exist. I have not found an
answer by reading and searching the documentation and searching the
internet
This is the same for the command line tool.
Using code based on http://www.sqlite.org/cvstrac/wiki?p=SimpleCode;
$ fred testdb create table bob(p1 text PRIMARY KEY, p2 text)
SQLITE_DONE: 101 : unknown error
$ fred testdb insert into bob (p1, p2) values('foo', 'sue')
SQLITE_DONE: 101 : unknown error
$ fred testdb insert into bob (p1, p2) values('foo', 'sue')
Error: 19 : constraint failed
$ fred testdb update bob set p2 = 'fred' where p1 = 'foo'
SQLITE_DONE: 101 : unknown error -- update ok
$ fred testdb update bob set p2 = 'fred' where p1 = 'fo1'
SQLITE_DONE: 101 : unknown error -- update fail
$ fred testdb select count(*) from bob
count(*) = 1
SQLITE_DONE: 101 : unknown error
$ fred testdb select * from bob
p1 = foo
p2 = fred
SQLITE_DONE: 101 : unknown error
$
When using the command line tool.
SQLite version 3.6.16
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table bob(p1 text PRIMARY KEY, p2 text);
sqlite .header on
sqlite .show
echo: off
explain: off
headers: on
mode: list
nullvalue:
output: stdout
separator: |
width:
sqlite update bob set p2 = 'fred' where p1 = 'foo';
sqlite insert into bob (p1, p2, p3) values('foo', 'sue', 'todd');
SQL error: table bob has no column named p3
sqlite insert into bob (p1, p2) values('foo', 'sue');
sqlite select * from bob;
p1|p2
foo|sue
sqlite update bob set p2 = 'fred' where p1 = 'foo';
sqlite select * from bob;
p1|p2
foo|fred
sqlite update bob set p2 = 'fred' where p1 = 'fo1';
sqlite
Thanks
Steve
Hi Pavel,
Thanks for the info, that works for me.
Steve
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
