Re: [sqlite] Am I understanding Blobs correctly?

[Darren Duncan]

Mike McGonagle wrote:
> I have never used Blobs before, and in reading some of the literature,
> I am just a bit confused. From what I am understanding, I get the
> impression that a Blob is no more than the binary data from a disk
> file, stored as a string of bytes in a field of the database.
> 
> Basically, I am trying to write some multimedia stuff, and as such,
> was thinking that I need to write some "filters" that access specific
> types of data (a wav file, or a MIDI file, or etc.). If this is the
> case, it almost sounds like it would be better to store the filename
> for the data, and then let the system take care of reading the
> particular format of the data.

A blob in the general sense is a lump of data that is undifferentiated (eg into 
characters or digits or fields etc) as far as the DBMS is concerned, just a 
string of bits; it serves as a catch all when you want to store anything that 
isn't better served by some more specialized type like text or number.

Others already mentioned some advantages of storing these in the database 
rather 
than the file system, for example making it easier to organize and you don't 
have to worry about addressing schemes etc like filenames, or rather you sort 
of 
do but a database gives you a lot more flexibility.

One big thing that wasn't mentioned is that storing things in a database can 
often give you improved reliability.  For example, if you have several changes 
to your data you want to make atomically, which includes your blob data, then 
simply marking all the changes as a transaction means the DBMS worries about 
making everything atomic, and you don't have the extra book keeping and hassle 
of doing that yourself, as you would if you have pieces in the file system.

Now that's not to say that some file systems aren't transactional; some are, 
but 
most and typical ones are't, and many that say they are only make meta-data 
atomic not changes to the file content itself.

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

Re: [sqlite] Am I understanding Blobs correctly?

[Dan]

On Jan 27, 2009, at 8:56 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Mike McGonagle wrote:
>> From what I am understanding, I get the
>> impression that a Blob is no more than the binary data
>
> Binary large object.

I came across this the other day:

   http://www.cvalde.net/misc/blob_true_history.htm

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

Re: [sqlite] Am I understanding Blobs correctly?

[P Kishor]

On 1/26/09, Mike McGonagle  wrote:
> Thanks, the datatypes are going to be varied, but I am sure the sizes
>  are the things I need to consider.

I am not totally sure what you mean by "datatypes are going to be
varied." The datatype for all the entries is going to be "blob."

Perhaps you are referring to the type of "media" (wav file, midi file,
mp3, image, etc.) that would be stored as a blob... well, that has
nothing to do with the database.

The database is just a store... you put data in it, and it faithfully
gives you back the same data quickly and efficiently. What you do with
it is up to you. Perhaps you can store the "type of media" (or call it
whatever you want to) as an attribute of the blob, and use that
attribute to figure out what to do with the data once it is pulled out
of the table.




>
>
>  Mike
>
>
>  On Mon, Jan 26, 2009 at 8:27 PM, P Kishor  wrote:
>  > If you have a lot of tiny images, especially if the images can be
>  > smaller than a page size in the db, then storing them in the db would
>  > be very efficient. If you have very large images then storing their
>  > path in the db, and keeping the images on the fs would be better.
>  >
>  > If you do keep your images on the file system, you will have to devise
>  > some method for naming and storing them... they will have to have a
>  > uniquely accessible path, and dumping them all in one folder will slow
>  > down access after a few hundred, perhaps a few thousand. In the db, on
>  > the other hand, you can just store the images in a separate table and
>  > assign them a primary key, then join them to your main attributes
>  > table.
>  >
>  > For most applications, it may not matter much, but when you are
>  > dealing with either very large size images, or very large number of
>  > images then one or the other system might be more suitable.
>
> > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>
>
>
>  --
>  Peace may sound simple—one beautiful word— but it requires everything
>  we have, every quality, every strength, every dream, every high ideal.
>  —Yehudi Menuhin (1916–1999), musician
>


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Am I understanding Blobs correctly?

[Mike McGonagle]

Thanks, the datatypes are going to be varied, but I am sure the sizes
are the things I need to consider.

Mike

