[sqlite] Does the list in tokenize.c represent the entire set of keywords?

[mswarm]

Being a compulsive reinventor of wheels, I've made my SQL code editor
smart enough to highlight the offending line of bad SQL--not a major leap
in technology, but fun nonetheless. Now I'd like to color-code the SQL.

I found this list of keywords in tokenize.c. A little search-and-replace--
replacing TK_ with ', etc.--and it makes a nice set for validating keywords.


TK_ABORT,  TK_AFTER,  TK_ALL,TK_AND,TK_AS, 
TK_ASC,TK_ATTACH, TK_BEFORE, TK_BEGIN,  TK_BETWEEN,
TK_BY, TK_CASCADE,TK_CASE,   TK_CHECK,  TK_COLLATE,
TK_COMMIT, TK_CONFLICT,   TK_CONSTRAINT, TK_CREATE, TK_JOIN_KW,
TK_DATABASE,   TK_DEFAULT,TK_DEFERRABLE, TK_DEFERRED,   TK_DELETE, 
TK_DESC,   TK_DETACH, TK_DISTINCT,   TK_DROP,   TK_EACH,   
TK_ELSE,   TK_END,TK_EXCEPT, TK_EXCLUSIVE,  TK_EXPLAIN,
TK_FAIL,   TK_FOR,TK_FOREIGN,TK_FROM,   TK_JOIN_KW,
TK_GLOB,   TK_GROUP,  TK_HAVING, TK_IGNORE, TK_IMMEDIATE,  
TK_IN, TK_INDEX,  TK_INITIALLY,  TK_JOIN_KW,TK_INSERT, 
TK_INSTEAD,TK_INTERSECT,  TK_INTO,   TK_IS, TK_ISNULL, 
TK_JOIN,   TK_KEY,TK_JOIN_KW,TK_LIKE,   TK_LIMIT,  
TK_MATCH,  TK_JOIN_KW,TK_NOT,TK_NOTNULL,TK_NULL,   
TK_OF, TK_OFFSET, TK_ON, TK_OR, TK_ORDER,  
TK_JOIN_KW,TK_PRAGMA, TK_PRIMARY,TK_RAISE,  TK_REFERENCES, 
TK_REPLACE,TK_RESTRICT,   TK_JOIN_KW,TK_ROLLBACK,   TK_ROW,
TK_SELECT, TK_SET,TK_STATEMENT,  TK_TABLE,  TK_TEMP,   
TK_TEMP,   TK_THEN,   TK_TRANSACTION,TK_TRIGGER,TK_UNION,  
TK_UNIQUE, TK_UPDATE, TK_USING,  TK_VACUUM, TK_VALUES, 
TK_VIEW,   TK_WHEN,   TK_WHERE,  
 
Two questions: Am I safe in assuming this represents the entire set of keywords 
in SQLite?

And why does TK_JOIN_KW, appear in there so many times?

For my purposes, I may eventually run a bunch of SQL code through a word-
frequency counter, and arrange these in frequency-of-use order--or maybe 
put them in a sorted, searchable list--as I suspect that this would be the 
choke point for code editors on really slow machines. 

Are we having fun yet?

Nathan Hawking

Re: [sqlite] Speeding up your SQLite DB (Windows mostly)

[mswarm]

>>From what I experienced, the exported functions from the DLL were 
>simple to use and did everything you wanted, though perhaps slightly 
>differently from the Delphi DBE way some people may be used to

I wouldn't sell the BDE-style components short, as they may be useful
for some, if well-written and well-documented, but I like simple. 


>I found a simple wrapper class worked better than trying to use a 
>bunch of componants pretending to be the BDE, but of course, a lot 
>depends on what you want to do and how confident you are using 
>exported DLL functions

I'm a big fan of small and lightweight, and have chosen that route
myself. If you're talking to a bare-bones DLL interface, be careful
of memory leaks. Much of the "Delphi wrapper plus" code I've tried
has problems I've caught even as a rank beginner. I think some mid-
level code is a good idea, though, even if only to get it right one 
time and insulate programmers from pointers, leaks and details. 



