[sqlite] SQLite server using execnet ?

[Stef Mientki]

hello,

knowing that SQllite is not a client/server database,
still want to see if a simple client/server setup would solve my current 
problems for the moment
(because I love the simplicity of SQLlite,
and planned to go to a client / server database in the future)

Now I wonder if anyone has considered  to use Python execnet-module to realize 
a simple SLQlite
client / server application.

If I look at the documentation of execnet,
(and I realize that I'm a great optimist)
it would take between 20 and 50 lines of Python code.

thanks very much for your opinions.
cheers,
Stef Mientki


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

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 20:12, Nicolas Williams wrote:
> On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote:
>>  On 15-09-2010 11:36, Benoit Mortgat wrote:
>>> Are you sure that after altering your tables adding columns, natural
>>> join still only joins on vlid?
>>>
>> no, very stupid of me !!
>> I added a column to each of the tables, with the same name, they
>> should have the same value, but due to another programming error, the
>> values were different.  again, thank all very much !!
>>
>> That brings another issue to my mind, which I still don't understand:
>> "why can we so easily manipulate complex pages of text, by adding /
>> deleting / copying / pasting etc, and is manipulating of a few tables
>> in a database so difficult?"
> You shouldn't select * and you shouldn't natural joins precisely to
> avoid these sorts of surprises.  Select * is handy for manual queries
> from a shell; ditto natural joins.
>
> Of course, you can always change your schema in such a way as to break
> existing statements.  But the point here is to write statements such
> that they will be stable in the face of a schema that evolves in a
> backwards compatible way.  When you have to radically change your schema
> you know you have to update your code; when you're merely adding columns
> that don't contribute to primary keys then you shouldn't have to go
> change any statements other than the ones where you specifically need
> the new columns.  SQL shortcuts like '*' and natural join are just not
> compatible with such a schema evolution model.
>
thanks Nico,
I think these are very valuable tips.

cheers,
Stef
> Nico

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 11:36, Benoit Mortgat wrote:
> On Tue, Sep 14, 2010 at 23:41, Stef Mientki <stef.mien...@gmail.com> wrote:
>
>> until a few moments ago, this worked perfectly
>>
>> select Header from vraag
>>  natural join vraaglist
>>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> but now it returns an empty string (while the string shouldn't be empty).
>>
>> I test that by doing an explicit join
>>
>> select Header from vraag
>>  join vraaglist on vraag.vlid = vraaglist.vlid
>>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
> Are you sure that after altering your tables adding columns, natural join
> still only joins on vlid?
>
no, very stupid of me !!
I added a column to each of the tables, with the same name,
they should have the same value, but due to another programming error, the 
values were different.
again, thank all very much !!

That brings another issue to my mind, which I still don't understand:
"why can we so easily manipulate complex pages of text, by adding / deleting / 
copying / pasting etc,
and is manipulating of a few tables in a database so difficult?"

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 03:42, Pavel Ivanov wrote:
> Did you by any chance introduced some unique constraint or unique
> index on a set of columns one of which is primary key? AFAIK, there
> was a problem in SQLite until some recent versions in processing of
> redundant unique constraints in conjunction with natural joins.
thanks, that's a good idea,
I indeed, removed a unique constraint of 1 of the columns (and also renamed 
that column).
So I'll try to replicate the transformations and let you know.

cheers,
Stef
> But the best idea would be to post the full definition of those two
> tables along with their indexes and constraints.
>
> Pavel
>
> On Tue, Sep 14, 2010 at 6:09 PM, Stef Mientki <stef.mien...@gmail.com> wrote:
>>  On 14-09-2010 23:50, Oliver Peters wrote:
>>> your version of sqlite?
>> I used several programs
>> SQLiteSpy,
>> SQLcc,
>> Python-programs,
>> so I don't know which versions of sqlite I used,
>> might that be the problem ?
>>> your OS?
>> windows XP
>>> your backend?
>> several, see above
>>> What exactly have you done before the Natural Join stopped working?
>> converted tables from string to unicode,
>> column rename + columns added + changed column constraints ( through copy 
>> table / drop table /
>> rename table )
>>
>> thanks,
>> Stef
>>> Oliver
>>>
>>>
>>> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
>>>> hello,
>>>>
>>>> after modifying some tables, natural join stopped working  ???
>>>>
>>>> I've 2 tables, each with a field named "vlid", which is the primary key in 
>>>> one of the tables
>>>>
>>>> until a few moments ago, this worked perfectly
>>>>
>>>> select Header from vraag
>>>>   natural join vraaglist
>>>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>>>
>>>> but now it returns an empty string (while the string shouldn't be empty).
>>>>
>>>> I test that by doing an explicit join
>>>>
>>>> select Header from vraag
>>>>   join vraaglist on vraag.vlid = vraaglist.vlid
>>>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>>>
>>>> which gives me the correct string of the field Header.
>>>>
>>>> exchanging the 2 tables in the above statements, gives exactly the same 
>>>> results.
>>>>
>>>> Is my database corrupt ?
>>>>
>>>> thanks,
>>>> Stef Mientki
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 14-09-2010 23:50, Oliver Peters wrote:
> your version of sqlite?
I used several programs
SQLiteSpy,
SQLcc,
Python-programs,
so I don't know which versions of sqlite I used,
might that be the problem ?
> your OS?
windows XP
> your backend?
several, see above
> What exactly have you done before the Natural Join stopped working?
converted tables from string to unicode,
column rename + columns added + changed column constraints ( through copy table 
/ drop table /
rename table )

thanks,
Stef
> Oliver
>
>
> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
>> hello,
>>
>> after modifying some tables, natural join stopped working  ???
>>
>> I've 2 tables, each with a field named "vlid", which is the primary key in 
>> one of the tables
>>
>> until a few moments ago, this worked perfectly
>>
>> select Header from vraag
>>   natural join vraaglist
>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> but now it returns an empty string (while the string shouldn't be empty).
>>
>> I test that by doing an explicit join
>>
>> select Header from vraag
>>   join vraaglist on vraag.vlid = vraaglist.vlid
>>   where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> which gives me the correct string of the field Header.
>>
>> exchanging the 2 tables in the above statements, gives exactly the same 
>> results.
>>
>> Is my database corrupt ?
>>
>> thanks,
>> Stef Mientki
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 hello,

after modifying some tables, natural join stopped working  ???

I've 2 tables, each with a field named "vlid", which is the primary key in one 
of the tables

until a few moments ago, this worked perfectly

select Header from vraag
  natural join vraaglist
  where Nr = 0 and vraaglist.Name = 'eortc_br23'

but now it returns an empty string (while the string shouldn't be empty).

I test that by doing an explicit join

select Header from vraag
  join vraaglist on vraag.vlid = vraaglist.vlid
  where Nr = 0 and vraaglist.Name = 'eortc_br23'

which gives me the correct string of the field Header.

exchanging the 2 tables in the above statements, gives exactly the same results.

Is my database corrupt ?

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

Re: [sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?

[Stef Mientki]

 thanks Ralf,

nice db-browser,
and conversion seems to work.

cheers,
Stef

On 13-09-2010 11:03, Ralf Junker wrote:
> On 11.09.2010 20:42, Stef Mientki wrote:
>
>> is there a program that converts sqlite database from windows-1252 to utf-8?
> Yes, SQLiteSpy does this:
>
> * Open database file
> * Menu Execute -> Text to Unicode Conversion.
> * Select tables and columns to convert.
> * Choose database codepage.
> * Press OK to start the conversion.
>
> After conversion, browse the database contents to verify the result. As
> SQLiteSpy supports Unicode text display and entry, you can do it right
> there.
>
> If you like what you see, execute "COMMIT" to store the conversion
> changes. "ROLLBACK" in case of conversion errors and try again with a
> different codepage.
>
> Download: http://yunqa.de/delphi/doku.php/products/sqlitespy/index
>
> Ralf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?

[Stef Mientki]

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

Re: [sqlite] Create a GUI for interacting with a test questions sqlite db

[Stef Mientki]

 On 05-09-2010 10:47, Arthur Avramiea wrote:
> I would like to use sqlite to create a question db for test generation. It
> will be organized in a couple of questions cathegories. A random function
> will generate a test by selecting a couple of questions from each section.
> The sqlite db will be encrypted with http://www.zetetic.net/code/sqlcipher
> so that the users of the application without the proper credentials will not
> be able to see or modify them. 
>
> I would like to create a graphical interface which will allow me (after
> using the proper pass), to add or modify questions, that will allow the
> generation of the tests themselves,of the formatted test pages ready to be
> printed, and of a page with the answers for correction. I do not want to use
> a web interface for that, to make it more portable. It will run on Windows
> XP/Vista. 
>
> Is C++ a proper language for creating this interface? If not, which
> programming language do you recommend for the task? Which libraries should I
> use? Thank you very much. 
take a look at web2py
http://www.web2py.com/

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

[sqlite] is there a way to extend the field information ?

[Stef Mientki]

hello,

I want to extend the field information of a field in a table,
e.g. I want to add a "description" to each field.

The only way that came up in my head was the following:

- create a "special" table, containing the additional field information:

create table _Field_Ex (
  Tabel   Text  Not Null,
  Field   Text  Not Null,
  Description Text )

- for visualizing the total information, the information of the pragma
table info

Pragma table_info (  )

- and the records from the table _Field_Ex

select * from _Field_Ex
  where Tabel = 

- as I can't think of a way to combine these result in SQL
(e.g. the pragma statement doesn't generate a header, the select does)
I combine the results in python.

Now I wonder if this combine can also be done in pure SQL.

And are there other (better) ways to extend the metadata of fields ?

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

Re: [sqlite] feature request - field exclusion list in select list

[Stef Mientki]

Stephan Wehner wrote:
> On Sun, Sep 27, 2009 at 5:19 PM, Darren Duncan  
> wrote:
>   
>> Following my previous message to the sqlite-users list, I've done a bit more
>> research and decided to escalate my reply to a formal feature request.
>>
>> I was initially going to file a ticket, but it seems that non-registered 
>> SQLite
>> developers can't do that anymore, and we're supposed to do it on sqlite-users
>> where a non-registered developer would then distill list chatter to real
>> tickets.  So here I go ...
>>
>> -
>>
>> I propose that SQLite (and other DBMSs, and the SQL standard) add support for
>> explicitly naming a list of result fields that they do *not* want, intended 
>> for
>> use in situations where users do want most of the fields from a source but 
>> don't
>> want a few.  It would make for much more robust code if users can explicitly
>> encode their intentions, meaning say that they want all fields except field 
>> foo,
>> and have them get exactly that (with the expectation that if non-foo fields 
>> are
>> added or removed to the source, they get or don't get those automatically, 
>> same
>> as if they did "SELECT *").  I have seen evidence from both first hand
>> experience and from many other developers, that this feature would be very
>> helpful to them.  And arguably it shouldn't be too terribly complicated for a
>> DBMS to implement.
>> 
>
> Its a nice idea, but I feel if you use some other interface to SQLite you can
> get the same effect.
>
> On the one hand, I think if you are writing a script/program to fetch
> the results, then your
> script can assemble the effective columns in the SELECT list (all
> columns minus the ones
> you want to leave out). Meaning you can write your own C function/Perl
> method or what-have-you to
> get that list, given the database connection, table name and the
> columns-to-leave-out.
>   
I'm not an expert in databases, but as possibly my question triggered 
this feature request,
I can give you the view from a simple user.

Indeed I can get the same functionality from the Python script I'm using.
But then I'm beginning to wonder, why I need a database at all ;-)

To give another example, a couple of weeks ago I needed to convert one 
column into a number of columns ( I think it's called pivotting ?),
I got a perfect answer from this group,
but when I implemented it in the real situation I got an error, not more 
than 64 tables in a query allowed.
So I finaly wrote it myself.
Doing a full outer join, is ( in my opinion) more than 50 % redundant 
information,
so I'll probably write a wrapper.

These extension works good in a script, but makes the script less readable.
> On the other hand I am not sure, but you seem to be interested in
> having this feature
> so that you get the functionality at the sqlite3 prompt. In which case
> I think you can
> use a script that has the columns you're interested in, and save
> typing that way (type script once,
> load many times / type script once, load, change script, load etc.)
>
> I feel there is great value in not changing software.
>   
Even if the software stays fully backwards compatible ?
Now I'm beginning to understand, why (please forgive me to have a 
judgement as a totally non database expert) such an ugly definition as 
SQL is still thé database language ;-)

cheers,
Stef

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

Re: [sqlite] SELECT ALL BUT syntax (was Re: less typing possible ?)

[Stef Mientki]

thanks Darren,

Darren Duncan wrote:
> Stef Mientki wrote:
>   
>> hello,
>>
>> I often want to see most of the columns of a table / view / query, but a 
>> few I don't want to see.
>> So I now create a huge list of fields,
>> but isn't there a more typo-frindly way, like :
>>
>> select * - field33 from table
>> 
>
> A syntax option introduced in Date and Darwen's Tutorial D language, an 
> analogy 
> of which I've included in my Muldis D language, is the ALL BUT modifier.
>
>   

> Adapted into SQL with that same spelling, if you wanted all the fields except 
> for "field33", it might be spelled like:
>
>SELECT ALL BUT field33 FROM table
>
> Or, since SQL already tends to use ALL as an implicit alternative to DISTINCT 
> to 
> mean "include duplicates", we could use the * instead:
>
>SELECT * BUT field33 FROM table
>
> Or if you don't like how that looks, maybe EXCEPT:
>
>SELECT * EXCEPT field33 FROM table
>
> This could be generalized so you could have any field list on the left of the 
> BUT/EXCEPT, so then you have the full flexibility of what you have now; eg:
>
>SELECT foo.*, bar.field20 EXCEPT foo.field5 FROM foo INNER JOIN bar USING 
> (id)
>
> Its nonstandard (unless the SQL standard has a feature like this which I'm 
> not 
> aware of), but I think very useful.
>
> For example, often users want to get all the result fields except for the 
> artificial fields just used to join the tables.
>   
Exact that's one of the major purposes.
I saw you even made a feature request, excellent.
For the moment, I'll make a Python wrapper with the same functionality.

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

[sqlite] less typing possible ?

[Stef Mientki]

hello,

I often want to see most of the columns of a table / view / query, but a 
few I don't want to see.
So I now create a huge list of fields,
but isn't there a more typo-frindly way, like :

select * - field33 from table

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

Re: [sqlite] full outer join questions

[Stef Mientki]

Pavel Ivanov wrote:
>> select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ...
>> 
>
> I find function ifnull() more readable in such cases. ;-)
>
>   
thanks guys,
both (of course ;-) works perfectly.
I have to study the functions in SQLite some more !!

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

Re: [sqlite] full outer join questions

[Stef Mientki]

thanks Pavel,

and sorry for mixing the wikipedia example with the real situation.

create table RT1 ( PID integer, V1 text );
insert into RT1 values ( '684', 'aap' );
insert into RT1 values ( '685', 'other empty' );
create table RT2 ( PID integer, V2 text );
insert into RT2 values ( '684', 'beer' );
insert into RT2 values ( '686', 'other empty' );
select RT1.*, RT2.*
  from   RT1
left join RT2  on RT1.PID = RT2.PID
  union
select RT1.*, RT2.*
  from  RT2
left join RT1  on RT1.PID = RT2.PID
where  RT1.PID IS NULL;

ok I tried in sqlite3 command line ( terrible job ;-)
and  as you said, both "union" and "union all" works correct (only a 
different order).
And I also can add the "order by" clause without affecting the result 
(except ordening).

So the problem is in the IDE  I use.
I tried another IDE which worked also correct.

Now I still have one question:
The result of the above query is:

PID   V1PID  V2
686 from RT2
684 from RT1684 from RT2
685 from RT1

Now I want to combine the columns PID, so the result would look like

PID   V1 V2
686from RT2
684 from RT1   from RT2
685 from RT1

Is that possible with SQL ?

cheers,
Stef




So let's try again:

This following code in the sqlite command line utility:

create table RT1 ( PID integer, V1 text );
insert into RT1 values ( '684', 'aap' );
insert into RT1 values ( '685', 'other empty' );
create table RT2 ( PID integer, V2 text );
insert into RT2 values ( '684', 'beer' );
insert into RT2 values ( '686', 'other empty' );
select RT1.*, RT2.*
  from   RT1
left join RT2  on RT1.PID = RT2.PID
  union
select RT1.*, RT2.*
  from  RT2
left join RT1  on RT1.PID = RT2.PID
where  RT1.PID IS NULL;

gives the following output ( I hope I don't make typo's because I don't 
know how to copy from the command line window)

684|from RT1|684|from RT2
685|from RT1||
||684|from RT2

which is as (I) expected.

If  "union all" is replaced by




Pavel Ivanov wrote:
>> Now the strange thing is that this query returns the correct number of rows,
>> but all the columns from the employee-table are empty.
>> 
>
> There's no "employee-table" in your query.
>
>   
>> If I change "UNION" to "UNION ALL" the join works as expected.
>> Is there an explanation for this behavior ?
>> 
>
> As we don't see your query we can't explain what's going on there.
>
>   
>> Also when I add an order clause, the left columns are all made empty ?
>> 
>
> Justing adding ORDER BY changes all "left" (from what?) columns in all
> rows to NULL? No way! Show the actual output from sqlite3 command line
> utility please.
>
>   
>> Is it uberhaupt possible to order the resulting table on the column PID
>> and preferable get just 1 PID column ?
>> 
>
> Sure, why not? But again no query - no advice on how to correct it to
> achieve what you want.
>
> Pavel
>
> On Thu, Sep 17, 2009 at 6:39 PM, Stef Mientki <s.mien...@ru.nl> wrote:
>   
>> hello,
>>
>> I'm trying to join 2 tables,
>> so I guess I need to perform a full outer join.
>>
>> On wikipedia, I found this solution for sqlite3:
>> http://en.wikipedia.org/wiki/Join_%28SQL%29
>>
>> select *
>>  from RT0
>>left join RT1 on RT1.PID = RT0.PID
>>  union
>>select RT0.*, RT1.*
>>  from RT1
>>left join RT0 on RT1.PID = RT0.PID
>>where RT0.PID IS NULL
>>
>> Now the strange thing is that this query returns the correct number of rows,
>> but all the columns from the employee-table are empty.
>>
>> If I change "UNION" to "UNION ALL" the join works as expected.
>> Is there an explanation for this behavior ?
>>
>> Also when I add an order clause, the left columns are all made empty ?
>>
>> Is it uberhaupt possible to order the resulting table on the column PID
>> and preferable get just 1 PID column ?
>>
>> thanks,
>> Stef
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] full outer join questions

[Stef Mientki]

hello,

I'm trying to join 2 tables,
so I guess I need to perform a full outer join.

On wikipedia, I found this solution for sqlite3:
http://en.wikipedia.org/wiki/Join_%28SQL%29

select *
  from RT0
left join RT1 on RT1.PID = RT0.PID
  union
select RT0.*, RT1.*
  from RT1
left join RT0 on RT1.PID = RT0.PID
where RT0.PID IS NULL

Now the strange thing is that this query returns the correct number of rows,
but all the columns from the employee-table are empty.

If I change "UNION" to "UNION ALL" the join works as expected.
Is there an explanation for this behavior ?

Also when I add an order clause, the left columns are all made empty ?

Is it uberhaupt possible to order the resulting table on the column PID 
and preferable get just 1 PID column ?

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

Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Stef Mientki]

the answer of Massimo (the web2py author):


I apologize and I conclude my statement is not correct.

I have experienced this problem once (out of disk space and sqlite
database corruption) but I made the mistake of confusing correlation
with causality. Except in that occasion I never had any problem with
sqlite and I do use it on production. On my web site (web2py.com) I
only run sqlite.

Massimo



Now I'm happy again ;-)
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Stef Mientki]

D. Richard Hipp wrote:
> On Sep 9, 2009, at 5:35 PM, Stef Mientki wrote:
>   
>> 
>> The worse thing about sqlite is that if the file grows and you run out
>> of memory, the file gets corrupted and you lose data.
>> 
>> 
>
> The web2py author seems to be misinformed.  Perhaps you could direct  
> him to the description of SQLite's Anomaly and OOM testing at 
> http://www.sqlite.org/testing.html 
>   and if that explanation does not suffice, to the description of why  
> SQLite is proof against such database corruption in 
> http://www.sqlite.org/atomiccommit.html
>
>
>   
done, keep you informed.

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

[sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Stef Mientki]

The worse thing about sqlite is that if the file grows and you run out
of memory, the file gets corrupted and you lose data.


cheers,
Stef

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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Shaun Seckman (Firaxis) wrote:
> It seems like the more ideal solution would be to create a third table for 
> ownership that contains a person ID and a vehicle ID.
thanks Shaun,
but that's what I (i'm not a database expert) call the Bind table.
I read some articles about normalization,
and I think I got with this database somewhere to the 5-order?

cheers,
Stef


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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Simon Slavin wrote:
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>   
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build- 
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>> 
>
> Well you cannot have one vehicle owned by more than one person.  So  
> you do not need your 'bind' table at all.  You just need to add a  
> column to the vehicle table which contains the person_ID.
>
>   
Sorry Simon,
I expressed myself not well enough:
1 type of car can (and will) be owned by many persons.
But that always the problem with simplifications.
The real problem is about psychological / medical tests and questionnaires,
where a patient for each test scores on a number of scales.
As all scales are of the same type of data, they are all put in one table.


>> I know that every person has 1 car and 1 bike
>> 
>
> Do you mean exactly one car ?  Or up to but no more than one car ?  Or  
> at least one car ?
>
>   
so yes exactly one car, or in reality 1 score on every scale.
>> I want to show up a table, with 3 columns:
>>  persons.name  vehicle.brand[car]
>> vehicle.brand[bike]
>>
>>
>> I can get the result in the following way:
>>
>> 1. create view Cars: persons join vehicle, where car_or_bike = car
>> 2. create view Bikes: persons join vehicle, where car_or_bike = bike
>> (and change the relevant colum names)
>> 3. join the above view 1 and 2
>>
>> The first problem is that the above approach is quiet slow.
>> Both starting tables (persons and vehicles are also views),
>> and maybe it would be faster to create (temporary) tables ?
>>
>> The second problem is that in the real situation
>> I'm not converting 1 column  to 2 columns,
>> but it can be as large as 1 column to 10 columns.
>>
>> btw I'm running SQLite from Python, so maybe I should do some data
>> handling outside SQL (in Python) ?
>> 
>
> Yes, you are trying to get SQL to do your programming work for you.   
> Just do two SELECT commands in Python: one to find the car for the  
> person, and another to find the bike for the person.  No need to  
> create any temporary tables, create any views, do any joins, or keep a  
> huge table with all the results in.  Much smaller and faster.
>   
Yes but that's not the goal,
the goal is to create a very easy environment,
where even a doctor or nurse can ask any question to the system.

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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Igor Tandetnik wrote:
> Stef Mientki <s.mien...@ru.nl> wrote:
>   
>> I'm just an amateur  struggling with databases,
>> so forgive if  I ask  a question every database user should know.
>>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type,
>> build-year, etc. The 2 tables are bounded by bind table: person_ID,
>> vehicle_ID
>>
>> I know that every person has 1 car and 1 bike
>>
>> I want to show up a table, with 3 columns:
>>  persons.name  vehicle.brand[car]
>> vehicle.brand[bike]
>> 
>
> select p.name, v1.brand, v2.brand
> from persons p join bind b1 on (p.person_ID = b1.person_ID)
> join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and 
> v1.car_or_bike = 'car')
> join bind b2 on (p.person_ID = b2.person_ID)
> join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and 
> v2.car_or_bike = 'bike');
>
> Igor Tandetnik 
>
>   
thanks Igor,