On Mon, Jan 26, 2009 at 8:27 PM, P Kishor  wrote:
> If you have a lot of tiny images, especially if the images can be
> smaller than a page size in the db, then storing them in the db would
> be very efficient. If you have very large images then storing their
> path in the db, and keeping the images on the fs would be better.
>
> If you do keep your images on the file system, you will have to devise
> some method for naming and storing them... they will have to have a
> uniquely accessible path, and dumping them all in one folder will slow
> down access after a few hundred, perhaps a few thousand. In the db, on
> the other hand, you can just store the images in a separate table and
> assign them a primary key, then join them to your main attributes
> table.
>
> For most applications, it may not matter much, but when you are
> dealing with either very large size images, or very large number of
> images then one or the other system might be more suitable.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Peace may sound simple—one beautiful word— but it requires everything
we have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INNER JOIN, JOIN Confusin

[Igor Tandetnik]

"Cnichols"  wrote
in message news:21676221.p...@talk.nabble.com
> What i am trying to do right now is select the questions that have
> been asked for the current session (ie 7)
> Stats - contains 46 rows with sessionid of 7
> Temp - holds 52 missed questions from the previous session (6)
>
> example - this works perfect; it returns 46 results
> 
> SELECT COUNT() FROM Stats S
> INNER JOIN Questions T ON S.QuestionId = T.Id
> WHERE S.SessionId = 7
>
> but if i do this
> --
> SELECT COUNT() FROM Stats S
> INNER JOIN Temp T ON S.QuestionId = T.Id
> WHERE S.SessionId = 7
>
> 51 results are returned? I do not understand why?

Temp contains duplicate values in Id field. Or else, it's not true that 
Stats contains 46 records with SessionId=7 - confirm by running

SELECT COUNT() FROM Stats S
WHERE S.SessionId = 7;

Igor Tandetnik 



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

Re: [sqlite] Am I understanding Blobs correctly?

[P Kishor]

On 1/26/09, Mike McGonagle  wrote:
> Hello all,
>
>  I have never used Blobs before, and in reading some of the literature,
>  I am just a bit confused. From what I am understanding, I get the
>  impression that a Blob is no more than the binary data from a disk
>  file, stored as a string of bytes in a field of the database.
>
>  Basically, I am trying to write some multimedia stuff, and as such,
>  was thinking that I need to write some "filters" that access specific
>  types of data (a wav file, or a MIDI file, or etc.). If this is the
>  case, it almost sounds like it would be better to store the filename
>  for the data, and then let the system take care of reading the
>  particular format of the data.
>

If you have a lot of tiny images, especially if the images can be
smaller than a page size in the db, then storing them in the db would
be very efficient. If you have very large images then storing their
path in the db, and keeping the images on the fs would be better.

If you do keep your images on the file system, you will have to devise
some method for naming and storing them... they will have to have a
uniquely accessible path, and dumping them all in one folder will slow
down access after a few hundred, perhaps a few thousand. In the db, on
the other hand, you can just store the images in a separate table and
assign them a primary key, then join them to your main attributes
table.

For most applications, it may not matter much, but when you are
dealing with either very large size images, or very large number of
images then one or the other system might be more suitable.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INNER JOIN, JOIN Confusin

[John Machin]

On 27/01/2009 12:00 PM, Cnichols wrote:
> I have stumped myself with this sql goal.
> 
> With this statement I am working with 3 tables
> Stats - contains question asked history and if it was answered correcty
>   sessionid
>   questionid
>   correct
> Questions - contains a list questions and mul choice answers and answer
>   id - pk
>   
>   .
>   etc.
> Temp - schema is exactly like questions except contains missed questions
> from a previous defined sessionid
> 
> What i am trying to do right now is select the questions that have been
> asked for the current session (ie 7)
> Stats - contains 46 rows with sessionid of 7
> Temp - holds 52 missed questions from the previous session (6)
> 
> example - this works perfect; it returns 46 results
> 
> SELECT COUNT() FROM Stats S
> INNER JOIN Questions T ON S.QuestionId = T.Id
> WHERE S.SessionId = 7
> 
> but if i do this
> --
> SELECT COUNT() FROM Stats S
> INNER JOIN Temp T ON S.QuestionId = T.Id
> WHERE S.SessionId = 7

