RE: [sqlite] Re: Handling null characters in blob data

[Shivshankar Subramani - TLS , Chennai]

Hi Igor Tandetnik,

I did follow the procedure to store the blob data.I have no problem in
excuting it but my problem is that when my data is of the below type in
memory
PK.
...¸Zn5
>À~Õe..
.è.
..simpl
e.datUT
...¨¦YE
¨¦YE¨¦Y
í'KNÃ0
.?S6Hoe
*}ã.6U..

Where there are lots of null character in the buffer the sqlite fails to
take them as values and hence im having difficulty in storing this kind of
buffer.

With Regards,

SHIVSHANKER S.
HCL Technologies Limited,  
Chennai-600058
Tel: +91-44-43935000 
Extn-5029 Mobile-9884656906
Email: [EMAIL PROTECTED]



-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 13, 2006 10:00 PM
To: SQLite
Subject: [sqlite] Re: Handling null characters in blob data


Shivshankar Subramani - TLS , Chennai
<[EMAIL PROTECTED]> wrote:
> Hi all,
>
>> SQLite version 2.8 and earlier could not (easily) store binary data - 
>> data with embedded \000 characters.  Thus the encode/decode routines 
>> were provide to transform data so that it contained no \000 
>> characters.
>>
>> SQLite version 3.0 can store binary data without difficulty.
>
> This is what  I read in the site 
>  c.gmane.org/msg04332.html>
>
http://www.mail-archive.com/[EMAIL PROTECTED]
.org/msg04332.html
> . but
> i am having difficulty in storing data with null characters in it.Is
> there
> any specific method in which i can solve this problem?

Use a parameterized query, e.g.

update tablename set blobfield=:blob where id=:id

Use sqlite3_prepare / sqlite3_step / sqlite3_finalize to execute such a 
query. Use sqlite3_bind_blob to bind a binary buffer to BLOB parameter.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-
DISCLAIMER 
The contents of this e-mail and any attachment(s) are confidential and intended 
for the 

named recipient(s) only. It shall not attach any liability on the originator or 
HCL or its 

affiliates. Any views or opinions presented in this email are solely those of 
the author and 

may not necessarily reflect the opinions of HCL or its affiliates. Any form of 
reproduction, 

dissemination, copying, disclosure, modification, distribution and / or 
publication of this 

message without the prior written consent of the author of this e-mail is 
strictly 

prohibited. If you have received this email in error please delete it and 
notify the sender 

immediately. Before opening any mail and attachments please check them for 
viruses and 

defect.

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

RE: [sqlite] INSERT INTO with SELECT

[RB Smissaert]

OK, thanks for the reply.
I am using the same construction to write directly from Interbase to Access
and that works fine. I can make an ADO recordset first from the Interbase
data and write that to SQLite in a (double) loop, but it is a bit slow.
Maybe I should write to text first (which is quite fast) and then write to
SQLite. I am not sure though how to import text into SQLite.
Trouble with the SQLite ODBC driver is that documentation is a bit sparse.
Maybe I should use a transaction, but not sure how that works either.

RBS

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: 14 November 2006 03:24
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT INTO with SELECT

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> Trying to move data from Interbase to SQLite via the ODBC driver and ADO
and
> having trouble to get the right syntax for the INSERT INTO statement.
>
> This is what I have now, but it fails with the error: only one SQL
statement
> allowed.

I notice your code creates this sql:

 INSERT INTO  READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)
 SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60
 FROM READCODE R
IN " [  ? ] "

Which isn't valid.  The IN clause is part of WHERE, not a separate
qualifier.
It works like this:

select x as prime  from mytable  where x in ( 1,2,3,5,7 );

Since the select is reading from READCODE you're inserting values
into the same table you're selecting them from. There's no way that I know
of to use odbc to copy from one database to another unless they're both
of the same type.

I would do it by selecting all the values to copy in one statement and
writing
them to memory or a disk file. Then import to the other database in a second
step.




--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] Newbie sqlite questions: check existence of column

[Jay Sprenkle]

On 11/13/06, Florent THIERY <[EMAIL PROTECTED]> wrote:

- check first if the column already exists; that, i have no idea how to
achieve it


Hello Florent,
Try this:
select * from sqlite_master;
This gives a lot of interesting info about tables and indices.


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] INSERT INTO with SELECT

[Jay Sprenkle]

On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Trying to move data from Interbase to SQLite via the ODBC driver and ADO and
having trouble to get the right syntax for the INSERT INTO statement.

This is what I have now, but it fails with the error: only one SQL statement
allowed.


I notice your code creates this sql:

INSERT INTO  READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)
SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60
FROM READCODE R
IN " [  ? ] "