>2) Indexes and DB structure are important. Fairly generic DB rule one 
>I suppose, but this is an SQL DB, its VITAL you add the indexs you 
>need, and even MORE important you *DON'T* add the indexs you don't 
>need.
>Plan your DB before hand with n eye that everything has a function, 
>if you don't use it, don't have it. There is more about this on the 
>SQLite docs
>
>3) The default cluster size for a Windows NTFS system seems to be 
>4096 bytes. Setting the SQLite database page size to the same size 
>will speed up your database on systems where the cluster size is the 
>same (Note, Linux cluster I believe to 1024 which is the default for 
>new SQLite databases)
>Easiest way to tell your cluster size is to defragment your drive and 
>analyze. It tells you in there
>
>To set the SQLite page size, create a new *EMPTY* database and do a
>PRAGMA page_size=4096;
>Now create your tables immedeatly (if you close down the SQLite 
>commandline program and reopen the DB, the page size is reset to 
>1024). The page size must be set before the first table is created. 
>Once that tables made, you can't change the size
>
>Typing
>PRAGMA page_size;
>will tell you what it is currently set at
>
>4) SQLite doesn't support clustered indexes (simply, indexes that 
>force the data in the database to be physically layed down in the 
>SAME order as the index needs it to be in.) 
>This means that if your index is sequential INTEGER, the records are 
>physically layed out in the database in that INTEGERs order, 1 then 2 
>then 3.
>
>You can't make a Clustered index, but you CAN sort your data in order 
>so that any historical data is ordered nicely. Of course, as the 
>database matures, you lose that, but it helps
>
>Someone else posted this, and it is a nice example to use, so I will. 
>If you have a table WIBBLE whose field KEY you want to access a lot, 
>it would be nice if everything was in order. Using the command line 
>tool, you can create a fake cluster by doing the following:
>
>   create table wibble2 as select * from wibble;  
>   delete from wibble;
>   insert into wibble select * from wibble2 order by key;
>   drop table wibble2;
>
>5) Ok, as a reward for reading this far, here is the dumb thing.
>
>Be *VERY, VERY* careful what you name your database, especially the 
>extension
>
>For example, if you give all your databases the extension .sdb 
>(SQLite Database, nice name hey?) you discover that the SDB extension 
>is already associated with APPFIX PACKAGES.
>
>Now, here is the cute part, APPFIX is an executable/package that 
>Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE 
>TO THE SYSTEM RESTORE FUNCTIONALITY*
>
>This means, stay with me here, every time you write ANYTHING to the 
>database, the Windows XP system thinks a bloody executable has 
>changed and copies your ENTIRE 800 meg database to the system restore 
>directory
>
>I recommend something like DB or DAT. 

Thanks for mentioning the bits and pieces. I'll keep them handy.

Nathan

Re: [sqlite] What forms of comments does SQLite honor?

[mswarm]

>>Not in C. The closing */ is necessary.
>
>That's the case in  Delphi also, practically speaking. Code 
>ends with a end. and any (* before that would hide it from the 
>compiler. 
>
>But I just tested the /* in SQLite, and apparently it comments 
>eveything to the end of the text block without causing an error. 
>
>So the valid comment forms appear to be: 
>
>-- to end of line
>/* to the next */
>/* to the end of the text block 
>
>-- causes a /* before EOL to be ignored 
>
>In looking up SQL comments in general on SQL newsgroups, 
>I found at least one claim that SQL has no comment mechanism.
>I wonder if that's true for the standard. 
>
>In any event, I'm glad to see SQLite has both line and block
>comments. 
>
>Nathan 

Not suggesting the DLL ought to have a requirement to terminate 
a /* comment, but I can see the wisdom of having my editor catch 
that. I spent some time looking for the problem with code I had 
block-commented with /* words /* instead of /* words */.  

Maybe longtime C users would spot that right away, but my eyes 
gloss right over it. 

NH

Re: [sqlite] What forms of comments does SQLite honor?

[mswarm]

>>  see whether
>> a /* comments to the end of text without a */ being syntactically
>> necessary.
>
>Not in C. The closing */ is necessary.

That's the case in  Delphi also, practically speaking. Code 
ends with a end. and any (* before that would hide it from the 
compiler. 

But I just tested the /* in SQLite, and apparently it comments 
eveything to the end of the text block without causing an error. 

So the valid comment forms appear to be: 

-- to end of line
/* to the next */
/* to the end of the text block 

-- causes a /* before EOL to be ignored 

In looking up SQL comments in general on SQL newsgroups, 
I found at least one claim that SQL has no comment mechanism.
I wonder if that's true for the standard. 

In any event, I'm glad to see SQLite has both line and block
comments. 

Nathan 

Re: [sqlite] What forms of comments does SQLite honor?

[mswarm]

>The source code in tokenize.c mentions: 
>
>/* SQL-style comments from "--" to end of line */
>
>and 
>
>/* C-style comments */
>

I got my code reconnected and managed to verify that these 
two are apparently valid comment forms. /* */ appears to span 
lines. 

Now to confirm that a /* */ spans -- comments, and see whether 
a /* comments to the end of text without a */ being syntactically
necessary.

NH

RE: [sqlite] Do _ErrMsg strings have to be freed?

[mswarm]

>
>Subject: RE: [sqlite] Do _ErrMsg strings have to be freed?
>   From: "steve" <[EMAIL PROTECTED]>
>   Date: Mon, 7 Feb 2005 19:31:23 -0800
> To: 
>
>I also read the documentation that you quoted below.
>If one must always free the char * using sqlite3_free, then doesn't that
>mean that in the example provided at http://www.sqlite.org/quickstart.html
>'zErrMsg' is actually a memory leak waiting to happen?

>And why isn't sqlite3_exec listed under sqlite3_free as one of the routines
>to use it to free the memory from? 

The possible need for amending the documentation crossed my mind as 
well. 

As I mentioned, I've been studying a number of Delphi implementations
of SQLite, and the more I learn the leakier some of them are looking. 

I know little about C, which is why I will doubtless ask questions
to which the answers seem obvious for some. Nevertheless, if you mean
the code at


  rc = sqlite3_open(argv[1], );
  if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
  }
  rc = sqlite3_exec(db, argv[2], callback, 0, );
  if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
  }
  sqlite3_close(db);
  return 0;
}