That works very good and fast, and still very readable code.
I just tried it with 1 to 5 columns on the same database
and it's much faster then mine 1 to 2 columns.
I tried some code quiet similar to yours, but got errors,
now I see an essental part is to use aliases for the tables used more 
than once.

thanks!

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

[sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

hello,

I'm just an amateur  struggling with databases,
so forgive if  I ask  a question every database user should know.

The situation is like this (simplified):

I've a table with persons: name, address, phone, etc.
I've another table with vehicles: car_or_bike, brand, type, build-year, etc.
The 2 tables are bounded by bind table: person_ID, vehicle_ID

I know that every person has 1 car and 1 bike

I want to show up a table, with 3 columns:
  persons.name  vehicle.brand[car]
vehicle.brand[bike]


I can get the result in the following way:

1. create view Cars: persons join vehicle, where car_or_bike = car
2. create view Bikes: persons join vehicle, where car_or_bike = bike 
(and change the relevant colum names)
3. join the above view 1 and 2

The first problem is that the above approach is quiet slow.
Both starting tables (persons and vehicles are also views),
and maybe it would be faster to create (temporary) tables ?

The second problem is that in the real situation
I'm not converting 1 column  to 2 columns,
but it can be as large as 1 column to 10 columns.

btw I'm running SQLite from Python, so maybe I should do some data 
handling outside SQL (in Python) ?

Any suggestions to make it faster and /or more structural will be 
greatly appreciated.

thanks,
Stef Mientki


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

Re: [sqlite] Is there an easy way to concatenate 2 views ?

[Stef Mientki]

thanks Woody ( Harold ?),

that works perfect.

cheers,
Stef

Harold Wood & Meyuni Gani wrote:
> Concantenate or join?
>
> To concantenate use the
> Select * from a
> Union all
> Select * from b
>
> Only use * if columns all columns in a are identical to all columns in b, if 
> not then build a column list and use that indtead of *
>
> Woody
>
> -Original Message-
> From: Stef Mientki <s.mien...@ru.nl>
> Sent: Saturday, July 25, 2009 3:19 PM
> To: SQLite List <sqlite-users@sqlite.org>
> Subject: [sqlite] Is there an easy way to concatenate 2 views ?
>
> hello,
>
> I've 2 views ( or maybe tables) with same columns,
> is there an easy way to concatenate those 2 views to 1 new view ?
>
> thanks,
> Stef Mientki
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there an easy way to concatenate 2 views ?

[Stef Mientki]

hello,

I've 2 views ( or maybe tables) with same columns,
is there an easy way to concatenate those 2 views to 1 new view ?

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

[sqlite] Use the result of a query, as a variable name in another query ?

[Stef Mientki]

hello,

to test complex queries, I want to use the command line utility ( or an 
equivalent that remembers what I type).

Now I've a table which contains the names of a set of other tables.
In Python I can easily create the SQL string (containing the field name) 
and commit it to the database.

Is there a way realize this in the command line ?
So I need to get the result of a query into a variable and then use that 
variable in a new query.

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

[sqlite] one or many tables, what gives the best performance ?

[Stef Mientki]

Hello,

Sorry I'm not very familiar with databases, so I probably don't use the 
right terms.

I wonder what's the best approach in my specific case:

I have a table "A",
in this table "A", each row is connected to about 50 rows of other 
information.

These 50 rows of other information have an identical structure for all 
rows in table "A".

The  probability that one of these 50 rows is identical to another row 
in other sets of 50 rows is approximately zero.

So I have 2 choices:
1. create 1 table with all the sets of 50 rows, by adding a reference in 
each row to the corresponding line in table "A"
2. create a separate table for each set of 50 rows
(3. I could flatten the data, so I only will have a huge table "A", but 
that doesn't sound very efficient to me)

Maybe there's one other issue, that might influence the choice,
and that's how I use the data:
when I extract data from this database,
- I search for a row in table "A"
- I read all the 50 extra rows of other information

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

[sqlite] database is slowing down, caused by Sophos

[Stef Mientki]

hello,

No question, just some information that might be useful to someone.

We have a number of small SQLite databases,
which are located on a network drive.

Users are complaining the last months that these databases becomes 
slower every week.
After investigating the case, we found that Sophos anti-virus software 
is causing this problem.
The standard setting of Sophos is to scan on each file read,
so every simple query to the database loads the whole database file to 
the local system for virus scanning.
The data transport when Sophos is on is about 300 times larger than in 
normal use.

After disabling Sophos for the SQLite database files, the system runs 
like a speer.

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

Re: [sqlite] Delete, sometimes doesn't seem to work ...

[Stef Mientki]

thanks Simon and Igor

Simon wrote:
> Difficult to say for sure, but it's possible the Indx of 0 were
> inserted with another type (ie, the string "0" and of course, 0 !=
> "0")
>   
I guess that might have happened.
Igor,
I can't try it anymore, because I already deleted the records,
I'll try to rmemeber that for the next time.

cheers,
Stef
> Simon
>
> On Sat, Feb 7, 2009 at 4:28 PM, Stef Mientki <s.mien...@ru.nl> wrote:
>   
>> hello,
>>
>> I'm a very happy user of sqlite for about 2 years.
>> And as I'm happy for a  long time,
>> I forgot all tiny details and funny things.
>>
>> Besides that, I changed from Delphi to Python,
>> and now I'm in trouble :-(
>> So I might have version problems,
>> but that's not the case here,
>> as I've the same problem executing the statement below from within my
>> Delphi application.
>>
>> The following statement doesn't seem to work (rows are not removed)
>> DELETE FROM [_1_aap] WHERE Indx=0
>>
>> But selecting another value than zero, does work (rows are indeed removed)
>> DELETE FROM [_1_aap] WHERE Indx=1
>>
>> Indx is declared as an integer field.
>> In Delphi I'm using SQLite 3.3.8
>>
>> One other point, those rows with Indx=0 were inserted by a wrong
>> statement (can't remember exactly what)
>>
>> Anyone has a clue ?
>>
>> btw. for the moment I solved the problem by recreating the whole
>> database again.
>>
>> thanks,
>> Stef Mientki
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Delete, sometimes doesn't seem to work ...

[Stef Mientki]

hello,

I'm a very happy user of sqlite for about 2 years.
And as I'm happy for a  long time,
I forgot all tiny details and funny things.

Besides that, I changed from Delphi to Python,
and now I'm in trouble :-(
So I might have version problems,
but that's not the case here,
as I've the same problem executing the statement below from within my 
Delphi application.

The following statement doesn't seem to work (rows are not removed)
DELETE FROM [_1_aap] WHERE Indx=0

But selecting another value than zero, does work (rows are indeed removed)
DELETE FROM [_1_aap] WHERE Indx=1

Indx is declared as an integer field.
In Delphi I'm using SQLite 3.3.8

One other point, those rows with Indx=0 were inserted by a wrong 
statement (can't remember exactly what)

Anyone has a clue ?

btw. for the moment I solved the problem by recreating the whole 
database again.

thanks,
Stef Mientki

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

Re: [sqlite] Recommended wrapper for Python?

[Stef Mientki]

Gilles Ganault wrote:

Hello

I browsed through the archives at Gname, but most threads regarding 
wrappers for Python date back to 2005.


There are several wrappers listed in the wiki 
(http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers), so I'd like 
some feedback about which you would recommend to use SQLite from 
Python (2.4).

I've no experience whatsover,
but if I see the list, the top one is the best choice,
because it'll be integrated in the standard Python.

cheers,
Stef


Thank you
GG.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629



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

[sqlite] Found the problem: Re: [sqlite] Is this a valid syntax

[Stef Mientki]

I found the problem and have a workaround now.

The problem is caused by a selection field,
which is a text field, that might be null.
This field can either be
  - null
  - empty string (don't know if this is different from null)
  -'0'
  - 0   (don't know if this is different from the string '0')


I'ld consider this as a bug,
but I'm not sure who's to blame ;-),
  SQLite
 or
 the Delphi wrapper
or
 my own program

I'll study the datatype page again: http://www.sqlite.org/datatype3.html

Thank you all for your suggestions, which finally solved my problem,
cheers,
Stef Mientki

Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629



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

Re: [sqlite] Is this a valid syntax

[Stef Mientki]

Dennis Cote wrote:

Stef Mientki wrote:



But it doesn't solve my problem :-(
I've the feeling that despite the suggestions of Igor,
the problem still exists, caused by the zero values ??
I'll try tomorrow again with some other values.


Stef,

Oh... I though Igor had solved your problem so I didn't give it much 
thought.


My reading of your query would suggest that you should try this:

SELECT PO.* FROM Koppel
LEFT JOIN PO ON (Koppel.K_App == PO.App)
WHERE (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0')

The last two conditions should select the result rows and not affect 
the join operation. Only the comparisons of the fields from both 
tables should affect the join.

Thanks Dennis,
but I tried that already :-(
Tomorrow is a new day, with new possibilities.
cheers,
Stef


HTH
Dennis Cote



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







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

Re: [sqlite] Is this a valid syntax

[Stef Mientki]

Dennis Cote wrote:

Stef Mientki wrote:




I don't know if this is the problem, but, for some reason you're 
mixing C/C++ syntax in with SQL there.


You don't use '==', you should just use '='
You don't use '!=', you should use '<>'


thanks Paul,

but although  I can never find this information when I need it :-(
AFAIK, both notations are allowed.
Besides that I tried both and it doesn't change the situation.


hi Dennis,

Paul and Stef,

Being a C programmer, Richard extended SQLite to allow C syntax for 
equality and inequality comparisons as shown at 
http://www.sqlite.org/lang_expr.html

Aha, that was the page I was looking for !!
even though it is non standard. If you want your SQL code to be 
portable to other database engines you should use the standard syntax 
that Paul suggested.

I agree, thanks.

But it doesn't solve my problem :-(
I've the feeling that despite the suggestions of Igor,
the problem still exists, caused by the zero values ??
I'll try tomorrow again with some other values.

cheers,
Stef


HTH
Dennis Cote

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







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

Re: [sqlite] Re: Is this a valid syntax

[Stef Mientki]

Igor Tandetnik wrote:

Stef Mientki <[EMAIL PROTECTED]> wrote:

I use the following syntax, and I get 7 records back,
(which is not correct in my opinion)

SELECT PO.* FROM Koppel
 LEFT JOIN PO
   WHERE (Koppel.K_App == PO.App)
 AND (Koppel.K_naam == 'MVE')
 AND (PO.ALL_answered == '0')

If I leave the last line out,
I get 16 records (which might be ok, I can't check it)


Left join would produce records with all NULLs in the PO half where no 
record in PO matches that in Koppel.

Thanks Igor,
I think you hit the nail on it's head.
Because the tables were quit large,
I imported some of tables just partially.
I'll check tomorrow.
The test of (PO.ALL_answered == '0') then filters out those records 
where PO.ALL_answered is NULL. Make it


SELECT PO.* FROM Koppel
 LEFT JOIN PO
   ON (Koppel.K_App == PO.App)
 AND (Koppel.K_naam == 'MVE')
 AND (PO.ALL_answered == '0')


Now if I only invert the last line,
and suposing the above results where ok (which isn't),
I should get 16-7= 9 records back.


When PO.ALL_answered is NULL, both (PO.ALL_answered == '0') and 
(PO.ALL_answered != '0') are false.



But this just returns 0 records 


Apparently, in all rows PO.ALL_answered is either '0' or NULL.

That's indeed the case , all '0' ;-)

cheers,
Stef Mientki


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

Re: [sqlite] Is this a valid syntax

[Stef Mientki]

I don't know if this is the problem, but, for some reason you're 
mixing C/C++ syntax in with SQL there.


You don't use '==', you should just use '='
You don't use '!=', you should use '<>'


thanks Paul,

but although  I can never find this information when I need it :-(
AFAIK, both notations are allowed.
Besides that I tried both and it doesn't change the situation.

cheers,
Stef

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

[sqlite] Is this a valid syntax

[Stef Mientki]

I don't understand this behaviour,
is this too complex ?
or am I doing something wrong ?

I use the following syntax, and I get 7 records back,
(which is not correct in my opinion)

SELECT PO.* FROM Koppel
 LEFT JOIN PO
   WHERE (Koppel.K_App == PO.App)
 AND (Koppel.K_naam == 'MVE')
 AND (PO.ALL_answered == '0')

If I leave the last line out,
I get 16 records (which might be ok, I can't check it)

SELECT PO.* FROM Koppel
 LEFT JOIN PO
   WHERE (Koppel.K_App == PO.App)
 AND (Koppel.K_naam == 'MVE')


Now if I only invert the last line,
and suposing the above results where ok (which isn't),
I should get 16-7= 9 records back.

SELECT PO.* FROM Koppel
 LEFT JOIN PO
   WHERE (Koppel.K_App == PO.App)
 AND (Koppel.K_naam == 'MVE')
 AND (PO.ALL_answered != '0')

But this just returns 0 records 

I tried all kinds of combinations, nested select statements etc,
but most of them (may be all, can't remember anymore),
returns 16-0-7 records.

Sorry, I must be doing something terrible wrong, but I don't see the clue.

thanks,
Stef Mientki


Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629



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

Re: [sqlite] trim available ??

[Stef Mientki]

Dennis Cote wrote:

Stef Mientki wrote:



I don't know which version I'm running, how can I detect that ?


Stef,

You can call sqlite3_libversion (see 
http://www.sqlite.org/capi3ref.html#sqlite3_libversion for details).



Thanks Dennis,
Yes, I see now, I've 3.3.8 ;-)
cheers,
Stef Mientki

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

Re: [sqlite] trim available ??

[Stef Mientki]

Liam Healy wrote:

An option for versions that do not have trim is
http://sqlite.org/contrib//download/extension-functions.tgz?get=17

thanks Liam,
but I think it's better to recreate the database, with trimmed itesm.
cheers,
Stef

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

Re: [sqlite] trim available ??

[Stef Mientki]

The TRIM function was added on 2007-03-17 17:52:42.
  

Thanks,
that explains ..
cheers,
Stef

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


  


Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629



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

Re: [sqlite] trim available ??

[Stef Mientki]

Mohd Radzi Ibrahim wrote:

Hi,
I'm running 3.3.15. No problem at all. Maybe your version does not 
have this implemented yet...



I don't know which version I'm running, how can I detect that ?
I use the SQLITE3.DLL dated 8 october 2006, but windows can't find the 
version info in that file.


cheers,
Stef


Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629



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

[sqlite] trim available ??

[Stef Mientki]

hello,

As I read here
 http://www.sqlite.org/lang_expr.html
there are trim functions availabel as part of an expression.


But when I try to execute the next statement:
 SELECT App FROM PO WHERE LOWER(App_Type) = TRIM(LOWER('{ 
Afzuigapparaat } '))


I get the following error
/ ERROR **
Error executing SQL
"SELECT App FROM PO WHERE LOWER(App_Type) = TRIM(LOWER('{ 
Afzuigapparaat } '))

"
:: no such function: TRIM
**/

What am I doing wrong ?

thanks,
Stef Mientki

Kamer van Koophandel - handelsregister 41055629  / Netherlands Chamber of 
Commerce - trade register 41055629



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

Re: [sqlite] ANN: SQLcc another open source Database manager

[Stef Mientki]

Jim Dodgen wrote:

Server not found

   
I get

"Firefox can't find the server at oase.uci.kun.nl."

when I click on:
http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html

very weird,
it's a server directly connected to the European backbone,
so please try again.

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] ANN: SQLcc another open source Database manager

[Stef Mientki]

Although there are already a lot of database manager,
I couldn't find a free (preferable open source) one, that fitted my needs.

So I wrote another one with a lot of help of this list (Thank you all !!),
and I'm returning the result now as an open sources alternative.

Compared to others it might have some rather distinctive characteristics.
- open source (BSD license) written in Delphi
- actions can be performed with less than half the clicks, compared to 
the most user friendly db-manager

- plain view of combination of database, SQL-query and SQL-query result
- can handle automatically most common image types, including PNG and 
animated GIF

- visual query design (much easier than M$ Access)
- all SQL statements caused by clicking, drag can be visualized, 
resulting in 3 learning levels

- logging of all commands per database
- user templates
- local storage of the settings of each database

An overview of the possibilities can be viewed here:
 http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html

A windows install program, with execuatble, help, demo-database and 
sources can be found here (2MB)

 http://oase.uci.ru.nl/~mientki/download/SQLcc_v1_0.exe

have fun !

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Re: to quote or not ?

[Stef Mientki]

Thanks very much Igor,
that explains it,
cheers,
Stef Mientki

Igor Tandetnik wrote:

Stef Mientki <[EMAIL PROTECTED]> wrote:

So I would expect that I now can always double quote the selection
fields, but unfortunately that doesn't seem to be true.

From a graphical design, I get for instance:

SELECT "Opnamen.PatNr", "Opnamen.Datum"


Opnamen, PatNr and Datum are three separate identifiers, and have to 
be quoted separatedly (if at all), as in "Opnamen"."PatNr". 
"Opnamen.PatNr" is a single identifier, distinct from Opnamen.PatNr 
(which is two identifiers separated by period).


To illustrate, consider these valid SQL statements:

create table Opnamen (PatNr, "Opnamen.PatNr");
insert into Opnamen values (1, 2);
select
   Opnamen.PatNr, "Opnamen"."PatNr",
   "Opnamen.PatNr", Opnamen."Opnamen.PatNr"
from Opnamen;

The last query should return a single row with values (1, 1, 2, 2)

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







KvK: 41055629



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

[sqlite] to quote or not ?

[Stef Mientki]

I've a problem with finding the correct algorithm for quoting.

When I've a view in the database, where 2 tables have the same field 
names "PatNr",

doing a pragma to get the fields of the view, I get:
 PatNr
 PatNr:1
For quering the data from that view, I don't ask for the view, but 
generate my own normal select

(that is because I want to reorder the fields, enable/disable fields etc)
and in that case the PatNr:1 is causing a problem.
So I thought I solved that, by double-quoting PatNr:1,
and indeed it works.

So I would expect that I now can always double quote the selection fields,
but unfortunately that doesn't seem to be true.

From a graphical design, I get for instance:

SELECT "Opnamen.PatNr", "Opnamen.Datum"
 FROM Patient
 LEFT JOIN Opnamen
   ON Opnamen.PatNr = Patient.PatNr

which doesn't work,
removing the double quotes here does work.

So I can think of the following solutions:
1. never double quote selection fields, unless there's a ':' or a space 
in the name

2. always double quote selection fields, unless there's a '.' in it

Which one is the most general, or are there even better ways ?

Thanks,

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Re: Meta Information: How to retrieve the column names of a table ?

[Stef Mientki]

How can I get all the names of a table without doing a query against
the table ?


PRAGMA table_info(table-name);

And it also works for views, as just found out by trial and error ;-)
Is this standard SQL behavior ?


--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Stef Mientki]

COS wrote:

Hi,

- Original Message - 
From: "Stef Mientki" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, March 13, 2007 3:47 PM
Subject: Re: [sqlite] Pragma table_info(), why no fields like UNIQUE,
AUTOINCREMENT


  

You should also consider how your change might effect
backwards compatibility.  The last time that table_info()
was modified, the Ruby-On-Rails community got really
upset.  I'm rather of a mind to leave table_info() alone.

  

Forgive my ignorance, I'm just a beginner in databases,
but what about TABLE_INFO2 ( ) ?
(with the explicit restriction that it can be extended in the future
when needed,
so Ruby users leave it alone ;-)
I really can't imagine that extension of a function can cause serious
compatibility issues)

The alternative is now that I've to
- build a table from table_info()
- query sqlite_master, to get the SQL string with which the table was
generated
- parse the SQL string from sqlite_master
- and add it to my edit grid form table_info
or are there simpler ways ?

--
cheers,
Stef Mientki
http://pic.flappie.nl




A small opinion on that matter: what I would really like to see is something
like system tables. Today sqlite uses only sqlite_master to keep information
about its objects and parsing is required to getter better info of each
object (if one needs to). Using other system tables to keep information
about each object seems very appropriated since most RDBMS already implement
that and it is compatible with SQL ANSI. I think it shouldn't be much of a
problem since sqlite engine already parses each object when it opens a
database. This would remove the need for PRAGMA commands and would make life
much simpler.

  
If it's  a standard habbit or at least a posibility to hold in the 
metadata in standard table,

that would be indeed a much better solution.

Just a thought.
  

But in my rather limited view a rather good one ;-)

cheers,
Stef Mientki


KvK: 41055629



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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Stef Mientki]

You should also consider how your change might effect
backwards compatibility.  The last time that table_info()
was modified, the Ruby-On-Rails community got really
upset.  I'm rather of a mind to leave table_info() alone.
  

Forgive my ignorance, I'm just a beginner in databases,
but what about TABLE_INFO2 ( ) ?
(with the explicit restriction that it can be extended in the future 
when needed,

so Ruby users leave it alone ;-)
I really can't imagine that extension of a function can cause serious 
compatibility issues)


The alternative is now that I've to
- build a table from table_info()
- query sqlite_master, to get the SQL string with which the table was 
generated

- parse the SQL string from sqlite_master
- and add it to my edit grid form table_info
or are there simpler ways ?

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Stef Mientki]

If ask the table sturcture, with pragma table_info()

I get of course the basic fields, like:
  CID,Name,Type,

And also SOME special values, like
 Null, DefaultValue, PrimaryKey

But NOT the following special values (and probably a lot more)
 Unique, AutoIncrement

Is this due to the DLL I use,
or is it (deliberately) left out of the pragma ?

thanks,
Stef Mientki

KvK: 41055629



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

Re: [sqlite] are nested joins possible ?

[Stef Mientki]

Stef Mientki wrote:

thanks guys,
for the fast and different responses,
I really appreciate that.
... now I need a couple hours to study all your ideas.


After studying all your suggestions, I must conclude
that my knowledge of SQL is too low to create a real fool proof solution.
For the moment I've it working for simple visual designs, with the 
following algorithm


- find all connected tables
- find all links between tables
- order the table-boxes, the most left table will get the highest number
- search all top tables, i.e.tables that have no left join to another table
- put all top tables in the "FROM" clause
- walk to the tree of tables and put them in a "JOIN" clause
- check if there are joins between top tables, if found, connect them 
through the "WHERE" clause

- add the user "WHERE" to the "WHERE" clause

You can see an example here
 
http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html#visual_link_design
(btw this page, although messy, gives an idea of the db-manager I'm 
working on)


thanks again for all your support,

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] are nested joins possible ?

[Stef Mientki]

thanks guys,
for the fast and different responses,
I really appreciate that.
... now I need a couple hours to study all your ideas.

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] are nested joins possible ?

[Stef Mientki]

I thought this would work,

SELECT *
 FROM Patient_Text
 INNER JOIN
 (
SELECT *
 FROM Patient
 INNER JOIN Opnamen
 ON Patient.PatNr = Opnamen.PatNr
 )
 ON Patient.PatNr = Patient_Text.PatNr
 
But I get an error on the second use of Patient.PatNr.
Is there a way to get such a nested statement working ??

I'm looking for some construct that can used to generated code
from a visual design, through recursion of the links,
so it must be simple.

thanks,

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Script Language

[Stef Mientki]

Cesar Rodas wrote:
I am planning to develop a interpreted language with LEMON && FLEX. 
The main

goal is to provide a easy way to script commons actions, and to teach
algorithm in my University (National University of Asuncion - Paraguay).

The language have syntax as PHP and Python

I wonder why you're not using one of these ?
Looks to me you're reinventing the wheel,
while with a lot less effort you could give a better wheel a nice color ;-)

cheers,
Stef


KvK: 41055629



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

Re: [sqlite] Argh, this must be a very stupid question ...

[Stef Mientki]

Yeah.  Or, if you are alergic to typing, drag the "mydb.db"
icon over top of the "sqlite3.exe" icon and let it go.
  

thanks for the tip,
and indeed I'm alergic to typing,
I want to run it from a GUI ;-)

btw. if I wasn't by accident bounced into your video, I would now still 
be manipulating ini-files, thanks for SQLite!

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


  


--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Re: Argh, this must be a very stupid question ...

[Stef Mientki]

thanks Igor,

so it was indeed a stupid question !

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] I Need database fot some test

[Stef Mientki]

thanks Dennis,


Single quote delimit a string literal. Double quotes delimit a quoted 
identifier (i.e a column or table name etc that contains special 
characters such as a space).



I just checked Sybase Manual, and there it's exactly as you say.

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] I Need database fot some test

[Stef Mientki]

thanks Dennis, Marco,

And the standard SQL syntax should work from any standard compliant 
database program.


   SELECT * FROM "Order Details"



I'm just a novice,
and although both suggested solutions work,
I thought SINGLE QUOTES were thé standard ?

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] I Need database fot some test