You said "Temp - holds 52 missed questions from the previous session 
(6)" so shouldn't the WHERE clause be:
 WHERE S.SessionId = 6
?
If not, change your query to do
 SELECT S.*, T.* FROM etc etc
also select the 52 from Temp and work out which one is missing -- maybe 
a value of Temp.Id is wrong (i.e. doesn't match up with a value of 
Stats.QuestionId)

> 
> 51 results are returned? I do not understand why?
> 

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

Re: [sqlite] Am I understanding Blobs correctly?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Mike McGonagle wrote:
> From what I am understanding, I get the
> impression that a Blob is no more than the binary data 

Binary large object.  Large is by comparison to other typical data in a
database.  For example numbers are typically up to 10 digits long and
text strings range from a few characters and probably average out at ten
to twenty characters.

Blobs are typically much larger than that. You also can't use text
functions to act on them (eg regular expressions, LIKE).

> from a disk file, 

The origin of blob data is irrelevant.

> stored as a string of bytes in a field of the database.

SQLite stores blobs as a sequence of bytes in the database file.

> Basically, I am trying to write some multimedia stuff, and as such,
> was thinking that I need to write some "filters" that access specific
> types of data (a wav file, or a MIDI file, or etc.). If this is the
> case, it almost sounds like it would be better to store the filename
> for the data, and then let the system take care of reading the
> particular format of the data.

Although SQLite does handle blobs fairly well, common practise is to do
exactly as you say - store the filename in the database.  Any blob you
store in the database has a practical size limit of 1GB and a
theoretical maximum of 2GB.  Storing the items in the database directly
is beneficial if the database has to be moved amongst machines since the
other files don't have to be dragged along as well.

If you want to hide from your database code what is really going on
underneath the hood then I'd recommend using a virtual table.  Your
virtual tables can automagically make it look like the blob data is
stored in the database but behind the scenes have it in separate files
or in the database.  Profiling will then tell you which is most
performant for your application.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkl+aeUACgkQmOOfHg372QSXtwCgqgd5ZXtHIC8mJZgPCY1/VK6H
bVkAnR2EgwbsxZD5gC44Dp3Wxm12oki2
=Nl/Y
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Am I understanding Blobs correctly?

[Mike McGonagle]

Hello all,

I have never used Blobs before, and in reading some of the literature,
I am just a bit confused. From what I am understanding, I get the
impression that a Blob is no more than the binary data from a disk
file, stored as a string of bytes in a field of the database.

Basically, I am trying to write some multimedia stuff, and as such,
was thinking that I need to write some "filters" that access specific
types of data (a wav file, or a MIDI file, or etc.). If this is the
case, it almost sounds like it would be better to store the filename
for the data, and then let the system take care of reading the
particular format of the data.

Thanks,

Mike



-- 
Peace may sound simple—one beautiful word— but it requires everything
we have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INNER JOIN, JOIN Confusin

[Cnichols]

I have stumped myself with this sql goal.

With this statement I am working with 3 tables
Stats - contains question asked history and if it was answered correcty
  sessionid
  questionid
  correct
Questions - contains a list questions and mul choice answers and answer
  id - pk
  
  .
  etc.
Temp - schema is exactly like questions except contains missed questions
from a previous defined sessionid

What i am trying to do right now is select the questions that have been
asked for the current session (ie 7)
Stats - contains 46 rows with sessionid of 7
Temp - holds 52 missed questions from the previous session (6)

example - this works perfect; it returns 46 results

SELECT COUNT() FROM Stats S
INNER JOIN Questions T ON S.QuestionId = T.Id
WHERE S.SessionId = 7

but if i do this
--
SELECT COUNT() FROM Stats S
INNER JOIN Temp T ON S.QuestionId = T.Id
WHERE S.SessionId = 7

51 results are returned? I do not understand why?


My overall goal is to select a random unique question from Temp that has not
been asked (so its not in stats for current session) and ORDER BY Random()
LIMIT 1

Any help would be greatly  appreciated!
-- 
View this message in context: 
http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21676221.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Progress update while Prepare() is executing