it looks like it has the "_Close even when a _Open is not SQLITE_OK"
part right, per the docs, but I'd have to ask about the zErrMsg part 
not being freed as well. 

Nathan

Re: [sqlite] Do _ErrMsg strings have to be freed?

[mswarm]

>You'd have to read the docs of your Delphi wrapper, but if this were the 
>plain C interface, then, yes, you would need to free the memory.

I've tested a dozen or so, and I'm hacking my own as well. The quality 
of the coding varies, naturally, from the ridiculous to the sublime.

In some places, the pChar call is treated to _Free, but not at other times.

Often as not, the coders fail to call _Close even if an _Open fails, even
though the SQLite docs are pretty specific about that. So I really don't
trust the wrapper/unit code as exemplary, and they are rarely documented 
with anything beyond pasdoc, if that. 



>"If an error occurs while parsing or evaluating the SQL (but not while 
>executing the callback) then an appropriate error message is written 
>into memory obtained from malloc() and *errmsg is made to point to that 
>message. The calling function is responsible for freeing the memory that 
>holds the error message. Use sqlite3_free 
>() for this. If 
>errmsg==NULL, then no error message is ever written."

I read that. Just wanted to see if I was interpreting it correctly. There
appears to be some ambivalence in the _prepare method.

Specifically, I'm concluding the "if it's not nil, free it" is OK.


>And now a meta-comment about asking questions in a technical forum.  It 
>is fine to ask questions, and newbies are certainly welcome, but...
>
>Your best bet is to check the www.sqlite.org site first, then check your 
>Delphi wrapper documentation, then if that doesn't anwer your questions, 
>you're more than welcome to ask questions here. 

While "Delphi wrapper documentation" is an existential possibility, 
in practice it's as rare as a poker-playing unicorn.  


>One of the first rules of asking questions in a technical forum is to 
>try to answer the question yourself from the docs before asking the 
>question.  It may not save you some time, but if a forum has many 
>readers, the time saved collectively if you find the answer yourself is 
>perhaps greater than the time it takes you to find the answer yourself.  
>That's what documentation is for, after all.  Plus you'll likely learn 
>something in the process which WILL save you some time later down the road.
>
>I would encourage you to read through the SQLite3 C reference.  It 
>doesn't take that long, and will be a rewarding experience:
>
>http://www.sqlite.org/capi3ref.html

Thanks, I'm doing that. 

Nathan

[sqlite] How to best achieve the SQL-processing behavior I'm after?

[mswarm]

I'm experimenting with SQLite in a Delphi 4 environment,  
simultaneously learning Delphi, database practices, SQL, etc., 
etc. I've tried out perhaps a dozen Delphi wrappers of the
SQLite DLL, and am hacking my own as well. Insane? You bet, 
but there it is. Getting my feet wet all the way to my neck.

What I want to achieve in application behavior resembles this:

I'm working on Delphi 4, in an "Integrated Development
Environment" which allows me to hack away at a little 
code, test the code with attempted compilation and running
the app, and I get immediate feedback on my coding, as 
errors show up a) as text describing the error, b) a pointer
to the line in code in which the error occurs, c) app behavior 
which is not what I wanted. 

Granting the differences between Delphi and SQL/SQLite,
/that/ behavior is what I want, more or less. Mostly more.

