Re: [sqlite] Windows performance problems associated with malloc()

[A.J.Millan]

From: "Richard Hipp" <d...@sqlite.org>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>; 
<sqlite-...@sqlite.org>
Sent: Friday, December 17, 2010 5:29 PM
Subject: [sqlite] Windows performance problems associated with malloc()


> An SQLite user has brought to our attention a performance issue in SQLite
> that seems to be associated with malloc().  If you have  insights or
> corroborating experience with this issue please let me know.
>

I have a large Windows application that uses SQLite extensively. Some weeks 
ago, suddenly began to almost hang in certain operations, where delayed 
minutes to complete some step that usually take less than a second.

I think that the problem was in a malloc operation in a associated 
library -OpenOffice's Hunspell-. The problem appeared consistently when 
running in Windows XP, but the same .exe run always perfectly in Windows 7.

Some days ago, the problem vanished as suddenly as it appeared, and I 
suspect that both times was the effect of one of the regular Windows's 
updates.

HTH.

A.J.Millan

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

Re: [sqlite] When to close a db?

[A.J.Millan]

Some time ago, I had a similar error because included a reference to a 
pointer

sqlErrMsgPtr = (CHAR *) sqlite3_errmsg (dbPtr)

in my SQLite class destructor.

As someone has suggested, does look to be associated with a dangling 
pointer.

HTH

A.J. Millan

- Original Message - 
From: "Sam Carleton" 
To: "General Discussion of SQLite Database" 
Sent: Sunday, June 20, 2010 11:17 PM
Subject: [sqlite] When to close a db?


>I am getting some strange behavior out of my app, which happens to be
> both an Apache module and some Axis2/C Web Services which run under
> Apache.  From time to time, it is VERY inconsistent, when the code
> calls sqlite3_close() the Apache server crashes.  I don't recall the
> error right off.
>
> From reading the documentation, it looks to me like sqlite3_close()
> should be called if *ppDb has a value, irregardless of the result code
> from the sqlite3_open_v2() call.  Is there more to it?
>
> Sam
> ___
> 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] Fw: What languages can include SQLite statically?

[A.J.Millan]

>
> Is MS Visual Studio the recommended solution to compile present-day
> SQLite, or are there lighter, open-source compilers that I could use
> instead?
>

Some time ago I used DevC++ with SQLite (it is a Ligth API that uses the GNU 
tool chain), but if your planned target is M$, the definitive choice is 
Visual Studio Express.

A.J.Millan

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

Re: [sqlite] What languages can include SQLite statically?

[A.J.Millan]

Not to say that in PHP you can't link any thing. But perhaps respond to your 
condition of a simpler language than C that would still be able to include 
SQLite.

There you end with a single script, not an stand alone executable.

A.J.Millan

- Original Message - 
From: "Gilles Ganault" <gilles.gana...@free.fr>
To: <sqlite-users@sqlite.org>
Sent: Friday, May 21, 2010 11:31 AM
Subject: [sqlite] What languages can include SQLite statically?


> Hello
>
> My C skills are very basic. I was wondering: After compiling SQLite
> into an .OBJ or .LIB file, what languages can be used to include this
> output into a main program, so we end up with a single EXE.
>
> I assume we have the choice of:
> - C
> - C++
> - Delphi (?)
> - Other?
>
> Thank you.
>
> ___
> 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] Fw: What languages can include SQLite statically?

[A.J.Millan]

Due the fact that you already know C and as my 2 cents to the question, 
depending on your requirements, perhaps would have a look to PHP.

As far as I know, you can use directly SQLite from that language and perhaps 
you find it simple to use; easy to port between platforms and direct and 
easy to debug.

HTH

A.J.Millan

- Original Message - 
From: "Gilles Ganault" <gilles.gana...@free.fr>
To: <sqlite-users@sqlite.org>
Sent: Friday, May 21, 2010 11:31 AM
Subject: [sqlite] What languages can include SQLite statically?


> Hello
>
> My C skills are very basic. I was wondering: After compiling SQLite
> into an .OBJ or .LIB file, what languages can be used to include this
> output into a main program, so we end up with a single EXE.
>
> I assume we have the choice of:
> - C
> - C++
> - Delphi (?)
> - Other?
>
> Thank you.
>
> ___
> 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] New book about SQLite

[A.J.Millan]

Perhaps some may find interesting email I just received from Amazon.

As someone who has purchased or rated The Definitive Guide to SQLite by Mike 
Owens, you might like to know that Inside Symbian SQL: A Mobile Developer's 
Guide to SQLite will be released on April 26, 2010.

Product Description
This is the definitive guide for Symbian C++ developers looking to use 
Symbian SQL in applications or system software.

Since Symbian SQL and SQLite are relatively new additions to the Symbian 
platform, Inside Symbian SQL begins with an introduction to database theory 
and concepts, including a Structured Query Language (SQL) tutorial.

Inside Symbian SQL also provides a detailed overview of the Symbian SQL 
APIs. From the outset, you will "get your hands dirty" writing Symbian SQL 
code. The book includes snippets and examples that application developers 
can immediately put to use to get started quickly.

A.J.Millan



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

Re: [sqlite] Fine-grainy error report needed

[A.J.Millan]

Jay:

You are abolutely rigth.

All my problem would be avoided if I do a error check in each individual 
bind. In fact the problem was a SQLITE_RANGE error.

Sure that some days is best go to the cinema when the mind is closed.

A.J. Millan

- Original Message - 
From: "Jay A. Kreibich" <j...@kreibi.ch>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, November 16, 2009 4:54 PM
Subject: Re: [sqlite] Fine-grainy error report needed


> On Mon, Nov 16, 2009 at 11:32:59AM +0100, A.J.Millan scratched on the 
> wall:
>> Martin:
>>
>> Thanks for the imput.  In fact, afther some years using SQLite I'v spent
>> almos a weekend because don't remember that the index in the
>> sqlite_column_xxx(sqlite3_stmt*,  iCol ) where I read the data from the
>> source table, "the leftmost column of the result set has the index 0", 
>> and
>> in the bind sentence,  "The leftmost SQL parameter has an index of 1"  -I 
>> do
>> both in the same loop-.
>
>> Any way, the question remains, and the method proposed by Owen perhaps is
>> good for debug, but not as a standard method.
>
>  Try checking the return codes from sqlite3_bind_xxx().  When you
>  attempted to bind something to parameter zero, the bind should have
>  returned SQLITE_RANGE.  Error codes only work if you check them.
>
>  Otherwise, the other answers are correct.  A quick scan of the code
>  makes me thing the only column capable of returning SQLITE_MISMATCH
>  is a ROWID or INTEGER PRIMARY KEY alias, and there can only be one of
>  those per table, and there can only be one table per INSERT.
>
>   -j
>
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
> a protractor."   "I'll go home and see if I can scrounge up a ruler
> and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] Fine-grainy error report needed

[A.J.Millan]

Igor:

As usual, you're right.

The only problem is that I think that generally, the type of SQL that you 
use is not too common, at least among those of us who do not have much 
practice with the language. At least I tend to get that type of expressions 
away of the SQL query. And I suppose that in my case, the C++ compiler is 
faster that the SQL engine.

Any way, again from a theoretical point of view you are right. If you could 
inspect my code I'm sure that you become horrified with the convolutions I 
need because my lack of an in deep SQL knowledge.

A.J.Millan

- Original Message - 
From: "Igor Tandetnik" <itandet...@mvps.org>
To: <sqlite-users@sqlite.org>
Sent: Monday, November 16, 2009 3:29 PM
Subject: Re: [sqlite] Fine-grainy error report needed


> A.J.Millan wrote:
>> Supposing a dBase without constraints (some like this but not limited to
>> it):
>>
>> CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc 
>> REAL,
>> Dm REAL, St INTEGER);
>>
>> A query to poblate:
>>
>> "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)"
>>
>> Then six binds (i = 0 to 5):
>>
>> sqlite3_bind_xxx (pStmt, i, ...);
>>
>> and a "step":  resp = sqlite3_step(pStmt);.
>>
>> Then, if resp ==  SQLITE_MISMATCH (Data type mismatch)
>>
>> Do is there some way to know the index (i) of the offending bind?
>
> It's not quite clear what you expect SQLite to report, in general. 
> Consider:
>
> insert into tbl(id) values (? + ?);
> insert into tbl(id) values (case when ? then ? else ? end);
>
> Which bind parameter is the offending one?
>
> Igor Tandetnik
>

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

