"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> I used sqlite3_prepare_v2(pDb,stmt,-1,,0)
> for preparing the statement and used sqlite3_step(pReadStmt); to get
> each row.
> my stmt is :
>select remoteId, bytesIn from compressTable
> then it worked ok
>
> but if my
Hi All,
I used sqlite3_prepare_v2(pDb,stmt,-1,,0)
for preparing the statement and used sqlite3_step(pReadStmt); to get each row.
my stmt is :
select remoteId, bytesIn from compressTable
then it worked ok
but if my statement has the attach database then it didn't work because the
> On 4/9/08, Richard Klein <[EMAIL PROTECTED]> wrote:
>>> Richard Klein <[EMAIL PROTECTED]>
>>> wrote:
One question: I thought that the '\' character is not part of the
SQL standard. (That's why I specified '/' instead as my ESCAPE
character).
>>> I'm not sure I understand. You
On 4/9/08, Richard Klein <[EMAIL PROTECTED]> wrote:
> > Richard Klein <[EMAIL PROTECTED]>
> > wrote:
> >> One question: I thought that the '\' character is not part of the
> >> SQL standard. (That's why I specified '/' instead as my ESCAPE
> >> character).
> >
> > I'm not sure I understand. You
Dennis,
Thanks for the reply. The original schema was causing us way to many
problems. We ended up going with the second schema and the last query I
posted we managed to get it down to .058 secs instead of 2.9 secs. I do
appreciate all the suggestions and assistance.
-Original
> Richard Klein <[EMAIL PROTECTED]>
> wrote:
>> One question: I thought that the '\' character is not part of the
>> SQL standard. (That's why I specified '/' instead as my ESCAPE
>> character).
>
> I'm not sure I understand. You can use any character as an escape
> character. E.g. LIKE '2!_%'
Richard Klein wrote:
>
> One question: I thought that the '\' character is not part of the
> SQL standard. (That's why I specified '/' instead as my ESCAPE
> character).
>
The '\' character is valid character in standard SQL. It just doesn't
have the property of being an escape character as
Andy Smith wrote:
>
> With all the changes that got me down to 4.8 seconds. Better.
Can you post the exact query that ran in 4.8 seconds? Did you set
case_sensitive_like on before you ran the query?
> We are
> also trying a schema in which we break up the container so it's a bit
> smaller.
Richard Klein <[EMAIL PROTECTED]>
wrote:
> One question: I thought that the '\' character is not part of the
> SQL standard. (That's why I specified '/' instead as my ESCAPE
> character).
I'm not sure I understand. You can use any character as an escape
character. E.g. LIKE '2!_%' ESCAPE '!'.
Ken wrote:
>
> Recomend getting the example from the wiki working first then move on to your
> own.
>
That's good advice.
Also, it would be better if you post your messages as replies to one of
the existing message threads you have already started on this topic
(rather than starting a new
> Xuanvinh Vu <[EMAIL PROTECTED]> wrote:
>> Sorry it was actually quite simple for the WHERE clause it should be
>>
>> WHERE Name LIKE '2_%'
>>
>> to select Name that starts with the string "2_"
>
> Note that '_' is one of the special characters LIKE operator recognizes:
> it matches any
> Xuanvinh Vu <[EMAIL PROTECTED]> wrote:
>> I have search google but have not found a solution. I have a field
>> called Name and I want to select the rows that have Name starts with
>> certain string. How could I do this?
>
> select * from tableName where Name LIKE 'xyz%';
>
> Igor Tandetnik
Xuanvinh Vu <[EMAIL PROTECTED]> wrote:
> Sorry it was actually quite simple for the WHERE clause it should be
>
> WHERE Name LIKE '2_%'
>
> to select Name that starts with the string "2_"
Note that '_' is one of the special characters LIKE operator recognizes:
it matches any character (the other
Thanks! :)> To: sqlite-users@sqlite.org> From: [EMAIL PROTECTED]> Date: Wed, 9
Apr 2008 18:32:02 -0400> Subject: Re: [sqlite] Select row names that start with
this string...> > Xuanvinh Vu <[EMAIL PROTECTED]> wrote:> > I have search
google but have not found a solution. I have a field> >
Sorry it was actually quite simple for the WHERE clause it should be
WHERE Name LIKE '2_%'
to select Name that starts with the string "2_"
thx> From: [EMAIL PROTECTED]> To: sqlite-users@sqlite.org> Date: Wed, 9 Apr
2008 18:21:28 -0400> Subject: [sqlite] Select row names that start with
Xuanvinh Vu <[EMAIL PROTECTED]> wrote:
> I have search google but have not found a solution. I have a field
> called Name and I want to select the rows that have Name starts with
> certain string. How could I do this?
select * from tableName where Name LIKE 'xyz%';
Igor Tandetnik
I have search google but have not found a solution. I have a field called Name
and I want to select the rows that have Name starts with certain string. How
could I do this?
SELECT Name, BuildNum AS 'Build', ComputerName AS 'Computer Name', Time AS
'TimeStamp', Description, OSType FROM
On Wed, Apr 09, 2008 at 05:14:33PM +0200, Aladdin Lamp? scratched on the wall:
>
> Hi all!
> Following the recent thread "Virtual tables used to query big external
> database", and the discussion with Mike Owens and Jay A. Kreibich, it
> seems that :
>
> - The "old" way of dealing with dirty
* Dennis Cote:
> From the wikipedia article you cited:
>
> Note: although many references say that the Julian in "Julian day"
> refers to Scaliger's father, Julius Scaliger, in the introduction to
> Book V of his Opus de Emendatione Temporum ("Work on the Emendation of
> Time") he states,
1. remove main.
2. compile file with -shared (to create a .so)
3. select loadd_extension('filename.so') ; This will load your .so into the
address space.
4. Use your function in a sql statement.
Recomend getting the example from the wiki working first then move on to your
own.
HTH,
Ken
Ok, but I did not understand step by step how work. In wiki there is:
#include
SQLITE_EXTENSION_INIT1
static void halfFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}
int sqlite3_extension_init(
Dennis,
With all the changes that got me down to 4.8 seconds. Better. We are
also trying a schema in which we break up the container so it's a bit
smaller. Now my other 54 queries run sub .5 secs except 1. Which is a
variant of the one I have asked for help on. Here is the new version on
a
Andy Smith wrote:
> I have to get this down to < 1 sec. I have 50 queries that are all
> formatted similar. We have created another schema where the container
> is split into smaller tables which might help in speeding this up. From
> what I see the Left joins are killing the speed.
>
I don't
Andy
I get this explain output:
order|from|detail
0|0|TABLE mediaitem AS a WITH INDEX mediaitem_type
1|1|TABLE mediaitem AS b WITH INDEX mediaitem_type
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE
Ok, but I did not understand step by step how work. In wiki there is:
#include
SQLITE_EXTENSION_INIT1
static void halfFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}
int sqlite3_extension_init(
I have to get this down to < 1 sec. I have 50 queries that are all
formatted similar. We have created another schema where the container
is split into smaller tables which might help in speeding this up. From
what I see the Left joins are killing the speed.
Here is what I am seeing when running
dark0s dark0s wrote:
> I have a new problem, I am attempting add extension function to sqlite3.
> My program labsinf.c now build successfully, but I didn't add my extension. I
> don't understand what it is miss.
> Help me please.
>
>
> bash-3.1# gcc -lsqlite3 labsinf.c -o inf
> bash-3.1# ./inf
For the solution see:
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
1. you may need to re-compile sqlite with loadable extensions, depending upon
the version of sqlite you may need to edit the makefile.
2. Create a .sqliterc file that loads your .so file
select load_extension('
Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins and
> they run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
>
Andy,
You didn't show your table and index definitions, so it's hard to be
sure about what would
Andy Smith
<[EMAIL PROTECTED]> wrote:
> I have quiet a few queries similar to this doing multiple Left Joins
> and
> they run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
>
>
>
> SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path,
>
I have a new problem, I am attempting add extension function to sqlite3.
My program labsinf.c now build successfully, but I didn't add my extension. I
don't understand what it is miss.
Help me please.
The program labsinf.c make inf, and after I typed ./inf, it makes dbforext.db.
Some output is
I have quiet a few queries similar to this doing multiple Left Joins and
they run extremely slow > 6 secs. Is there a better way to be writing
the below query for sqlite.
SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID,
a.mimeType, a.width, a.height, a.genreID,
On Apr 9, 2008, at 10:26 AM, Aladdin Lampé wrote:
>
> Hi Phil and list!
>
> Thank you for this very instructive post about SQLite's internals
> and btrees. I'm just curious about what could be real-life use cases
> of having direct access to the btree stuff. As I understand your
> example,
Hi all!
Following the recent thread "Virtual tables used to query big external
database", and the discussion with Mike Owens and Jay A. Kreibich, it seems
that :
- The "old" way of dealing with dirty pages with bitmaps limited SQLite to an
approximate maximal capacity of 10s of GBs, as
[EMAIL PROTECTED] wrote:
> I'm new to SQLite and SQL in general and I have a question about
> handling foreign key relationships when adding items to a database
> when the related items may not already exist. For example, if I have
> two tables: foods with "id", "name", and "type_id" columns and
>
Mahalakshmi.m wrote:
>
> "CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
> NULL COLLATE NOCASE ,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"
>
> AlbumId AlbumName AlbumTrackCount
> 1 aaa 3
> 2 ddd 2
> 3
That is such a simple update statement.
Have you tried writing one yourself? Get a good book on the SQL language and
you'll learn.
I think most people on the list do not mind helping but this is just actually
doing the coding for you. And you won't learn anything if someone else does the
Hi Phil and list!
Thank you for this very instructive post about SQLite's internals and btrees.
I'm just curious about what could be real-life use cases of having direct
access to the btree stuff. As I understand your example, you store (key,value)
pairs inside the btree and then get them
I have just instaleld apache 2.2 and php 5.2.5 on a windows xp machine. I would
also like the current version of sqlite. running sqlite3.exe gives me a verion
of 3.3.15. Is this the version of sqlite3.exe or of the dqlite database engine?
If this is the engine version how do I upgrade it? I do
Gerhard Häring wrote:
> I need to know if SQLite works at all if the platform doesn't have a
> 64-bit integer type.
>
> I see that SQLite has some #ifdefing like
>
> #ifdef SQLITE_INT64_TYPE
>
> that seems to be for the case when the platform does not have the type
> natively, but I don't see
I'm new to SQLite and SQL in general and I have a question about
handling foreign key relationships when adding items to a database
when the related items may not already exist. For example, if I have
two tables: foods with "id", "name", and "type_id" columns and
food_types with "id" and "name"
You may want to read the docs for the following functions:
- sqlite3_release_memory: http://www.sqlite.org/c3ref/release_memory.html
- sqlite3_soft_heap_limit: http://www.sqlite.org/c3ref/soft_heap_limit.html
Additionally some pragmas may help, if properly used:
Now, I change my code as follows, but, the problem is
still there. The PDA memory usage become larger and
larger. Did I miss something? Anything wrong with my
code? Thanks.
sqlcmd = sqlite3_mprintf( "INSERT INTO TEST( BARCODE,
TAGTYPE, PRINTDATA ) VALUES( ?, ?, ? );" );
rc =
I just create table as below:
CREATE TABLE msglog (uuid varchar primary key,uid varchar,...)
After several days run, I found something very strange in it;
I exec sql: select rowid,* from msglog where rowid >53 and rowid < 55 and
msgtype='TM' order by uuid
then I get two
No, you only need to create the statement once. And bind all variables
inside the loop. This at least saves computation time. Additionally you
should use sqlite3_mprintf instead
of sprintf to protect against sql injection (if that is an issue for you.)
You're already doing it with the blob, why
Hi,
"CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
NULL COLLATE NOCASE ,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"
AlbumId AlbumName AlbumTrackCount
1 aaa 3
2 ddd 2
3 ccc 1
Thanks, Mike.
Because, each record has different barcode, tagtype,
and printdata. That means, I need to call:
sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE,
PRINTDATA ) VALUES( %s, %d, ? );", chrBarCode, tagtype
);
rc = sqlite3_prepare( db, sqlcmd, -1, , 0 );
to update the contents of
47 matches
Mail list logo