It seems easy enough to feed SQLite a line of SQL at a time
and check each time for an error, stopping the process and 
pointing to the error in SQL text. 

It's also instructive to open the database file as a hexdump,
to see what changes the SQL hath wrought, not to mention doing 
data retrieval with known-good SQL to examine changes in the 
database structure or data. 

But this raises a few questions. Given the SQL:

CREATE TABLE table01 (aVar, bVar);
CREATE TABxxLE table02 (cVar, dVar);

it seems that table01 has been created in the open database
before the error has been noticed in the second line.

Is there any way to test the syntax of /all/ the SQL before 
any of it is applied to the database? (With SQLite.)

I know that for 

BEGIN;
INSERT INTO table01 ('aaa', 'bbb'); 
uNSERT INTO table02 ('ccc', 'ddd'); 
COMMIT;

I could reverse the effects with a ROLLBACK. Without that 
BEGIN, though, that first line would be inserted, I believe.
Is there  any such provision for CREATE statements? (Just
downloaded Birchall's SQL book--thanks to the poster who
mentioned that!)  

I can think of a few ways to handle this: an SQL-aware
editor, auto-throwaway files for the tests. 

Any suggestions on how I might handle this to get something
resembling a "SQL Development" environment?

For starters, which would I be better off using, the sqlite3_prepare 
method, or the _exec? 

Nathan

Re: [sqlite] Do _ErrMsg strings have to be freed?

[mswarm]

>>Does this 
>>
>>var
>>  pMsg: PChar;
>>
>>..
>>pMsg := SQLite3_ErrMsg(aDB);
>>
>>necessitate this?
>>
>> if pMsg <> nil then SQLite3_Free(pMsg);
>
>
>It depends.  If you use sqlite3_exec, then yes, if you use the other
>method (sqlite_prepare) then I would say no, depending on your
>definition of the word "ephemeral"  ;-)

Not sure I like the sound of this. Heh.

I'd like to write a generalized message handler (I'm not calling it
an error message handler because "OK" is not an error... well, not 
usually.) 



>>From the docs:
>
>sqlite3_exec method:
> If an error occurs while parsing or evaluating the SQL (but not while
>executing the callback) then an appropriate error message is written
>into memory obtained from malloc() and *errmsg is made to point to
>that message. The calling function is responsible for freeing the
>memory that holds the error message. Use sqlite3_free() for this. If
>errmsg==NULL, then no error message is ever written.

So my 

  if pMsg <> nil then SQLite3_Free(pMsg);

would cover that, wouldn't it?



>sqlite3_prepare method
>The sqlite3_errcode() routine returns a result code for the most
>recent major API call. sqlite3_errmsg() returns an English-language
>text error message for the most recent error. The error message is
>represented in UTF-8 and will be ephemeral - it could disappear on the
>next call to any SQLite API function. sqlite3_errmsg16() works like
>sqlite3_errmsg() except that it returns the error message represented
>as UTF-16 in host native byte order.


My understanding of how the _prepare method works is limited--I'll
follow up on that in another post. But from this my interpretation
suggests that 

   if pMsg <> nil then SQLite3_Free(pMsg);

would still be a safe line to write, but the pMsg /content/ would 
not be a reliable indicator of the /present/ state of the SQL being
processed, only of the last error. 

This also seems to imply that the code can continue to run even after 
an error, which is not the behavior I'm looking for. More on that 
in my next post. 

Thanks

Nathan

Re: [sqlite] Fundamental database use questions

[mswarm]

>>1. Is there a way using SQL code alone to open databases, or will
>>that always be done by my native (Delphi) code calling the DLL? 
>>
>>I suppose another way to ask that question is: Would "scripts" 
>>which could manage ALL database activities require embedded 
>>SQL in another language, or could SQL do the job alone?
>>  
>>
>Opening a database is a function of the library, not SQL.  You need to 
>have the database open for there to be something for the SQL to act on.

Good to know. I'd have figured that out eventually, of course, but sooner
is better than later. 



>>2. If I wish to write and test SQL code "live" can/should I open
>>one or more databases (however) and manipulate them via SQL at 
>>leisure, or should each invocation of SQL code open, run, then 
>>close the files?
>>  
>>
>It's perfectly fine to execute multiple queries on a single connection. 

By "connection" I'm understanding you to mean that my code can call
SQLite3_Open and leave it open while I fiddle around with various 
bits and pieces of SQL, trying this and that INSERT or SELECT or 
whatever. IOW, I don't have to call _Open in a procedure, run the SQL
(however many statements) then call the _Close immediately in the same 
code block. 