Re: [sqlite] Fine-grainy error report needed

[A.J.Millan]

He list:

My apology for respond to myself:

Perhaps can be the matter a good candidate to be included in the Extended 
Error Codes?

Greetings
A.J. Millan

- Original Message - 
From: "A.J.Millan" <a...@zator.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, November 16, 2009 11:32 AM
Subject: Re: [sqlite] Fine-grainy error report needed


>
> Any way, the question remains, and the method proposed by Owen perhaps is
> good for debug, but not as a standard method.
>
> A.J. Millan
>



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

Re: [sqlite] Fine-grainy error report needed

[A.J.Millan]

Martin:

Thanks for the imput.  In fact, afther some years using SQLite I'v spent 
almos a weekend because don't remember that the index in the 
sqlite_column_xxx(sqlite3_stmt*,  iCol ) where I read the data from the 
source table, "the leftmost column of the result set has the index 0", and 
in the bind sentence,  "The leftmost SQL parameter has an index of 1"  -I do 
both in the same loop-.

Any way, the question remains, and the method proposed by Owen perhaps is 
good for debug, but not as a standard method.

A.J. Millan



- Original Message - 
From: "Martin Engelschalk" <engelsch...@codeswift.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, November 16, 2009 10:52 AM
Subject: Re: [sqlite] Fine-grainy error report needed


> Hi,
>
> First, the index of the bind variable (second parameter to
> sqlite3_bind_, your 'i') must begin with 1, and not 0, see
> http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
>
> Sqlite does not use strong typing, which means that you can put any data
> into any column.
> However INTERGER PRIMARY KEY is an exception and must be an integer.
> Therefore, the error most probably comes from your 'Id' - column.
>
> Martin
>
> A.J.Millan wrote:
>> Hi list:
>>
>> Supposing a dBase without constraints (some like this but not limited to
>> it):
>>
>> CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc 
>> REAL,
>> Dm REAL, St INTEGER);
>>
>> A query to poblate:
>>
>> "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)"
>>
>> Then six binds (i = 0 to 5):
>>
>> sqlite3_bind_xxx (pStmt, i, ...);
>>
>> and a "step":  resp = sqlite3_step(pStmt);.
>>
>> Then, if resp ==  SQLITE_MISMATCH (Data type mismatch)
>>
>> Do is there some way to know the index (i) of the offending bind?
>>
>> By the way: I'm trying to make a general function to import (duplicate) 
>> data
>> from an attached table.
>>
>> Thanks in advance.
>>
>> A.J.Millan
>>
>> ___
>> 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] Fine-grainy error report needed

[A.J.Millan]

Hi list:

Supposing a dBase without constraints (some like this but not limited to 
it):

CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc REAL, 
Dm REAL, St INTEGER);

A query to poblate:

"INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)"

Then six binds (i = 0 to 5):

sqlite3_bind_xxx (pStmt, i, ...);

and a "step":  resp = sqlite3_step(pStmt);.

Then, if resp ==  SQLITE_MISMATCH (Data type mismatch)

Do is there some way to know the index (i) of the offending bind?

By the way: I'm trying to make a general function to import (duplicate) data 
from an attached table.

Thanks in advance.

A.J.Millan

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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

- Original Message - 
From: "John Crenshaw" <johncrens...@priacta.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Thursday, October 29, 2009 10:55 PM
Subject: Re: [sqlite] Some clarification needed about Unicode


>No, I mean which encoding. You can't give a UTF-16 string to an API that
>only knows how to handle UCS-2 encoded data, just like you can't use a
>UTF-8
>string when ASCII data is expected. When I tackle this nightmare the last
>time I was left with the understanding that the wide Win32 APIs expected
>data to be UCS-2 encoded. Now I'm no longer sure, and I can't find any
>reliable documentation on this either way. It would be good if the APIs
>accept UTF-16, because that would mean they also accept UCS-2, but I
>couldn't find anything reliable to support this idea. Some folks say yes.
>Some say no. The documentation says nothing.

John:

Perhaps this page do some clarification on the point. Anyway, the only clear
is that the matter is not absolutely clear. Of course that is a big
consolation those us who feel lost the first time who went to this oddity
matter of the internationalization.

http://codesnipers.com/?q=utf-8-versus-windows-unicode

The author asset that .NET is the only platform that offer full UTF-16
support in the Windows API.

A.J.Millan.

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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

Jean-Christophe:

Thanks for your advice -very helpful and ilustrative for my-.

Fortunately I'm not in the same horror history thank to the people in this 
list, and because I use the old and simple way: say plain SQLite C 
interface; plain Windows32 C API and plain Visual Cpp (Multibyte build in 
the existing versions, and Unicode build for the new international release 
of the app).

Despite of that, I'm aware that I have some more that pure US-ASCII in the 
blob objects, in fact I'm near your situation because used the Spanish 
languaje and have 8-bit extended ASCII with some special 
characters -accented characters and so-.

So the question is Yes, I have upper-ANSI data stored, and need convert it 
to MS VCpp w_char strings to rebuild the dBase. In this point I plain use 
mbstowcs() to do the thing.

Because there are some users in the field, the new app can detect if the 
dBase correspond to a previous version and rebuild it to the new version if 
needed. So I can be almost sure that the user use the same system code page 
that the one used when the data was
originally inserted.

Do is there some weird in my tought?

A.J. Millan


- Original Message - 
From: "Jean-Christophe Deschamps" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, October 29, 2009 3:04 PM
Subject: Re: [sqlite] Some clarification needed about Unicode


Hi,


Please, follow Igor advices, he is right.


>[1] Read the actual textual data with sqlite3_column_blob()

Which you can directly convert to TEXT if, as you say, you entered only
7-bit ASCII or UTF-8 compliant data.

>[2] Assuming the system code page matches the one used when the data was
>originally inserted, convert with mbstowcs()

Forget that.

>[3] (Doubt) The result can be directly written with
>sqlite3_bind_text() -I
>want store in UTF-8-

Why not convert the column(s) directly from blob to TEXT?

>OR must I write the result with sqlite3_bind_text16()? Them, the data is
>stored as UTF-16? or as UTF-8?

Forget this too.

>[4] Afterward once converted the dBase

I take his to mean "converts your blobs to TEXT" : yes, this is the
_only_ step you need for the whole thing.

>  and in regular use:
>
>[4-1a] Read with sqlite3_column_text()
>
>[4-1b] convert with WideCharToMultiByte(CP_UTF8)

Why?  Read it off as text16 direct into you cpp string , there must
exist zillions [working] wrappers to VC++.

>[4-1c] Use the result with Win32 api -SetTex()-

No particular API.  Try a simple MsgBox to see by yourself, or even a
basic "Héllô wörld!" as a Windows _console_ (printf).

>OR?
>
>[4-2a] Read with sqlite3_column_text16()
>[4-2b] No convertion needed.
>[4-2c] Use the result ...

YES !!!

The only catch would be if you have (knowingly or not) entered 8-bit
data (the upper 128 characters in the Win codepage) encoded in __ANSI__
(1 char = 1 byte) and stored in your blobs.  In such case, the
representation of the characters is not the expected UTF-8.

As far as I understand it, current SQLite _should_ read/write
non-compliant UTF-8 without problem, but I didn't checked that latest
versions are still byte-neutral.  But it is not the right lane.  If you
have upper_ANSI strings, convert them to UTF-*, where * is your
encoding choice for the new database.


I've spend indecent time sorting out all these questions, just because
someone decided to silently convert UTF-16 native Windows stings to
ANSI for every SQLite UTF-8 interface in the SQLite wrapper built into
the development tool I use.

It was very hard for me to figure this out, because a call similar to
printf _also_ converted to ANSI (silently) and a call used to display a
2D table _also_ converted to ANSI (silently). So sometimes I had French
and other European diacritics converted (i.e. completely destroyed)
like this: UTF-16 --> ANSI --> UTF-16 --> ANSI.

It was just like if I tried to read original plain text by just looking
at its MD5.  It's been a _real_ nightmare and I had data from 15
countries, some using "weird" (to me) scripts.



