Re: [sqlite] Re: Loss of table feature.. ever?

[Edwin Knoppert]

Yes but imo a rather daunting task.
I better leave it to the author of sqlite :)


- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>

To: "SQLite" <sqlite-users@sqlite.org>
Sent: Tuesday, December 12, 2006 8:01 PM
Subject: [sqlite] Re: Loss of table feature.. ever?



Edwin Knoppert <[EMAIL PROTECTED]> wrote:

Hello people, i really need to ask this thing again.
My visual design tool has a custom written datacontrol for sqlite.
This means MoveFirst, Previous, next and movelast buttons.

This control depends heavily on the gettable() sqlite functions.

While i already understand that these functions are deprecated and we
should use the prepare stuff.
I do that already except for this control.
At this time the prepare stuff does not seem to work the same as the
table stuff.
Like moveprevious and such?


sqlite3_get_table simply loads the whole resultset into memory. You can do 
that with sqlite3_prepare et al just as well. In fact, sqlite3_get_table 
is implemeted in terms of sqlite3_prepare, sqlite3_step and so on.


Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Loss of table feature.. ever?

[Edwin Knoppert]

Hello people, i really need to ask this thing again.
My visual design tool has a custom written datacontrol for sqlite.
This means MoveFirst, Previous, next and movelast buttons.

This control depends heavily on the gettable() sqlite functions.

While i already understand that these functions are deprecated and we should 
use the prepare stuff.

I do that already except for this control.
At this time the prepare stuff does not seem to work the same as the table 
stuff.

Like moveprevious and such?

I don't mind, the table stuff is good for me, i'm only want to let you know 
that some of us are depending on this stuff.


Hope you'll leave this part in?

Thanks!


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Icon for sqlite?

[Edwin Knoppert]

My tool needs an sqlite icon, is there one?
I see a logo only.

:)

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

Question remains if it isn't better to go to a new major version on such 
changes.

Forward compatibility is assumed by users imo.



- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, June 27, 2006 1:08 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


Eugene Wee <[EMAIL PROTECTED]> wrote:

Hi,

[EMAIL PROTECTED] wrote:
> The new file format stores boolean values (the integers 0 and 1)
> more efficiently - requiring only 1 bytes of disk space instead of
> 2.  There are no other changes.
>
>
> Let me reemphasize that the new file format has caused so much
> grief that I will likely revert to the older format with 3.4.0.
> That is to say, databases created by 3.4.0 will be readable by
> 3.2.8.  3.4.0 will be able to read and write both the old and the
> new formats, of course.  And you will still be able to use the new
> format using a pragma or a compile-time option.  It just won't be
> the default any more.
>
> I have learned my lesson.  Do not enhance the file format without
> a very good reason.  Saving one byte of space when storing booleans
> is not a sufficiently good reason...

I think that reverting back is not the solution. At the moment,
the news about the change in file format is several entries down
the news and changes list. People may also not be aware that newer
SQLite versions are backwards compatible but not forwards compatible.
If the documentation was clearer on the file format, its changes
and compatibility, an enhancement of the file format may not
cause so much confusion.



You know what - the new file format supports an additional feature
that I completely forgot about: descending indices.  So I suppose
it was worth going to the new format after all.  I put in the
change back in December of last year and had completely forgotten
about the descending index addition.  But it is coming back to
me now.  I added descending indices and said to myself, as long
as I am having to change the file format, I might as well enhance
the boolean value representation too.  But then I completely forgot
about the descending index change.  Silly me



P.S.: any news on when 3.4.0 will be out? :D



I still have not made a final decision on whether it will be
3.3.7 or 3.4.0.  There is no incompatibility so 3.3.7 would
technically be correct.  But there are a lot of enhancements
so I was thinking of going to 3.4.0 just to emphasize the
magnitude of the change.

I'm thinking end of July or early August.

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

Thanks, but do not base your decision on my.
I'm using sqlite to little to complain, i simply was not aware of the 
change.


I write software to make use of sqlite for other people (a designer).
I only need to mention this issue.



- Original Message - 
From: <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, June 26, 2006 8:20 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


"Edwin Knoppert" <[EMAIL PROTECTED]> wrote:

Just wanted to warn you i can not read a newly created table created with
the current release and opening it in v3.2.1 (afaik)

Sorry, i removed the older dll, i overwrote it with the latest and read 
the

table instantly.
Before i had 0 tables shown.

A simple query was used:

CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.

I may assume only a major version will have a different format?



SQLite 3.3.0 can read and write all prior versions of SQLite
databases.  But SQLite 3.2.8 cannot read or write a database
created by SQLite 3.3.0, unless you use

 PRAGMA legacy_file_format=TRUE;

prior to creating the database, or unless you compile 3.3.0
with -DSQLITE_DEFAULT_FILE_FORMAT=1.

The file format enhancement in version 3.3.0 has caused an
inordinate amount of grief for the benefit it provides.  I
deeply regret making it the default.  I might yet, in a future
release, make the old file format the default.  The in a couple
of years time, once all the legacy versions of SQLite that
do not understand it have faded from existance, I can make
the enhanced file format the default again.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

OK, thanks!
Must been a change in the fileformat then..
A bit odd the system does not handle backwards compatibility in minor 
releases.


:)

PS, still can't find my own post, while a reply is given.. odd



- Original Message - 
From: "C.Peachment" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, June 26, 2006 8:16 PM
Subject: Re: [sqlite] v3.2.1 and current differences!



On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:


Just wanted to warn you i can not read a newly created table created with
the current release and opening it in v3.2.1 (afaik)


Sorry, i removed the older dll, i overwrote it with the latest and read 
the

table instantly.
Before i had 0 tables shown.



A simple query was used:



CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )



I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.



I may assume only a major version will have a different format?



After opening the database file with a later library version and
before doing anything else, issue the command:

PRAGMA legacy_file_format=ON;

[sqlite] v3.2.1 and current differences!

[Edwin Knoppert]

Just wanted to warn you i can not read a newly created table created with 
the current release and opening it in v3.2.1 (afaik)


Sorry, i removed the older dll, i overwrote it with the latest and read the 
table instantly.

Before i had 0 tables shown.

A simple query was used:

CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.

I may assume only a major version will have a different format?

Re: [sqlite] how can I import CSV file into SQLite quickly

[Edwin Knoppert]

PBDBMS on www.hellobasic.com

All through ADO..


- Original Message - 
From: "Cariotoglou Mike" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, December 07, 2005 9:23 AM
Subject: RE: [sqlite] how can I import CSV file into SQLite quickly


sqlite3Explorer does that



From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Wed 07-Dec-05 8:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how can I import CSV file into SQLite quickly



Someone somwhere must have a simple Perl script which does what you want.
JS

Robert L Cochran wrote:

I create an SQL file that has contents like this:

[EMAIL PROTECTED] elections]$ cat insert_precinct.sql
BEGIN TRANSACTION;
INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3,
'Greenbelt', 'Maryland', 0);
INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6,
'Greenbelt', 'Maryland', 0);
INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111
Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0);
COMMIT;

Then I fire up sqlite3 on the command line, and issue

.read insert_precinct.sql

I realize this will probably make you unhappy because it means editing
your CSV file so that each line is transformed into an sql statement.
This can be done most easily with sed (if you are a Linux or Unix
person), but you need to know sed commands and you need to be willing to
patiently experiment until the sed script applies exactly the right edits.
Bob Cochran


ronggui wong wrote:


I have a very large CSV file with 1 rows and 100 columns.and the
file looks like the following:
"a","b","c","d",
"1","2","1","3" ,
"3","2","2","1",
..

If I use .import,It seems I have to set the variable names manually .
Is there any way to import the whole data file into SQLite quickly?
Thank you!

ronggui

Re: [sqlite] Re: converting a mysql database

[Edwin Knoppert]

If the mysql db is reachable through dsn (ado-providerstring) you can use my 
pbsdbms tool to import tables.


http://www.hellobasic.com




- Original Message - 
From: "Arjen Markus" <[EMAIL PROTECTED]>

To: 
Sent: Monday, November 07, 2005 8:43 AM
Subject: Re: [sqlite] Re: converting a mysql database



Dave Dyer wrote:


I looked into this, and the actual problem is windows returning
a "access denied" error code when trying to recreate the journal
file immediately after deleting it.   I can't find any documentation
that says create might fail for this reason, but filemon (from
systeminternals.com) says the status is "delete pending" in this
situation.  So I think this is a genuine windows glitch, which
might be related to my having a fast, dual-core processor.

In any case, it seems to be possible to paper over the problem
by waiting a few milliseconds and retrying the create.


I have had weird problems in the past on a Windows platform
with the deletion and re-creation of files. I have never
been able to figure out what happened.

I am not familiar with inner workings of SQLite but I can imagine
that this will not be a problem anymore when you use a
transaction instead of individual inserts.

Regards,

Arjen

Re: [sqlite] Tool to load a database and see all tables and fields.

[Edwin Knoppert]

On my site i have a tool, www.hellobasic.com



- Original Message - 
From: "Eno Thereska" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, September 28, 2005 4:36 PM
Subject: Re: [sqlite] Tool to load a database and see all tables and fields.



What about sqlitebrowser? That's what I currently use.

Eno

spudse bud wrote:
I always was a big fan of phpmyadmin because when php/mysql errored I 
could

check table/field names etc to see where my script goes wrong.

I am wondering if a easy tool exsists for SQLite, that just lists the 
tables

and fields in a database. I have tried sqlitemanager, but I didn't really
liked it.

Greetings koen (spudse).




--


Eno Thereska
-
Carnegie Mellon University
Parallel Data Laboratory
CIC Building Cube 2221-D
5000 Forbes Ave Pittsburgh, PA 15213
Tel: 412-268-5908

Re: [sqlite] Storing RTF text in a field

[Edwin Knoppert]

Simple convert it to 2byte hex "0AFF00..." etcc.

So the update query should use X'your hex data here'




- Original Message - 
From: "Gaurav Patole" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, September 20, 2005 7:14 AM
Subject: [sqlite] Storing RTF text in a field



Hello,

  I have the following table



CREATE TABLE Tasks(taskID INTEGER PRIMARY KEY, parentTaskID INTEGER, 
projID

INTEGER, name varchar(25), assignee varchar(25), start_date timestamp,
end_date timestamp, completed_date timestamp, last_edited timestamp,
created_date timestamp, last_edited_by varchar(25), author varchar(25),
percent_complete INTEGER, priority INTEGER, status INTEGER, description
blob, last_modified timestamp);



With description field as BLOB.



I wish to store the description that may contain RTF text.

Example:



{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fprq2\fcharset
0 Times New Roman;}}

{\colortbl ;\red0\green0\blue255;}

\viewkind4\uc1\pard\fi-360\li900\tx900\f0\fs24 1.\tab Showing comment
notifications though all the notification options are unchecked when we 
copy

and paste the task having comments.

\par \pard\fi-360\li900 2.\tab Description of 15 to 20 lines is not
retaining when we reassigned the task.

\par 3.\tab Problem in copy and paste: Copy and pasting the task having
attachments, source file's attachments gets disappeared - \cf1\b
Completed\cf0\b0 .

\par 4.\tab Normal user is not able to mark his task as completed - \cf1\b
Completed\cf0\b0 .

\par 5.\tab Problem in copy paste: Not asking for changing resource when
resource is not available in the source project.

\par 6.\tab User level rights user s/d be able to move his task.

\par \pard

\par \pard\li360 Date: 19 September' 05

\par Private Build 62

\par

\par \pard\fi-360\li720\tx720 1.\tab User s/d attaches attachment while
assigning the new task.

\par \pard\fi-360\li720 2.\tab Problem with right click move to option: 
The
option is not working when user has not changed the resource name, means 
its

not working with default resource name.

\par 3.\tab Once click on no to all (if resource is not available) next 
time

unable to copy and paste the task from one project to another, also
simulating with moving tasks by drag and drop.

\par 4.\tab While right click move to don't allow the user to select
multiple resources initially rather than showing message later.

\par 5.\tab Making project status as on-hold or proposed, all its existing
tasks are not becoming on-hold.

\par 6.\tab Project manager s/d be able to view his completed and proposed
projects.

\par 7.\tab All the tasks of the completed projects s/d become completed.

\par 8.\tab Not changing assigned to field while moving the task from one
project to another project, by using right click move to option.

\par 9.\tab Assignee field s/d gets changed while reassigning the task 
from

right click - reassigned to option for example if amol is reassigning the
task to bhavna which is assigned to amol by sayali , in this case author 
s/d
be sayali and assignee s/d be amol. Presently assignee and author remains 
as

sayali.

\par \pard Reassigned to is not changing the assignee field, author and
assignee field remains same though task is reassigned by

\par }







Now when I try to save it to database, I get an error: unrecognized token:
"\". What should I do?



Thanks,

Gaurav.

Re: [sqlite] Duration of reading records seems longer when sortedas not.

[Edwin Knoppert]

I'm using 3.2.1 and have no index.
Note that i'm not talking about the query itself but the data retrieval.



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 25, 2005 2:59 PM
Subject: Re: [sqlite] Duration of reading records seems longer when sortedas 
not.




On Thu, 2005-08-25 at 05:56 -0700, Cory Nelson wrote:

I believe the recent release can use an index for sorting, so you may
want to try that.



To clarify:  SQLite has been able to use indices for sorting
for a long time - going back to version 2.5.1 on 2002-June-19.
Recent releases just do a better job of it.
--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Duration of reading records seems longer when sorted as not.

[Edwin Knoppert]

FYI:

Duration of reading records seems longer when sorted as not.
~600 rec's in 500ms instead of 2400 (also ~500ms)
I'm not talking about prepare but the data retrieval per record.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Maybe we should explain the term 'wrapper'.
I'm using wrappers around the salite dll in my own code.
But a wrapper might be seen over here as additional library (dll or so) 
others can use.

A redistribute part, which i don't like.
(Like the VB 'wrapper' dll)


- Original Message - 
From: "Walter Meerschaert" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 25, 2005 3:43 AM
Subject: Re: [sqlite] Why can i open a textfile?



D. Richard Hipp wrote:

Most people who are using SQLite successfully have, I imagine, either 
written their own wrappers around the core API  (which

is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else.
Indeed I wrote a thin wrapper when I started using SQLite 6 months ago. I 
now retract my request for more control over the open function, as one of 
the features of SQLite that I really liked was that it didn't have an 
extensive set of "features". I can and will implement the read/only and 
exclusive tests in my own open wrapper, and return or throw where 
appropriate.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Wow, that suprises me, i just stepped of the _gettable() call to get me the 
result using prepare and step.

So i finally could read the $NUL BLOB data.
I can't imagne what would be better since a forward only behaviour is the 
best there is.

Besides it's side-effects of not being able to navigate back.

There are people like me they never heard about TCL, i don't know what it is 
and also what schema's are.

It's an area i never visit since it's not familiar to me.
I can not imagne to do things like this not using your API.

I don't think i'm blind for other things but there siomply are programmers 
used to use plain functions.


To make sure you understand, through PowerBASIC i'm able to connect to your 
sqlite through api's.

The issue is that conversion from your api to a BASIC syntax is pretty hard.
Espec. if calls have pointers which are in fact only interesting for 
including the lib into c but is irrellevant to dll users.


Don't get me wrong, it's not to annoy you, just want to show my perspective 
and might even help to consider this for the future.
Like a major rewrite to standard calls and making use of not-so-c syntax 
(pointers/casting or whatever removed, just an example)


(Again: it's not handy to show *sqlite in a param but simply have a byval 
hstmt as long (but then in c syntax :) ) would be much better since it does 
not tell unecessary info that *sqlite is actually a pointer to a structure 
or similar, this might reach more end-users)


I usually keep in mind a 'dumb' VB should be able to access it.
If that works it usually works with anything.

Using wrappers is no option to me unless it's for dotnet (asp.net), i fell 
dumb if i would use a wrapper lib.
Of course the PowerBASIC code being spread for sqlite gives me a headstart 
to access the dll.

But the code is not my taste so i do it myself.

Is there any reason not to stick with prepare() and step()?
Works for me at this time.

:)



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 1:28 AM
Subject: Re: [sqlite] Why can i open a textfile?



On Tue, 2005-08-23 at 00:04 +0200, Edwin Knoppert wrote:
All i meant is that sqlite is created by a c programmer, like my 
colleague

he seems to forget there are different languages but he doesn't care.



Actually, I'm a TCL programmer.  I only resort to C code to write
new TCL extensions.  I personally never use the SQLite C API except
when I am working on internals of the TCL bindings.  I use SQLite
in my professional work a lot, but I always access it through the
TCL bindings.  Even when I am working on SQLite itself, I almost
always access it through the TCL bindings.  (All of the test
scripts are writtne in TCL.)

And this brings up an important point:  I'm increasingly aware of
the fact that the C API to SQLite really wants to be wrapped.  There
are a lot of loose ends that can cause complication and trouble if
you try to use the C API directly in your application.  Things like
the infamous SQLITE_SCHEMA returns.  And the notorious SQLITE_BUSY.
And then the recent conversation about how to tell if the file is
really a database or not.  These kinds of things are best dealt with
once in a wrapper and then forgotten.  You do not want to have to
be worrying about error return codes when you are trying to make
the next great klller app.  Programmers need to save their brain
cycles to focus on the really hard problems, not on handling lots
of pesky return codes.

Most people who are using SQLite successfully have, I imagine,
either written their own wrappers around the core API  (which
is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else.

If you are not doing this - if you are trying to make low-level
SQLite API calls like sqlite3_prepare and sqlite3_step for each
bit of SQL you want to run, then let me suggest that you are using
the library incorrectly.  That is way too much work.  I expose
those calls because they provide you with a lot of control and
give wrapper writers the freedom to do lots of interesting things
with the database.  If you are working on an application that just
needs access to data, sqlite3_prepare and sqlite3_step are way
too low level.  Find a wrapper suitable for your needs and use it
instead.  Or write your own wrapper if an appropriate one can't
be found.  This will result in a program that is easier to write,
easier to maintain, has fewer bugs, and just works better.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Yes, the master thing is what i tried with the previous version.
I guess i'll use it on the newest dll again.
Not so important from now on.


- Original Message - 
From: "Drew, Stephen" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>; <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 1:02 AM
Subject: RE: [sqlite] Why can i open a textfile?


Point taken about viruses perhaps, but there are other reasons one might 
want to encrypt data - which by its very nature could be related to 
anything.


For example, in a commercially competitive environment, it might be easy 
for a competitor to gain access to files, or even colleagues within the 
same organisation who do not have the privilege to see such data. A file 
that can be easily read, often will be - whereas an encrypted file, of any 
reasonable level, will often be enough to deter prying eyes.


If one needs to know whether a file is a database or not immediately after 
opening it, surely a user-written wrapper function around sqlite(3)_open 
which does a simple select on sqlite_master is the way to go?  Keeps 
everybody happy Personally I'd wrap the calls to sqlite anyway but C++ 
sort of suggests doing this regardless


Steve

-Original Message- 
From: Edwin Knoppert [mailto:[EMAIL PROTECTED]

Sent: Mon 22/08/2005 23:20
To: sqlite-users@sqlite.org
Cc:
Subject: Re: [sqlite] Why can i open a textfile?



At the other hand, this is database stuff, what on earth would you encrypt
in real life business databases.
No one cares, except for a few purposes.
(Now i done it :) )
Encrypting a header, like if any virus writer is busy with a tool like
sqlite..
pfffttt.


- Original Message -
From: "Dennis Jenkins" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 12:07 AM
Subject: Re: [sqlite] Why can i open a textfile?


> Mike Shaver wrote:
>
>>On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>>
>>>I very much disagree.  I want the entire file, header included, to be
>>>encrypted.  Sometimes you don't want anyone to know what the file type
>>>is.  Security through obscurity is not secure.  However, you don't want
>>>to give the bad guys a road map either...
>>>
>>
>>Finding out that it's a sqlite file is not a hard problem for an
>>attacker who has any interesting access to your machine, since your
>>programs must find that file somehow.  Once they find it, are you not
>>concerned about lightening their cryptanalysis burden through
>>known-plaintext attacks?
>>
>>Mike
>>
> No, not really.  The sqlite crypto engine consumes the first several
> hundred bytes of the rc4 random number generator output.  It is my
> understanding that this would significantly complicate the plain-text
> attack.  But I'm not a crytologist.  I do find it facinating though.
>
> I do not understand how "finding the file" would give the attackers any
> clue to what kind of file it is (unless I make the filename something 
> like

> "sqlite3.db3").  If the file were named "jimbob.dat", and the contents
> looked like gibberish, then what do they know?  They must analyze the
> program that accesses the file.
>
> I once thought that I could remove all text strings from the sqlite code
> that would give the attacker any clues.  I then realized that the 
> strings

> are important to the proper functioning.  The ones that need to be left
> behind are significant enough to be good clues that the program uses
> sqlite technology.  So, I do agree with you, that it is not too 
> difficult

> to determine if a data file _might_ be an sqlite database, even if it in
> encrypted.
>
> That being said, I still like having the header encrypted as it is. 
> Maybe

> it just makes me feel warm and fuzzy on the inside :)
>
> In the end, I feel that our software is much more vulnerable to someone
> attacking it with a debugger than with crypto analytic attacks.  At some
> point, you must call "sqlite3_key()" and pass it three things: the 
> sqlite