[Stef Mientki]

Did anyone test this database ?
With 3 out of 4 programs I'm not able to read the table "Order Details",
does anyone what's the problem here ?

thanks,

Mikey C wrote:
Here is a database http://www.nabble.com/file/6997/Northwind.db Northwind.db 


It is an exact SQLite implementation of the well known Microsoft Northwind
sample that can be found for MS Access and SQL Server.


  


--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Re: Re: A few (probably) simple questions ...

[Stef Mientki]

Igor Tandetnik wrote:

Stef Mientki <[EMAIL PROTECTED]> wrote:

Q2:
Why isn't ALIAS supported in the JOIN-line, or am I doing something
wrong ? 
SELECT   Patient_text.*, Opnamen.*
 FROM   Patient
 INNER JOIN Patient_text, Opnamen AS O
 WHERE  Patient.PatNr = Patient_text.PatNr
 ANDPatient.PatNr = '1'



What exactly do you believe is not supported? Do you get an error
with this statement? It looks good to me.


I get the following error ":: no such table: Opnamen" (running from
Delphi)


Well, a logical follow up question is, do you actually have a table 
named Opnamen in your database? 

Yes,
and 
"INNER JOIN Patient_text, Opnamen

works as expected.
Also, I'm somewhat surprised you don't mention Opnamen in the WHERE 
clause. Do you really want a full cartesian product?