But you're not even close to this terrible situation.

Just determine if you have upper-ANSI data stored and convert it if needed.

Well, stay tuned, I'll do something for you, just allow me some time...

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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

- Original Message - 
From: "Igor Tandetnik" <itandet...@mvps.org>
To: <sqlite-users@sqlite.org>
Sent: Thursday, October 29, 2009 1:45 PM
Subject: Re: [sqlite] Some clarification needed about Unicode


>
> The only Win32 API function that can handle UTF-8 strings is 
> MultiByteToWideChar (when called with CP_UTF8 flag). You should have a 
> Unicode build, where Win32 API takes UTF-16.
>
>> OR?
>>
>> [4-2a] Read with sqlite3_column_text16()
>> [4-2b] No convertion needed.
>> [4-2c] Use the result ...
>
> Yes, this should work, assuming that "use the result" part actually knows 
> how to deal with UTF-16.
>

Thanks again.

Indeed I forgot mention that I assume using a Unicode build in my app.

Also your clarification

>The result can be passed to sqlite3_bind_text16. If the database's encoding 
>is in fact UTF-8
> (determined when the database file is originally created), SQLite will 
> convert your
> string to UTF-8 automatically. If the database's encoding is UTF-16,
> the string will be inserted as-is.

>The original call to sqlite3_open{16,_v2} which created the database file 
>in the
> first place determines its encoding. After that, it doesn't matter which 
> API calls
> you use: SQLite will convert strings to the database encoding as 
> necessary.
> Pass UTF-16 strings to *16 versions of API accepting void*, UTF-8 to
> versions accepting char*.

is invaluable

A.J.Millan


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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

Igor:

Thanks for your answer; let me see if I understood correctly the process:

[1] Read the actual textual data with sqlite3_column_blob()

[2] Assuming the system code page matches the one used when the data was 
originally inserted, convert with mbstowcs()

[3] (Doubt) The result can be directly written with sqlite3_bind_text() -I 
want store in UTF-8-

OR must I write the result with sqlite3_bind_text16()? Them, the data is 
stored as UTF-16? or as UTF-8?

[4] Afterward once converted the dBase and in regular use:

[4-1a] Read with sqlite3_column_text()

[4-1b] convert with WideCharToMultiByte(CP_UTF8)

[4-1c] Use the result with Win32 api -SetTex()-

OR?

[4-2a] Read with sqlite3_column_text16()
[4-2b] No convertion needed.
[4-2c] Use the result ...

The opposite to go from GetText() to dBase.

--
A.J.Millan

- Original Message - 
From: "Igor Tandetnik" <itandet...@mvps.org>
To: <sqlite-users@sqlite.org>
Sent: Thursday, October 29, 2009 12:51 PM
Subject: Re: [sqlite] Some clarification needed about Unicode


> A.J.Millan wrote:
>> Really, here you touched tangentially the core of my question. Besides 
>> all
>> those great theories, at last I have UTF-8 encoded data in a dBase, and 
>> the
>> UCS-2 encoded data of the MS Win32 API (w_chars in muy Cpp app). The
>> question is: What is the concrete way to and from that w_chars to the 
>> UTF-8
>> that need SQLite? (my guess is use UTF-8 to the stored data) Can I use
>> wcstombs() or even WideCharToMultiByte()? -this last seem a nightmare to
>> me-.
>
> Not wcstombs - it uses system default codepage, and that is never UTF-8. 
> You can use WideCharToMultiByte(CP_UTF8) - I don't quite see why you find 
> it so offensive. You can use a third-party library if you are so inclined, 
> e.g. ICU. You can roll your own - a conversion between UTF-8 and UTF-16 is 
> pretty straightforward. Or, you can simply use SQLite's 16-bit API (e.g. 
> sqlite3_bind_text16) and have it convert to UTF-8 for you automatically.
>
>> Really, preventing future possible problems, my textual data was stored 
>> as
>> blob. The idea is rebuild converting from 8 bits ASCII to UTF-8. Then the
>> question: can be done with mbstowcs() prior to using sqlite3_bind_text()?
>
> You probably mean sqlite3_bind_text16. Yes, you can use mbstowcs, assuming 
> your system code page matches the one used when the data was originally 
> inserted.
>
> Igor Tandetnik

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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

John:

From: "John Crenshaw" <johncrens...@priacta.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Thursday, October 29, 2009 11:46 AM
Subject: Re: [sqlite] Some clarification needed about Unicode


>
> My main point is that you can't take the UTF-16 string and safely supply
> it to APIs which want UCS-2 encoded text, such as Win32 APIs (including
> things like SetWindowText()). Odds are that the only library you are
> using which supports UTF-16 is SQLite. You should always be converting
> the text to UCS-2 before you use it.
>

Here you address the core question. Thanks for your imput; also to the other 
senders.

Now, do you know about some library to conver to and from UTF-8 or UTF-16 to 
UCS-2?

Thanks again.

A.J.Millan

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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

John:

> 2. UTF-8 is NOT the same as ASCII for values greater than 127.
> Similarly, UTF-16 is NOT the same as UCS-2 (the wide "Unicode" chars
> used by MS APIs), though it looks the same at low values. UTF-16 is a
> multibyte character set, while UCS-2 is always 2 bytes per character.
> You have to convert these values. Better to just use the regular UTF8
> versions.

Really, here you touched tangentially the core of my question. Besides all 
those great theories, at last I have UTF-8 encoded data in a dBase, and the 
UCS-2 encoded data of the MS Win32 API (w_chars in muy Cpp app). The 
question is: What is the concrete way to and from that w_chars to the UTF-8 
that need SQLite? (my guess is use UTF-8 to the stored data) Can I use 
wcstombs() or even WideCharToMultiByte()? -this last seem a nightmare to 
me-.

> If you are only now internationalizing your code, and you've been
> passing 8bit strings to SQLite, you may already have a problem, since
> some of the data stored may be invalid if you attempt to treat it as a
> UTF-8 string. You may find that you'll need to dump the data, then
> convert from ASCII to UTF-8 and rebuild the database.
>

Really, preventing future possible problems, my textual data was stored as 
blob. The idea is rebuild converting from 8 bits ASCII to UTF-8. Then the 
question: can be done with mbstowcs() prior to using sqlite3_bind_text()?

A.J.Millan

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

Re: [sqlite] Some clarification needed about Unicode

[A.J.Millan]

From: "Jean-Denis Muys" <jdm...@kleegroup.com>
Sent: Thursday, October 29, 2009 11:10 AM
Subject: Re: [sqlite] Some clarification needed about Unicode


> This may be seen as nit picking, but when discussing character encodings 
> and
> representations, the issues can become so subtil and confusing, that it's
> very important to be as rigorous as possible.

Absolutely agree with your's assert. Although in this case, the I'v 
understodd that Crenshaw refers to extended ASCII (8 bits).

But really muy problem is not the theory -I've read a lot about characters 
sets, Unicode, Multibite, and so- but with the details and seasoning of the 
stew.

A.J.Millan
 

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

[sqlite] Some clarification needed about Unicode

[A.J.Millan]

Hi list:

After some years using this wonderful tool, I embraced the 
internationalization of a application, and despite some readings in this 
list, and muy own test -not conclusive-, I still have some obscure corners.

[1] Supposing some textual data already inserted as UTF-8 (default mode) in 
a dBase, and a connection opened with sqlite3_open(): Does a 
sqlite3_column_text16 retrieves a correct UTF-16 content? Is to say, do 
SQLite the convertion internally?


[2] Assuming the previous -or a UTF-16 content obtained by any other means- 
and an develop using M$ VCpp for Windows32, say sizeof(w_char) == 2: can be 
used the UTF-16 content to directly fill an w_char string?

Any clarification would be greatly appreciated.

A.J.Millan

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

Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table

[A.J.Millan]

Absolutely agree; only mention that my request for "a 16 bit version of the 
sqlite3_get_table" refers to a UTF-16 version -as usual in the actual C/C++ 
API-.

Of course, besides -or instead- the requested function, would be great 
disclose the SQLite UTF-8/UTF-16 conversion mechanism.

A.J.Millan

- Original Message - 
From: "Nicolas Williams" <nicolas.willi...@sun.com>