Which isn't valid.  The IN clause is part of WHERE, not a separate qualifier.
It works like this:

select x as prime  from mytable  where x in ( 1,2,3,5,7 );

Since the select is reading from READCODE you're inserting values
into the same table you're selecting them from. There's no way that I know
of to use odbc to copy from one database to another unless they're both
of the same type.

I would do it by selecting all the values to copy in one statement and writing
them to memory or a disk file. Then import to the other database in a second
step.




--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

[sqlite] Re: Trouble with Trigger

[Igor Tandetnik]

A.J.Millan <[EMAIL PROTECTED]> wrote:

I want know if is possible to do a DELETE, INSERT or UPDATE statement
from
inside a TRIGGER.


Yes. Otherwise a trigger would be rather pointless.


Let's say:

CREATE TABLE  tableA (
Id  INTEGER,
... );

CREATE TABLE  tableB (
Ref  INTEGER,
... );

CREATE  TRIGGER DeleteReferences BEFORE DELETE ON tableA
BEGIN
  SELECT CASE
  WHEN (SELECT count(*) FROM  tableB  WHERE  Ref  =  OLD.Id) != 0 THEN
RAISE(ABORT, 'Id field referenced in Table-B')
// These one Work!!
  WHEN (SELECT count(*)  FROM tableB  WHERE  Ref  =  OLD.Id) != 0 THEN
DELETE FROM  tableB  WHERE  Ref = OLD.Id
 // I get an error here
 END;
END;


You cannot put a DELETE statement inside a SELECT statement. Note that 
RAISE() is a function and may appear where an expression can appear. 
DELETE is a statement.


Make it

CREATE  TRIGGER DeleteReferences BEFORE DELETE ON tableA
BEGIN
  SELECT RAISE(ABORT, 'Id field referenced in Table-B')
  where exists (SELECT * FROM  tableB  WHERE  Ref  =  OLD.Id);

   DELETE FROM  tableB  WHERE  Ref = OLD.Id;
END;

If there are in fact no records in tableB that match the condition, 
DELETE will safely do nothing. You don't need an explicit check first.


Igor Tandetnik 



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

Re: [sqlite] Trouble with Trigger

[Dennis Cote]

A.J.Millan wrote:

I want know if is possible to do a DELETE, INSERT or UPDATE statement from
inside a TRIGGER.


Any idea in this respect, or alternative, will be grateful


  
See the documentation at 
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers for a complete 
description of such triggers.


HTH
Dennis Cote

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

[sqlite] Trouble with Trigger

[A.J.Millan]

I want know if is possible to do a DELETE, INSERT or UPDATE statement from
inside a TRIGGER.

Let's say:

CREATE TABLE  tableA (
Id  INTEGER,
... );

CREATE TABLE  tableB (
Ref  INTEGER,
... );

CREATE  TRIGGER DeleteReferences BEFORE DELETE ON tableA
BEGIN
   SELECT CASE
   WHEN (SELECT count(*) FROM  tableB  WHERE  Ref  =  OLD.Id) != 0 THEN
 RAISE(ABORT, 'Id field referenced in Table-B')
 // These one Work!!
   WHEN (SELECT count(*)  FROM tableB  WHERE  Ref  =  OLD.Id) != 0 THEN
 DELETE FROM  tableB  WHERE  Ref = OLD.Id
  // I get an error here
  END;
END;

What I want, to avoid future inconsistences, is to erase all the tableB rows
that contains references to the tableA row that will be eliminated.

I obtain same error whichever is the utilized sentence:  DELETE,  INSERT or
UPDATE.

Any idea in this respect, or alternative, will be grateful

A. J. Millan




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

[sqlite] INSERT INTO with SELECT

[RB Smissaert]

Trying to move data from Interbase to SQLite via the ODBC driver and ADO and
having trouble to get the right syntax for the INSERT INTO statement.

This is what I have now, but it fails with the error: only one SQL statement
allowed.


Sub InsertIntoSQLLite()

   Dim cn As SQLiteDb.Connection
   Dim objCommand As ADODB.Command
   Dim ADOConn2 As ADODB.Connection
   Dim strConn2 As String
   Dim strSQL As String

   Set ADOConn = New ADODB.Connection

   strConn2 = "ODBC;" & _
  "DB=localhost:C:\Torex\Synergy\Meddata\S6000\db\s6.gdb;" & _
  "DSN=System 6000;" & _
  "UID=un;" & _
  "PWD=pw;"

   'All this is only to create the .db file
   'must be a better way avoiding this non-ODBC connection
   '--
   Set cn = New SQLiteDb.Connection
   cn.ConnectionString = "Data