I don't remember what a full cartesian product is (too long ago),
but you're completely right, without the "Opnamen" in the WHERE clause I 
get much too much ;-)

(I left it out to simplify)




Yes, and the above quote is from
  http://www.sqlite.org/lang_select.html
where nothing is said about not supporting "RIGHT",


That's why I pointed you to http://www.sqlite.org/omitted.html.


Yes,
but I'm a "positive" guy,
interesting in what's in SQLite,
not what's not in it ;-)

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] A few (probably) simple questions ...

[Stef Mientki]

unfortunately it only supports sqlite 2 :-(



I wondered if the source for the database interface of
http://OpenOffice.org might help, since they appear to implement exactly
that -- even including an sqlite driver.
  


--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] A few (probably) simple questions ...

[Stef Mientki]

thanks Donald,

I should be ashamed, as OpenOffice is my standard office package,
didn't know OpenOffice supported SQLite (MS-Access didn't ),
so I'll take a look.

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Re: A few (probably) simple questions ...

[Stef Mientki]

thanks Igor,





Q2:
Why isn't ALIAS supported in the JOIN-line, or am I doing something
wrong ? 
SELECT   Patient_text.*, Opnamen.*
 FROM   Patient
 INNER JOIN Patient_text, Opnamen AS O
 WHERE  Patient.PatNr = Patient_text.PatNr
 ANDPatient.PatNr = '1'