> On Tue, Sep 01, 2009 at 10:41:27AM +0200, A.J.Millan wrote:
>> *  Make sure there was no 16-bit version of the sqlite3_get_table at
>> function -perhaps it would be a good idea to include it in the standard 
>> API.
>> The reason is the same who advised include the current version.
>
> It might be easier to provide utility functions for UTF-8<->UTF-16
> conversion and let developers use those when 16 variants of various
> functions are missing.
>
> (Yes, there should be libraries in your OS for UTF-8<->UTF-16
> conversion, but SQLite3 already has its own.  Exporting them should not
> hurt.)
>
> But wchar_t <-> UTF-8 and wchar_t <-> UTF-16 conversions should
> definitely be out of scope for SQLite3.  (wchar_t is very OS- and
> locale-specific.  Expecting SQLite3 to know about wchar_t seems to me
> like asking for bloat.)
>

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

Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table

[A.J.Millan]

Atul, Igor:

Thanks for your kind replies.

In fact, actually I'm using the new style querys, and my question was an 
attempt to:

*  Make sure there was no 16-bit version of the sqlite3_get_table at 
function -perhaps it would be a good idea to include it in the standard API. 
The reason is the same who advised include the current version.

*  Check if someone had previously resolved the same problem for not 
changing the logic of my application in the many points that I use the 
function.

A.J. Millan

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

[sqlite] About the substr(x,y,z) core function

[A.J.Millan]

Hi List:

May be of interest to add in the doc, that, when the second argument is 
negative, "the the first character of the substring is found by counting 
from the right rather than the left", but remember that the last actual 
character is -2, because -1 is just the ending NULL.

Of course it is rather obvious, but to me supposed some headache.

HTH.

A.J.Millan

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

[sqlite] Looking for a w_char alternative to the sqlite3_mprintf famili of functions

[A.J.Millan]

Hi list:

Actually I have an application that regularly uses expresions as:

char *zUserName = "My name";
char *zSQL = sqlite3_mprintf ("INSERT INTO table VALUES('%q')", zUserName);

Because of internationalization I need use Unicode instead of ASCII chars:

w_char *zwUserName = L"My Chinese Name";

w_char *zwSQL = sqlite3_x ("INSERT INTO table VALUES('%q')", zwUserName);

Some suggestion?

Thanks in advance

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

[sqlite] .lib file?

[A.J.Millan]

Basides the mentioned option of build a .lib file and link it statically 
against your code, or a dinamic library, there are the option of include the 
sqlite3.c file in your code without so many problems with the casting of 
pointers -and some others-.

If you are using a C++ project in MS Visual Studio, the sqlite3.c file must 
be compiled as "C".

See in:   File-Properties > C/C++ > Advanced > Compile As

Select  "Compile as C Code (/TC)"

The rest of the project can remain compiled as usual C/C++ files.

Probably you must disable the use of precompiled headers also.

HTH.

A.J.Millan


> -Original Message-
> From: Paul Claessen paul at claessen.com
> Sat Jul 25 15:48:37 GMT 2009
> To: paul at claessen.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] .lib file?
>> Two points though:

> 1. The amalgated sources were, apparently, not written with MS Visual 
> Study in mind, since compiling it results in over 100 errors
> (mostly invalid pointer conversions)
> 2. If I have a number of apps, it would really be more efficient to use 
> the DLL.

> I'm sure I can fix all the pointer casts (shouldn't be necessary if the 
> code used more consistent types!), but that would take me a
> lot of time, plus, there should be a way to simply use the .dll: since 
> there IS a windows console app, there must be either a .lib
> file somewhere, or there is an alternative way of using DLL's from a 
> windows console app, that I'm not aware of. 

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

Re: [sqlite] design question / discussion

[A.J.Millan]

Rich Rattanni wrote:>Hi I have a general design question.  I have the
following senario...

>In an embedded system running linux 2.6.2x I have a sqlite database
>constantly being updated with data acquired by the system.  I cant
>lose data (hence why I am using sqlite in the first place).  However
>periodically I have download the data contain within the database to a
>central server.  The system cannot stall during the download and must
>continue to record data.  Also, after the download I need to shrink
>the database size, simply because if the database is allowed to grow
>to its max size (~50MB) then every download thereafter would be 50MB,
>which is unacceptable.

After thinking in your's problem, according to yours first exposition, it 
seems that you are using the SQLite dbase as a mere tampon or 
temporarybuffer to the acquired data.  In that condition, with no further 
process of those data in the embedded system, perhaps you can consider 
simply write a flat file appending to it the incoming data (may be 
alternating between two or more files) and then compress and send the data 
to the host where they can be further processed or appended to a dbase.

>From the security point of view, the data in the embedded device are not 
necesarily  safer in a SQLite dbase that in a flat file.  Perhaps that 
layer(SQLite) are not necessary at all in the embedded device.

Just thinking out loud :-)

Adolfo.

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

Re: [sqlite] text datatype and referential integrity

[A.J.Millan]

- Original Message - 
From: "palmer ristevski" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 

> I always saw SQLite as trying to be the database to beat out MS Access 
> for the desktop user.
> And due to this belief I thought that referential integrity would have 
> been the one of the first fundamental things
> that would have been created from version 1.0, as wells as support for 
> the basic fundamental types of joins.
> I hope these things will be coming in the next release.
>
> If this is done, in my opinion SQLite will have won over MS Access!!!

With the exception of the joins, in my opinion that is precisely the thing 
that SQLite mustn't do.  M$ Access is already there and a bis version isn't 
really needed.  IMHO SQLite is beauty because is *different* (as far as SQL 
let it be).

> I am waiting for that GLORIOUS day to come!!!

Abolutely agree.

Adolfo

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

Re: [sqlite] Help with sqlite3_blob_open()

[A.J.Millan]

That work!

Thanks a lot.

Adolfo.

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, March 24, 2008 1:48 PM
Subject: Re: [sqlite] Help with sqlite3_blob_open()


> "A.J.Millan" <[EMAIL PROTECTED]> wrote:
>> Hello all:
>>
>> Using Windows XP:
>>
>> D:\Z\Zator5>sqlite3 zdb1
>> SQLite version 3.5.4
>> Enter ".help" for instructions
>> sqlite> .tables
>> AgEfHolder  AgEfemerAgVtHolder  AgVtos  Usr lnk
>> AgEfIDt AgPdHolder  AgVtIDt Block   atm prm
>> AgEfKlv AgPdIDt AgVtPre FreqUse blb
>>
>> sqlite> .schema blb
>> CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER);
>> sqlite>
>>
>> I'm having troubles with this pseudo code:
>>
>> > begin transaction
>>
>> > some successful operations on tables atm and blb
>>
>> > sqlite3_blob* pBlob;
>>
>> > // open Blob for read/write access
>> > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1,
>> > );
>>
>> Here res = 1, and I get the following error:
>>
>> SQL error: no such table: D:\Z\Zator5\zDB1.blb
>>
>
> The "database name" is not the same thing as the name of the
> file tht contains the database.  The database name is the name
> assigned to the database when you ATTACH it.  Or, for the original
> database, then name is "main".  Or for TEMP tables, the name is
> "temp".
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>

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

[sqlite] Help with sqlite3_blob_open()

[A.J.Millan]

Hello all:

Using Windows XP:

D:\Z\Zator5>sqlite3 zdb1
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .tables
AgEfHolder  AgEfemerAgVtHolder  AgVtos  Usr lnk
AgEfIDt AgPdHolder  AgVtIDt Block   atm prm
AgEfKlv AgPdIDt AgVtPre FreqUse blb

sqlite> .schema blb
CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER);
sqlite>

I'm having troubles with this pseudo code:

> begin transaction

> some successful operations on tables atm and blb

> sqlite3_blob* pBlob;

> // open Blob for read/write access
> int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, 
> );

Here res = 1, and I get the following error:

SQL error: no such table: D:\Z\Zator5\zDB1.blb

Neither I have luck with:

> int res = sqlite3_blob_open (dbPtr, "zDB1", "blb", "nm", Id, 1, );

D:\Z\ is my actual (working) directory

Do is there some that I'm missing or misunderstood?

Thanks for any idea.

Adolfo.

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

Re: [sqlite] Last insert in a table