Source=C:\RBSSynergyReporting\ReadCodes\ReadCode.db"
   cn.Open
   cn.Close
   Set cn = Nothing

   SetADOConn

  strSQL = "CREATE TABLE READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)"

   Set objCommand = New ADODB.Command

   With objCommand
  .CommandText = strSQL
  .ActiveConnection = ADOConn
  .Execute
   End With

   Set ADOConn2 = New ADODB.Connection
   ADOConn2.Open strConn2

   strSQL = "INSERT INTO " & _
"READCODE " & _
"(SUBJECT_TYPE, READ_CODE, TERM30, TERM60) " & _
"SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60 " & _
"FROM READCODE R " & _
"IN  [" & strConn2 & "]"

   With objCommand
  .CommandText = strSQL
  .ActiveConnection = ADOConn
  .Execute    >> fails here << '''
   End With

End Sub


Any suggestions now this should be done?


RBS



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

[sqlite] Format change to fts2 module.

[Scott Hess]

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

This changes the storage to delta-encode docids, rather than storing
them in absolute form.  Unfortunately, since this changes the format
of the backing data, if you've been experimenting with fts2 databases,
you'll have to regenerate them (see the email snippet below).

I'm going to try to get a couple other format-changes checked in this
week, in the hopes of getting the fts2 file format frozen.

Thanks,
scott


On 10/12/06, Scott Hess <[EMAIL PROTECTED]> wrote:

Caveats: It uses a new storage format, and there is currently no
support for "upgrading" a table from fts1 to fts2.  Additionally, I
have a number of follow-on changes which will be going in over the
next couple weeks, some of which are likely to break data
compatibility.  I'm just tossing this out here in case people are
interested in experimenting with it.


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

RE: [sqlite] What is the best way to connect to SQLite from VB/VBA?

[RB Smissaert]

Have done one simple test now and for now it looks SQLiteDB is about twice
as fast as the ODBC driver. I am a novice with SQLite, so maybe my test is
no good and in that I am interested to know.
I have tested on a large file (few millions rows and 19 fields) and I have
set an index on the field to search. This is the relevant code:


Option Explicit
Private lStartTime As Long
Public Declare Function timeGetTime Lib "winmm.dll" () As Long
Private strConn As String
Private ADOConn As ADODB.Connection
Private cn As SQLiteDb.Connection

Sub StartSW()
   lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional ByRef strMessage As Variant = "")
   MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
End Sub

Sub SearchSQLite()

   Dim sqliteRS As SQLiteDb.Recordset
   Dim strSQL As String
   Dim arr

   'On Error GoTo ERROROUT

   SetSQLiteConn

   StartSW
   
   strSQL = "SELECT PATIENT_ID, TERM_TEXT, ADDED_DATE, START_DATE FROM
[ENTRY] WHERE TERM_TEXT like '%angina%'"

   Set sqliteRS = cn.Execute(strSQL)

   If sqliteRS.EOF Then
  Exit Sub
   End If

   arr = sqliteRS.GetRows

   StopSW

   MsgBox UBound(arr, 2) + 1

   Exit Sub
ERROROUT:

   MsgBox Err.Description & vbCrLf & vbCrLf & _
  "Error number: " & Err.Number & vbCrLf & _
  "Error line: " & Erl


End Sub

Sub SetADOConn()

   If ADOConn Is Nothing Then
  Set ADOConn = New ADODB.Connection
   End If

   If ADOConn.State = 0 Then
  'strConn =
"DSN=SQLite3;Database=C:\SQLite\Terra\rc2.db;SyncPragma=Off;"
  strConn = "Provider=MSDASQL.1;" & _
"Extended Properties=DSN=SQLite3;" & _
"Database=C:\SQLite\Terra\rc2.db;" & _
"StepAPI=0;" & _
"SyncPragma=Off;" & _
"NoTXN=0;" & _
"Timeout=10;" & _
"LongNames=0;" & _
"NoCreat=0"

  ADOConn.Open strConn
   End If

End Sub

Sub SetSQLiteConn()

   If cn Is Nothing Then
  Set cn = New SQLiteDb.Connection
   End If

   If cn.State = 0 Then
  cn.ConnectionString = "Data Source=C:\SQLite\Terra\rc2.db"
  cn.Open

  cn.Execute "PRAGMA synchronous=off;", , slExecuteNoRecords   ' Just to
speed up things
  cn.Execute "PRAGMA encoding='UTF-8';", , slExecuteNoRecords
   End If

End Sub

Sub SearchSQLiteODBC()

   Dim rs As ADODB.Recordset
   Dim strSQL As String
   Dim arr
   Dim i As Long
   Dim c As Long

   On Error GoTo ERROROUT

   SetADOConn

   StartSW

   'MsgBox ADOConn.ConnectionString

   strSQL = "SELECT PATIENT_ID, TERM_TEXT, ADDED_DATE, START_DATE FROM
[ENTRY] WHERE TERM_TEXT like '%angina%'"
   'strSQL = "SELECT PATIENT_ID, TERM_TEXT, ADDED_DATE, START_DATE FROM
ENTRY WHERE READ_CODE GLOB 'G3*'"

   Set rs = New ADODB.Recordset

   rs.Open Source:=strSQL, _
   ActiveConnection:=ADOConn, _
   CursorType:=adOpenForwardOnly, _
   LockType:=adLockReadOnly, _
   Options:=adCmdText

   If rs.EOF Then
  Set rs = Nothing
  Exit Sub
   End If

   arr = rs.GetRows

   StopSW

   MsgBox UBound(arr, 2) + 1

   Exit Sub
ERROROUT:

   MsgBox Err.Description & vbCrLf & vbCrLf & _
  "Error number: " & Err.Number & vbCrLf & _
  "Error line: " & Erl

End Sub


RBS


-Original Message-
From: Carlos Avogaro [mailto:[EMAIL PROTECTED] 
Sent: 13 November 2006 12:04
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is the best way to connect to SQLite from VB/VBA?

With the odbc driver, is ease and faster

RB Smissaert <[EMAIL PROTECTED]> wrote:  Have spent 2 days
looking at all the different wrappers and the one ODBC
driver and maybe the best one is the commercial dll from Terra...
Still, I would be very interested what opinions are about the best (speed,
ease of use, so methods etc. close to ADO) way to connect.
This is from VB6/VBA. The database I am working with is Interbase 5.6 and
I will have to move data from IB to SQLite.

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]

-



 
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.



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

Re: [sqlite] Database Corruption and fix suggsted

[drh]

"jayanth KP" <[EMAIL PROTECTED]> wrote:
> Hi Richard,
> I downloaded the sqlite-2.8.17.bin from sqlite.org (sqlite-2.8.17.bin.gz) 
> and ran it on media.vdb.corrupt. I am getting the following error . I am 
> confused how were you able to open this file. Plz open media..vdb.corrupt.
> 

Once a file gets corrupted, there is nothing that can magically
undo the corruption.  

What I did to open the file was put both the database as it
appeared on disk after the power failure, but before it was
opened by your broken version of sqlite, and the rollback
journal together in the same directory.  Then I opened the
database with a working version of SQLite and it recovered
the database correctly.

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


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

Re: [sqlite] so many warnings on vc6 with warning level 4

[Noel Frankinet]

Christian Smith a écrit :

Gunnar Roth uttered:


Hello,
i want to use sqlite3 in a sub-project at work.
when compiling with warning level 4 with vc6 ( internal philosophy ) , i
get over 480 warnings. With warning level 3 there are still 119 left.
This leads to problems to convince the project lead of the quality of
sqlite3 ( which i do not doubt, as i have used it for my private project
since some years).



I'd be more worried about using VC6 period. This is a really old 
release, and is unsupported by MS since something like 2003.
Sqlite is pure C, I would be surprised if the c compiler has changed so 
much since vc6 ... C++ and especially STL is another story.




Is there any chance this warnings will be fixed sometime in the future?
How are others dealing with this problem at work?



Use a current, supported compiler first, then check again.




regards,
gunnar



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





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

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








--
Noël Frankinet
Gistek Software SA
http://www.gistek.net


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

Re: Re: [sqlite] Database Corruption and fix suggsted

[jayanth KP]

Hi Richard,
I downloaded the sqlite-2.8.17.bin from sqlite.org (sqlite-2.8.17.bin.gz) 
and ran it on media.vdb.corrupt. I am getting the following error . I am 
confused how were you able to open this file. Plz open media.vdb.corrupt.

---
 ./sqlite-2.8.17.bin  /home/jayanth/to_drh/media.vdb.corrupt
Unable to open database "/home/jayanth/to_drh/media.vdb": file is encrypted or 
is not a database


Regards
Jayanth


On Mon, 13 Nov 2006 [EMAIL PROTECTED] wrote :
>"jayanth KP" <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I am using sqlite 2.8.13 which is supposed to have fix for "Corrupt 
> > problem encountered on Windows NT platform" check this 
> > (http://www.sqlite.org/cvstrac/tktview?tn=599). But the code in pager.c 
> > still has the following rc = write32bits(&pPager->jfd, pPager->noSync ? 
> > 0x : 0); . But as suggested in the fix it should have been rc = 
> > write32bits(&pPager->jfd, 0x); .
> >
> > I also saw 2.8.13 and 2.8.12 code in sqlite CVS. Even that does not have 
> > the suggested change
> >
> > Can anybody Please  explain ..
> >
>
>I took the after-power-failure sample database you sent me and
>opened it using SQLite version 2.8.17.  That worked great.  There
>were no errors.  PRAGMA integrity_check showed that the database
>was recovered intact.
>
>I suggest you download the latest 2.8.17 code and recompile
>using that.
>--
>D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

Re: [sqlite] so many warnings on vc6 with warning level 4

[Teg]

Hello Gunnar,

You can use this pragma to disable the warnings that don't actually
mean anything.

#pragma warning (disable: 4786 4666 4100 4786 4146)

I debug/develop using VC2005 and release using VC6.

C


Monday, November 13, 2006, 9:43:02 AM, you wrote:

GR> Hello,
GR> i want to use sqlite3 in a sub-project at work.
GR> when compiling with warning level 4 with vc6 ( internal philosophy ) , i
GR> get over 480 warnings. With warning level 3 there are still 119 left.
GR> This leads to problems to convince the project lead of the quality of
GR> sqlite3 ( which i do not doubt, as i have used it for my private project
GR> since some years).
GR> Is there any chance this warnings will be fixed sometime in the future?
GR> How are others dealing with this problem at work?

GR> regards,
GR> gunnar



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




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


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

Re: [sqlite] so many warnings on vc6 with warning level 4

[Christian Smith]

Gunnar Roth uttered:


Hello,
i want to use sqlite3 in a sub-project at work.
when compiling with warning level 4 with vc6 ( internal philosophy ) , i
get over 480 warnings. With warning level 3 there are still 119 left.
This leads to problems to convince the project lead of the quality of
sqlite3 ( which i do not doubt, as i have used it for my private project
since some years).



I'd be more worried about using VC6 period. This is a really old release, 
and is unsupported by MS since something like 2003.




Is there any chance this warnings will be fixed sometime in the future?
How are others dealing with this problem at work?



Use a current, supported compiler first, then check again.




regards,
gunnar



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



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

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

[sqlite] Re: Re: Newbie sqlite questions: check existence of column

[Igor Tandetnik]

Florent THIERY <[EMAIL PROTECTED]> wrote:

On 11/13/06, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:


Florent THIERY
<[EMAIL PROTECTED]>
wrote:

Some files have optional fields, so what i'd like to do is, whenever
such a file is scanned and parsed, i want my software to
- check first if the column already exists; that, i have no idea how
to achieve it


PRAGMA table_info: http://sqlite.org/pragma.html#schema


I don't quite get how to use these commands to do a trivial presence
test


PRAGMA table_info allows one to enumerate all the columns in a table. It 
should be trivial to check whether a particular column appears in this 
enumeration.



If i query something like:
" select * from jpgs where "/sof/field1/nb_comp" != NULL "

What result would i get if the column doesn't exist yet?


If the column "/sof/field1/nb_comp" does not exist yet, the query will 
fail to compile (sqlite3_prepare will fail).



In any case, I don't quite see how this is related to the issue of

determining the existence of a column.



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

" After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier until the database is
VACUUMed.
"

Is it mandatory then?


Apparently yes, if you want the database to be readable by SQLite 
version 3.1.3 and earlier. VACUUM works basically by creating a new 
table with the same structure as the old one, copying all the data over, 
and finally dropping the old table. I guess ALTER TABLE is implemented 
with some data structure (a linked list of columns or something) that 
older SQLite versions don't understand. By completely rebuilding a table 
from scratch, VACUUM also removes this data structure.


And I was wrong: you can vacuum an individual table, and even an 
individual index.


Igor Tandetnik 



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

[sqlite] Re: Handling null characters in blob data

[Igor Tandetnik]

Shivshankar Subramani - TLS , Chennai
<[EMAIL PROTECTED]> wrote:

Hi all,


SQLite version 2.8 and earlier could not (easily) store binary
data - data with embedded \000 characters.  Thus the encode/decode
routines were provide to transform data so that it contained no
\000 characters.

SQLite version 3.0 can store binary data without difficulty.


This is what  I read in the site

http://www.mail-archive.com/[EMAIL PROTECTED]/msg04332.html
. but
i am having difficulty in storing data with null characters in it.Is
there
any specific method in which i can solve this problem?


Use a parameterized query, e.g.

update tablename set blobfield=:blob where id=:id

Use sqlite3_prepare / sqlite3_step / sqlite3_finalize to execute such a 
query. Use sqlite3_bind_blob to bind a binary buffer to BLOB parameter.


Igor Tandetnik 



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

[sqlite] Re: so many warnings on vc6 with warning level 4

[Igor Tandetnik]

Clay Dowling  wrote:

Second, examine the actual warnings, not just the count.  You'll find
that a large number of them are warnings about deprecated library
methods such as strcpy.


Not with VC6 (which the OP is using). These deprecation warnings are new 
with VC8 (aka VC 2005), and are easy to turn off by defining 
_CRT_SECURE_NO_DEPRECATE macro. For more details, see


http://msdn2.microsoft.com/en-us/library/8ef0s5kh(VS.80).aspx

Igor Tandetnik 



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

[sqlite] Handling null characters in blob data

[Shivshankar Subramani - TLS , Chennai]

Hi all,
 
> SQLite version 2.8 and earlier could not (easily) store binary
> data - data with embedded \000 characters.  Thus the encode/decode
> routines were provide to transform data so that it contained no
> \000 characters.
>
> SQLite version 3.0 can store binary data without difficulty.

This is what  I read in the site

http://www.mail-archive.com/sqlite-users@sqlite.org/msg04332.html  . but
i am having difficulty in storing data with null characters in it.Is there
any specific method in which i can solve this problem?
 
Thanks in advance
 

With Regards,


SHIVSHANKER S.


HCL Technologies Limited,  

Chennai-600058
Tel: +91-44-43935000 

Extn-5029 Mobile-9884656906
Email: [EMAIL PROTECTED]  

 
DISCLAIMER 
The contents of this e-mail and any attachment(s) are confidential and intended 
for the 

named recipient(s) only. It shall not attach any liability on the originator or 
HCL or its 

affiliates. Any views or opinions presented in this email are solely those of 
the author and 

may not necessarily reflect the opinions of HCL or its affiliates. Any form of 
reproduction, 

dissemination, copying, disclosure, modification, distribution and / or 
publication of this 

message without the prior written consent of the author of this e-mail is 
strictly 

prohibited. If you have received this email in error please delete it and 
notify the sender 

immediately. Before opening any mail and attachments please check them for 
viruses and 

defect.

Re: [sqlite] so many warnings on vc6 with warning level 4

[Clay Dowling]

First, I use the pre-built DLL from the SQLite web site, and then use my
compiler's library import utility to generate the appropriate import lib. 
That saves me all the warnings.

Second, examine the actual warnings, not just the count.  You'll find that
a large number of them are warnings about deprecated library methods such
as strcpy.  The fact that Microsoft has chosen to deprecate standard
library functions is a Microsoft issue rather than an SQLite issue.  It's
true that a lot of these functions are dangerous in inexperienced and
careless hands.  It's also true that they're very safe and have been used
successfully for 30 years by programmers who understand their limitations
and use them appropriately.

Examine the warnings, look at the code around them, see if there is any
merit to the warnings or if it's more of a chicken little situation.  If
you can't check all 600, spot check several which you find at random.  And
remember that there's nothing wrong with strcpy and strcat, as long as the
destination buffer is large enough for the string being received.  Look
for the allocation of that buffer and see how it's come about and how the
size is checked.

Clay Dowling

Gunnar Roth said:
> Hello,
> i want to use sqlite3 in a sub-project at work.
> when compiling with warning level 4 with vc6 ( internal philosophy ) , i
> get over 480 warnings. With warning level 3 there are still 119 left.
> This leads to problems to convince the project lead of the quality of
> sqlite3 ( which i do not doubt, as i have used it for my private project
> since some years).
> Is there any chance this warnings will be fixed sometime in the future?
> How are others dealing with this problem at work?
>
> regards,
> gunnar
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Simple Content Management
http://www.ceamus.com


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

Re: [sqlite] so many warnings on vc6 with warning level 4

[drh]

Gunnar Roth <[EMAIL PROTECTED]> wrote:
> Hello,
> i want to use sqlite3 in a sub-project at work.
> when compiling with warning level 4 with vc6 ( internal philosophy ) , i
> get over 480 warnings. With warning level 3 there are still 119 left.
> This leads to problems to convince the project lead of the quality of
> sqlite3 ( which i do not doubt, as i have used it for my private project
> since some years).
> Is there any chance this warnings will be fixed sometime in the future?

You'll have to talk with the compiler people at Microsoft about
that.  I consider those warnings problems with the compiler, not
problems with SQLite.

> How are others dealing with this problem at work?
> 

The regression test suite for SQLite has like 98% coverage. 
High-coverage testing like this is a much, much better way
to find problems that compiler warnings.  My suggestion is
that you turn all the compiler warnings off when compiling
SQLite.

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


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

[sqlite] so many warnings on vc6 with warning level 4

[Gunnar Roth]

Hello,
i want to use sqlite3 in a sub-project at work.
when compiling with warning level 4 with vc6 ( internal philosophy ) , i
get over 480 warnings. With warning level 3 there are still 119 left.
This leads to problems to convince the project lead of the quality of
sqlite3 ( which i do not doubt, as i have used it for my private project
since some years).
Is there any chance this warnings will be fixed sometime in the future?
How are others dealing with this problem at work?

regards,
gunnar



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

Re: [sqlite] Re: Newbie sqlite questions: check existence of column

[Florent THIERY]

Thanks for this quick reply :)

On 11/13/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Florent THIERY <[EMAIL PROTECTED]> wrote:
> Some files have optional fields, so what i'd like to do is, whenever
> such a file is scanned and parsed, i want my software to
> - check first if the column already exists; that, i have no idea how
> to achieve it

PRAGMA table_info: http://sqlite.org/pragma.html#schema



I don't quite get how to use these commands to do a trivial presence test

If i query something like:
" select * from jpgs where "/sof/field1/nb_comp" != NULL "

What result would i get if the column doesn't exist yet?



I read something about vaccuming the table? What about it?

You can't vacuum a table, you can vacuum an entire database. VACCUUM
command cleans up free space possibly left after deleted records, making
DB file smaller. It should be noted that this free space will eventually
be used by new records, but for some usage patterns it may still be
useful to vacuum (e.g. the application usually keeps a small amount of
data, but it has spikes where it adds a large number of records then
deletes most of them).





In any case, I don't quite see how this is related to the issue of

determining the existence of a column.



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

" After ADD COLUMN has been run on a database, that database will not be
readable by SQLite version 3.1.3 and earlier until the database is
VACUUMed.
"

Is it mandatory then?

Re: [sqlite] sqlite3_table_column_metadata????

[Trevor Talbot]

On 11/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


if(sqlite3_table_column_metadata(handle,"temp.db",


"temp.db" should be either "main" or NULL.  It's used to identify
ATTACHed databases (by alias).

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

[sqlite] Re: Newbie sqlite questions: check existence of column

[Igor Tandetnik]

Florent THIERY <[EMAIL PROTECTED]> wrote:

Some files have optional fields, so what i'd like to do is, whenever
such a file is scanned and parsed, i want my software to
- check first if the column already exists; that, i have no idea how
to achieve it


PRAGMA table_info: http://sqlite.org/pragma.html#schema


I read something about vaccuming the table? What about it?


You can't vacuum a table, you can vacuum an entire database. VACCUUM 
command cleans up free space possibly left after deleted records, making 
DB file smaller. It should be noted that this free space will eventually 
be used by new records, but for some usage patterns it may still be 
useful to vacuum (e.g. the application usually keeps a small amount of 
data, but it has spikes where it adds a large number of records then 
deletes most of them).


In any case, I don't quite see how this is related to the issue of 
determining the existence of a column.


Igor Tandetnik 



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

[sqlite] Re: Newbie sqlite questions: check existence of column

[Florent THIERY]

I'd add :

python


import sqlite
sqlite.version

'1.0.1'

[sqlite] Newbie sqlite questions: check existence of column

[Florent THIERY]

Hi

First of all i must say i'm pretty much impressed of how easy it is to buid
a sqlite db up within minutes/hours of work. I'm... amazed :)

Still, i'm lacking answears.

My (soon-to-be-open-source-when-there-will-be-interesting-source-to-open)
project is the following:

i scan for files recursively (jpeg files) and parse it's header. Every piece
of the header means something, represented by an absolute path (such as
"/sof/content/nr_components" ). I'd like to automatically fill a local db
with these parameters, with:
- the column name = the absolute path
- a line = a file

Some files have optional fields, so what i'd like to do is, whenever such a
file is scanned and parsed, i want my software to
- check first if the column already exists; that, i have no idea how to
achieve it
- if not, create the table :
  cur.execute("alter table jpgs add column %s" % (field.path) )
- then add the parameter value to the (new or not) column; but i don't want
a new line to be created every time; is an "update" query the good one?

I read something about vaccuming the table? What about it?
Should i rather parse the entire file and commit the summary to the table?
How to add columns?

Thanks a lot for your help

Regards

Florent

[sqlite] Re: SPAM-LOW: [sqlite] Re: Changing order - why??

[jphillip]

On Sun, 12 Nov 2006, Igor Tandetnik wrote:

> Ralph Wetzel <[EMAIL PROTECTED]> wrote:
> > might it be possible, that someone explains, why anyone would put
> > effort in this task? Aren't databases designed to keep data in whatever
> > order... :-?
> 
> Imagine you want to store playlists in the database (lists of songs to play).
> You may want to allow the user to specify which order the songs are to be
> played in, which may or may not be the order they were added to the playlist.
> Exercise for the reader: design database schema for this application.
> 
> Igor Tandetnik 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 

Add another column for the play list order and "order by" on that column.


You have to be BRAVE to grow OLD.
There are no old CARELESS pilots or electricians.


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

RE: [sqlite] sqlite3_table_column_metadata????

[ravi.karatagi]

HI All,
Below is the code.
get_table_data() is giving the expected values.Where as I am not able
get meta info of the same.

sqlite3 *handle;
char *errmsg;
const char *datatype;
const char *colseq;
intNotNull,PrimaryKey,Autoinc;
const char *err;
   

   
if(sqlite3_open("temp.db",&handle)==SQLITE_OK)

{
sqlite3_exec(handle,"create table person (integer pid,text
name)",NULL,0,&errmsg);
sqlite3_exec(handle,"insert into person
values(1,\"Ravi\")",NULL,0,&errmsg);
sqlite3_exec(handle,"insert into person
values(2,\"Anand\")",NULL,0,&errmsg);
sqlite3_exec(handle,"insert into person
values(3,\"Sujit\")",NULL,0,&errmsg);
if(sqlite3_table_column_metadata(handle,"temp.db",

"person","pid",&datatype,&colseq,&NotNull,&PrimaryKey,&Autoinc)==SQLITE_
OK)
cout<<"Success";
else

err=sqlite3_errmsg(handle);

Regards,
Ravi K

-Original Message-
From: Glenn [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 12, 2006 9:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_table_column_metadata

[EMAIL PROTECTED] wrote:
> Hi All,
>
> The function sqlite3_table_column_metadata() is giving the
> error message "No such TableName.ColName" even if I pass correct table
> name and col name
>
> (All the parameters are correct).
>
> Is there any problem with the API or Any extra care needs to be Taken?
> 
> Please let me Know ASAP. I am totally struck

Providing your code which is calling sqlite3_table_column_metadata()
would help.  Just saying that you are calling it correctly doesn't give
anyone a place to investigate; if you are calling everything correctly
then it should work.

--
Glenn McAllister <[EMAIL PROTECTED]>  +1 416 348 1594
SOMA Networks, Inc.  http://www.somanetworks.com/  +1 416 977 1414


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email.

www.wipro.com

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

RE: [sqlite] What is the best way to connect to SQLite from VB/VBA?

[RB Smissaert]

Thanks for that. I got this driver going now and will compare it with
SQLiteDb.dll from TerraInformatica.
Have you come across any problems with the ODBC driver? The author states
that there there could be quite a few like memory leaks.
Being able to use the same ADO methods etc. with the ODBC driver makes
things easier indeed.

RBS


-Original Message-
From: Carlos Avogaro [mailto:[EMAIL PROTECTED] 
Sent: 13 November 2006 12:04
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is the best way to connect to SQLite from VB/VBA?

With the odbc driver, is ease and faster

RB Smissaert <[EMAIL PROTECTED]> wrote:  Have spent 2 days
looking at all the different wrappers and the one ODBC
driver and maybe the best one is the commercial dll from Terra...
Still, I would be very interested what opinions are about the best (speed,
ease of use, so methods etc. close to ADO) way to connect.
This is from VB6/VBA. The database I am working with is Interbase 5.6 and
I will have to move data from IB to SQLite.

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]

-



 
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.



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

Re: [sqlite] Database Corruption and fix suggsted

[drh]

"jayanth KP" <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I am using sqlite 2.8.13 which is supposed to have fix for "Corrupt 
> problem encountered on Windows NT platform" check this 
> (http://www.sqlite.org/cvstrac/tktview?tn=599). But the code in pager.c still 
> has the following rc = write32bits(&pPager->jfd, pPager->noSync ? 0x 
> : 0); . But as suggested in the fix it should have been rc = 
> write32bits(&pPager->jfd, 0x); . 
> 
> I also saw 2.8.13 and 2.8.12 code in sqlite CVS. Even that does not have the 
> suggested change
> 
> Can anybody Please  explain ..
> 

I took the after-power-failure sample database you sent me and
opened it using SQLite version 2.8.17.  That worked great.  There
were no errors.  PRAGMA integrity_check showed that the database
was recovered intact.

I suggest you download the latest 2.8.17 code and recompile
using that.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] What is the best way to connect to SQLite from VB/VBA?

[Carlos Avogaro]

With the odbc driver, is ease and faster

RB Smissaert <[EMAIL PROTECTED]> wrote:  Have spent 2 days looking at all the 
different wrappers and the one ODBC
driver and maybe the best one is the commercial dll from Terra...
Still, I would be very interested what opinions are about the best (speed,
ease of use, so methods etc. close to ADO) way to connect.
This is from VB6/VBA. The database I am working with is Interbase 5.6 and
I will have to move data from IB to SQLite.

RBS



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



 
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.