What exactly do you believe is not supported? Do you get an error with 
this statement? It looks good to me.



I get the following error ":: no such table: Opnamen" (running from Delphi)

Q3:
In the SQL help on the web, I read:
"/join-op/ ::= *, *|* *[*NATURAL*]* *[*LEFT *|* RIGHT *|* FULL*]*
*[*OUTER *|* INNER *|* CROSS*]* JOIN*"
But when I try a RIGHT JOIN, I get an error message ???


SQLite doesn't support right outer joins at this time, only left 
joins. By the way, SQL syntax supported by SQLite is documented here


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

Yes, and the above quote is from
  http://www.sqlite.org/lang_select.html
where nothing is said about not supporting "RIGHT",
and now I assume this page lists a lot more that's not supported (yet) ;-)

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] A few (probably) simple questions ...

[Stef Mientki]

Q1:
What's the difference between ON and WHERE,
the 2 statements below return exactly the same ?

SELECT   Patient_text.*, Opnamen.*
 FROM   Patient as P
 INNER JOIN Patient_text, Opnamen
 ON P.PatNr = Patient_text.PatNr
 WHERE  P.PatNr = '1'



SELECT   Patient_text.*, Opnamen.*
 FROM   Patient
 INNER JOIN Patient_text, Opnamen
 WHERE  Patient.PatNr = Patient_text.PatNr
   AND  Patient.PatNr = '1'


