Re: [sqlite] error: sqlite doesn't free mutexes on windows

[Wilson, Ron P]

> sqlite-amalgamation-3_6_16.zip
> win32
> msvc 2008 sp1
> 
> func sqlite3_open_v2() is called with 'flag' param = SQLITE_OPEN_FULLMUTEX
> | SQLITE_OPEN_READWRITE
> 
> in sqlite3_initialize() func winMutexInit() is indirectly called three
> times:
>   1) in sqlite3MutexInit()
>   2) in sqlite3PcacheInitialize()
>   3) in sqlite3_os_init() (in func sqlite3_vfs_register())
> so var winMutex_lock == 3
> 
> in sqlite3_shutdown() functions
>   sqlite3PcacheShutdown()
>   sqlite3_os_end()
>   sqlite3MutexEnd()
> are called, but sqlite3PcacheShutdown() and sqlite3_os_end() do not
> decrement var winMutex_lock
> so when sqlite3MutexEnd() is called the var winMutex is still == 3, and
> mutexes are not destroyed


Did you ever resolve this?

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] translating CSV file into sqlite3 database for iPhone?

[Wilson, Ron P]

> This is why I generally advocate TAB delimited files over CSV

How does .mode tabs cope with quoted strings with tabs or newlines in them?

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] translating CSV file into sqlite3 database for iPhone?

[Wilson, Ron P]

> I'm trying to take a CSV file and create a sqlite3 database for the
> iPhone.
> The CSV file has 33K entries and is 2 MB.  The problem I am having is that
> only about 1/10 of the database file gets written into the sqlite3
> database.

The .import csv method is imperfect; if you have quoted strings in your csv 
that have commas or newlines in them, the import will do surprising things.  I 
had to write my own code to do imports with quoted strings.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Wilson, Ron P]

> Well, based on what others wrote about your initial comments, I suggest
> that you replace XP with a linux distribution. Then you can compile that
> source code all by yourself. On the other hand, if you insist on sticking
> with Microsoft, download one of the pre-built Winduhs .zip files as I
> indicated in my previous message.

SQlite compiles just fine on windows XP.  You just need tools to do it, which 
unlike linux, don't automatically come with the OS.  So there is most likely a 
teaching opportunity here, though admittedly the OP does not appear to be in a 
teachable frame of mind.

I think the OP just has the wrong expectations.  SQlite is not a windows 
application you install like MS Word or Firefox.  It is a code library that you 
embed into other code.  It is possible to use it in a 'stand alone' manner by 
using the command line utility, but I'm 99% sure this will not meet the OP's 
expectations either.

Also, I for one would appreciate it if in the future you avoid making the 
sweeping generalization that "Winduhs" users are dummies or lemmings.  I'm sure 
others would agree.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division assuredcommunicationsT
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lemon parser : compile error when using "%token_destructor" directive

[Wilson, Ron P]

Very interesting.  I had no idea that lemon.c and lempar.c were being revised.  
I assumed they were static.

Would it be too much to increment the version that lemon -x prints?  Currently 
it prints out "Lemon version 1.0" which led me to believe it was not being 
actively developed.

RW

P.S.  I like what I see in the revision history.

Ron Wilson, Engineering Project Lead, 434.455.6453

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


-Original Message-
From: Vincent Zweije [mailto:vzwe...@wcc-group.com] 
Sent: Monday, June 15, 2009 12:54 PM
To: Wilson, Ron P
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] Lemon parser : compile error when using 
"%token_destructor" directive

On Mon, Jun 15, 2009 at 11:42:26AM -0400, Wilson, Ron P wrote:

||  It has been a while since I used lemon (big fan though).  Did you resolve
||  this issue or do you still need help?

[It appears my previous response did not get through.]

Looks suspiciously like this problem, which was fixed in version 3.6.2:

http://www.sqlite.org/cvstrac/tktview?tn=3299

Ciao.