[A.J.Millan]

Puneet, Dennis, Jay:

Thank a lot for yours replies.

Always is nice to have several views of a matter.  I'm agreeing also that 
the Dennis one is the most elegant answer. In my case it have an additional 
benefit, with that table I can also track the last modified row. Sometimes 
I need that also.

Cheers.

A.J.Millan 

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

Re: [sqlite] Last insert in a table

[A.J.Millan]

From: "P Kishor" <[EMAIL PROTECTED]>

>>  I would know if there a some way to know the last insert in a know 
>> table.
>>
>>  Some like the result returned by  sqlite3_last_insert_rowid(), but 
>> referred
>>  to a specific table inside the database connection, and not in all 
>> tables
>>  into the database from the database connection.
>
> last_insert_rowid() is indeed the result of the insert into a
> *specific* table, the one that happens to have a row inserted at the
> very last! You can't possibly insert the "last" row in *all* the
> tables just like you can't take away the last coach from a train... no
> matter what you take away, there will always be a last one.
>
> When the *next* insert operation happens, the result of that insert
> will become the last insert.


Puneet:

Thanks for your reply, but let me be a bit more concise.

I need update several tables in a dBase, and after all, I need to know the 
las insert id of one, or several of them.

Odviously I can keep a variable with the last_insert_rowid() of each 
specific table, obtained inmediately after the update of the table/s of my 
interest, but I try avoid global variables, so my question.

Thanks anyway.

A.J.Millan

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

[sqlite] Last insert in a table

[A.J.Millan]

Hi all:

I would know if there a some way to know the last insert in a know table.

Some like the result returned by  sqlite3_last_insert_rowid(), but referred 
to a specific table inside the database connection, and not in all tables 
into the database from the database connection.

Thanks in advance

A.J.Millan

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

[sqlite] Re: About designing a query in sqlite3-style

[A.J.Millan]

My mistake:

Of course, in the design A, the resp in while (resp == xxx) refers to the 
result in the prepare statement sqlite3_prepare_v2()


Adolfo



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

[sqlite] About designing a query in sqlite3-style

[A.J.Millan]

Hi:

Assuming that the definition of the dBase does not change, I wonder if the 
two pseudo code schemas are equally valid or if there are pros and cons in 
each one:


//  Design -A-  

do {
  // Compile the statement into a virtual machine
  resp = sqlite3_prepare_v2( );
  if (resp !=  ) {
 // some action
  }
  // Bind the data to SQL variables
  sqlite3_bind_xxxb  ();

  // Call sqlite3_step() to run the virtual machine
  resp = sqlite3_step(pStmt);
  if ( some condition on resp) { /* ... */ }

  // Finalize the virtual machine.
  resp = sqlite3_finalize();
  if (resp == SQLITE_OK)  break;
} while( resp == xxx );

// Design -B-  

// Compile the statement into a virtual machine
resp = sqlite3_prepare_v2( );
if (resp !=  ) {
// some action
}
// Bind the data to SQL variables
sqlite3_bind_xxxb  ();

do {
  // Call sqlite3_step() to run the virtual machine
  resp = sqlite3_step(pStmt);
  if ( some condition on resp) { /* ... */ }
} while( resp == xxx );

// Finalize the virtual machine.
resp = sqlite3_finalize();
if (resp != SQLITE_OK)  Some-action;

The above construct is supposed inside a function who process a query; may 
be a SELECT;  UPDATE or INSERT statement.


Thanks in advance.

Adolfo


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

Re: [sqlite] compiling for mingw

[A.J.Millan]

on Fri, 30 Nov 2007 15:21:56 -0800

Joe Wilso wrote:


I would not bother with make install on MinGW - too much hassle -
just copy out sqlite3.exe, libsqlite3.a and sqlite3.h manually
to wherever you want to put it.

Joe:So, do is there a libsqlite3.a file?Where is it?Thanks--

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

Re: [sqlite] Trying to build a static library

[A.J.Millan]

John:

Thanks for your feedback, but the question is:  are you compiling gcc in 
Linux or in Windows-32 with minGW?


The problem is that I'm using the GNU gcc compiler under Windows (minGW), 
trying to build a static library as a C (not C++) project, and get a lot of 
compiler error.  All related to type conversion.  As an example here is the 
first:


sources/where.c: In function `int whereClauseInsert(WhereClause*, Expr*, 
int)':

sources/where.c:231: error: invalid conversion from `void*' to `WhereTerm*'

Perhaps I'm missing some compiler option, but by the moment the system work 
pretty well compiling projects in both C and C++ using that platform.


Some idea?


I use static libraries and gcc.  Just use --enable-static and you should
get the link library created.



A.J.Millan wrote:
Hi all:

Instead the supplied DLL, I would like to statically link the SQLite
library in a new project, and I wonder if someone has build a SQLite
static library xxx.a using minGW in Windows and the sources/headers
contanied in the sqlite-source-3_5_2.zip


Any tips on that are welcome.





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

[sqlite] Trying to build a static library

[A.J.Millan]

Hi all:

Instead the supplied DLL, I would like to statically link the SQLite 
library in a new project, and I wonder if someone has build a SQLite static 
library xxx.a using minGW in Windows and the sources/headers contanied in 
the sqlite-source-3_5_2.zip


Any tips on that are welcome.

A.J.Millan 



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

RE: [sqlite] Data encryption

[A.J.Millan]

Andreas:

Some time ago, in response to a similar question, Günter Greschenz sent to 
this forum a email:



hi,


i've written some sqlite-functions to crypt (blowfish) or compress (bzip) 
data in sqlite:



e.g.
  insert into blubs values (crypt('data','pwd'))
or
  select from xyz where decompress(data) = 'blablabla'
or
  select from xyz where data = compress('blablabla')


but you have to wait until next weekend, because i'v traveling for my 
company at the moment and >return on friday (i hope :-)


Some day later:

after a long time being on a business trip, i finally came home and have 
now the chance to upload >the sources to my webserver:



http://greschenz.dyndns.org/sqlite.html


these sources have never been in a productive system, i just implemented 
it for fun...



what i want to say: i never tested it really good !


To me it was a wonderful source of information and ideas, but this morning 
the URL was unable.  May be if you recite certain magic spell, Günter can 
appear again...


Cheers

A.J.Millan 



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

Re: [sqlite] Suggests for improving the SQLite website

[A.J.Millan]

Regarding the basic "look" of the site, we were considering
using a style similar to the once found at ActiveState

  http://www.activestate.com/



However the tendency in computers screen is wider than until now. Today the 
standard is about 1440 pixels x 900, so a unique horizontal arrange y a 
waste of space.  The newspapers designers tend to be specialist in this, 
and they use several columns to arrange the information.  Obviously this is 
not a newspaper, but IMHO tree columns would be good, at least in the main 
page. Two for the rest.


Good luck!


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

Re: [sqlite] Suggests for improving the SQLite website

[A.J.Millan]

In general I'm agree with Kishor comments, specially those related to the 
ability to include comments to the documentation pages and the need of 
*more* examples.


I recognize that the present design has its charm (those of a Web site made 
by an engineer) but I recognize that has found some difficulties to find 
some information, specially when beginning  with the matter.


Keeping in mind the type of public of this web, don't worry about certain 
language frank and direct .  All us have the same problem (bugs) every day.