(I'm interested in this differences, because I'm trying to generate SQL 
statements from a visual design, like in MS-Access.
If anyone knows good literature about generating SQL statements from 
graphical designs, I'ld be much obliged.)



Q2:
Why isn't ALIAS supported in the JOIN-line, or am I doing something wrong ?

SELECT   Patient_text.*, Opnamen.*
 FROM   Patient
 INNER JOIN Patient_text, Opnamen AS O
 WHERE  Patient.PatNr = Patient_text.PatNr
 ANDPatient.PatNr = '1'


Q3:
In the SQL help on the web, I read:
"/join-op/ ::= *, *|* *[*NATURAL*]* *[*LEFT *|* RIGHT *|* FULL*]* 
*[*OUTER *|* INNER *|* CROSS*]* JOIN*"

But when I try a RIGHT JOIN, I get an error message ???

thanks,

--
cheers,
Stef Mientki
http://pic.flappie.nl

Re: [sqlite] I Need database for some test

[Stef Mientki]

lordphoenix wrote:

Le Sun, 04 Mar 2007 22:01:30 +0100,
Stef Mientki <[EMAIL PROTECTED]> a écrit :

  

lordphoenix wrote:


I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org)
  

this link doesn't seem to work :-(


Sorry for mistake here is the good one :
http://litemanager.tuxfamily.org/
  