[Kees Nuyt]

On Mon, 26 Jan 2009 10:44:12 -0600, Nicolas Williams
 wrote in General Discussion of
SQLite Database :

>On Sun, Jan 25, 2009 at 01:32:57PM +0100, Kees Nuyt wrote:

[...]

>> To get a % progress indicator you need a more or less
>> accurate estimate of what 100% is. You could gather some
>> statistics and keep that in a table, and/or assume worst
>> case every time.
>
>All excellent advice, but a small nit, tiny really: you can still have
>some form of mildly useful progress indicator (a spinner, sand clock
>mouse sprite turning, whatever) when you don't know what 100% is.  

All true, but Derek explicitly asked:

>>> [...] the real point is that no one cares that the
>>> first Querie takes so long as long as there some
>>> progress indication (and I dont mean a endlessly 
>>> repeating progress bar)


>I say
>"mildly useful" because such indicators aren't useful when, 
>say, the app gets stuck in an infinite loop, or the operation
>takes too long for the user to distinguish between "too long" 
>and "infinite loop" :)
>
>Still, it's useful enough as described by the docs:
>
>"
>If the progress callback returns non-zero, the operation is interrupted.
>This feature can be used to implement a "Cancel" button on a GUI
>progress dialog box.
>"

Yes, that's useful. I hope Derek is still listening.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Smaller issues with transactions in "undefined mode"

[Olaf Schmidt]

"D. Richard Hipp"  schrieb im
Newsbeitrag news:f6c1943c-f8ef-400a-800a-4afb2013f...@hwaci.com...


> > This forgotten switch was causing a JournalMode = Off.
> >
> > Later on Transactions were used inside the Copy-over-
> > process from FireBird.

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

Ah, good - and with [6197] these problems should be gone.

Will wait nonetheless for the next official release of
your amalgamation - think the scenario is not all
that common to require a "fast intermediate patch"
of the engine-compile my wrapper uses.

Thanks for the pointer - and as it seems you devs
fix the bugs already before they are mentioned from
"outside" - now that's really great "anticipatory support"
I'd say... :-)


Regards,

Olaf



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

Re: [sqlite] commit causes error - why?

[Igor Tandetnik]

Vance E. Neff  wrote:
> I'm having trouble on the "COMMIT TRANSACTION" Query.
>
> I seem, for some reason, to be violating a SQLite rule.
>
> This is from a language called RUN BASIC.
>
> Here is the test program, note that "" translates to a single " and
> the print statements are just for this demo:
>
>#SQLiteDataBase EXECUTE( "SELECT MAX( ShoppingListIndex ) AS
> MaxShoppingListIndex FROM ShoppingLists" )
> PRINT "Executed select max"
>#CurrentQueryRow = #SQLiteDataBase #NEXTROW()
>
>#SQLiteDataBase EXECUTE( "COMMIT TRANSACTION" )

Before you can commit a transaction, you need to close all outstanding 
select statements. Figure out how to do the equivalent of sqlite3_reset 
or sqlite3_finalize in your programming language.

Igor Tandetnik 



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

Re: [sqlite] Smaller issues with transactions in "undefined mode"

[D. Richard Hipp]

On Jan 26, 2009, at 12:03 PM, Olaf Schmidt wrote:
>
> This forgotten switch was causing a JournalMode = Off.
>
> Later on Transactions were used inside the Copy-over-
> process from FireBird.
>
> And that is, what I mean with "undefined mode" -
> Journal-Mode=Off and nonetheless "transactions
> on top" were somehow tolerated by the older
> engines < 3.6.9 - but the newer engines are not
> tolerating that anymore (and cause a crash).
>

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

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Smaller issues with transactions in "undefined mode"

[Olaf Schmidt]

Hi Devs,

This is not really a "bug-report", but I want to
mention it nonetheless, since engine-crashes are
probably not what should be left unhandled, regardless
if the user tries to use the engine in an undefined-mode.

Let me explain:

Background:
Platform Windows.
StdCall-compile of the original "amalgamation".
COM-Wrapper "on top".
Crashes with each of the newer versions (3.6.9
and 3.6.10) - so, yes - the whole thing is probably
related to the changes in the internal transaction-
support of the engine.