As an additional suggestion for those of us who love SQLite but can't 
afford the cost of a maintenance subscription (or don't need it); put a 
"donate" button and add a list of contributors.


A.J.Millan 



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

Re: [sqlite] Re: A beginner SQL question

[A.J.Millan]

Igor Tandetnik


> select rowid, A, B, C
> from t
> where A||B||C in
> ( select A||B||C
> from t
> group by A, B, C
> having count(*)>1
> );

Wouldn't that mistakenly consider a record ('xy', 'z', 'w') to be
duplicate of ('x', 'yz', 'w') ?

Igor Tandetnik



Due the fact that, in this case, the columns are integers,  can be done some 
trickery in the concatenation to avoid that?


Some like:   A||'*'||B||'*||C ?

A.J.Millan



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

RE: [sqlite] Re: A beginner SQL question

[A.J.Millan]

Igor:

The proposed solution seem list all rows in the table.  Perhaps miss some 
thing in between myTable t1 and myTable t2?


Gerry:

Yours solution does not list any at all.  It is likely that there are not 
duplicated entrys (at least must not happen that.).


Thanks for yours input.

A.J.Millan



A.J.Millan <> wrote:

Suppose a table:

CREATE TABLE 'myTable' (
A INTEGER NOT NULL,
B INTEGER NOT NULL,
C INTEGER);

Do is there some query to return if there are some duplicate files
and/or
who are they?

Consider duplicate file if there are two or more rows with the same
values.
I.E:

two or more with same values  A=X, B=Y, C=Z

or

A= X, B=Y, C=NULL

for any 3-tuple X Y Z


select t1.A, t1.B, t1.C
from myTable t1, myTable t2
where t1.A = t2.A and t1.B = t2.B and
 (t1.C = t2.C or (t1.C is null and t2.C is null));

Igor Tandetnik


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

[sqlite] A beginner SQL question

[A.J.Millan]

I All:

Suppose a table:

CREATE TABLE 'myTable' (
A INTEGER NOT NULL,
B INTEGER NOT NULL,
C INTEGER);

Do is there some query to return if there are some duplicate files and/or 
who are they?


Consider duplicate file if there are two or more rows with the same values. 
I.E:


two or more with same values  A=X, B=Y, C=Z

or

A= X, B=Y, C=NULL

for any 3-tuple X Y Z

Thanks in advance.


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

[sqlite] About cloning a table in a new dBase

[A.J.Millan]

Hi all:

Suppose a dBase named dBase1 and a table declared in it:

CREATE TABLE 'tb1' (Id INTEGER PRIMARY KEY,  Nm INTEGER,  Ci INTEGER);

Then, after some inserts I open a new dBase, say dBase2, and duplicate 
dBase1.tb1 with the C API functions executing the querys:


"ATTACH dBase1 AS dbOriginal"
"CREATE TABLE tb1 AS SELECT * FROM dbOriginal.tb1"

The new table is created and filled with proper values, but the new table 
schema isn't the same:


sqlite>.schema tb1
CREATE TABLE 'tb1' (Id INTEGER,  Nm INTEGER,   Ci INTEGER);

The PRIMARY KEY and any other constraints are lost.

Is this the usual behavior?

Must I use again the complete create sentence to get the desired design in 
the new table?


Thanks in advance

A.J.Millán
ZATOR Systems


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

Re: [sqlite] Why do you use SQLite? Comments for an article needed

[A.J.Millan]

John Elrick wrote:

> Tim Anderson wrote:
>
> We are working on a project for the Census Bureau and needed an
> embeddable database that was zero configuration for the user and fast.
> We evaluated SQLite against numerous competitors...

IMHO, a bit exaggerated the "numerous competitors" part of the sentence.  In
fact there are not so many dbases that match the proposed conditions for
it's Bureau.

A.J.Millan
ZATOR Systems


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

Re: [sqlite] About a Vista problem

[A.J.Millan]

- Original Message - 
From: "Tian-Jian "Barabbas" [EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, May 23, 2007 2:58 PM
Subject: Re: [sqlite] About a Vista problem


> Hi Millan,
>
> I encountered the same problem, the record is on
>
>
http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html
>
> and the issue was sent to
>
> http://www.sqlite.org/cvstrac/tktview?tn=2178
>

Hi  Mike:

Before my post, I did a search about "Vista" in the forum. Indeed I see
yours post about "The previous version ghost on Vista can be seen by
SQLite". May be that I misunderstand the point, but I believed that it refer
to undeterminacy after reinstalling in Vista.  Literally:  "... Vista kept
previous versions, and it somehow confused some so-called-legacy apps to
connect to incorrect shadow copies...".

What I say does nothing to do with that, although is under the same option
"Restore earlier version", but not in the "previous versions" tab, but in
the "Compatibility" (not shown in yours image). I also discovered that the
proposed solution is not stable and consistent (really the Vista's behavior
is a nightmare).  If You reboot the system after you have got a succeed run,
the SQLite fail "may" appear again... or not!.  Then your need go again to
the entire process of select the "Compatibility mode". There are an option
to "Show the configuration to all users", but that also fail...

I agree with You in that it can be a problem with the Vista-DLL mechanism;
more precisely the DLL/security mechanism, although I don't use the standard
SQLite DLL, but one constructed from the sources. So I plain do some test
with the "official" one, and because in my situation isn't there a real need
of a "shared" library, I plain do some tests using a library statically
linked with he application instead.

I would be gratefully to hear other experiences about this.

A.J.Millan
ZATOR Systems


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

[sqlite] About a Vista problem

[A.J.Millan]

May be of interest:

Currently I'm using SQLite in a medium/big C++ application for Windows-32
using GNU gcc compiler (MinGW versión).

Usually I develop in a Windows98 SE box, although for testing purposes
regularly run a copy under Windows XP (professional version) and under Vista
(regular Business version).  Despite some minor bugs and adjustments, due to
visual-styles issues,  the application run pretty well in all systems, but
"after" some of the last automatic actualizations in Vista, the application
start to fail. A strange "null" error was reported in every query.

The problem has been bypassed selecting the "Restore earlier versions" [1].
Then "Execute the program in compatibility mode with Window Server 2003
(Service Pack 1)" or in Windows XP (Service Pack2).  Also select the option
"disable scale adjustment in screen...".

Note that despite that the application runs Ok in Windows98, the option
"Compatibility with Windows98/Windows ME) hangs the proccess :-D

Note also that it wasn't necessary to run with administrator privileges.

[1] I'm unsure of the true title of those options in the english version
(I'm translating from spanish)

HTH to someone

A.J.Millan
ZATOR Systems


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

Re: [sqlite] A suggestion

[A.J.Millan]

Sorry!!

Although there are two other boxes around; one with XP and the other with
Vista, for test purposes, actually I still use  a third, with my old Windows
98SE and a utility, DOSKEY.COM, who emulates the Linux behavior in the
Windows shell (who natively does not do that), but when I use it with
sqlite3.exe that ability is lost.

By the way, after this response, I have been looking in the XP box and there
are a doskey.exe, but does not work in that way, but when try with sqlite3
in it, that works fine!!

Perhaps that would make me jump to that system despite my reluctance :-)

A.J.Millan
ZATOR Systems



- Original Message - 
From: "Peter van Dijk" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, May 09, 2007 12:11 PM
Subject: Re: [sqlite] A suggestion


>
> On 9-mei-2007, at 11:06, A.J.Millan wrote:
>
> > As a suggestion, and even in the risk to abuse of Mr Hipp's
> > patience. Would
> > it be possible to include in the command-line program (sqlite3.exe)
> > the
> > ability to edit, an repeat at least the five or six last commands,
> > as in
> > Linux?. Is to say with up-arrow and down-arrow.  I believe it would
> > be too
> > helpful.
>
> Arrow up and down work fine for me, I'm not sure what your actual
> request is then?
>
> Cheers, Peter.


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

[sqlite] A suggestion

[A.J.Millan]

As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would
it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would be too
helpful.

A.J.Millan
ZATOR Systems


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

Re: [sqlite] Re: How to obtain the integer part of a result

[A.J.Millan]

Igor:

Thanks a lot. That korks!!

Thanks also publicly to Filip who mail me the same response directly, but
still I have a question:

I couldn't find that sentence (CAST) in the SQLite doc, neither in the
Owens's book.  Is it a SQL standard?, or a built-in SQL function? or a
SQLite built-in aggregate funcion?

Thanks in advance

A.J.Millan
ZATOR Systems



- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" <sqlite-users@sqlite.org>
Sent: Sunday, May 06, 2007 3:32 PM
Subject: [sqlite] Re: How to obtain the integer part of a result


> A.J.Millan <[EMAIL PROTECTED]> wrote:
> > I would like to know if there are a way to obtain a result without
> > decimals (the integer part) in this query:
>
> CAST(expr AS INTEGER)
>
> Igor Tandetnik
>


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

Re: [sqlite] I'm Starving for New User Information

[A.J.Millan]

Of course there are may questions in your's post, but believe me, I
understand You because some months ago, and steel now, I feel exactly as
You.  IMHO that is the matter pending in the SQLite proyect, although I
suppose that it is not a Hipp fault (who has done much more that enough for
all us), but for the lack of volunteers.

Actually there are NOT that information that You need, alt least together.
Although in the past I've write against it, now I can suggest the less bad
alternative:  a book of Michael Owens, "The Definitive Guide to SQLite"
Apress.  I feel that in your's case it worth its price.

A.J.Millan
ZATOR Systems.


- Original Message - 
From: "km4hr" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Monday, May 07, 2007 2:59 AM
Subject: [sqlite] I'm Starving for New User Information


>
> Is there a sqlite introduction for programmers wanting to use the sqlite C
> API? The info on the web site is pretty sparse. There seems to be plenty
of
> info regarding the use of sqlite3 all over the web.  But not much on
getting
> set up to write programs that use sqlite.
>
> I have some specific questions below. I am a C programmer and I want use
> sqlite in my programs.


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

[sqlite] How to obtain the integer part of a result

[A.J.Millan]

Hi all:

I would like to know if there are a way to obtain a result without decimals
(the integer part) in this query:

SELECT  round(julianday('now')-Dat) FROM SomeTable WHERE ...;

Obviously Dat contain a Julian Date (nnn.nnn) and I want the difference in
days from today.

None the above, neither

SELECT  round(julianday('now')-Dat, 0) FROM SomeTable WHERE ...;

Produces the desired result.  I always get  xxx.0 (1 decimal).

A.J.Millan
ZATOR Systems



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

Re: [sqlite] Make test under windows

[A.J.Millan]

Surely You must link those two files explicitly in yours project.

A.J.Millan



> Hi All,
>
> I'm trying to compile sqlite3.3.16 under windows using MinGW, and MSYS,
when typing make, it compiles correctly, but make test, give me 2 errors
> gcc.exe user32.lib [no such file or directory]
> gcc.exe advapi32.lib [no such file or directory]
>
> I've checked the 2 files and they are on the my path, anyone has any idea,
>


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

[sqlite] Query before ending a query

[A.J.Millan]

Hi all:

Probably due to my weak knowledge of SQL techniques, I'm having some
problems using SQLite.

Consider this scenario:

sql = "SELECT a, b, c FROM someTable WHERE someCondition";

result = sqlite3_exec (dbPtr, sql, callBackFunct, ptrDest, );
if ( result != SQLITE_OK ) {
   // showError...
   return false;
}
return true;

Now in the callback function:

int callBackFunt (void* hwnd, int nCols, char** colValue, char** colName) {
   ...
if (some-condition-on colValue[0]) {// can be any other condition on
the results
foo(); // here foo performs some other query on the table.
   }
   
   return 0;
}

Usually I get the correct behavior and results, but an error warning whit no
much sense.  Error in some part of the SQL query, that by other side is
correct if I perform it in the shell.

The questions:

Is this a miss use of SQLite?

Is there a way to avoid the error?

I suppose that not always is possible perform all desired actions in a
single SQL query.

Is that correct?

Is there a best way to get that kind of results?

Thanks in advance

A.J.Millan
ZATOR Systems.


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

Re: [sqlite] how it works

[A.J.Millan]

> Hi
>
> I need to know how this works in detail:
> I have sqlite3 database file stored on SD/MMC FLASH card and i would to
insert
> rows to some tables often. There will be triggers to clean old and
obsolete
> data.
> How often it will write to database file? After every INSERT command, or
it
> goes to some buffer in ram and file is updated as late as multiple inserts
> are done?
> Do you understand me?
> I do not know how to conceive this question in English exactly.
>
> Best regards
>
> Jakub Ladman
>

Besides what can say on the matter the forum's gurus, included the own
author Mr. Hipp, it is a matter of fact that the process to write in
external devices is regularly monitorized by the OS. Sometimes the compiler
uses its own buffer system that run over the Systems buffers. Although there
are some resources to force a real write in some circumstances, with some
languages and some compilers.  flush, commit, etc. (_commit in MS VC++).

A.J. Millan


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

Re: [sqlite] Search engines and the Sqlite.Org website

[A.J.Millan]

>> I consider the behavior of the MSN bot to be abusive.  I'm
>> sorely tempted to ban the MSN bot from the entire sqlite.org
>> website.

Absolutely agree.

>>  If SQLite ceased to be indexed by MSN, would that
>> seriously inconvenience any users?

IMHO none at all


A.J.Millan
ZATOR Systems



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

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

>Removing the index is one of the silliest suggestions I've ever
>encountered. A book without an index is almost useless, unless of course
>you're blessed with a photographic memory. As far as putting in on a CD
>or on the web, that's fine as long as a _good_ index is also included in
>the book. I often read books on the subway, train, in a car, etc., where
>there is no possibility of access to a CD or the web. Many people I know
>have similar reading habits and consequently have no use for this sort
>of thing.

Certainly I admit that a stupid idea can seem to some superior minds, but
then the editors of almost all the novels that I have in my library should
be stupid.

As almost all in this forum has understood, I am not referring to that index
type that can occupy a couple of pages at the end,  and that is limited to
point out the title of each chapter, but to the exhaustive index of words
and terms that appear in this type of technical books; manuals and
references that by the way, are not those that usually read the people in
the subway or in the bus, but like guide in the desktop.  As an example, the
work "Win32 Programming" from Brent E. Rector and Joseph M. Newcomer,
Addison-Wesley.  A "brick" with 1522 pages, those that the index occupies
187 pages of words to three columns per page. The one that can let You pass
three years of continuous trips without getting bored.

Greetings

A.J.Millan
ZATOR Systems


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

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

>I will get back to you about the index. I also would like to invite
>anyone else who has any specific things they would like to see changed
>in the index to tell me. Assuming I can get a revised index out
>electronically, I will make any such changes immediately to the file I
>have on-hand.

>-- Mike

I have continued thinking of the index matter, and I consider that the
possibility to take out it completely in the printed version, or leaving it
summarized to a minimum, would have the additional advantage to be able to
dedicate more pages to the truly useful things (in the past, I also
published some thing, so I have some idea about the editorial world.)

On the other hand, besides the mentioned possibility, to put on-line, or in
a CD, the index, I believe that equally, an artifice could be included that
allow "Google" any word or combination of them (in the case of a CD, the
original content could be ciphered to avoid the piracy).  As in Google,
besides the page and line-number, the result it could include certain
quantity of characters around all the matchs-points. For example, say 30
before and 30 later, to give an idea of the context.  Beside the utility for
the book owners, the potential buyers could check what can wait.  It could
be a good "hook " to attract possible clients.

As I stated before, the same thing for the idea.  Please, don't patent it
(yes I have some paranoia about patents).  Following the Hipp's blessing ,
and inside my much smaller possibilities, I would like that these ideas
remains in the public domain.

Greetings

A. J. Millan
ZATOR Systems.


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

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

>.For the record, Apress had the index generated by a third party. I've
>used the index myself recently, and frankly it worked for me -- I
>found what I was looking for.

>The SQL and C API references to which you refer --- taken from the
>on-line documentation --- start on page 365. Furthermore, the
>inclusion of this material was and has been considered useful by
>everyone who reviewed the book, as it serves as an authoritative and
>succinct reference. Many technical books include a reference similar
>to this as a convenience to the reader.

>I devoted 100 pages on the subject of SQL. I tried to systematically
>and progressively describe SQL so that someone completely unfamiliar
>could read the chapter in order, and by the end of the chapter
>understand topics such as 3NF, left outer joins, and the intricacies
>of NULL.

>For an average programmer who needs to use SQLite, I hope this book
>can save a lot of time whether you are a unfamiliar to or experienced
>with SQL or the SQLite API. I wrote this book purely because I like
>SQLite, wanted to help get the word out, and someone presented me with
>the opportunity to write a book. Once I decided to write it, I went to
>great lengths to write a book that would be genuinely useful. Apress
>has put a lot of time and effort into making this a useful book as
>well.

>As I said in the book, and I think on this list, I am always open to
>suggestions and feedback. If you really feel the book has failed you
>in some way, or missed an important topic, I would like to know about
>it so I can try to make it better should it ever make it to a second
>edition. While I have many other things going on in life just like
>everybody else, it is important to me that the book serves its
>purpose, and I am more than happy to take whatever efforts required to
>do so.

>-- Mike

I wish remark that my message should be interpreted in its context; it is
and advise to somebody for what the acquisition of the book can suppose a
great economic effort.

In other circumstances the advice can be different.  Go ahead that, in my
case, I have never acquired a book  the one that cannot say that I have
learned something, or that it didn't contain something valuable that had not
found in any other one.

I also want to pick up your's offer to add some personal suggestion in this
respect (IMHO):

*  Eliminate the part devoted to the SQL generalities.  In my opinion, the
buyer of a book whose title is "The Definitive Guide to SQLite", doesn't
want an introduction to the SQL; for that there already exist good manuals,
even in the Web.  In the best case, a chapter dedicated the SQL
"peculiarities" of SQLite.

*  I'm not against cut and paste the SQL reference or the C-API from the
on-line manual  (it is really a convenience for the reader), but include an
example, that can be synthetic and not commented, in each epigraph. This
gives true utility.

*  Say goodbye to the index subcontractor, and as an idea: Why not a
mini-CD, or a downloadable file with the index? (editors: thought in that,
but please, not patent it, the idea is mine :-))