> handle, a void* to the key initializer and an "int" (# of bytes in the
> key).  All the attacker has to do is locate that code and determine what
> those last two arguments are.  Personally, I find this to be an easier
> approach.  But then, I've been coding in assembly since I was 8 and C 
> for

> the last 10 years.  I'm not much of a mathematician or code breaker.
>
> I have often wondered how difficult it would be to derive the rc4
> initialization key given a known plain text and a known cipher text
> generated from the unknown key and known plain text.  I imagine it as a
> breadth-first search of the key space.
> Lets say that it is computa

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

At the other hand, this is database stuff, what on earth would you encrypt 
in real life business databases.

No one cares, except for a few purposes.
(Now i done it :) )
Encrypting a header, like if any virus writer is busy with a tool like 
sqlite..

pfffttt.


- Original Message - 
From: "Dennis Jenkins" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 23, 2005 12:07 AM
Subject: Re: [sqlite] Why can i open a textfile?



Mike Shaver wrote:


On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:


I very much disagree.  I want the entire file, header included, to be
encrypted.  Sometimes you don't want anyone to know what the file type
is.  Security through obscurity is not secure.  However, you don't want
to give the bad guys a road map either...



Finding out that it's a sqlite file is not a hard problem for an
attacker who has any interesting access to your machine, since your
programs must find that file somehow.  Once they find it, are you not
concerned about lightening their cryptanalysis burden through
known-plaintext attacks?

Mike

No, not really.  The sqlite crypto engine consumes the first several 
hundred bytes of the rc4 random number generator output.  It is my 
understanding that this would significantly complicate the plain-text 
attack.  But I'm not a crytologist.  I do find it facinating though.


I do not understand how "finding the file" would give the attackers any 
clue to what kind of file it is (unless I make the filename something like 
"sqlite3.db3").  If the file were named "jimbob.dat", and the contents 
looked like gibberish, then what do they know?  They must analyze the 
program that accesses the file.


I once thought that I could remove all text strings from the sqlite code 
that would give the attacker any clues.  I then realized that the strings 
are important to the proper functioning.  The ones that need to be left 
behind are significant enough to be good clues that the program uses 
sqlite technology.  So, I do agree with you, that it is not too difficult 
to determine if a data file _might_ be an sqlite database, even if it in 
encrypted.


That being said, I still like having the header encrypted as it is.  Maybe 
it just makes me feel warm and fuzzy on the inside :)


In the end, I feel that our software is much more vulnerable to someone 
attacking it with a debugger than with crypto analytic attacks.  At some 
point, you must call "sqlite3_key()" and pass it three things: the sqlite 
handle, a void* to the key initializer and an "int" (# of bytes in the 
key).  All the attacker has to do is locate that code and determine what 
those last two arguments are.  Personally, I find this to be an easier 
approach.  But then, I've been coding in assembly since I was 8 and C for 
the last 10 years.  I'm not much of a mathematician or code breaker.


I have often wondered how difficult it would be to derive the rc4 
initialization key given a known plain text and a known cipher text 
generated from the unknown key and known plain text.  I imagine it as a 
breadth-first search of the key space.
Lets say that it is computationally feasible to do just that.  The sqlite 
header string is.. um, heck, I don't know, let's say 20 bytes.  Then you 
can derive the exact values for at most 20 values of the key state vector 
(it might be less if a value gets muted more than once).  What do you know 
about the remaining bytes of the first 256 bytes of the sqlite file?  Some 
of those bytes have "sane" values or other constraints.  I think that it 
would be too difficult to fully derive the key b/c you don't know much of 
the plain text.


This is the extent of what I know about rc4.  If someone else knows more, 
please enlighten me. :)

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

50% o well just have us an indication, nothing counted.
I meant 50% of this forum users, or... this planet, whatever.

All i meant is that sqlite is created by a c programmer, like my colleague 
he seems to forget there are different languages but he doesn't care.


Well, to keep cdecl functions and provide examples with pointers is not 
really a benefit to other kinds of users.
c is hard to read and/or convert for BASIC users, oops now i said it, BASIC 
is often poison in c perspective :)


Well, i can not complain, sqlite is performing well and it's free.
But just wanted to make note to inform you there are other kind of 
programmers in the world.
Wrapper libraries wouldn't not be necessary to access sqlite directly with a 
little thought.

With my language i can safely access it, it's just all to much c imo.

The remark about the encrypted header is really a thing never experianced by 
myself.
I don't think any fileformat supporting an encryption will also encrypt 
these kinds of thing.

You are over-doing it imo.
I can imagne you want a totally safe file(format) this way.
But then, we are in disagreement :)






- Original Message - 
From: "Dennis Jenkins" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 11:31 PM
Subject: Re: [sqlite] Why can i open a textfile?



Edwin Knoppert wrote:


Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.



Sqlite has a header.  Part of it is plain text, part binary.  You can 
learn about it by reading the source code.



Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)



I very much disagree.  I want the entire file, header included, to be 
encrypted.  Sometimes you don't want anyone to know what the file type is. 
Security through obscurity is not secure.  However, you don't want to give 
the bad guys a road map either...




Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)


And we love it.


But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.


Sad, it is truely neat-o code.

I guess at least 50 percent over here does not really care and would 
never explore how to solve these kind of things.


50 percent of whom?  Programmers who use sqlite?  Where is "over here"?



I hope examples and such will eventually get more polished towards other 
languages as well.
Examples for instance show *sqlite as hstmt and there is really no use to 
understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as cdecl) 
is not that trival due conversion and such.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Hmm, the latter might have sounded a bit harsh.
It must be seen as reminder, no pointing finger or so :)




- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 11:12 PM
Subject: Re: [sqlite] Why can i open a textfile?



Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.
Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)

Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)
But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.
I guess at least 50 percent over here does not really care and would never 
explore how to solve these kind of things.