thanks "lordphoenix"  (and Gerry too, for the lucky guess)
looks promising,
I hope to see it in English one day,
because my French is much much worse than your English !!

I think good open source database managers are very welcome.
Tested about 7 free and open source database managers (for windows),
and none of them was satisfactory.
( So I'm too building an opensource database manager (probably windows 
only, or wine),

maybe it would be nice to exchange some ideas)

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] I Need database for some test

[Stef Mientki]

lordphoenix wrote:

I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org)

this link doesn't seem to work :-(

cheers,
Stef Mientki


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

Re: [sqlite] Re: Re: How to change or add fields to a table ?

[Stef Mientki]

On the other hand I fear that I can only change the name of the Table.
If the later is true, why isn't possible to change the name of a
column (shouldn't be difficult to implement) ?


Please feel free to submit a patch.

Thank you, but I'm just a Newbie ;-)
It was certainly not to criticize SQLite,
(It's the first database I found without difficult couplings / logins, 
so I really love it),
but I wondered if there was a special nifty reason to omit the renaming 
of columns.


thanks again,
Stef Mientki


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

Re: [sqlite] Re: How to change or add fields to a table ?

[Stef Mientki]

thanks Igor,
for the fast answer.
(should have found that myself ;-)

But there's a strange thing here I don't understand:
somewhere on the ALTER TABLE documentation it says:
  "to rename or add a new column ..."
My English isn't too well,
but from the rest of the text I don't understand how I can change a 
columns name.

On the other hand I fear that I can only change the name of the Table.
If the later is true, why isn't possible to change the name of a column
(shouldn't be difficult to implement) ?

cheers,
Stef



Igor Tandetnik wrote:

Stef Mientki <[EMAIL PROTECTED]> wrote:

Are there SQL commands to add or change the fields of an existing
table, or should the table completely be rebuild ?


Some limited changes may be done with ALTER TABLE statement:

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

For anything else, you would need to create a new table and migrate data.

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







--
cheers,
Stef Mientki
http://pic.flappie.nl


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

[sqlite] How to change or add fields to a table ?

[Stef Mientki]

With the pragma table_info I can get the complete table structure
Pragma table_info(Patient)

Are there SQL commands to add or change the fields of an existing table,
or should the table completely be rebuild ?

--
thanks,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Re: Quotes in SQLite ?

[Stef Mientki]

Dennis, Igor thanks very much,
I think I'm getting the picture.
cheers,
Stef



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

Re: [sqlite] Re: Quotes in SQLite ?

[Stef Mientki]

Igor Tandetnik wrote:

Stef Mientki <[EMAIL PROTECTED]> wrote:

This might be a stupid question,
but how do you store quotes in textstrings ?

Is it really so that you have to replace,
both single and double quotes,
each time you read or write something ?


You need to escape single quotes if you insist on putting string 
literals directly into queries. No other characters need to be escaped.

I've to translate doublequotes too,
possibly because I'm using double quotes around textfields ??,
Is that wrong ?

btw, I'm writing (or maybe I should say "trying to write") another raw 
database manager (open source),

so I must be able to do everything that's legal.

See also sqlite3_mprintf.

where can I find that (looked in the SQL wiki, but couldn't find it)



However, I recommend using parameterized queries and binding your 
strings to parameters. Then you don't need to worry about escaping at 
all.
Sorry don't know what "parameterized queries" and "binding strings to 
parametrs" are,

can you give me link where I can find some more information.

thanks,
Stef Mientki


Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








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

[sqlite] Quotes in SQLite ?

[Stef Mientki]

This might be a stupid question,
but how do you store quotes in textstrings ?

Is it really so that you have to replace,
both single and double quotes,
each time you read or write something ?

Are there other such kind of special characters ?

thanks,
Stef Mientki


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

[sqlite] The best way to delete a column ?

[Stef Mientki]

hello,

I just read it's not possible to delete a column in an existing table.
Now what would be the best way to remove the column indirect
(from Delphi code),

would it be something like this:

start transaction
 create new table with the same structure, but without the column to 
delete (how?)

 copy the data from old table to new table
 delete the old table
 rename the new table to the old name
 vacuum-clean the database
end transaction

I this the best way, or are there any other ?
Sorry I'm just starting SQL, so I just used human SQL.
(and therefor a complete code section would be welcome ;-)

thanks,
Stef Mientki



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

Re: [sqlite] (newbie) pictures in tables, what's the best way ?

[Stef Mientki]

Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Stef Mientki wrote:
  

Uptill now the dbase is small so no performance problems yet.



Wait until you have performance problems and then solve them :-)  It is
almost always the case that performance issues don't crop up where you
think they will.

  

Yes, that might be the right approach ...
... but if I read somewhere that you can't delete columns ... 
... I want to take some pre-cautions.

I think I'm getting more insight by now.
I'm using a Delphi wrapper SQLiteTable3 from Tim Anderson,
which always grabs all data of a query in a table,
so also all pictures from blobfields, while I only need the first 2 bytes,
so I'll modify that.


I can think of several solutions, but as my experience with databases is
almost zero, I can't decide



One nice feature SQLite has is virtual tables.  You don't need to do
anything now, but if you have performance issues in the future, you can
use a virtual table for your data and behind the scenes do whatever you
want including storing the data as files.

  

Thanks Roger,
I didn't know that.
cheers,
Stef Mientki

Re: [sqlite] Re: Re: how to get field names of empty tables ?

[Stef Mientki]

Scott Hess wrote:

I think only the dot commands are special (.help, etc).  Everything
else is fair game.  Best reference for what you can feed a prepare or
exec is http://www.sqlite.org/lang.html .

Scott,
thanks for this valuable information.
Now how beautiful it would be if it was all in 1 pdf file ;-)

cheers,
Stef


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

Re: [sqlite] Re: how to get field names of empty tables ?

[Stef Mientki]

Igor Tandetnik wrote:

Stef Mientki <[EMAIL PROTECTED]> wrote:

is there an SQL statement to get the field-names of empty tables ?


PRAGMA table_info(tableName);

Igor Tandetnik

Thanks Igor,
I had seen that command,
but I wrote something about that these commands could only be run from 
the command line.


cheers,
Stef Mientki


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

[sqlite] how to get field names of empty tables ?

[Stef Mientki]

is there an SQL statement to get the field-names of empty tables ?

thanks,
Stef Mientki

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

Re: [sqlite] (newbie) pictures in tables, what's the best way ?

[Stef Mientki]

Cesar Rodas wrote:

Save into a blob.


thanks but that's not what I meant.
I'll try to explain with an example:

I want to create this database

Persons
 - Name
 - Birthday
 - Picture

Now when I want to view this database with a general database browser / 
manager,

I'll first find out what tables there are in this database,
Secondly I'm going to ask for an overview of each table by, that will be 
displayed in a flat table (2-dimensional matrix)

 SELECT   *   FROM   Persons
Now the browser sees the blob field at displays  is something like "{blob}"

So I assume, that the browser get's all the data, including all the bits 
and bytes of each image in each record,
so a whole lot of not-needed data, because the database browser / 
manager can con display 1 picture from a selected record.


So I think, it would be more efficient to create 2 tables:

Persons
 - Name
 - Birthday
 - ID ("this is a picture")

Blobs
 - Name
 - Picture

Now if I ask for the main table "Persons", I just get an ID (some 
integer), that tells me that there is a picture.
The browser / manager can ask for just one record from the table Blobs, 
to show me the picture of the selected line.


I've never seen such a construct in examples, so maybe I see ghosts 
where there are none.


forgive me my ignorance.
cheers,
Stef Mientki



here is a C example http://www.sqlite.org/cvstrac/wiki?p=BlobExample

On 23/02/07, Stef Mientki <[EMAIL PROTECTED]> wrote:

hello,

I've started with SQLite, because it's one of the first dbases with a
local filessytem without authenciation.
It looks great, compared to ini-files.

Now I've a basic problem:
I've a table, with 10 simple fields and 1 blob field, containing a
(possibly large) image.
Uptill now the dbase is small so no performance problems yet.
But what if it grows ?

I want to look at the table in a table overview,
where the blob-field just displays the type of image
(identified by the fisrt 2 bytes SOI-code).

When I ask for the table through a query,
I get all the blob-filed information, while I only need the first 2 
bytes,

and only the full picture of 1 selected record.

I can think of several solutions, but as my experience with databases is
almost zero, I can't decide
(and maybe I'm overlooking some better solutions)
solution 1:
add a extra field for each blob field, filled with the first 2 bytes of
a blob
solution 2:
put the blob fields in a separate table, with a link to the main table

any suggestions would be appreciated.
thanks,
Stef Mientki

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









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

[sqlite] (newbie) pictures in tables, what's the best way ?

[Stef Mientki]

hello,

I've started with SQLite, because it's one of the first dbases with a 
local filessytem without authenciation.

It looks great, compared to ini-files.

Now I've a basic problem:
I've a table, with 10 simple fields and 1 blob field, containing a 
(possibly large) image.

Uptill now the dbase is small so no performance problems yet.
But what if it grows ?

I want to look at the table in a table overview,
where the blob-field just displays the type of image
(identified by the fisrt 2 bytes SOI-code).

When I ask for the table through a query,
I get all the blob-filed information, while I only need the first 2 bytes,
and only the full picture of 1 selected record.

I can think of several solutions, but as my experience with databases is 
almost zero, I can't decide

(and maybe I'm overlooking some better solutions)
solution 1:
add a extra field for each blob field, filled with the first 2 bytes of 
a blob

solution 2:
put the blob fields in a separate table, with a link to the main table

any suggestions would be appreciated.
thanks,
Stef Mientki

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