A user reported "full crashes" in a larger "copy-
over-scenario" from another DB (FireBird).

Switching back to the former engine 3.6.7
was working Ok.

Finally the cause was a "left-over" switch, somewhere
hidden in an Ini-File, which was introduced regarding
"performance tuning" (together with switches for the
different SyncModes and different PageSizes).

This forgotten switch was causing a JournalMode = Off.

Later on Transactions were used inside the Copy-over-
process from FireBird.

And that is, what I mean with "undefined mode" -
Journal-Mode=Off and nonetheless "transactions
on top" were somehow tolerated by the older
engines < 3.6.9 - but the newer engines are not
tolerating that anymore (and cause a crash).

The transactions in question were used with the
"older" standard-syntax (Begin Immediate, etc.)
and not with the new introduced SavePointName-
syntax.

Regards,

Olaf



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

Re: [sqlite] Progress update while Prepare() is executing

[Nicolas Williams]

On Sun, Jan 25, 2009 at 01:32:57PM +0100, Kees Nuyt wrote:
> If the replace statement is the same every time, you only
> have to prepare it once. As drh said, to be able to help you
> more, we need the schema and more detailed code.
>  
> prepare()
> begin()
> while data_available{
>   bind()
>   step()
>   reset()
>   [ clear_bindings() ]
> }
> commit()

You might also prepare all statements at app startup time, which users
might expect to be slow anyways, and maybe in a separate thread if there
is other initialization work to do that can be done in parallel.

If the schema changes, forcing you to re-compile your statements, and
they really take that long to prepare, then have some sort of UI
indication of this.  Hopefully you can keep the frequency of schema
changes down.

Cheers,

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

Re: [sqlite] Progress update while Prepare() is executing

[Nicolas Williams]

On Sun, Jan 25, 2009 at 01:32:57PM +0100, Kees Nuyt wrote:
> The progress indicator of both REPLACE and SELECT can be
> driven by the while { step() } loops in your program. 
> A progress callback is not of much use here, except for the
> first step(), because the first step() returns after any
> intermediate tables have been built.
> 
> To get a % progress indicator you need a more or less
> accurate estimate of what 100% is. You could gather some
> statistics and keep that in a table, and/or assume worst
> case every time.

All excellent advice, but a small nit, tiny really: you can still have
some form of mildly useful progress indicator (a spinner, sand clock
mouse sprite turning, whatever) when you don't know what 100% is.  I say
"mildly useful" because such indicators aren't useful when, say, the app
gets stuck in an infinite loop, or the operation takes too long for the
user to distinguish between "too long" and "infinite loop" :)

Still, it's useful enough as described by the docs:

"
If the progress callback returns non-zero, the operation is interrupted.
This feature can be used to implement a "Cancel" button on a GUI
progress dialog box.
"

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

[sqlite] commit causes error - why?

[Vance E. Neff]

I'm having trouble on the "COMMIT TRANSACTION" Query.

I seem, for some reason, to be violating a SQLite rule.

This is from a language called RUN BASIC.

Here is the test program, note that "" translates to a single " and the 
print statements are just for this demo:

[code]
CLS
PRINT "Start"
SQLITECONNECT #SQLiteDataBase, "Shopper.dbs"
#SQLiteDataBase EXECUTE( "SELECT MAX( ShoppingListIndex ) AS 
MaxShoppingListIndex FROM ShoppingLists" )
PRINT "Executed select max"
#CurrentQueryRow = #SQLiteDataBase #NEXTROW()
ShoppingListIndex$ = STR$( (#CurrentQueryRow 
MaxShoppingListIndex()) + 1 )
PRINT "Figured next index value"
#SQLiteDataBase EXECUTE( "BEGIN TRANSACTION" )
PRINT "Began transaction"
#SQLiteDataBase EXECUTE( "INSERT Into ShoppingLists 
(CustomerIndex, ShoppingListDate, ShoppingListTime, ShoppingListIndex, 
CombinedShoppingList ) Values (""1"", ""01/25/2009"", ""12:50:30"", 
""1"", ""0"" )" )
PRINT "Inserted new data into ShoppingLists"
#SQLiteDataBase EXECUTE( "COMMIT TRANSACTION" )
PRINT "committed transaction"
#SQLiteDataBase DISCONNECT()
PRINT "End"
END
[/code]