I hope examples and such will eventually get more polished towards other 
languages as well.
Examples for instance show *sqlite as hstmt and there is really no use to 
understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as cdecl) 
is not that trival due conversion and such.





- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 9:23 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Mon, 2005-08-22 at 20:58 +0200, Mark de Vries wrote:

I must say I agree. Perhaps there is some verry good reason to delay
actually opening the DB untill the first real access.



There is a good reason, actually.  SQLite has the ability
to read and write encrypted databases.  The hooks to do this
are there in the public-domain code, though the encryption
code itself is missing.  Nevertheless, it is a capability
of SQLite.

The encryption key is specified after the database is
opened.  But without an encryption key, SQLite has no
way of knowing if the file it opened is a valid database.
It might *look* like a text file, but that just might
be because the database is encrypted in a way that makes
it appear so.  Hence, SQLite cannot pass judgement on
a file until you actually try to do something with it.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.
Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)

Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)
But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.
I guess at least 50 percent over here does not really care and would never 
explore how to solve these kind of things.


I hope examples and such will eventually get more polished towards other 
languages as well.
Examples for instance show *sqlite as hstmt and there is really no use to 
understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as cdecl) is 
not that trival due conversion and such.





- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Monday, August 22, 2005 9:23 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Mon, 2005-08-22 at 20:58 +0200, Mark de Vries wrote:

I must say I agree. Perhaps there is some verry good reason to delay
actually opening the DB untill the first real access.



There is a good reason, actually.  SQLite has the ability
to read and write encrypted databases.  The hooks to do this
are there in the public-domain code, though the encryption
code itself is missing.  Nevertheless, it is a capability
of SQLite.

The encryption key is specified after the database is
opened.  But without an encryption key, SQLite has no
way of knowing if the file it opened is a valid database.
It might *look* like a text file, but that just might
be because the database is encrypted in a way that makes
it appear so.  Hence, SQLite cannot pass judgement on
a file until you actually try to do something with it.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Confirmed but open still opens the textfile, i wish it cancelled that as 
well.

There is no use to an open textfile for sqlite.


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Monday, August 22, 2005 5:54 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Mon, 2005-08-22 at 08:42 -0700, Clark Christensen wrote:


--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> ...
> SQLite never "blows away" a file that is not a database.
>
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>


Sorry to report this doesn't seem to be the case.  At least
not under Windows.  Using v3.2.2, I can "sqlite3 junk.txt",
then "create table junk (a,b)", and the text file becomes a
SQLite3 database :-(

FWIW, junk.txt contained several lines of text before
becoming a database.



See http://www.sqlite.org/cvstrac/tktview?tn=1370
The problem has been fixed in version 3.2.3.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

O well, read-only?
Then exclusive as well :)


- Original Message - 
From: "Walter Meerschaert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, August 21, 2005 2:04 AM
Subject: Re: [sqlite] Why can i open a textfile?


I agree, since that makes error/exception handling easier. On the 
subject of open(), I also would like it to have a read_only option, if 
that is possible. I am not even sure that a read-only state is tracked 
through the library, or if the writing attempts just fail with an file 
access error.


Or is there already a way to open the database read only?

Edwin Knoppert wrote:

I haven't test on exactly 1kb file but larger and indeed an error is 
shown.


May i stress again that the test should be during open() imo?
And rather not using a 2nd function to examine the db.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Oops.. good bye simple telephone list!
I just added a table and the list is replaced by an sqlite db.



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to  
> sqlite.

> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

This does not result in an error but returns the fields (no records of 
course):


SELECT * FROM sqlite_master where [type]='table'


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to
> sqlite.
> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

I could do a simple test myself of course, like checking the master or so..


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to  
> sqlite.

> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

I ever read the suggestion to read in the db myself and check for a 
signature.

Well, if i strongly suggest an extra test is done by the system itself.
Sure if 0 bytes it's a go as well, as it is now but if > 0 bytes.. test..
I see no point in opening the database while other commands will fail.

Thanks,



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to
> sqlite.
> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] PBSDBMS, sqlite database tool

[Edwin Knoppert]

On my site http://www.hellobasic.com/ (free downloads) you can find PBSDBMS,
a DBMS for sqlite v3.07 and up.

Does handle the main aspects for sqlite like tables, columns, views and a
few others..
Also does import and export.
Blob data having nul chars is supported.
An extensive querybuilder.
Zero installation, just unzip and run.

Yes, intention is to ever smoothen the gui more, i know..

Hope you like it..

Thanks!

Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i wrote.

[Edwin Knoppert]

Hmm, my previous post got not posted because the correct address wasn't set.
Can't get a grip on this board yet :)

A duplicate is not created if there was an error.
And if the name already exists but that would generate an error as well.
You might have been mistaken since there is a sort in the tablenames (?)

I know the gui is not 100%, the technical part is more important for me now.
Writting a better gui can always be done in the future.

There are enough parts in the code i wouldn't publish.

The bottlenecks are the table rebuilds and duplicates+data.
In both cases the table is copied to a new one.
All data *must* be equal as before of course.
pbsdbms transfers data in the X' HEX ' format and should be safe.
But still, databases are very important and if a mistake is made you might 
never find out until...?

Therefore the warnings and this extra testing.

Don't forget to check the help, import is a nice feature, all OLEDB/ADO 
stuff can be read in (i hope :) )


Give the QBE part a try, it's clean and simple.
Looks like access QBE.
The result query can be copied onto the clipboard and placed in a view or 
whatever.

The querydef's are maintained in an 'hidden' table.
(It's made hidden in pbsdbms only of course)

Version 2 is rather obsolete, are there more people interested in v2.8 
support?

And why?
Afaik only the blob part might be troublesome for me.
Maybe no NUL char support.
Not my favourite, i like sqlite3 as it is now very.



- Original Message - 
From: "Andrew Brampton" <[EMAIL PROTECTED]>

To: "Edwin Knoppert" <[EMAIL PROTECTED]>
Sent: Tuesday, August 16, 2005 10:12 PM
Subject: Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i 
wrote.




Hi again,
ok I just tested Duplicate again and it did work, but I swear 10minutes 
ago it didn't. Maybe there is some odd situation that caused that :/



Mail me back and I'll write you out the list.

What does that mean, you're no longer testing?


Sorry I meant, if you want me to give you feedback on the GUI mail me back 
and I'll write out a list. I didn't want to write out the list if you 
didn't want it (because in your orginal email you said "not what to change 
like gui changes".


Oh as for the open sourcing I'm not going to convince you to, but I'm 
curious as to why you want to stay closed?


So far I've not found any "critical" bugs, only the little things I 
already mentioned. It seems pretty good, and with a few minor tweaks I'm 
sure it be ready to release.


oh another idea!. Any chance for SQLite 2 and 3 support?

Thanks
Andrew

- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: "Andrew Brampton" <[EMAIL PROTECTED]>
Sent: Tuesday, August 16, 2005 9:00 PM
Subject: Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i 
wrote.




All those things you mention might change in the future.
Main idea about this test to find serious issues.
The table create and a few others is not preferred by me either.
But has no serious impact on it's use at this time.
Duplicate does duplicate a table with or without it's contents, should 
work.

Ok name is a random one but it should be identical, if not... ?
I'm considering to rewrite the listview 'engine' to make use of 
sqlitre_prepare() instead of .. gettable()
This will have impact on the virtual grid and in the future i might 
rewrite that part completely + including a way to edit the contents.

And yes with an optional binary edittor for BLOB data as well.
That's far from now (if ever).

That combo.. well i wanted that myself but at this time that grid is not 
able to handle both edit and combo.

Otherwise it would be in right-away.


Mail me back and I'll write you out the list.

What does that mean, you're no longer testing?

Source will not be public no.



----- Original Message - 
From: "Andrew Brampton" <[EMAIL PROTECTED]>

To: "Edwin Knoppert" <[EMAIL PROTECTED]>
Sent: Tuesday, August 16, 2005 9:52 PM
Subject: Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool 
i wrote.




Hi,
I've received it now... I had a quick play with it and I couldn't find 
any way to crash it.


A few comments:
1) When I add a table why does a default table get made? Can't I select 
some options before its created?
2) When I duplicate structure why can't I chose its name before its 
created?
3) Duplicate doesn't seem to do anything. No message, no error, no 
change.