*  More and more and more... code examples

Finally, if you wants a gift for the hearing, I will tell You that indeed, I
find some parts stupendous.  For example, the epigraph "The thing called
NULL", or the explanation of the supported data types (Storage Classes;
Manifest Typing, and Type Affinity).  All the chapter 5 "Design and
concepts"; chapter 6, "The Core C API"; chapter 7, "Extension C API", and,
of course, the chapter 9 "SQLite internals".  These last chapters should be
more cared and enlarged, because it is the core of the book.

Greetings.

A.J.Millán
ZATOR Systems.

P.D: excuse my English.


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

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

> Thank you i will try my possibilities buying from amazon, while i am based
in
> the Czech republic.
> Unfortunately it is little bit expensive for me, to buy it without looking
> into it before.
> Jakub

I would recommend not to waste your's money in that book.

More of a half is about SQL generalities, an a quarter a copy-paste of the
on-line manual without more comment.  At the end, the horrific index i've
ever seen.

A.J.Millan
ZATOR Systems.



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

Re: [sqlite] Re: Newbie question about LIKE and ESCAPE

[A.J.Millan]

- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" 
Sent: Sunday, March 04, 2007 12:59 AM
Subject: [sqlite] Re: Newbie question about LIKE and ESCAPE


> For example, this query retrieves all rows where someField contains '%'
> character:
>
> SELECT * FROM someTable WHERE someField LIKE '%\%%' ESCAPE '\';
>
> > Can I use some like:
> >
> > SELECT someField IN someTable WHERE name LIKE 'xyzetc' ESCAPE
> > ;
>
> No. ESCAPE should be followed by a string consisting of exactly one
> character.
>