||  -Original Message-
||  From: sqlite-users-boun...@sqlite.org 
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ferrety ferrety
||  Sent: Monday, June 08, 2009 7:15 PM
||  To: sqlite-users@sqlite.org
||  Subject: [sqlite] Lemon parser : compile error when using 
"%token_destructor" directive
||
||  Hi List,
||  This is the only place I found to ask for "lemon parser" error.
||
||  When trying to use the "%token_destructor" directive:
||
||  %include {
||  #include 
||  void token_dtor (struct Token * t)
||{
||  fprintf(stderr, "In token_destructor: t -> value=%s\n", t -> value);
||}
||  }
||  %token_destructor { token_dtor($$); }
||
||
||  I got the following error:
||  error: too many arguments to function 'yy_destructor'
||
||  I tried to understand how "lemon" generated the calls for that function and
||  found that:
||
||  1) The definition of "yy_destructor" is with only 2 arguments:
||  static void yy_destructor(YYCODETYPE yymajor, YYMINORTYPE *yypminor){
||switch( yymajor ){
||  /* Here is inserted the actions which take place when a
||  ...
||
||  2) "Lemon" generates different calls to "yy_destructor", sometimes with 2 or
||  3 parameters :
||  yy_destructor( yymajor, >minor); <- HERE with 2 arguments,
||  which is fine
||  yy_destructor(yypParser,4,[0].minor); <- HERE with 3 arguments, which
||  is an error
||  yy_destructor(yypParser,5,[-2].minor); <- same
||  yy_destructor(yypParser,6,[0].minor);  <- same
||  ...
||
||  Sometime, the "yy_destructor" si called with 2 arguments as defined and some
||  other time,
||  it's called with 3 arguments.
||
||  Is it a bug or am I missing something?
-- 
WCC - Smart Search & Match
NL  +31 30 7503222
vzwe...@wcc-group.com
www.wcc-group.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lemon parser : compile error when using "%token_destructor" directive

[Wilson, Ron P]

It has been a while since I used lemon (big fan though).  Did you resolve this 
issue or do you still need help?

RW

Ron Wilson, Engineering Project Lead, 434.455.6453

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of ferrety ferrety
Sent: Monday, June 08, 2009 7:15 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Lemon parser : compile error when using "%token_destructor" 
directive

Hi List,
This is the only place I found to ask for "lemon parser" error.

When trying to use the "%token_destructor" directive:

%include {
#include 
void token_dtor (struct Token * t)
  {
fprintf(stderr, "In token_destructor: t -> value=%s\n", t -> value);
  }
}
%token_destructor { token_dtor($$); }


I got the following error:
error: too many arguments to function 'yy_destructor'

I tried to understand how "lemon" generated the calls for that function and
found that:

1) The definition of "yy_destructor" is with only 2 arguments:
static void yy_destructor(YYCODETYPE yymajor, YYMINORTYPE *yypminor){
  switch( yymajor ){
/* Here is inserted the actions which take place when a
...

2) "Lemon" generates different calls to "yy_destructor", sometimes with 2 or
3 parameters :
yy_destructor( yymajor, >minor); <- HERE with 2 arguments,
which is fine
yy_destructor(yypParser,4,[0].minor); <- HERE with 3 arguments, which
is an error
yy_destructor(yypParser,5,[-2].minor); <- same
yy_destructor(yypParser,6,[0].minor);  <- same
...

Sometime, the "yy_destructor" si called with 2 arguments as defined and some
other time,
it's called with 3 arguments.

Is it a bug or am I missing something?

Thanks in advance
Frederic
___
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] sqlite database to xml converter??

[Wilson, Ron P]

Cruel.  Just cruel.

RW

Ron Wilson, Engineering Project Lead, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Vinnie
Sent: Friday, April 17, 2009 8:49 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite database to xml converter??


> From: candd 
> 
> Dear All!
> 
> I am new user of sqlite3
> I want to have a sample C source for a program that convert
> an sqlite 
> data base file to xml file.
> could you help me please!

Hi and welcome to the group. Your problem is very easy to solve, just rename 
your database file to have the extension ".xml" and you should be good to go.

___
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] Newbie: Further reading / books recommendations

[Wilson, Ron P]

OT, but related (AGAIN)

I was adding these books to my amazon wishlist (aka Ron's list of books that he 
would forget about if he didn't put them on the list) and noticed that these 
titles are all available on the amazon kindle, which is delivered wirelessly 
via 3G network.  I don't have a kindle, but it is interesting to see such 
technical books available.  How many times have you traveled to a customer's 
site and thought, "Now if I only had my ABC reference?"  I wonder if I could 
get my boss to buy me a kindle.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Wednesday, March 18, 2009 2:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Newbie: Further reading / books recommendations

On Wed, 18 Mar 2009, Dermot wrote:

> I need get up to speed on a number of aspect of databases. I do need to
> grasp SQL syntax but also I also could use a more ideas on schema design
> and the process of modelling.

   For a well-written, in-depth book on SQL I highly recommend Rick van der
Lans' "Introduction to SQL, 4th Ed." Very comprehensive. For schema and
normalization insight, as well as structuring your code to prevent future
problems, read Joe Celko's "SQL Programming Style." He has other books, such
as "SQL for Smarties, 3rd Ed.", but that may be more advanced than you want
right now. Check out his other books and you're sure to find what you need.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

[Wilson, Ron P]

Ah yesss.  My apologies.  Please don't bill me. 

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of D. Richard Hipp
Sent: Wednesday, March 18, 2009 1:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz


On Mar 18, 2009, at 1:22 PM, Wilson, Ron P wrote:

> OT, but tangentially related...
>
> Is there any reason why all the dates are a month off in cvstrac  
> remarks?  See for example, the link below in dr.h's reply.  That is,  
> I'm assuming that the anonymous remark on february 18 and dr.h's  
> followup remark 2 hours later were really posted today, i.e. march 18.

The dates are correct. That ticket (#3583) was closed a month ago  
today.  Look at a recent ticket (ex: 
http://www.sqlite.org/cvstrac/tktview?tn=3733) 
  to see that dates are reported on comments correctly.

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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

[Wilson, Ron P]

OT, but tangentially related...

Is there any reason why all the dates are a month off in cvstrac remarks?  See 
for example, the link below in dr.h's reply.  That is, I'm assuming that the 
anonymous remark on february 18 and dr.h's followup remark 2 hours later were 
really posted today, i.e. march 18. 

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of D. Richard Hipp
Sent: Wednesday, March 18, 2009 11:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz


On Mar 18, 2009, at 11:31 AM, Gregg Reynolds wrote:

> FYI, a minor bug in sqlite-amalgamation-3.6.11.tar.gz in file  
> sqlite3.pc.in:
>
> Version: @RELEASE@  =>  Version: @VERSION@
>
> Without this change it won't link with the Redland RDF library.
>

http://www.sqlite.org/cvstrac/tktview?tn=3583

I still have no idea what a ".pc" file is or what it is used for.  But  
apparently it is very important to some compilers.


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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE clause doesn't seem to work right

[Wilson, Ron P]

See section 3.0 http://www.sqlite.org/datatypes.html

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Fitter Man
Sent: Friday, March 13, 2009 11:10 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] WHERE clause doesn't seem to work right


@John: Is that documented anywhere? I ask because there are some cases I'd
like to understand better without going through a lot of testing to
determine how it works.  I'm inferring from your example the rule is all
numerics come first, with integer and floats interleaved according to their
numeric sequence, and the remaining values would be treated as strings and
sorted accordingly. (Null values get stuck somewhere: I think MySQL puts
them at the beginning, but again I'll have to fiddle with this to figure it
out, hence my request for a document reference.) Thanks for the prompt
reply.
-- 
View this message in context: 
http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22498479.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] drop table question ?

[Wilson, Ron P]

Sorry for the redundant reply.  It looks like it was already answered.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wilson, Ron P
Sent: Tuesday, February 03, 2009 9:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] drop table question ?

You will have to do this with code.

select name from sqlite_master where name like 'x%';

that will give you a list of all tables that start with x;  you can then
delete all tables with names in the result set.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of baxy77bax
Sent: Tuesday, February 03, 2009 4:08 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] drop table question ?


hi 

simple question : How to drop all tables in my database that start , for
example, with X?

table 1 is X1998
table 2 is X8676
table 3 is X2912
...

thanx
-- 
View this message in context:
http://www.nabble.com/drop-table-question---tp21806118p21806118.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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] drop table question ?

[Wilson, Ron P]

You will have to do this with code.

select name from sqlite_master where name like 'x%';

that will give you a list of all tables that start with x;  you can then
delete all tables with names in the result set.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of baxy77bax
Sent: Tuesday, February 03, 2009 4:08 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] drop table question ?


hi 

simple question : How to drop all tables in my database that start , for
example, with X?

table 1 is X1998
table 2 is X8676
table 3 is X2912
...

thanx
-- 
View this message in context:
http://www.nabble.com/drop-table-question---tp21806118p21806118.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] Index keywords

[Wilson, Ron P]

If you click the link for 'create table' on the page below, you get
here:

http://www.sqlite.org/lang_createtable.html

Most of the constraint keywords are explained briefly there.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Daniel Watrous
Sent: Thursday, January 29, 2009 1:10 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Index keywords

oops, sorry, here's the link: http://www.sqlite.org/lang.html

On Thu, Jan 29, 2009 at 11:09 AM, Daniel Watrous 
wrote:
> Could this be what you're looking for?
>
> On Thu, Jan 29, 2009 at 11:04 AM, Vance E. Neff 
wrote:
>> I have seen that list of keywords, but I'm looking for a description
of
>> what they mean.  In particular those associated with index
definitions.
>>
>> Vance
>>
>> D. Richard Hipp wrote:
>>
>>>On Jan 29, 2009, at 9:38 AM, Vance E. Neff wrote:
>>>
>>>
>>>
Hi,

I've searched and can not find where the keywords like PRIMARY,
UNIQUE,
etc. in the Documentation.  Please point me to the correct section.
Also, if I use autoincrement, how do I retrieve the new index value
for
use in other tables?  Again a doc. pointer would be good.


>>>
>>>http://www.sqlite.org/lang_keywords.html
>>>
>>>
>>>
Thanks!
Vance

___
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-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] howto setup SQLite with Powershell ?

[Wilson, Ron P]

I think it would be cool to use System.Data.SQLite with powershell; I'm waiting 
until you figure it out before I try it.  I don't have a compelling reason to 
try it myself yet, but the neato factor has me paying attention.  Some of the 
readers seem to think you should ask these questions elsewhere because this is 
neither the powershell list nor the System.Data.SQLite list, but please stick 
around if only to give the answer once you figure it out.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Thursday, October 09, 2008 3:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] howto setup SQLite with Powershell ?

> access ... dll from PowerShell ... PowerShell docs 

the procedure given there is to register the dll with installutil.
When I first ran it, it looks ok.
As I rerun it during my efforts I got an error:
No public installers with the RunInstallerAttribute.Yes attribute could be 
found in the ...\System.Data.SQLite.dll assembly.


What I'm wondering is, am I the only one who wants to use SQLite together with 
Powershell?



 Original-Nachricht 
> Datum: Thu, 9 Oct 2008 15:26:04 -0400
> Von: "Brad Stiles" <[EMAIL PROTECTED]>
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] howto setup SQLite with Powershell ?

> > The original problem is given in the topic.
> > The System.Data.SQLite provider (which I currently can't get running) is
> hopefully only one solution.
> 
> You mentioned the System.Data.SQLite provider in every message you
> posted, and presented it in such a way that I interpreted you to have
> asked "howto setup SQLite with Powershell using System.Data.SQLite".
> That's what I was responding to.
> 
> > SQLite provides sqlite3.exe and sqlite3.dll.
> 
> However you'd access any other non-.NET, non-COM, dll from PowerShell
> is probably going to be the way you access this one.  Are the
> PowerShell docs not any help with this?
> 
> > How can I use that with powershell?
> 
> Not a clue, other than to execute the sqlite3.exe program with the
> appropriate command line parameters to accomplish what I want.
> 
> Brad
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite syntax diagrams

[Wilson, Ron P]

I love the diagrams.  They are much easier to read (for me) than plain
text.  To make them searchable, perhaps alt-text would suffice?

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Klemens Friedl
Sent: Monday, October 06, 2008 1:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite syntax diagrams

2008/10/3 D. Richard Hipp <[EMAIL PROTECTED]>:
> http://www.sqlite.org/draft/syntaxdiagrams.html
> http://www.sqlite.org/draft/lang.html

The diagrams are nice and for some people probably easier to read than
plain text BNF* syntax.
Although, images have two disadvantages:
1) web search engines cannot extract and therefor not index its content
2) the neither the browser's in-page search function nor a desktop
search engine or a grep like tool work either.

For example a search for "sqlite SELECT NATURAL JOIN" on your favorite
web search engine will list you
"http://www.sqlite.org/lang_select.html; hopefully in one of the first
search results.
As e.g. "natural" is not in the text content of the new draft
(http://www.sqlite.org/draft/lang_select.html) it won't be indexed if
this draft become the reality.

I suggest to offer both, the images and the BNF syntax.


Best regards, Klemens


* Backus-Naur Form
___
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] Reducing SQLite Memory footprint(!)

[Wilson, Ron P]

You can try using the command line tool to import csv data:

sqlite> .mode csv
sqlite> .import yourdata.csv yourtablename

however, if you have quoted strings with commas embedded it won't work.
You can try using any delimiter with .separator command.

RW

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel
Sent: Friday, August 22, 2008 1:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)

I just ran Dennis's test databases through the test application and
we're getting similar results:
1k Pages (17.4 MB) used 18102 KB High 20416 KB
4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why
the high is higher?)
My test database however with the same test application produces the
following:
1k Pages (7.46 MB) used 22735 KB, High 25138 KB.

So it looks my issue could be data related if my test database going
through the same app is coming out so large, Dennis's database is
expanding to about 101.6% of its original size but mine is expanding to
297.6% of its original size.  This begs the question is the 3rd party
tool (SQLite Analyzer) I'm using to import from an excel file causing
this expansion with bad data type choices?  And is there any other way
to import table structure and contents from xls (or csv) to sqlite?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Friday, August 22, 2008 7:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)

Brown, Daniel wrote:
> 2. And the other thing to try would be if anyone has a fairly meaty
test
> database they don't mind sharing that I could fling at my test
> application to try and rule out the data?
> 

Daniel,

I can send you copies of the databases I am using for my testing, both 
the version with the 1K page size (17.4 MB) and the one with the 4K page

size (12.2 MB).

Where would you like me to send them? The zipped versions are each about

1.3 MB in size.

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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import with .separator and quoted strings

[Wilson, Ron P]

Hmm.  Ok I'll think about munging the data.  If I find some time perhaps
I'll submit some code to support quoted values in the sqlite3 command
line tool.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald
Sent: Thursday, August 21, 2008 5:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] .import with .separator and quoted strings

Hi Ron,

I've encountered that as well.

You're using the sqlite3 commandline interface program, I'm sure.  
I think it was intended as a test and demo utility, but it's found its
way into a number of released products.

I'm not aware of a way to make this work with the current utility.
Since the source is available, you may want to modify it as needed.  I
know you prefer not to pre-process your input file, so enhancing the
source may be your best option.

In my case, *all* the fields were quoted in the input file, and so I
replaced occurances of:
","(quote comma quote)
with a vertical bar  |
and trimmed the quotes from the beginning and end of each line.



You can even perform this using an sqlite3 script itself if you don't
mind a bit of madness.
  -Set the separator to something very odd such as '@$%'
  -Import the original text to a temporary table with a single field to
contain the entire row.
  -UPDATE each row, using REPLACE() to change  ","  to  |   (perhaps
after first checking for any actual virgules in the original data).
  -Use SUBSTR() to remove the two remaining quotes at each end of the
line.
  -Set the separator to | and export to a temp file. Delete the
temporary table and .import the data into your real table.



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Thursday, August 21, 2008 4:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] .import with .separator and quoted strings

Here is an easy way to reproduce the symptom.  Given the following file
as input for the .import command:

---csvtest.csv---
"1","wilson, ron"
"2","momma, your"
-

Here is the sqlite output:

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table names (id integer, name); .mode csv .import 
sqlite> csvtest.csv names
csvtest.csv line 1: expected 2 columns of data but found 3
sqlite> .quit

Clearly it is parsing the comma in the name column as a record
delimiter.  Is there a mode that causes the .import command to honor
quoted entries?

RW

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Tuesday, August 19, 2008 4:49 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] .import with .separator and quoted strings

I'm trying to import a table using the command line tool.

 

sqlite> .separator ,

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

sqlite> .mode csv

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

All entries are quoted strings, but some of them have commas within the
strings.  It appears that SQLite is ignoring the string quoting and
taking all commas literally.  Is this intended?  The same import works
fine in Excel with 53 columns resulting.  I have also tried tab
delimited and apparently some of the strings in this dataset also
contain tabs.

 

sqlite> .mode tabs

sqlite> .import export.txt library

export.txt line 162: expected 53 columns of data but found 55

 

I don't control the data source, and I would really like to avoid
pre-munging the data.

 

RW

 

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

 

___
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


This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are
virus free, however the recipient must take full responsibility for
virus checking. 
This email message is intended for the named recipient only. It may be
privileged and/or confidential. If you are not the named recipient of
this email please notify us immediately and do not copy it or use it for
any purpose, nor disclose its contents to any other person.
___
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] .import with .separator and quoted strings

[Wilson, Ron P]

Here is an easy way to reproduce the symptom.  Given the following file
as input for the .import command:

---csvtest.csv---
"1","wilson, ron"
"2","momma, your"
-

Here is the sqlite output:

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table names (id integer, name);
sqlite> .mode csv
sqlite> .import csvtest.csv names
csvtest.csv line 1: expected 2 columns of data but found 3
sqlite> .quit

Clearly it is parsing the comma in the name column as a record
delimiter.  Is there a mode that causes the .import command to honor
quoted entries?

RW

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

-Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Tuesday, August 19, 2008 4:49 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] .import with .separator and quoted strings

I'm trying to import a table using the command line tool.

 

sqlite> .separator ,

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

sqlite> .mode csv

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

All entries are quoted strings, but some of them have commas within the
strings.  It appears that SQLite is ignoring the string quoting and
taking all commas literally.  Is this intended?  The same import works
fine in Excel with 53 columns resulting.  I have also tried tab
delimited and apparently some of the strings in this dataset also
contain tabs.

 

sqlite> .mode tabs

sqlite> .import export.txt library

export.txt line 162: expected 53 columns of data but found 55

 

I don't control the data source, and I would really like to avoid
pre-munging the data.

 

RW

 

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

 

___
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] .import with .separator and quoted strings

[Wilson, Ron P]

I'm trying to import a table using the command line tool.

 

sqlite> .separator ,

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

sqlite> .mode csv

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

All entries are quoted strings, but some of them have commas within the
strings.  It appears that SQLite is ignoring the string quoting and
taking all commas literally.  Is this intended?  The same import works
fine in Excel with 53 columns resulting.  I have also tried tab
delimited and apparently some of the strings in this dataset also
contain tabs.

 

sqlite> .mode tabs

sqlite> .import export.txt library

export.txt line 162: expected 53 columns of data but found 55

 

I don't control the data source, and I would really like to avoid
pre-munging the data.

 

RW

 

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Wilson, Ron P]

+1 for removal of quote munging.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Thursday, August 07, 2008 1:26 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] bug with NULL in NOT IN

[Wilson, Ron P]

Ah.  Thanks for the clarification.  It seems to me that using NULL as
'anything' or 'unknown' - it becomes a wildcard of sorts and could
create a lot of confusion in queries.

note to self : use NULL with extreme caution.

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Wednesday, June 25, 2008 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug with NULL in NOT IN


On Jun 25, 2008, at 12:12 PM, Wilson, Ron P wrote:

> Why should the second query return zero rows?  Clearly ids 1 and 2  
> don't
> exist in b.

The meaning of "NULL" in SQL is overloaded.  In some contexts NULL  
means "anything" or "unknown".  In other contexts it means "nothing".   
If we assume NULL means "nothing" then your statement above is  
correct.  But if we assume NULL means "anything" or "unknown" then we  
don't know if the right-hand side (RHS) of the NOT IN contains a 1 or  
2 because it contains a NULL which is a placeholder for an unknown  
value which might be a 1 or a 2 - we just don't know.

If I understand Peter correctly, he is saying that NULL should mean  
"unknown" in the context of the RHS of a NOT IN operator.  SQLite does  
not currently operate this way.  SQLite currently interprets a NULL in  
the RHS of a NOT IN operator to mean "nothing".

D. Richard Hipp
[EMAIL PROTECTED]



___
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] bug with NULL in NOT IN

[Wilson, Ron P]

Why should the second query return zero rows?  Clearly ids 1 and 2 don't
exist in b.  I'm not defending sqlite per se, just asking, logically
speaking, why would those other databases return zero rows for that
query?

On a related note, what if NULL exists in both tables?  Sqlite doesn't
return that row for the first query:

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table a(id integer);
sqlite> insert into a values(1);
sqlite> insert into a values(2);
sqlite> insert into a values(3);
sqlite> insert into a values(NULL);
sqlite> create table b(id integer);
sqlite> insert into b values(NULL);
sqlite> insert into b values(3);
sqlite> insert into b values(4);
sqlite> insert into b values(5);
sqlite> select * from a where id in (select id from b);
3
sqlite>

Sqlite deliberately ignores all NULL values in a select.  This explains
the result, but logically doesn't follow because in fact NULL does exist
in both tables.  Interesting.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Wednesday, June 25, 2008 11:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug with NULL in NOT IN


On Jun 25, 2008, at 11:33 AM, [EMAIL PROTECTED] wrote:

> Hello,
>
> with the following schema and contents:
>
> BEGIN TRANSACTION;
> CREATE TABLE a(id INTEGER);
> INSERT INTO a VALUES(1);
> INSERT INTO a VALUES(2);
> INSERT INTO a VALUES(3);
> CREATE TABLE b(id INTEGER);
> INSERT INTO b VALUES(NULL);
> INSERT INTO b VALUES(3);
> INSERT INTO b VALUES(4);
> INSERT INTO b VALUES(5);
> COMMIT;
>
> mysql, postgres, sqlite and mssql agree on:
>
>  SELECT * FROM a WHERE id IN (SELECT id FROM b);
>
> yielding one row with id=3.
>
> However, on the query:
>
>  SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
>
> mysql, postgres and mssql correctly return zero rows. SQLite
> however returns two rows, for id=1 and id=2.
>
> http://www.sqlite.org/nulls.html doesn't list it, so perhaps
> this has never come up before.


No, this has never come up before.  The behavior of SQLite is as I  
intended it to be.  NULLs are deliberately and willfully filtered out  
of the subquery to the right of NOT IN.  Are you saying that this is  
incorrect?  Other than the fact that three other database engines do  
it differently, do you have any evidence that this really is incorrect?

NULL behavior in SQL is highly unintuitive.  In fact, as far as I can  
tell it is arbitrary.  Can you or anybody else point to text in any  
SQL spec that would suggest that SQLites behavior in this case is wrong?


D. Richard Hipp
[EMAIL PROTECTED]



___
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] SQlite and C works with "like" but not with "="

[Wilson, Ron P]

Also, don't forget trailing white space, e.g. "Hexion " != "Hexion".

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: Saturday, June 14, 2008 10:55 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQlite and C works with "like" but not with "="

"Daniel White" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
>> Which way is it stored in the database? Show the output of this
>> statement:
>>
>> SELECT SongTitle FROM songs WHERE SongTitle like 'hexion';
>
> There are 8 records of Hexion in the database, so after a printout
> to the console with a carriage return after each value, I basically
> get:
> Hexion
> Hexion
> Hexion
> Hexion
> Hexion
> Hexion
> Hexion
> Hexion

And you are saying the statement

SELECT SongTitle FROM songs WHERE SongTitle='Hexion';

doesn't return any rows? With all due respect, I find it difficult to 
believe. Would it be possible for you to email a copy of the database 
file to me at [EMAIL PROTECTED] ? Perhaps a stripped-down version, 
with just enough data to reproduce the problem.

Igor Tandetnik 



___
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] graph question

[Wilson, Ron P]

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Monday, June 09, 2008 1:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] graph question

Wicked.  Thanks David and Dennis!

And this works like a charm for all parents:

select f || ' ' || group_concat(t, ' ') from w group by f;

This pushes me up to a '2' on the SQL Guru Meter.

RW

SQLGuru-O-Meter |0-+--510|

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Monday, June 09, 2008 1:41 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] graph question

David Baird wrote:
> 
> Okay, just built SQLite 3.5.9 and group_concat does in fact work:
> 
> select group_concat(t, ' ') from w where f=1;

You forgot the parent value at the beginning. Also, the OP may want to 
do this for several parents which can be accomplished by grouping the 
results.

   select f || ' ' || group_concat(t, ' ')
   from w
   where f in (1, 2, 3);
   group by f;

HTH
Dennis
___
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] graph question

[Wilson, Ron P]

Wicked.  Thanks David and Dennis!

And this works like a charm for all parents:

select f || ' ' || group_concat(t, ' ') from w group by f;

This pushes me up to a '2' on the SQL Guru Meter.

RW

SQLGuru-O-Meter |0-+--510|

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Monday, June 09, 2008 1:41 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] graph question

David Baird wrote:
> 
> Okay, just built SQLite 3.5.9 and group_concat does in fact work:
> 
> select group_concat(t, ' ') from w where f=1;

You forgot the parent value at the beginning. Also, the OP may want to 
do this for several parents which can be accomplished by grouping the 
results.

   select f || ' ' || group_concat(t, ' ')
   from w
   where f in (1, 2, 3);
   group by f;

HTH
Dennis
___
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] graph question

[Wilson, Ron P]

Yes.  I have been lurking here long enough to hear that response 100x -
and it is a fair answer.  Thanks!

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: Monday, June 09, 2008 1:09 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] graph question

Wilson, Ron P
<[EMAIL PROTECTED]>
wrote:
> select t from w where f=1;
> 2
> 3
> 4
> 5
> 6
> 7
>
> I would like the output to look like this:
>
> 1 2 3 4 5 6 7
>
> i.e. parent child1 child2 ... childN

SQL is not formatting or reporting library. It gives you raw data, and 
it's up to your application to build a presentation from it.

Igor Tandetnik



___
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] graph question

[Wilson, Ron P]

Given the following:

create table w (f, t);
begin;
insert into w (f, t) values (1, 2);
insert into w (f, t) values (1, 3);
insert into w (f, t) values (1, 4);
insert into w (f, t) values (1, 5);
insert into w (f, t) values (1, 6);
insert into w (f, t) values (1, 7);
...
commit;

select t from w where f=1;
2
3
4
5
6
7

I would like the output to look like this:

1 2 3 4 5 6 7

i.e. parent child1 child2 ... childN

is there any way to do that with sql or should I just do it in my
program?

RW

--Tyco Electronics--
Ron Wilson, S/W Systems Engineer III
434.455.6453 tycoelectronics.com


Important:

This electronic mail message and any attached files contain information
intended for the exclusive use of the individual or entity to which it
is addressed and may contain information that is proprietary,
privileged, confidential and/or exempt from disclosure under applicable
law.  If you are not the intended recipient, you are hereby notified
that any viewing, copying, disclosure or distribution of this
information may be subject to legal restriction or sanction. Please
notify the sender, by electronic mail or telephone, of any unintended
recipients and delete the original message without making any copies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Wilson, Ron P]

PS. Also, I am not sure about the BETWEEN command - does it use indices?
If not you could write the query without BETWEEN.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to speed up my queries?

Wilson, Ron P schrieb:
> Hi Christophe,
>
> 1. Please give us an example query.  SELECT * FROM Cities where
> LONGITUDE_DDS=? AND LATITUDE_DDS=?
> 2. Paste in the EXPLAIN results from the command line tool.
> 3. Is the database file local or are you accessing it over a network?
>   
Hi,

the database file is local, right next to the app using it. I am using 
the sqlite3.exe command line tool for the queries, but would eventually 
like to ditch it for the native support.
However, since the app I am using is a single threaded application 
(Adobe Director), eventual queries that take too long to complete do 
completely block the app which is why i have threaded the queries using 
a multi-threaded shell extension which does the queries, then reports 
back the results.

A typical query that causes problems would be:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

Am i right that no matter what limit is given to the SQL statement, the 
complete query is executed first, AND THEN filtered according to the 
limit? This is what i think i a seeing here...

I am therefore also after something that cuts off the query after a 
certain amount of results have been found.

The explain results from the command line tool:

sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and 
(longitude_DDS BETWE
EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORD
ER BY class_dds ASC Limit 20
   ...> ;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT * FROM Cities 
WHERE class_
dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND 
(latitude_DDS BETWE
EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20
;  00
1 Noop   0 0 000
2 Integer201 000
3 MustBeInt  1 0 000
4 IfZero 1 42000
5 Integer112 000
6 Real   0 3 0 6.765103   00
7 Real   0 4 0 7.089129   00
8 Real   0 5 0 44.2617710001  00
9 Real   0 6 0 44.424779  00
10Goto   0 43000
11SetNumColumns  0 6 000
12OpenRead   0 3 000
13SetNumColumns  0 2 000
14OpenRead   2 6 0 keyinfo(1,BINARY)  00
15Rewind 2 408 0  00
16SCopy  2 8 000
17IsNull 8 40000
18Affinity   8 1 0 cb 00
19IdxGE  2 408 1  00
20Column 2 0 11   00
21IsNull 1139000
22IdxRowid   2 11000
23MoveGe 0 0 11   00
24Column 0 3 12   00
25Lt 3 3912collseq(BINARY)  6b
26Gt 4 3912collseq(BINARY)  6b
27Column 0 4 17   00
28Lt 5 3917collseq(BINARY)  6b
29Gt 6 3917collseq(BINARY)  6b
30Column 0 0 22   00
31Column 2 0 23   00
32Column 0 2 24   00
33Column 0 3 25   00
34Column 0 4 26   00
35Column 0 5 27   00
36ResultRow  226 000
37AddImm 1 -1000
38IfZero 1 40000
39Next   2 19000
40Close  0 0 000
41Close  2 0 000
42Halt   0 0 000
43Transaction0 0 000
44VerifyCookie   0 202   000
45TableLock  0 3 0 Cities 00
46Goto  

Re: [sqlite] How to speed up my queries?

[Wilson, Ron P]

I'm not a guru yet, but I think you are not using the latlon index in
your query.  Perhaps if you index on lat and lon separately your query
will use those indices.  I think the lines below indicate using the
indices on class_dds and rowid.

19IdxGE  2 408 1  00
22IdxRowid   2 11000

LIMIT 20 should also limit the query to the first 20 matches; i.e. I
don't think it is actually finding N results and filtering down to the
first 20.  At least I think that's what this means:

37AddImm 1 -1000
38IfZero 1 40000

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to speed up my queries?

Wilson, Ron P schrieb:
> Hi Christophe,
>
> 1. Please give us an example query.  SELECT * FROM Cities where
> LONGITUDE_DDS=? AND LATITUDE_DDS=?
> 2. Paste in the EXPLAIN results from the command line tool.
> 3. Is the database file local or are you accessing it over a network?
>   
Hi,

the database file is local, right next to the app using it. I am using 
the sqlite3.exe command line tool for the queries, but would eventually 
like to ditch it for the native support.
However, since the app I am using is a single threaded application 
(Adobe Director), eventual queries that take too long to complete do 
completely block the app which is why i have threaded the queries using 
a multi-threaded shell extension which does the queries, then reports 
back the results.

A typical query that causes problems would be:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

Am i right that no matter what limit is given to the SQL statement, the 
complete query is executed first, AND THEN filtered according to the 
limit? This is what i think i a seeing here...

I am therefore also after something that cuts off the query after a 
certain amount of results have been found.

The explain results from the command line tool:

sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and 
(longitude_DDS BETWE
EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORD
ER BY class_dds ASC Limit 20
   ...> ;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT * FROM Cities 
WHERE class_
dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND 
(latitude_DDS BETWE
EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20
;  00
1 Noop   0 0 000
2 Integer201 000
3 MustBeInt  1 0 000
4 IfZero 1 42000
5 Integer112 000
6 Real   0 3 0 6.765103   00
7 Real   0 4 0 7.089129   00
8 Real   0 5 0 44.2617710001  00
9 Real   0 6 0 44.424779  00
10Goto   0 43000
11SetNumColumns  0 6 000
12OpenRead   0 3 000
13SetNumColumns  0 2 000
14OpenRead   2 6 0 keyinfo(1,BINARY)  00
15Rewind 2 408 0  00
16SCopy  2 8 000
17IsNull 8 40000
18Affinity   8 1 0 cb 00
19IdxGE  2 408 1  00
20Column 2 0 11   00
21IsNull 1139000
22IdxRowid   2 11000
23MoveGe 0 0 11   00
24Column 0 3 12   00
25Lt 3 3912collseq(BINARY)  6b
26Gt 4 3912collseq(BINARY)  6b
27Column 0 4 17   00
28Lt 5 3917collseq(BINARY)  6b
29Gt 6 3917collseq(BINARY)  6b
30Column 0 0 22   00
31Column 2 0 23   00
32Column 0 2 24   00
33Column 0 3 25   00
34Column 0 4 26   00
35Column 0 5 27   00
36ResultRow  226 0

Re: [sqlite] How to speed up my queries?

[Wilson, Ron P]

Hi Christophe,

1. Please give us an example query.  SELECT * FROM Cities where
LONGITUDE_DDS=? AND LATITUDE_DDS=?
2. Paste in the EXPLAIN results from the command line tool.
3. Is the database file local or are you accessing it over a network?

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:27 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to speed up my queries?

Hi,
 
i am a new member of this list and interested in speeding up my sqlite 
queries.

I am using SQlite in a 3d environment which is close to Google Earth or 
Nasa WorldWind.

We have a city database that is being queried regurlarly depending on 
the lat/long position of the viewport in order to show city names and 
labels.

Plus, there are additional databases for special features, like natural 
hazards and catastrophies.

The city database has around 840.000 records,  the following schema and 
weights currently short under 40Mb:

sqlite> .schema cities
CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS 
NUMERIC, LONGI
TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC);
CREATE INDEX class ON Cities(CLASS_DDS ASC);
CREATE INDEX latlon on Cities(latitude_dds,longitude_dds);

My questions are:

- how do I speed up the queries? For small lat/long windows, and high 
classes for the cities, i get long query times (e.g. about 600ms)
Is this reasonable to ask for, or IS that already a top speed for this 
kind of query?

- I have indexed latitude AND longitude,as you can see above. Is this
ok?

- I came across the EXLPAIN command, and have read an email by someone 
on this list on how to analyze my queries. I should probably do that, 
yet i am unfamiliar with reading the output of the Explain command.

Thanks for your time and eventual help,

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
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] transaction recovery question

[Wilson, Ron P]

Welcome to the list!

First - some sample code or queries would be helpful.
Second - start a new topic
(http://en.wikipedia.org/wiki/Thread_hijacking).

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 12:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] transaction recovery question

Hi,

i am new to this list, can anyone point me to a good FAQ document on how

to improve the speed of a SQLite database?

I got a city database (a geographical database) that I need to query for

lat/long values, and importance of the city (class value).

For my smallest query, i am waiting several hundred milliseconds in a 
database that is about 40Mb in size and that has indices on latitude and

longitude, as well as the class itself.

I have indexed the database, analyzed it (in order to get the stats 
table), and vacuumed it.

Any other hint on how one can speed up the queries? I ahve set PRAGME 
CACHE as well...

Thanks in advance,

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
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] a suggestion to write tutorial for sqlite

[Wilson, Ron P]

Along these lines, also note that the "quickstart" (url below) still
shows the callback method instead of the v2 methods.  The last time
another programmer asked me for help, I referred him there and I was
shocked later at the code he produced.  "Nice code, but you could have
done the same thing a lot easier with the v2 methods."  "The v2
methods?"  "Yeah."  "But this is SQlite3.  Why would I use v2 methods?"

http://www.sqlite.org/quickstart.html 

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use of two columns for a key and query on first clm.

[Wilson, Ron P]

order by id is not guaranteed.  use 'order by id' and it will.

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Neville Franks
Sent: Thursday, March 20, 2008 8:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Use of two columns for a key and query on first
clm.

Let me rephrase the question slightly. If I do

select * from table where clm1='def';

then step through the results will I see all rows that include 'def'.
The answer must be yes. The only issue is what order they will be in.

If I want them ordered by clm2 then yes I'd probably need use ORDER
BY. However in this specific example I would have thought the index
order would be used, which is clm1+clm2 which is the same as using
ORDER BY clm2. But I appreciate this isn't guaranteed.


Friday, March 21, 2008, 11:41:52 AM, you wrote:

JS> No, use ORDER BY

JS> Neville Franks wrote:
>> If I use two columns for a key (primary or separate index) and query
>> just on the first column component will I always get back the first
>> match in a set. For example.
>> 
>> -
>> create table mytable ( clm1 text collate nocase, clm2 text
>> collate nocase, constraint mycs1 primary key( clm1, clm2 ) );
>> 
>> insert following:
>> Clm1  Clm2
>> abc   123
>> abc   456
>> abc   789
>> def   123
>> def   456
>> def   789
>> 
>> select * from table where clm1='def';
>> -
>> 
>> Will the returned row always be def - 123. ie. the first row for def?
>> 
>> I've looked at the query plan for this select and it does use the
>> index if clm1 alone is in the query and it appears to match on the
>> first row.
>> 
>> Also my tests indicate I do get back the first matching row. But I'd
>> like confirmation if possible.
>> 




-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.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] SQL Blobs - what am i doing wrong here?(code inside)

[Wilson, Ron P]

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)

[Wilson, Ron P]

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

Re: [sqlite] SQL Blobs - what am i doing wrong here?(code inside)

[Wilson, Ron P]

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] Unrecognized "Z" UTC time zone signifier

[Wilson, Ron P]

Heh.  He implemented it with a goto statement!  While I personally have no 
problem with this, especially when it is the most efficient solution, we have a 
client who demands all source code comply with MISRA and I don't relish the day 
we have to defend SQlite and our parser (lemon generated).

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ralf Junker
Sent: Friday, February 22, 2008 9:09 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unrecognized "Z" UTC time zone signifier

Aristotle Pagaltzis wrote:

>* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2008-02-21 13:45]:
>> Ralf Junker <[EMAIL PROTECTED]> wrote:
>> > SQLite does not recognize "Z" as the zero offset time zone
>> > specifier. 
>> 
>> If we start accepting any symbolic timezone names, seems like
>> we would then need to start accepting them all.
>Not hardly. FWIW, the IETF recommendation for timestamps in
>any new internet standards is to use the format specified in
>RFCÂ 3339, which is based on codified experience. For time zones,
>it prescribes that they be given as either a numeric offset or
>`Z` a shortcut for `+00`; no provision is made for other symbolic
>names as those only cause trouble. So you should have no trouble
>refusing requests to support those.

Richard did it, and it works like a charm:

  http://www.sqlite.org/cvstrac/chngview?cn=4805

Many thanks!

Ralf  

___
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