4) Is there a way for me to edit the records in the database?

I haven't played with the Views, Triggers, Indexes or Query Builder yet, 
but I will soon


I had some comments about the GUI, but you say you dont' want to hear 
about them. They were general HCI comment ie ("I would like drop down 
box for the types in the table editor), as opposed to "I don't like the 
look of blah, etc", so you may find them useful. Mail me back and I'll 
write 

Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i wrote.

[Edwin Knoppert]

Sent..


- Original Message - 
From: "Andrew Brampton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 16, 2005 10:38 AM
Subject: Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i 
wrote.




Sure I'll help out.

Andrew

- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 16, 2005 8:29 AM
Subject: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i 
wrote.





I only need a list what i need to correct like critical errors, not what 
to change like gui changes or similar.
It's a freeware tool and not being released before it had a better test 
than i did.


Mail me if you want to help.

App consists from exe, hlp and sqlite3 dll.
hope you'll look into the help as well.

Here is an image:
http://www.hellobasic.com/images/pbsdbms.jpg

Thanks!

Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i wrote.

[Edwin Knoppert]

Only Windows i'm affraid.
(Virtual PC?)


- Original Message - 
From: "Dr Gerard Hammond" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Cc: "Edwin Knoppert" <[EMAIL PROTECTED]>
Sent: Tuesday, August 16, 2005 10:51 AM
Subject: Re: [sqlite] I need one or two testers for my SQLITE3 DBMS tool i 
wrote.




At 9:29 AM +0200 16/8/05, Edwin Knoppert wrote:
I only need a list what i need to correct like critical errors, not what 
to change like gui changes or similar.
It's a freeware tool and not being released before it had a better test 
than i did.


Mail me if you want to help.

App consists from exe, hlp and sqlite3 dll.
hope you'll look into the help as well.

Here is an image:
http://www.hellobasic.com/images/pbsdbms.jpg

Thanks!


Does it run under MacOS X. If so, then I'd be happy to test.
--

Cheers,

Dr Gerard Hammond
Bioinformatic Analyst
Garvan Institute of Medical Research, Sydney, Australia.

Bioinformatics is the science of managing,  mining, and interpreting 
information from biological and clinical data, including  sequences, 
structures, gene expression profiles, mass spectra and medical records.

[sqlite] I need one or two testers for my SQLITE3 DBMS tool i wrote.

[Edwin Knoppert]

I only need a list what i need to correct like critical errors, not what to 
change like gui changes or similar.
It's a freeware tool and not being released before it had a better test than 
i did.


Mail me if you want to help.

App consists from exe, hlp and sqlite3 dll.
hope you'll look into the help as well.

Here is an image:
http://www.hellobasic.com/images/pbsdbms.jpg

Thanks!

[sqlite] DB locked on prepare?

[Edwin Knoppert]

Is it me doing something wrong (again :) )?
I have a transaction and renaming a table like table1 to let's say table1_tmp 
(whatever)
I open table1_tmp using prepare.
Right after that i create a table named table1, i get an error 6.

Previously i used gettable instead of prepare and left it open and worked fine.

[sqlite] Data/time storing?

[Edwin Knoppert]

Wonder what the best way is to store a date (and time).
At this time i use "mmdd" but is there a by sqlite recommended method?

This aplies time as well, not sure what i'll use, prob HH:MM:SS.
But then it lacks ms's.

Re: [sqlite] BLOB field returns as TEXT type

[Edwin Knoppert]

Oops, forgot to use X before the storage (grrr!)

X'hexdata'

sqlite3_column_text returns a pointer to binary data wich i can copy by 
obtaining the columndata size.


BLOB type is now properly returned as well :)

Thanks anyway.





- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Friday, July 29, 2005 4:21 PM
Subject: Re: [sqlite] BLOB field returns as TEXT type


Only the sqlite3_column_decltype() return "BLOB" but that might be any 
declared keyword.

Point is that the lib knows that it must return the data in hex notation.
I would like to know how i can determine this as well so i can convert the 
hex data back to normal.





- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Friday, July 29, 2005 3:39 PM
Subject: [sqlite] BLOB field returns as TEXT type



I'm expecting SQLITE_BLOB when using sqlite3_column_type()
It keeps returning SQLITE_TEXT (3) while the field was created as BLOB.

The text returned from sqlite3_column_text() is in hex notation.
At this time i don't mind the wrong type nr as long i can distinguish the 
HEX blob data from ordinary fielddata (text e.o)
I have no problem with a conversion from hex to binary data, it's my wish 
to use a common function to return any fielddata requested as binary 
string data.

(And thus not knowing at forhand what datatype was stored)

How?

Re: [sqlite] BLOB field returns as TEXT type

[Edwin Knoppert]

Only the sqlite3_column_decltype() return "BLOB" but that might be any 
declared keyword.

Point is that the lib knows that it must return the data in hex notation.
I would like to know how i can determine this as well so i can convert the 
hex data back to normal.





- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Friday, July 29, 2005 3:39 PM
Subject: [sqlite] BLOB field returns as TEXT type



I'm expecting SQLITE_BLOB when using sqlite3_column_type()
It keeps returning SQLITE_TEXT (3) while the field was created as BLOB.

The text returned from sqlite3_column_text() is in hex notation.
At this time i don't mind the wrong type nr as long i can distinguish the 
HEX blob data from ordinary fielddata (text e.o)
I have no problem with a conversion from hex to binary data, it's my wish 
to use a common function to return any fielddata requested as binary 
string data.

(And thus not knowing at forhand what datatype was stored)

How?

[sqlite] BLOB field returns as TEXT type