Yesterday I began to experiment with sending multiple SQL lines to 
SQLite, testing for error after each line before proceeding. Presumably
I should also be testing for completion of the activity of each line 
before proceeding. 

Which API calls would be required for a complete assessment of readiness?
Would sqlite3_busy_handler be a candidate?  I presume callbacks would be 
involved as well.


>In fact I use the sqlite[3] client to test out my SQL while my program 
>is running.

Can you give a rough idea of the algorithm you use to present your SQL
to the DLL? So far I've simply given it one line after another, checking 
for error after each line. With INSERTs but no BEGIN, the data made its
way into the file up until the line with the error. With a BEGIN before
the SQL INSERTS, I could invoke a ROLLBACK from Delphi on error and 
nothing would end up in the file.

Interesting, but I'm not sure how to use this behavior, i.e., what should
be left to SQL and what should be done in Delphi. My goal is to leave as
much to SQL code as possible, to have an SQL-friendly environment which 
gives the user as much SQL syntax and error information as possible, 



>>3. If I want to exchange data between files, what's the general 
>>algorithm for opening, doing transactions, then closing? As an 
>>extension to this question, if one wished to backup data by doing
>>a record-for-record copy during application idle time, what would 
>>be a good strategy for doing this quickly yet safely (given the 
>>possibility of premature computer power-down)?
>>
>>  
>>
>The answer here depends on how you want your backups.  If you want to 
>make sure that nothing is committed until all of the data has been 
>copied, starting a transaction, copying all of the data, then commiting 
>the transaction would be a good bet.  It you're willing to risk 
>partially incomplete data, smaller transactions for single tables are 
>suitable and probably provide a better backup of your data. For that 
>matter, simply copying the file is often suitable. With a compression 
>utility you can even make a relatively compact backup of your data.

Thanks, Clay. I'll keep all this in mind.

Nathan

[sqlite] Fundamental database use questions

[mswarm]

I'm new to both SQL and SQLite, not to mention the various Delphi 
wrappers and database work in general. I have a good idea of 
what I want to achieve in my various projects, but need a feel 
for how some things are done. 

At this point, I can create database files, use basic SQL to 
create structure and add/access data. 

1. Is there a way using SQL code alone to open databases, or will
that always be done by my native (Delphi) code calling the DLL? 

I suppose another way to ask that question is: Would "scripts" 
which could manage ALL database activities require embedded 
SQL in another language, or could SQL do the job alone?

2. If I wish to write and test SQL code "live" can/should I open
one or more databases (however) and manipulate them via SQL at 
leisure, or should each invocation of SQL code open, run, then 
close the files?

3. If I want to exchange data between files, what's the general 
algorithm for opening, doing transactions, then closing? As an 
extension to this question, if one wished to backup data by doing
a record-for-record copy during application idle time, what would 
be a good strategy for doing this quickly yet safely (given the 
possibility of premature computer power-down)?

Thanks

Nathan

Re: [sqlite] [SQLite] Variations in DLLs

[mswarm]

>Hi,
>
>I'm sorry to hear that you find the components too complicated. They are based
>upon the standard Delphi controls and the functionality was keps as standard as
>possible. They are fatter than a simple wrapper where you have to do all by
>yourself, but that's a choice anyone has to make for themselves.

That's absolutely true, Albert. They give every appearance of being
very good controls, but I need something as light and simple as possible,
to a point. As you say, your goal was to mirror the standard functionality.


>About the dll, there was an early isue about the dll in the beta stage of 
>Sqlite
>where not all functions where exported. A forum user sent me a working dll and 
>I
>put this into the package you downloaded. New versions of the source however 
>are
>delivered as a single source download update. You need to install this source
>file and recompile the package. Functions like the version where implemented
>later. We always advise to use the latest dll to be downloaded from sqlite,
>info about this is published on our website.
>
>I sometimes read questions here about our (open-source) components. Just to let
>you know there is a forum on our website where you can put all kind of
>questions regarding our components. Lots of you actually do. It can be found on

Thanks.

[sqlite] [SQLite] Variations in DLLs

[mswarm]

My simple Delphi test of libSQL worked fine, creating a database 
file, executing some SQL, returning the DLL version.

Used the same code in my app framework, however, and everything kept 
coming up dead: no file created, no error reply, no version message.

The difference turned out to be the DLL. I had installed the Aducom
controls, which turned out to be fatter and more complicated than 
I wanted. I desinstalled the controls, but left the DLL in place.