Leaving to a side the mentioned example (that is possibly a very old
comment), I had really misinterpreted the meaning and objective of the
ESCAPE clause. Now, thanks to yours comments and to Igor's example,
everything is clear and my querys works properly.

By the way, some simple examples next to each epigraph in the on-line manual
would be a lot of help.  Maybe a system of comments from the readers, as the
one in MySQL or PHP (I believe to remember).

Thaks to all.

A. J.Millan
ZATOR Systems


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

[sqlite] Newbie question about LIKE and ESCAPE

[A.J.Millan]

Hi all:

I need a simple search, say:

SELECT someField IN someTable WHERE name LIKE '%xyzetc%';

After some search in this list, I'm a bit more confused that before.  For
example after reading literally:

>The escape mechanism for LIKE has never been implemented in
>SQLite.  The work-around is to use GLOB if your pattern
>contains '_' or '%' (unfortunately, GLOB is not std SQL).

>In case you're curious, the std SQL syntax is

>  like 'ba_foo\_png' escape '\'

>Without the (unimplemented) escape clause, the '\' is just
>an ordinary character".

Can someone unveil me the correct syntax for that query?

Can I use some like:

SELECT someField IN someTable WHERE name LIKE 'xyzetc' ESCAPE ;

Thanks in advance.

A.J.Millan
ZATOR Systems


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

Re: [sqlite] Re: Newbie SQL question

[A.J.Millan]

Igor:

Thank you very much for your's help

A.J.Millan


- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" <sqlite-users@sqlite.org>
Sent: Tuesday, February 06, 2007 10:50 PM
Subject: [sqlite] Re: Newbie SQL question


>
> See sqlite3_changes, sqlite3_total_changes
>
> Igor Tandetnik
>
> --
---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
---
>
>


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

Re: [sqlite] Newbie SQL question

[A.J.Millan]

Dennis:

Thank you very much for your's Help.

A. J.Millan



- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, February 06, 2007 11:16 PM
Subject: Re: [sqlite] Newbie SQL question

> >
> Yes. See the documentation for PRAGMA count_changes at
> http://www.sqlite.org/pragma.html.
>
> SQLite version 3.3.12
> Enter ".help" for instructions
> sqlite> create table t (id integer primary key, b text);
> sqlite> insert into t(b) values('one');
> sqlite> insert into t(b) values('two');
> sqlite> insert into t(b) values('three');
> sqlite> pragma count_changes= 1;
> sqlite> delete from t where b like 't%';
> 2
> sqlite> select * from t;
> 1|one
>
>
> HTH
> Dennis Cote
>
> --
---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
---
>
>


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

[sqlite] Newbie SQL question

[A.J.Millan]

Hi all:

Does exist some method to erase records and to obtain the number of erased
records at the same time?

It is to say:

SELECT count() FROM someTable WHERE some-condition;
DELETE FROM someTable WHERE some-condition;

in only one statement?

Thanks in advance

---
A.J.Millan
ZATOR Systems


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

[sqlite] Fw: Problem in version 3.3.10

[A.J.Millan]

 Hi:

 An application was running Ok under version 3.3.7.  When recompiling with
v.
 3.3.10, crashed at certain point in runtime.  "invalid page in module
 MSVCRT.DLL of 0167:780027a3".

 The Sistem is running MS Windows98 SE fully patched.

 Msvcrt.dll is version 6.10.8924.0.

I compile with GNU G++ 3.4.2-20040916-1 for Windows (Dev-C++).

Is there some known bug?

Some way to compile without the Msvcrt.dll dependency?

 I'd be grateful for any help.

 A. J. Millan



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

[sqlite] Trouble ensuring partial uniqueness in trigger

[A.J.Millan]

Let's say:

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

 I need the following conditions:

 a:  Both fields, Id and Cl, can be NULL
 b:  If  Id is not null, then the pair Id - Cl must be unique.

 I use a TRIGGER for BEFORE INSERT on tableA that works pretty well for the
 mentioned condition, but an analogous trigger for BEFORE UPDATE not
 behave properly:

 CREATE TRIGGER  UpdControl BEFORE UPDATE ON tableA
 BEGIN
   SELECT CASE
   WHEN (NEW.Nm IS NOT NULL) AND (NEW.Cl IS NOT NULL) AND
  (SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl = NEW.Cl) IS NOT NULL THEN
   RAISE(ABORT, 'Duplicated Item.')
  WHEN (NEW.Nm IS NOT NULL) AND (NEW.Cl IS NULL) AND
  (SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl  IS NULL) THEN
RAISE(ABORT, 'Duplicated Item')
   END;
END;

 Apparently, the problem is that,  *under all conditions*, the clauses:

 (SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl = NEW.Cl)
 (SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl  IS NULL)

 result in a NOT NULL

Using sqlite3 ver 3.3.7 for Windows  (sqlite3.dll).

 Any clarification, or idea, in this respect will be grateful.

 A. J. Millan



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

Re: [sqlite] Re: Trouble with Trigger

[A.J.Millan]

Igor:

Your answer includes two points that I consider very illustrative, at least
for my.

Although I am relatively new in this forum, I believe that your
interventions always mean interesting.

Greetings.

A. J. Millan

- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" 
Sent: Tuesday, November 14, 2006 1:01 AM
Subject: [sqlite] Re: Trouble with Trigger


>
> 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

[A.J.Millan]

Dennis:

Thanks for unveil (to me) that interesting page.

Greetings

A. J. Millan

- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, November 14, 2006 12:57 AM
Subject: Re: [sqlite] Trouble with Trigger


> 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]
-