[Edwin Knoppert]

I'm expecting SQLITE_BLOB when using sqlite3_column_type()
It keeps returning SQLITE_TEXT (3) while the field was created as BLOB.

The text returned from sqlite3_column_text() is in hex notation.
At this time i don't mind the wrong type nr as long i can distinguish the HEX 
blob data from ordinary fielddata (text e.o)
I have no problem with a conversion from hex to binary data, it's my wish to 
use a common function to return any fielddata requested as binary string data.
(And thus not knowing at forhand what datatype was stored)

How?

Re: [sqlite] Sorry, trivail question probably, chr(0) in blob.

[Edwin Knoppert]

Sorry for asking, but is there a read blob for this without binding?
At this moment i'm using ..GetTable() and seems to offer only null 
terminated strings.



- Original Message - 
From: "Christian Smith" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 27, 2005 6:13 PM
Subject: Re: [sqlite] Sorry, trivail question probably, chr(0) in blob.



On Wed, 27 Jul 2005, Edwin Knoppert wrote:


I reread the faq hundred times but i don't understand the \000 remark.

For INSERT how can i embed BLOB data having single quote and chr(0) bytes?
Currently i replace all singlequotes with 2x single quotes and that works 
fine.

The chr(0) byte did not succeed.
I'm using the dll and don't do any c to prepare me the encoding call(s).
Afaik this one should be obsolete with v3.x anyway is it?



BLOBS can be inserted by using the form:
X'x1x2x3..xn'

Where:
x1..xn are two hex-digit representation of data.

eg.
sqlite> select X'48656c6c6f20576f726c6400';
Hello World
sqlite> select quote(X'48656c6c6f20576f726c6400');
X'48656C6C6F20576F726C6400'


You'll have to convert to the hex form yourself, as SQL appears to have no
way to quote NUL bytes within a string.



Thanks!



--
   /"\
   \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X   - AGAINST MS ATTACHMENTS
   / \

Re: [sqlite] Sorry, trivail question probably, chr(0) in blob.

[Edwin Knoppert]

Wow, super!

I made a very fast converter for that so..

Thanks!



- Original Message - 
From: "Christian Smith" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 27, 2005 6:13 PM
Subject: Re: [sqlite] Sorry, trivail question probably, chr(0) in blob.



On Wed, 27 Jul 2005, Edwin Knoppert wrote:


I reread the faq hundred times but i don't understand the \000 remark.

For INSERT how can i embed BLOB data having single quote and chr(0) bytes?
Currently i replace all singlequotes with 2x single quotes and that works 
fine.

The chr(0) byte did not succeed.
I'm using the dll and don't do any c to prepare me the encoding call(s).
Afaik this one should be obsolete with v3.x anyway is it?



BLOBS can be inserted by using the form:
X'x1x2x3..xn'

Where:
x1..xn are two hex-digit representation of data.

eg.
sqlite> select X'48656c6c6f20576f726c6400';
Hello World
sqlite> select quote(X'48656c6c6f20576f726c6400');
X'48656C6C6F20576F726C6400'


You'll have to convert to the hex form yourself, as SQL appears to have no
way to quote NUL bytes within a string.



Thanks!



--
   /"\
   \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X   - AGAINST MS ATTACHMENTS
   / \

Re: [sqlite] Commit and Rollback

[Edwin Knoppert]

And i wish for a test if the transaction is already set or not, without 
using a callback.

Even better, nested tranactions would be the best.
:)



- Original Message - 
From: "Marco Bambini" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, July 28, 2005 9:24 AM
Subject: [sqlite] Commit and Rollback


I need to know when a commit or a rollback is executed, I know that I  can 
use the sqlite_commit_hook routine to be notified of a commit  command, but 
what about rollback?

Is there a way to know (or to be notified) when a rollback happens?

Thanks a lot for your help,
Marco Bambini

Re: [sqlite] Sorry, trivail question probably, chr(0) in blob.

[Edwin Knoppert]

I will look that up, read something about binding.
But *can* 'escaping' be used as alternative?


- Original Message - 
From: "Cory Nelson" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 27, 2005 5:41 PM
Subject: Re: [sqlite] Sorry, trivail question probably, chr(0) in blob.


in sqlite 3.x you can bind data to prepared statements, no escaping is 
required.


On 7/27/05, Edwin Knoppert <[EMAIL PROTECTED]> wrote:

I reread the faq hundred times but i don't understand the \000 remark.

For INSERT how can i embed BLOB data having single quote and chr(0) bytes?
Currently i replace all singlequotes with 2x single quotes and that works 
fine.

The chr(0) byte did not succeed.
I'm using the dll and don't do any c to prepare me the encoding call(s).
Afaik this one should be obsolete with v3.x anyway is it?

Thanks!





--
Cory Nelson
http://www.int64.org

[sqlite] Sorry, trivail question probably, chr(0) in blob.

[Edwin Knoppert]

I reread the faq hundred times but i don't understand the \000 remark.

For INSERT how can i embed BLOB data having single quote and chr(0) bytes?
Currently i replace all singlequotes with 2x single quotes and that works fine.
The chr(0) byte did not succeed.
I'm using the dll and don't do any c to prepare me the encoding call(s).
Afaik this one should be obsolete with v3.x anyway is it?

Thanks!

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

Just tested, seems to work :)

Thanks!

- Original Message - 
From: "Nuno Lucas" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, July 25, 2005 10:38 AM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames


I'm not understanding what's the problem here. I always get the column 
names

even on empty result sets (so I can show them in a grid header).

I confess I haven't been using the latest sqlite versions to be sure it
is still returned, but isn't this what you want?

My guess is that maybe you are using the callback API, that need
"PRAGMA empty_result_callbacks = 1" to return the columns on empty result
sets, but I haven't used that API for a long time.

Regards,
~Nuno Lucas


[25-07-2005 9:08, Edwin Knoppert escreveu]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to 
obtain the fields properly.

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

That's good news!
Thanks, will try.

:)


- Original Message - 
From: "Nuno Lucas" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, July 25, 2005 10:38 AM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames


I'm not understanding what's the problem here. I always get the column 
names

even on empty result sets (so I can show them in a grid header).

I confess I haven't been using the latest sqlite versions to be sure it
is still returned, but isn't this what you want?

My guess is that maybe you are using the callback API, that need
"PRAGMA empty_result_callbacks = 1" to return the columns on empty result
sets, but I haven't used that API for a long time.

Regards,
~Nuno Lucas


[25-07-2005 9:08, Edwin Knoppert escreveu]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to 
obtain the fields properly.

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

I might be mistaken but you should never expect row-0 for data.
What i meant is that the fieldnames are present when there is data.
And not when there is no data.
In either case, row-0 should never be threated as data row.
So one can not expect data in there.

The point is when there is data, SQLite returns the fieldnames from a 
(joined) query.

For what reason it should not return the names when there was no data?
(And not having an error of course)