The DLL which worked (dated 10/11/04) turned out to be 218K, while 
the Aducom DLL--which did not work with the libSQL calls--is 382K. 

Any idea what the extra code (debug?) is doing and why didn't it 
work? (I presume the calls have been changed.)

[sqlite]

[mswarm]

"Sqlite is finally a database product that values performance 
and minimal footprint (disk and memory) above a trashcan strategy 
that would add whatever feature to make so-called 'feature rich'
result a bloated piece of software."  

YES! This comment, from the wiki "sqlite - Unsupported Sql" 
page, slightly reworded for clarity, says EVERYTHING about
what I'm coming to like about the product. 

/Thank you/, DRH, for your work on this product, for generously
giving it to the rest of us. Not only have you produced what
seems to be a fine product (I'm only in the initial stages of 
exploring it), but your documentation is a model of complete-
ness and clarity. 

I use Delphi, not C, but I have to say that had I been exposed
to more examples of such well-structured and thoroughly commented
C code I may have been using C as well.

I've been unable to find a Delphi interface to SQLite which easily 
and simply does what I want without misbehaving, either in operation 
or upon attempted compilation. As is so often the case, under these
circumstances the Do It Yourself notion will arise. Usually one 
look at the product in question is enough to disabuse us, as the 
product is  so bloated and poorly documented that writing 
something from scratch in native Delphi would not be much harder.

SQLite is exceptional! The more one looks at the copious code 
comments and accompanying documentation, the simpler it looks.

There oughta be a rule: If your documentation is not longer
that your code, the project is underdocumented. 

This would ensure quantity even if not quality, but it would 
be a start. Fortunately, SQLite has both. Thanks again, DRH.

Re: [sqlite] How can SQLite return indication of where SQL failed?

[mswarm]

>
>Subject: Re: [sqlite] How can SQLite return indication of where SQL failed?
>   From: "Peter Bartholdsson" <[EMAIL PROTECTED]>
>   Date: Mon, 17 Jan 2005 03:21:25 +0100
> To: sqlite-users@sqlite.org
>
>On Sun, 16 Jan 2005 02:42:52 -0800, <[EMAIL PROTECTED]> wrote:
>
>> I'm using Delphi 4 and various versions of SQLite wrapper.
>> To begin, my only desire is to write SQL in a text box and
>> execute it.
>>
>> What I'd like if a pointer to the place in the SQL where
>> the syntax fails, and I'm presuming the in-DLL parser is
>> capable of telling me that.
>>
>> How can I get that information back?
>
>If you're using SQLite 3 use the sqlite3_errmsg function.
>Granted, that assumes the wrapper supports it.

Yes, one of the SQLite wrappers has the function 

function  SQLite3_ErrMsg  (db: TSQLiteDB): PChar; cdecl; 
   external 'sqlite3.dll' name 'sqlite3_errmsg';

If this is unique to 3+, that explains why some of my
wrapper/component tests would raise an exception with
a message something like "error near 'CREATD'" and 
others didn't. 

Since there is no numeric value returned, I assume I'll
have to do a search on the source text for the quoted 
string and highlight it. 

Thanks

[sqlite] How can SQLite return indication of where SQL failed?

[mswarm]

I'm using Delphi 4 and various versions of SQLite wrapper.
To begin, my only desire is to write SQL in a text box and 
execute it. 

What I'd like if a pointer to the place in the SQL where 
the syntax fails, and I'm presuming the in-DLL parser is
capable of telling me that. 

How can I get that information back?

Re: [sqlite] Suppressing column (field) headers?

[mswarm]

>
>Subject: Re: [sqlite] Suppressing column (field) headers?
>   From: [EMAIL PROTECTED]
>   Date: Mon, 22 Nov 2004 10:23:37 +1000
> To: [EMAIL PROTECTED]

>[EMAIL PROTECTED]
>21/11/2004 04:56 PM
>Please respond to sqlite-users
>
> 
>To: [EMAIL PROTECTED]
>cc: 
>Subject:[sqlite] Suppressing column (field) headers?
>
>
>
>> 1. How can I get the dll to return JUST the desired data? 
>>When I send the following SQL, for example,
>>   select colEntry from tblEntry where colNum = 3
>>   the textbox shows 
>>  colEntry
>>  "The text in the column."
>>  
>

>This is being done by your delphi wrapper. 

I beat around doing traces until I began to get that picture. The 
code is doing things with TStrings and TSringlists. 


>Please consult its documentation. 

I wish. Heh. 

>The "C" interface of sqlite (for both 2.8 series and 3.0 
>series) returns the column names and column values separately, and doesn't 
>return any extra "quote" characters.