And here is the displayed output:

Start
Executed select max
Figured next index value
Began transaction
Inserted new data into ShoppingLists

Runtime Error in program 'TestProblem': #SQLiteDataBase EXECUTE( "COMMIT 
TRANSACTION" )
SQL logic error or missing database

This error was triggered by SQLite.

If I remove the 3 lines (not counting the PRINT statements):


[code]
#SQLiteDataBase EXECUTE( "SELECT MAX( ShoppingListIndex ) AS 
MaxShoppingListIndex FROM ShoppingLists" )
PRINT "Executed select max"
#CurrentQueryRow = #SQLiteDataBase #NEXTROW()
ShoppingListIndex$ = STR$( (#CurrentQueryRow 
MaxShoppingListIndex()) + 1 )
PRINT "Figured next index value"
[/code]

The program runs successfully.

Or, if I insert a disconnect and connect sequence just after those 
lines, the program runs OK.

Why?

Thanks for any help!

Vance



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

Re: [sqlite] codepages

[Olaf Schmidt]

"Sherief N. Farouk" 
schrieb im Newsbeitrag news:000301c97e09$2556f0f0$7004d2...@com...
> > dear users
> > I use sqlite for vb6, utf-8 database,
> > when insert arabic data to database
> > and search it with 'like' operator
> > (within % syntax), return all records!!!
> > Please help me...
> > thanks

I'm somehow missing the original message,
so I'll reply here:

Regarding VB6 - you could try out my
wrapper (either the older dhSQLite or the
newer version which comes in dhRichClient.dll)

These VB6-based COM-Wrappers already contain
appropriate Overrides for the Like-function,
Upper(), Lower() and also the NoCase-Collation,
where under the hood a fast UTF8->UCS2-BSTR-
conversion is done - also included is an optimization
for the overriden Like with regards to the usage of
a predefined index for Like-queries of the form:
SomeField Like 'abc%'.

So normally that should work with no problems.

Regards

Olaf Schmidt



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

Re: [sqlite] codepages

[MikeW]

Sherief N. Farouk  writes:

> 
> > dear users
> > I use sqlite for vb6, utf-8 database,
> > when insert arabic data to database
> > and search it with 'like' operator
> > (within % syntax), return all records!!!
> > Please help me...
> > thanks
> > John Smith
> > ___
> 
> Arabic, in general, is one hell of a language to deal with. Can you provide
> some sample data, like a db file and sample query?
> 
> - Sherief
> 

See
 http://www.sqlite.org/lang_expr.html#like
 and
 http://www.sqlite.org/lang_corefunc.html#like

You might wish to implement your own Arabic-aware like().

MikeW

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

Re: [sqlite] What is the advantage of using native c API over ODBC

[MikeW]

goldy  writes:

> 
> Hi All,
> 
> What are the basic advantage of using SQLite with C API over ODBC.

Whereas the advantage of ODBC is having the same(?) interface to
different database systems.

Horses for courses ...

MikeW


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

Re: [sqlite] How many tables can a database hold?

[MikeW]

J. R. Westmoreland  writes:

> 
> Can someone tell me how many tables a given database can hold.
> 
> I'm looking at an initial design of an application that could have a table
> of data for each city in a state. This could be possibly more than a
> thousand tables.
> 
> With that many tables how would the performance of the database be effected?
> 
> Thanks,
> 
> J. R.

As others have observed, this is an unwise design, not least that any code
written will have to 'edit' in the city table name by program rather than
having it manipulable by SQL statements and 'bound' values.

Like sets of items ("entity/relationship sets") should go into the
same table with a column/field value to distinguish the instances.
You can easily say "... WHERE City='Boston' ..." or whatever.

Another way of putting this is that separate tables should not
usually be used just to group sets of otherwise 
identically-structured data records.

Search keywords: database schema design entity relationship

Regards,
MikeW

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

