Re: [sqlite] custom collation problem with delphi

[Ralf Junker]

Hello jp,

with DISQLite3, I use the the following colation callback function without 
problems:

function SQLite3_Compare_System_NoCase_Ansi(
  UserData: Pointer;
  l1: Integer; const s1: Pointer;
  l2: Integer; const s2: Pointer): Integer;
begin
  Result := CompareStringA(LOCALE_SYSTEM_DEFAULT, NORM_IGNORECASE, s1, l1, s2, 
l2) - 2;
end;

This function should be functionally equivalent to your implementation, but 
accesses the Win32 API directly. Also, it does not implicitly convert the PChar 
pointers to AnsiStrings like your call "copy(B,1,lenB);" implicitly does.

Notes:

* The above function is not declared as "cdecl" because DISQLite3 uses the 
faster "register" calling convention instead.

* The above function (just as yours) does not treat UTF-8 sequences properly. 
You might want to consider a WideString function instead, depending on the data 
you are processing.

Regards,

Ralf

>I have a custom collation which worked well in 3.3.6,
>but now gives random errors on 3.3.13.  Might just be
>coincidence but wanted to get feedback from the
>community.
>
>Under random circumstances, my Delphi function
>(compare function defined in sqlite3_create_collation)
>doesn't seem to receive the right parameters.  After
>debugging, the error...
>
>"Access violation at address 00405190 in module
>'app.exe'. Read of address 016D000" 
>
>...happens in line #15, like if the pointer to the
>second variable (B) is invalid:
>
> 1 FUNCTION fnComp(user:pointer;
> 2   lenA:integer; A:pChar;
> 3   lenB:integer; B:pChar
> 4   ):integer; cdecl;
> 5
> 6 VAR S1,S2 :string; 
> 7 i:integer;
> 8 BEGIN
> 9  S1 := '';
>10  S2 := '';
>11  IF lenA>0 THEN 
>12S1 := copy(A,1,lenA);
>13
>14  IF lenB>0 THEN
>15S2 := copy(B,1,lenB); // error happens here!
>16
>17  i := ansiCompareText(S1,S2);
>18
>19  IF (i=0) THEN 
>20IF (lena21  i := -1
>22ELSE
>23  i := 1
>24  ELSE ;
>25
>26 Result := i;
>27 END;
>...
>The collation is created using:
>
>sqlResult:=sqlite3_create_collation(db3,
>   'myCollate',
>   SQLITE_UTF8,
>   self,
>   fnComp);
>
>- - - -
>No error happens when using the BINARY and NOCASE
>collation.
>
>Does anybody have similar experiences or have a clue
>of what might be going on?  The error happens after
>processing several thousand records, but sometimes
>happens on an INSERT, other times during a SELECT, and
>never in exactly the same place (even with the same
>set of data/database).
>
>jp
>
>
>
>
> 
>
>It's here! Your new message!  
>Get new email alerts with the free Yahoo! Toolbar.
>http://tools.search.yahoo.com/toolbar/features/mail/
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-


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

RE: [sqlite] Question about regexp function

[Griggs, Donald]

Hi Neil,
 
Re: " I would like to perform regular expression queries interactively, i.e. 
from the sqlite> prompt"I
 
I'm not sure what operating system you're using, but there are sqlite GUI 
utilities that register a REGEX function.   In windows, one such free one is 
Sqlite3Explorer:
 http://www.singular.gr/sqlite/  
which includes a perl case-insensitive version.
 
If your needs require that you use the command-line utility included with the 
product, you'd want to link a regex function into your compile to produce your 
own version of sqlite3.exe.




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

Re: [sqlite] Compromised PayPal Account

[drh]

"PayPal" <[EMAIL PROTECTED]> wrote:
> You have received this email because your account

Please be assured that I am working diligently to block
this kind of spam on the mailing list.  But the spammers 
keep getting more and more clever...
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Compromised PayPal Account

[Rich Shepard]

On Sat, 3 Mar 2007, [EMAIL PROTECTED] wrote:


Please be assured that I am working diligently to block this kind of spam
on the mailing list.  But the spammers keep getting more and more
clever...


Richard,

  Yes, they are getting more devious. Most lists are spammed only rarely;
newsgroups constantly. When junk comes in via a mail list, I just delete it
and let it go.

  You're doing an excellent job on all fronts. Thank you.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

[sqlite] Moving from 2.8.16

[Mitchell Vincent]

I have a business application written that uses SQLite 2.8.16 as the
backend database. I love it and it's been as stable as anyone could
ever hope for. However, I want to move it forward before it's too
late. I'd like to go into it expecting potential problems but from
what I've read the move from 2.6.18 to 3.3.13 *should* be pretty
smooth.

Other than the information here : http://www.sqlite.org/version3.html,
is there anything I might run into with queries not working (like any
date/time function differences?).

I appreciate any tips or tricks anyone would like to share!

--
- Mitchell Vincent

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

Re: [sqlite] Extension functions for SQLite in C for free

[John Stanton]

T&B wrote:

Last month, Mikey C wrote (in part):

I've had these functions hanging around for some time... if anyone  
wants the code, please take it.


I have all the code as a MS Visual Studio 2003 project.  It is  based 
on source code 3.3.5



replace(X,Y,Z) Returns the string X with every occurence of Y  
replaced by Z.




rightstr(X,Y) Returns the Y last characters of the string X.



strfilter(X,Y) Returns the string X with the characters not in Y  
removed.



trim(X) Returns a string equal to X but with all the whitespaces at  
the begining and at the end removed.



median(X) Returns the value of the group such that the number of  
elements smaller is equal to the number of larger elements.




mode(X) Returns the most frequent value in the sample X.




stdev(X) Returns the standard deviation of the sample X.




http://www.nabble.com/file/6285/SQLite.zip SQLite.zip
--
View this message in context: http://www.nabble.com/Extension- 
functions-for-SQLite-in-C-for-free-tf3182921.html#a8833684



For the non-C programmer (but with access to standard UNIX C compile  
tools etc), can someone please explain or point to a step by step  
procedure to add these functions so they are accessible within the  
sqlite3 shell command?


Thanks,
Tom


This requires that you produce a custom version of the sqlite3 shell 
incorporating the extra functions.  That requires some basic programming 
understanding.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Extension functions for SQLite in C for free

[jp]

> T&B wrote:
> > Last month, Mikey C wrote (in part):
> > 
> >> I've had these functions hanging around for some
> time... if anyone  
> >> wants the code, please take it.
> >>
> >> I have all the code as a MS Visual Studio 2003
> project.  It is  based 
> >> on source code 3.3.5
> > 
> > 
> >> replace(X,Y,Z) Returns the string X with every
> occurence of Y  
> >> replaced by Z.
> > 
> > 
> >> rightstr(X,Y) Returns the Y last characters of
> the string X.
> > 
> > 
> >> strfilter(X,Y) Returns the string X with the
> characters not in Y  
> >> removed.
> > 
> > 
> >> trim(X) Returns a string equal to X but with all
> the whitespaces at  
> >> the begining and at the end removed.
> > 
> > 
> >> median(X) Returns the value of the group such
> that the number of  
> >> elements smaller is equal to the number of larger
> elements.
> > 
> > 
> >> mode(X) Returns the most frequent value in the
> sample X.
> > 
> > 
> >> stdev(X) Returns the standard deviation of the
> sample X.
> > 
> > 
> >> http://www.nabble.com/file/6285/SQLite.zip
> SQLite.zip
> >> -- 
> >> View this message in context:
> http://www.nabble.com/Extension- 
> >>
>
functions-for-SQLite-in-C-for-free-tf3182921.html#a8833684
> > 
> > 
> > For the non-C programmer (but with access to
> standard UNIX C compile  
> > tools etc), can someone please explain or point to
> a step by step  
> > procedure to add these functions so they are
> accessible within the  
> > sqlite3 shell command?
> > 
> > Thanks,
> > Tom
> 
> This requires that you produce a custom version of
> the sqlite3 shell 
> incorporating the extra functions.  That requires
> some basic programming 
> understanding.

I think you can compile the functions into a loadable
library (follow the instructions on creating a sqlite
loadable extension), and then load them in the console
app.  In linux:

  gcc myLoadableLibs.c -shared -o myLoadableLibs.so

then

  sqlite3> .load /home/jp/myLoadableLibs.so

I am not sure how to do that on VC though.

jp.



 

Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html 

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

RE: [sqlite] SQLITE3 bombs on Windows 95

[Zvi Dershowitz]

I was able to link with UNICOWS and my application now runs on Windows 95
even with the newest revision of SQLITE. 

Thanks for the tip.


-Original Message-
From: Roger Binns [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 02, 2007 8:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLITE3 bombs on Windows 95

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brownie wrote:
> But a lot of APIs of UNICODE version was not provided for Win95/98.
> So, you cannot use SQLite on Win95.

Yes it was.  You need to link against unicows.

http://www.microsoft.com.nsatc.net/globaldev/handson/dev/mslu_announce.mspx

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

iD8DBQFF58IPmOOfHg372QQRArerAJ4uiTRsz3A/8mTIdIr9NSq3Fk/dJgCgmq0E
uqi8Qa0r7tFJGOZ2gqMS/Vc=
=kYkg
-END PGP SIGNATURE-


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

[sqlite] sqlite3_total_changes() and multiple connections

[Ron Stevens]

Hi,

I have multiple database connections opened against the same database
and I'm having problems with sqlite3_total_changes(). The docs state:

"This function returns the total number of database rows that have be
modified, inserted, or deleted since the database connection was
created using sqlite3_open()."

but it seems like only changes made through the connection I call the
function on are counted. Is there any way to get the total number of
changes made through all opened connections?

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

[sqlite] Re: sqlite3_total_changes() and multiple connections

[Igor Tandetnik]

Ron Stevens  wrote:

I have multiple database connections opened against the same database
and I'm having problems with sqlite3_total_changes(). The docs state:

"This function returns the total number of database rows that have be
modified, inserted, or deleted since the database connection was
created using sqlite3_open()."

but it seems like only changes made through the connection I call the
function on are counted.


This is correct.


Is there any way to get the total number of
changes made through all opened connections?


Get the numbers for each connection, and add them up.

Igor Tandetnik

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

[sqlite] Re: Newbie question about LIKE and ESCAPE

[Igor Tandetnik]

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

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.


I don't know where you are reading this. In any case, this information 
is obsolete. ESCAPE clause works since SQLite 3.1.0



Can someone unveil me the correct syntax for that query?


For what query? You only need ESCAPE if you want to look for strings 
that themselves contain % or _ characters. Do you? What exactly are you 
trying to achieve?


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.


Igor Tandetnik 



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

Re: [sqlite] Newbie question about LIKE and ESCAPE

[P Kishor]

On 3/3/07, A.J.Millan <[EMAIL PROTECTED]> wrote:

Hi all:

I need a simple search, say:

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


This is standard SQL syntax, and works just fine in SQLite. It will
match all rows where someField contains the string 'xyzetc'

You can also anchor the string by using either '%xyzetc' or 'xyzetc%'
if desired.

..

Can I use some like:

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



I don't have experience with the ESCAPE clause, but even if your
syntax above were correct, you would find only those rows where
someField is exactly like 'xyzetc', and I think that is not what
you want.

Just use '%xyzetc%' and you will be fine.


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

[sqlite] Last ID from Primary Key through ODBC

[Mitchell Vincent]

I found the nifty ODBC driver for SQLite and have been trying to use
it to replace a 2.8 SQlite implementation in some desktop software.
It's working very well except for a rather large problem of not being
able to call the last_id API functions. I need to get the primary keys
assigned to parent tables in order to preserve referential integrity.

SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous
seeing as multipl people *could* be accessing this database file at
the same time..

Are there alternatives?

Thanks!

--
- Mitchell Vincent

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

Re: [sqlite] Re: sqlite3_total_changes() and multiple connections

[Ron Stevens]

> but it seems like only changes made through the connection I call the
> function on are counted.

This is correct.


The documentation is misleading and should mention this limitation.


> Is there any way to get the total number of
> changes made through all opened connections?

Get the numbers for each connection, and add them up.


The connections are opened from different processes, so collecting the
numbers isn't trivial. It would be nice if sqlite stored a change
count in the database that was easily accessible.

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

[sqlite] sqlite3 shared object question.

[aaron smith]

I'm somewhatenew to the linux world. I'm running a dedicated CenteOS box.
I'm trying to get sqlite3 installed. I've downloaded this binary
http://www.sqlite.org/sqlite-3.3.13.so.gz. gunzip it. I see an
sql.x.x.x.sofile. What do I do with that? if I try to execute it I get
s segmentation
fault.

thanks.

Re: [sqlite] Last ID from Primary Key through ODBC

[Dan Kennedy]

On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote:
> I found the nifty ODBC driver for SQLite and have been trying to use
> it to replace a 2.8 SQlite implementation in some desktop software.
> It's working very well except for a rather large problem of not being
> able to call the last_id API functions. I need to get the primary keys
> assigned to parent tables in order to preserve referential integrity.
> 
> SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous
> seeing as multipl people *could* be accessing this database file at
> the same time..

What if you wrap the INSERT and "SELECT MAX(id)" statements together
in a transaction?

> 
> Are there alternatives?
> 
> Thanks!
> 


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

Re: [sqlite] sqlite3 shared object question.

[Nuno Lucas]

On 3/4/07, aaron smith <[EMAIL PROTECTED]> wrote:

I'm somewhatenew to the linux world. I'm running a dedicated CenteOS box.
I'm trying to get sqlite3 installed. I've downloaded this binary
http://www.sqlite.org/sqlite-3.3.13.so.gz. gunzip it. I see an
sql.x.x.x.sofile. What do I do with that? if I try to execute it I get
s segmentation
fault.


A .so file is similar to a windows DLL, so you can't execute it, only
use it from your programs.
What you need is either the sqlite binary or the sqlite source and
compile it yourself.

What you are trying to do? Your distribution should already have some
pre-made package you can download to install it. If I'm not mistaken
CentOS uses RPM as it's package manager, and if I remember correctly,
yast uses SQLite internally, so maybe it is already installed?


Regards,
~Nuno Lucas

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

Re: [sqlite] Last ID from Primary Key through ODBC

[Mitchell Vincent]

I really don't know. Will it work the way I think it will? :-)

On 3/3/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:

On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote:
> I found the nifty ODBC driver for SQLite and have been trying to use
> it to replace a 2.8 SQlite implementation in some desktop software.
> It's working very well except for a rather large problem of not being
> able to call the last_id API functions. I need to get the primary keys
> assigned to parent tables in order to preserve referential integrity.
>
> SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous
> seeing as multipl people *could* be accessing this database file at
> the same time..

What if you wrap the INSERT and "SELECT MAX(id)" statements together
in a transaction?

>
> Are there alternatives?
>
> Thanks!
>


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





--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

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

Re: [sqlite] sqlite3 shared object question.

[aaron smith]

Ultimately what I am trying to do is install the ruby wiki, Instiki. In
doing some research I found that rubys sqlite-lib wasn't compatible with
sqlite3. So i removed sqlite3 to get sqlite2. Later to find out that, oh.
sqlite3-ruby library is the one I needed to use. So now I have to figure out
how to get sqlite3 back on.

So far I have been compiling everything myself from source,
ruby,lighttpd,apache, etc.. Except sqlite. I was confused about this .so. As
on sqlites site there is a previous version binary of 2.8.xx. Which was a
binary that executed as expected. Is the solution just to recompile sqlite3
source myself? Any idea where I can find out information about RPM for
centOS?

thanks




On 3/3/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:


On 3/4/07, aaron smith <[EMAIL PROTECTED]> wrote:
> I'm somewhatenew to the linux world. I'm running a dedicated CenteOS
box.
> I'm trying to get sqlite3 installed. I've downloaded this binary
> http://www.sqlite.org/sqlite-3.3.13.so.gz. gunzip it. I see an
> sql.x.x.x.sofile. What do I do with that? if I try to execute it I get
> s segmentation
> fault.

A .so file is similar to a windows DLL, so you can't execute it, only
use it from your programs.
What you need is either the sqlite binary or the sqlite source and
compile it yourself.

What you are trying to do? Your distribution should already have some
pre-made package you can download to install it. If I'm not mistaken
CentOS uses RPM as it's package manager, and if I remember correctly,
yast uses SQLite internally, so maybe it is already installed?


Regards,
~Nuno Lucas


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Last ID from Primary Key through ODBC

[Christian Werner]

Dan Kennedy wrote:
> 
> On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote:
> > I found the nifty ODBC driver for SQLite and have been trying to use
> > it to replace a 2.8 SQlite implementation in some desktop software.
> > It's working very well except for a rather large problem of not being
> > able to call the last_id API functions. I need to get the primary keys
> > assigned to parent tables in order to preserve referential integrity.
> >
> > SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous
> > seeing as multipl people *could* be accessing this database file at
> > the same time..
> 
> What if you wrap the INSERT and "SELECT MAX(id)" statements together
> in a transaction?

Or what about issuing a "SELECT last_insert_rowid()" ?

Regards,
Christian

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