Makes sense.


>Clay Dowling wrote:
>> You can get just the data that you want by using the prepared statements
>> feature of the 3.0.x dll.  It sounds like you're using the table output
>> method for getting your results.  This is fine, but it means that you 
>need
>> it ignore the first row of data, which in the table interface is the
>> column labels.
>
>This statement makes you sound like you're taking crazy pills, Clay 
>(prepared statements and non-prepared statements aren't functionally 
>different in sqlite)... but I'm interested to know if you have a grain of 
>truth hidden in there. Are you familiar with the delphi wrapper Mswarm is 
>using? Which delphi wrapper are you using, Mswarm? 

It says: "Simple class interface for SQLite. Hacked in by Ben Hochstrasser 
([EMAIL PROTECTED]) Thanks to Roger Reghin ([EMAIL PROTECTED]) for his 
idea to ValueList." 

>What does the code that issues your query look like?

Benjamin, so far as I can tell, the relevant code is as follows:


fTable: TStrings;
fLstName: TStringList;
fLstVal: TStringList;


SQLite_Exec: function(db: Pointer; SQLStatement: PChar; CallbackPtr: Pointer; 
Sender: TObject; var ErrMsg: PChar): integer; cdecl;



function ExecCallback(Sender: TObject; Columns: Integer; ColumnValues: Pointer; 
ColumnNames: Pointer): integer; cdecl;
var
  PVal, PName: ^PChar;
  n: integer;
  sVal, sName: String;
begin
  Result := 0;
  with Sender as TSQLite do
  begin
if (Assigned(fOnData) or Assigned(fTable)) then
begin
  fLstName.Clear;
  fLstVal.Clear;
  if Columns > 0 then
  begin
PName := ColumnNames;
PVal := ColumnValues;
for n := 0 to Columns - 1 do
begin
  fLstName.Append(PName^);
  fLstVal.Append(PVal^);
  inc(PName);
  inc(PVal);
end;
  end;
  sVal := fLstVal.CommaText;
  sName := fLstName.CommaText;
  if Assigned(fOnData) then
fOnData(Sender, Columns, sName, sVal);
  if Assigned(fTable) then
  begin
if fTable.Count = 0 then
  fTable.Append(sName);
fTable.Append(sVal);
  end;
end;
  end;
end;


function TSQLite.Query(Sql: String; Table: TStrings = nil): boolean;
begin
  fError := SQLITE_ERROR;
  if fIsOpen then
  begin
fPMsg := nil;
fBusy := True;
fTable := Table;
if fTable <> nil then
  fTable.Clear;
fError := SQLite_Exec(fSQLite, PChar(Sql), @ExecCallback, Self, fPMsg);
SQLite_FreeMem(fPMsg);
fChangeCount := SQLite_Changes(fSQLite);
fTable := nil;
fBusy := False;
if Assigned(fOnQueryComplete) then
  fOnQueryComplete(Self);
  end;
  fMsg := ErrorMessage(fError);
  Result := (fError <> SQLITE_OK);
end;


I haven't used TStrings much, but I'm guessing that appending to 
TStrings creates the newlines, and that the .commatext method creates
the commas in the TStringlists. I still can't figure out which code
would add the "double quote" marks around returned text, though, if the 
DLL doesn't return a string that way. I've search the code for the " 
mark and the #value, but nothing turns up. 

Thanks

Re: [sqlite] Suppressing column (field) headers?

[mswarm]

>
>Subject: Re: [sqlite] Suppressing column (field) headers?
>   From: Clay Dowling <[EMAIL PROTECTED]>
>   Date: Sun, 21 Nov 2004 19:19:11 -0500
> To: [EMAIL PROTECTED]
>
>[EMAIL PROTECTED] wrote:
>
>>I'm using the 2.8 dll. You seem to be suggesting that I have to 
>>move up to the 3.0 dll to get just the data without the headers. 
>>
>>Will I still have to strip quotes off text, and an appended newline?
>>
>>I'm not sure what "prepared statements" means. Googling the subject
>>gets me lots of 'xxSQLxxx will do prepared statements' but little
>>explanation of what that is. 
>>  
>>
>Prepared statements let you write your SQL like this: SELECT name, qty, 
>location FROM inventory WHERE id=?
>You then get to substitute any value for ? that you want.  It's great 
>for avoiding data escaping problems, and speeds up your SQL processing.

OK. As I said in my original post, I send such through a Delphi
procedure. Quoting:

---
1. How can I get the dll to return JUST the desired data? 
   When I send the following SQL, for example,

  select colEntry from tblEntry where colNum = 3

   the textbox shows 

  colEntry
  "The text in the column."
  

   What I really want is just: 

  The text in the column.
---

>The part that's relevant for you is that you get your result set back 
>one row of data at a time, getting one field for each function call.  
>What you get is the raw data without any quotes or trailing newlines or 
>labels, in the data type that you asked for.
>
>>some insight, especially the comments. I'm forced to rely on someone 
>>else's Delphi wrapper, not all of which I understand. 
>>
>>Was this last comment your way of answering my "Can I drop in the 
>>3.0x dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect 
>>it to work the same?" question? 
>>  
>>
>Well, it was a pretty second rate answer in response to your question. 
>If you look in the Delphi documentation about external libraries you'll 
>see that Delphi can call them directly without really needing a 
>wrapper.  The sqlite3 dll won't be a drop in replacement, but it will 
>let you get the data back in a way that's likely to be more useful to 
>you. 

I tried. It didn't work right. Don't know enough yet about calling DLLs to
know why. It claims the library isn't loaded.


>All you need is a single pascal unit that imports the functions 
>defined in the DLL (or at least the functions you need).  The Delphi 
>documentation will have examples of how to write that module.  Then you 
>make Delphi calls to access the data in exactly the same way that you 
>would access it from a C program. This means that the C documentation 
>now also works for you writing in Delphi.

I presume you mean the structure in the TSQLite code I'm using, which begins:

  SQLite_Open: function(dbname: PChar; mode: Integer; var ErrMsg: PChar): 
Pointer; cdecl;
  SQLite_Close: procedure(db: Pointer); cdecl;
  SQLite_Exec: function(db: Pointer; SQLStatement: PChar; CallbackPtr: Pointer; 
Sender: TObject; var ErrMsg: PChar): integer; cdecl;
etc.


>If you can wait a little while, I actually need to write a Delphi 
>interface to SQLite myself, and I can send you the Pascal unit and a 
>short example off list.

All info a welcome part of the puzzle, I'm sure. Off list is fine, 
but please use the [sqlite] thingie in the header, if you can think
of it, as I get 100 pieces of spam a day to this account. 

Thanks

Re: [sqlite] Suppressing column (field) headers?

[mswarm]

>
>Subject: Re: [sqlite] Suppressing column (field) headers?
>   From: "Clay Dowling" <[EMAIL PROTECTED]>
>   Date: Sat, 20 Nov 2004 13:03:33 -0500 (EST)
> To: [EMAIL PROTECTED]
>
>
>[EMAIL PROTECTED] said:
>
>> 1. How can I get the dll to return JUST the desired data?
>>When I send the following SQL, for example,
>
>You can get just the data that you want by using the prepared statements
>feature of the 3.0.x dll.  It sounds like you're using the table output
>method for getting your results.  This is fine, but it means that you need
>it ignore the first row of data, which in the table interface is the
>column labels.

I'm using the 2.8 dll. You seem to be suggesting that I have to 
move up to the 3.0 dll to get just the data without the headers. 

Will I still have to strip quotes off text, and an appended newline?

I'm not sure what "prepared statements" means. Googling the subject
gets me lots of 'xxSQLxxx will do prepared statements' but little
explanation of what that is. 


>Because sqlite is implemented as a C library, you can use it pretty much
>directly from Delphi in exactly the way you would use the C interface.  It
>might be worth your while to do that, since you'd have a lot more control
>over your interaction.

I'm not a C person, but though going through the C code has given me
some insight, especially the comments. I'm forced to rely on someone 
else's Delphi wrapper, not all of which I understand. 

Was this last comment your way of answering my "Can I drop in the 
3.0x dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect 
it to work the same?" question? 

 

[sqlite] Suppressing column (field) headers?

[mswarm]

New to SQL, SQLite, and the Delphi wrapper I'm using to 
talk to sqlite.dll. 

A couple of questions:

1. How can I get the dll to return JUST the desired data? 
   When I send the following SQL, for example,

  select colEntry from tblEntry where colNum = 3

   the textbox shows 

  colEntry
  "The text in the column."
  

   What I really want is just: 

  The text in the column.

   No header, no quotes, and no newline. Just data. I get 
   the impression the dll is sending all this back, not that
   the Delphi code is adding it. If so, any way to supress, or 
   must I strip the unneeded stuff off?

2. The Delphi setup is using the 2.something dll. Can I drop in the 
   3.something dll and change the 'sqlite.dll' line to 'sqlite3.dll'
   and expect it to work the same? 

   If so, would there be any advantages?

Thanks