Re: [sqlite] problem with view

[P Kishor]

On 1/26/09, MEVEL Marie (PDL/ELOG PDL)  wrote:
> Hello. I use SQlite and i create a database since 3 week. I'm never
>  have problem but yesterday, i consult my database and one table  doesn't
>  want to show it. When i want to see data to this table, the message is :
>  "there are no  items to show in this view". I search in the internet but
>  i don't find  solution to my problem. Can you help me please ?!
>
>  ps : i'm french, so , do you want use simple language to answer me
>  please.
>


Please provide the following information --

1. The operating system and sqlite version you are using;

2. What program you use to look at your data. In other words, how do
you "consult" your database.

3. Your database schema, that is, the table definitions;

4. How many records do you expect to see in the table that "shows" no records;

5. What SQL query do you use to see the records in that table;



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

[RB Smissaert]

Thanks; will do that.

RBS

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW
Sent: 26 January 2009 12:02
To: sqlite-users@sqlite.org
Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock?

RB Smissaert  writes:

> 
> Thanks for the tip, but does that work on a Windows Mobile device?
> 
> RBS
Don't know - also look at http://www.freeotfe.org/
(Google: encrypted files windows mobile)

Cheers,
MikeW

___
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] Lock SQLite file by overwriting bytes and then un-lock?

[MikeW]

RB Smissaert  writes:

> 
> Thanks for the tip, but does that work on a Windows Mobile device?
> 
> RBS
Don't know - also look at http://www.freeotfe.org/
(Google: encrypted files windows mobile)

Cheers,
MikeW

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

Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock?

[RB Smissaert]

Thanks for the tip, but does that work on a Windows Mobile device?

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW
Sent: 26 January 2009 11:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock?

RB Smissaert  writes:

> 
> For some reason this mail went to the junk mail folder.
> That sounds good and I would be happy to buy this, but I am not sure
about:
> > All you need to do is replace the DLL
> I have number of dll's:
> For the desktop: an ActiveX dll and a plain Windows dll. The ActiveX is
used
> by my VBA/VB6 app and that ActiveX uses the plain Windows dll. These files
> are compiled by Olaf Schmidt.
> On the WM6 device: A .net dll System.Data.SQLite.DLL and a little
Basic4PPC
> dll, SQLDevice.dll, which I think is also a .net dll. These files are
> compiled by Erel, the author of Basic4PPC.
> How would SEE fit in with all this?
> 
> RBS

Since you are running Windows, I would have the app and standard SQLite DB,
and all ancillary data, stored on an properly encrypted drive.

e.g. http://www.truecrypt.org/

You can't be too careful with sensitive data like this.

Regards,
MikeW




___
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] Lock SQLite file by overwriting bytes and then un-lock?

[MikeW]

RB Smissaert  writes:

> 
> For some reason this mail went to the junk mail folder.
> That sounds good and I would be happy to buy this, but I am not sure about:
> > All you need to do is replace the DLL
> I have number of dll's:
> For the desktop: an ActiveX dll and a plain Windows dll. The ActiveX is used
> by my VBA/VB6 app and that ActiveX uses the plain Windows dll. These files
> are compiled by Olaf Schmidt.
> On the WM6 device: A .net dll System.Data.SQLite.DLL and a little Basic4PPC
> dll, SQLDevice.dll, which I think is also a .net dll. These files are
> compiled by Erel, the author of Basic4PPC.
> How would SEE fit in with all this?
> 
> RBS

Since you are running Windows, I would have the app and standard SQLite DB,
and all ancillary data, stored on an properly encrypted drive.

e.g. http://www.truecrypt.org/

You can't be too careful with sensitive data like this.

Regards,
MikeW




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

[sqlite] problem with view

[MEVEL Marie (PDL/ELOG PDL)]

Hello. I use SQlite and i create a database since 3 week. I'm never
have problem but yesterday, i consult my database and one table  doesn't
want to show it. When i want to see data to this table, the message is :
"there are no  items to show in this view". I search in the internet but
i don't find  solution to my problem. Can you help me please ?!

ps : i'm french, so , do you want use simple language to answer me
please.

Thanks

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