I'm pretty certain this is only a technical matter, not a logical one.

schema table?
I'm not familiar with that, i don't think it is realted to fields from 
join-ed queries.


This question has not much to do with my earlier post, i know how to obtain 
the fields properly.





- Original Message - 
From: "Charlie Clark" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, July 24, 2005 4:26 PM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames




On 2005-07-24 at 16:11:26 [+0200], Edwin Knoppert <[EMAIL PROTECTED]>
wrote:

Why defending it?
It's prob. a simple issue.
Besides, why i want the names, why should a system need to parse a query?
You approach this way to 'static' imo (what you enter is your result).
You can easily say to also remove the fieldnames when we *have* data.


An 0-row containing the relational variables is a *special* case and
non-relational and causes problems for results which do not expect the
names of the values to be included in the result set.

.schema(table) gives you all you need.

Charlie

Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

Why defending it?
It's prob. a simple issue.
Besides, why i want the names, why should a system need to parse a query?
You approach this way to 'static' imo (what you enter is your result).
You can easily say to also remove the fieldnames when we *have* data.



- Original Message - 
From: "Puneet Kishor" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, July 24, 2005 3:40 PM
Subject: Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames




On Jul 24, 2005, at 6:52 AM, Edwin Knoppert wrote:

I would recommend the SQLite engine keeps returning the 0-row having 
the fields from the result query (select)

It does not seem to do that when there was no data returned.
At the end my goal would be to enumerate the fieldnames + it's 
tablename.

As far is i know, msaccess does this.



your question is very confusing, at least to me. It seems that you want 
to get the names of the table and the columns even when no rows are 
returned. But you already know the names of the table and the columns 
otherwise you wouldn't have been able to make the query in the first 
place!


MS-Access is a strange beast, and not the best of examples to use as a 
benchmark for SQLite.


MS-Access is designed for desktop use by end-users, and as such, it 
happily violates a fair number of standards all the while concentrating 
on making things easy for the end-user (on which, the jury is mixed).


SQLite is a server. Its focus would be on sticking to the standards as 
much as possible while delivering the results as fast as possible. Now, 
it is up to the user (not the end-user) to create a program based on 
SQLite that may or may not be as friendly as MS-Access to the end-user.


Would it be fair for me to say, "I still think it is a bug that 
MS-Access doesn't run on my Mac!"?


--
Puneet Kishor

[sqlite] I still think it's a bug, 0 rows, no fieldnames

[Edwin Knoppert]

I would recommend the SQLite engine keeps returning the 0-row having the fields 
from the result query (select)
It does not seem to do that when there was no data returned.
At the end my goal would be to enumerate the fieldnames + it's tablename.
As far is i know, msaccess does this.

Am i wrong?

Thanks!

Re: [sqlite] How to store ' ?

[Edwin Knoppert]

Gosh, sorry, just re-read the faq:
INSERT INTO xyz VALUES('5 O''clock');


- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, July 19, 2005 1:49 PM
Subject: [sqlite] How to store ' ?



If i read correct BLOB data can now handle chr(0) as well.
But another issue i solved with access is the embedding of ' characters.
I'm using single quotes for field data during INSERT.
How can a field contain single quotes for data?
Access allows to use BASIC code and let's you insert + Chr() stuff.

[sqlite] How to store ' ?

[Edwin Knoppert]

If i read correct BLOB data can now handle chr(0) as well.
But another issue i solved with access is the embedding of ' characters.
I'm using single quotes for field data during INSERT.
How can a field contain single quotes for data?
Access allows to use BASIC code and let's you insert + Chr() stuff.

Re: [sqlite] My first post, a few wishes..

[Edwin Knoppert]

I'm not using c and thus i do not compile the source into my app :)
So i'm calling sqlite3_get_table() to obtain data.
It's purely to instruct the sqlite 3 dll, think as if i'm where using VB6 
(without some 'odd' wrapper/helper dll's)

I'm actually using PowerBASIC - PB/WIN80.

I'm also having trouble obtaining the field types, but that will be another 
post.





- Original Message - 
From: <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Cc: "Kiel W." <[EMAIL PROTECTED]>
Sent: Thursday, July 07, 2005 3:35 PM
Subject: Re: [sqlite] My first post, a few wishes..



Edwin Knoppert <[EMAIL PROTECTED]> writes:


Sorry, i seem to have posted to another thread somehow..

No you misunderstood me, i'm well able to obtain the fields but it seems 
not

to have the fieldnames when no rows where inserted.


I think what you're looking for is PRAGMA TABLE_INFO(table_name)

SQLite version 3.1.3.1
Enter ".help" for instructions
sqlite> create table aaa(i integer, t text);
sqlite> .mode line
sqlite> pragma table_info(aaa);
  cid = 0
 name = i
 type = integer
  notnull = 0
dflt_value =
   pk = 0

  cid = 1
 name = t
 type = text
  notnull = 0
dflt_value =
   pk = 0
sqlite>

Re: [sqlite] My first post, a few wishes..

[Edwin Knoppert]

Sorry, i seem to have posted to another thread somehow..

No you misunderstood me, i'm well able to obtain the fields but it seems not 
to have the fieldnames when no rows where inserted.


?


- Original Message - 
From: "Kiel W." <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Thursday, July 07, 2005 2:52 PM
Subject: Re: [sqlite] My first post, a few wishes..


On 7/7/05, Edwin Knoppert <[EMAIL PROTECTED]> wrote:


1) I wish for a better way to recreate a table's definition.
The FAQ example is no good if you are unknown to it's fields.
For example, imy dbms obtains the create table query for the user, he 
might

want to reset table name as add or remove fields.
After that the copy as suggested in the FAQ does not know what fields to
use.


SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

Is this what you are after?  This is (9) of the FAQ.


See also #2

2) Empty table does not return fields.
After i create a table + fields, there seems to be no way to enumerate the
fieldnames if there isn't any data inserted.
Both cols and rows return 0.
I'm using sqlite3_get_table()
I heard before to make use of prepare but i can't figure out how.

3) May i suggest a better (easier) forum for sqlite?
I can setup a forum independant of my own sites.
No commercials etc..







--
Kiel W.
[EMAIL PROTECTED]
--

time is swift <<

[sqlite] My first post, a few wishes..

[Edwin Knoppert]

1) I wish for a better way to recreate a table's definition.
The FAQ example is no good if you are unknown to it's fields.
For example, imy dbms obtains the create table query for the user, he might 
want to reset table name as add or remove fields.
After that the copy as suggested in the FAQ does not know what fields to 
use.

See also #2

2) Empty table does not return fields.
After i create a table + fields, there seems to be no way to enumerate the 
fieldnames if there isn't any data inserted.

Both cols and rows return 0.
I'm using sqlite3_get_table()
I heard before to make use of prepare but i can't figure out how.

3) May i suggest a better (easier) forum for sqlite?
I can setup a forum independant of my own sites.
No commercials etc..