from:"hector"

Re: [sqlite] cann't work

2012-07-11 Thread Hector Guilarte

I am going to quote Drake Wilson, from a thread a couple days old
("[sqlite] DELETE only deletes some records, not others"):

"Aside from the more immediately relevant aspects the other posters
already mentioned, remember that double quotation marks in SQL are
normally used for _identifiers_.  For string literals, use single
quotation marks.  SQLite will sort of autocorrect the former into the
latter sometimes, but it is not good practice to rely on this."

On Wed, Jul 11, 2012 at 2:36 PM, Rob Richardson wrote:

> Does SQLite care about the use of double quotes instead of single quotes?
>
> RobR
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies
> Sent: Wednesday, July 11, 2012 5:10 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] cann't work
>
> On 11 July 2012 10:00, YAN HONG YE  wrote:
> > two same structure tables, when use this sql:
> > insert into table2  values(select * from table1 where filename like
> "%55");
> >
> > but show error: sqlite error 1 - near "select": syntax error
>
> insert into table2 select * from table1 where filename like "%55";
>
> http://www.sqlite.org/lang_insert.html
>
> Regards,
> Simon
> ___
> 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

Re: [sqlite] DELETE only deletes some records, not others

2012-07-08 Thread Hector Guilarte

I've never really used sqlite, but here's a lucky guess based on other
databases I have used. Is 'price' column a Numeric field with decimal
places? If so, I think that's your problem. For some reason when using LIKE
on Select statement it automatically casts price to a String, and so the
like matches some results. When doing it on Delete it doesn't cast it
automatically, so I would try doing a cast on 'price' first and then trying
to match that with LIKE.

If what I said makes no sense in Sqlite I'm sorry, but I couldn't try first
for myself.

I hope it helps,

Hector Guilarte
On Jul 8, 2012 8:44 PM, "Corey Nelson" <corey.nel...@gmail.com> wrote:

> Some bad data found it's way into my database and I'm trying to delete it.
> But my DELETE command is only deleting some of the records while leaving
> most as is. See below. The sqlite database file is
> here<https://docs.google.com/open?id=0B567nEzakPDFbktGbUVGeHVLTGM>
> .
>
> Am I doing something wrong? Is my database file corrupted some how?
>
> corey$ sqlite3 02.fxdb
> SQLite version 3.7.7 2011-06-25 16:35:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%";
> 129435210|12.0169
> 1294352102000|12.017
> 1294352107000|12.0166
> 1294352125000|12.0168
> 1294352129000|12.0166
> 1294352139000|12.0168
> 129435215|12.017
> 129435216|12.0161
> 1294352174000|12.0169
> 1294352182000|12.0171
> 1294352212000|12.0151
> 1294352217000|12.0182
> 129435222|12.0177
> 1294352226000|12.0156
> 1294352228000|12.0157
> 1294352229000|12.0177
> 129435223|12.0178
> 1294352232000|12.0177
> 1294352233000|12.0178
> 1294352234000|12.0176
> 1294352236000|12.0177
> 1294352238000|12.0178
> 1294352252000|12.0179
> 1294352257000|12.0147
> 1294352258000|12.0175
> 1294352259000|12.0178
> 1294352261000|12.0147
> 1294352264000|12.0148
> 1294352265000|12.0147
> 129435228|12.0135
> 1294352281000|12.0138
> 1294352282000|12.0133
> 1294352284000|12.0149
> 1294352292000|12.014
> 1294352312000|12.0125
> 1294352314000|12.0146
> 1294352319000|12.0144
> 129435232|12.0136
> 1294352323000|12.0138
> 1294352337000|12.014
> 1294352342000|12.0124
> 129435236|12.0138
> 1294352373000|12.0124
> 1294352379000|12.0105
> 1294352385000|12.0124
> 1294352387000|12.0121
> 1294352388000|12.0117
> 1294352415000|12.0118
> 129435244|12.0125
> 1294352446000|12.0105
> 1294352459000|12.011
> 1294352466000|12.0117
> 1294352469000|12.011
> sqlite> DELETE FROM GBP_CHF_BID WHERE price LIKE "12.%";
> sqlite> SELECT * FROM GBP_CHF_BID WHERE price LIKE "12.%";
> 129435210|12.0169
> 1294352102000|12.017
> 1294352107000|12.0166
> 1294352125000|12.0168
> 1294352129000|12.0166
> 1294352139000|12.0168
> 129435215|12.017
> 129435216|12.0161
> 1294352174000|12.0169
> 1294352182000|12.0171
> 1294352212000|12.0151
> 1294352217000|12.0182
> 129435222|12.0177
> 1294352226000|12.0156
> 1294352228000|12.0157
> 1294352229000|12.0177
> 129435223|12.0178
> 1294352232000|12.0177
> 1294352233000|12.0178
> 1294352234000|12.0176
> 1294352236000|12.0177
> 1294352238000|12.0178
> 1294352252000|12.0179
> 1294352257000|12.0147
> 1294352258000|12.0175
> 1294352259000|12.0178
> 1294352261000|12.0147
> 1294352264000|12.0148
> 1294352265000|12.0147
> 129435228|12.0135
> 1294352281000|12.0138
> 1294352282000|12.0133
> 1294352284000|12.0149
> 1294352292000|12.014
> sqlite>
>
>
> Corey
> ___
> 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] Load SQLite from InputStream in Java as ReadOnly

2012-06-28 Thread Hector Guilarte

I don't think it would.

Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db",
config.toProperties());

is telling to open a connection to the file "sample.db" which is located in
the same folder as the application is executing, but is going for a *file*.
However, I'm going to check what configuration properties can be passed to
the method to see if one says something about a socket, an inputStream or
something like that.

Thanks!

On Thu, Jun 28, 2012 at 3:37 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:

> Does this article help?
>
>
> http://stackoverflow.com/questions/4574303/java-sqlite-how-to-open-database-as-read-only
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Hector Guilarte [hector...@gmail.com]
> Sent: Thursday, June 28, 2012 8:22 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly
>
> Because I want it to run on Google App Engine. GAE doesn't allow to write
> to the FileSystem, so I can't open the JDBC Connection to the SQLite file
> and therefore I can't read the data to convert to CSV.
>
> I upload the file to Google App Engine with a HTML form and that's how I
> get it in an InputStream
>
> On Thu, Jun 28, 2012 at 3:19 PM, OBones <obo...@free.fr> wrote:
>
> > If you've got the database in a stream, why can't you save it to a disk
> > file and then use this file with sqlite?
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

2012-06-28 Thread Hector Guilarte

Because I want it to run on Google App Engine. GAE doesn't allow to write
to the FileSystem, so I can't open the JDBC Connection to the SQLite file
and therefore I can't read the data to convert to CSV.

I upload the file to Google App Engine with a HTML form and that's how I
get it in an InputStream

On Thu, Jun 28, 2012 at 3:19 PM, OBones  wrote:

> If you've got the database in a stream, why can't you save it to a disk
> file and then use this file with sqlite?
>
> __**_
> 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] Load SQLite from InputStream in Java as ReadOnly

2012-06-28 Thread Hector Guilarte

Hello?

I'm starting to feel hopeless, No luck in StackOverflow and no luck here
:-(...

Well, i guess it just can't be done.

On Wed, Jun 27, 2012 at 10:13 AM, Hector Guilarte <hector...@gmail.com>wrote:

> Hello everyone,
>
> I'm new to SQLite as well as to this list. I'm writing because I was
> planning on using SQLite for a personal -but public- project that I wanted
> to make available through Google App Engine. It is basically a SQLite to
> CSV converter and a SQLite to VCard converter. In other words, I have an
> Address Book in a SQLite database and I wanted to export it to a well-known
> format for importing it to some other places, as CSV and as VCard.
>
> I already placed my question in StackOverflow.com last friday with no
> luck, it has only been seen 21 times and the only answer I received was not
> helpfull since it was telling me somehing like "first use something like
> what you are trying to develop yourself and then use yours with their
> output" (nahh, I'm kidding, but the real answer is not far from that and it
> can be seen here:
> http://stackoverflow.com/questions/11155537/load-sqlite-from-inputstream-in-java-as-readonly
> )
>
> If somebody has an answer, even if it is "It's not possible at all, so
> drop it" and is a stackoverflow user, feel free to go ahead and answer over
> there to earn the points, but please post your answer here as well. Now my
> question as I wrote it in StackOverflow:
>
> I have an App which receives a SQLite database to read some data and
> export it as an CSV. I'm trying to upload it to Google App Engine but I
> faced a huge problem which I think makes it impossible to use the GAE for
> this app.
>
> The problem is that since on the GAE I can't write to the FileSystem, I
> can't open the JDBC Connection to the SQLite file and therefore I can't
> read the data to convert to CSV. I've been looking for other options such
> as Google Cloud Storage, but I don't want to use my only "free trial" of it
> on this application, and actually I don't want to have to pay ever for this
> app after the Free Trial ends, so this is not an option.
>
> After a lot of research, my only guess is that I might be able to load the
> database straight from the InputStream as I received it from the upload
> form I'm using to get it, however, this is a 100% lucky guess and I've not
> been able to find anything about this approach online, but I just don't
> want to believe it can't be done with any of the existing JDBC libraries to
> SQLite and I'm hoping somebody here will tell me how to do it.
>
> If the InputStream approach is not possible, but you know some other way
> to open a SQLite DB in GAE to READ ONLY, and then dispose it, feel free to
> comment as well...
>
> If there is another option like "don't use JDBC, use a socket connection
> with a pipe to open the connection with the InputStream", I'd also like to
> hear that, it does not HAVE to be done with JDBC.
>
> Thanks a lot,
>
> Héctor Guilarte
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Load SQLite from InputStream in Java as ReadOnly

2012-06-27 Thread Hector Guilarte

Hello everyone,

I'm new to SQLite as well as to this list. I'm writing because I was
planning on using SQLite for a personal -but public- project that I wanted
to make available through Google App Engine. It is basically a SQLite to
CSV converter and a SQLite to VCard converter. In other words, I have an
Address Book in a SQLite database and I wanted to export it to a well-known
format for importing it to some other places, as CSV and as VCard.

I already placed my question in StackOverflow.com last friday with no luck,
it has only been seen 21 times and the only answer I received was not
helpfull since it was telling me somehing like "first use something like
what you are trying to develop yourself and then use yours with their
output" (nahh, I'm kidding, but the real answer is not far from that and it
can be seen here:
http://stackoverflow.com/questions/11155537/load-sqlite-from-inputstream-in-java-as-readonly
)

If somebody has an answer, even if it is "It's not possible at all, so drop
it" and is a stackoverflow user, feel free to go ahead and answer over
there to earn the points, but please post your answer here as well. Now my
question as I wrote it in StackOverflow:

I have an App which receives a SQLite database to read some data and export
it as an CSV. I'm trying to upload it to Google App Engine but I faced a
huge problem which I think makes it impossible to use the GAE for this app.

The problem is that since on the GAE I can't write to the FileSystem, I
can't open the JDBC Connection to the SQLite file and therefore I can't
read the data to convert to CSV. I've been looking for other options such
as Google Cloud Storage, but I don't want to use my only "free trial" of it
on this application, and actually I don't want to have to pay ever for this
app after the Free Trial ends, so this is not an option.

After a lot of research, my only guess is that I might be able to load the
database straight from the InputStream as I received it from the upload
form I'm using to get it, however, this is a 100% lucky guess and I've not
been able to find anything about this approach online, but I just don't
want to believe it can't be done with any of the existing JDBC libraries to
SQLite and I'm hoping somebody here will tell me how to do it.

If the InputStream approach is not possible, but you know some other way to
open a SQLite DB in GAE to READ ONLY, and then dispose it, feel free to
comment as well...

If there is another option like "don't use JDBC, use a socket connection
with a pipe to open the connection with the InputStream", I'd also like to
hear that, it does not HAVE to be done with JDBC.

Thanks a lot,

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

[sqlite] SQLITE3 in backend RPC client/server environment

2010-03-10 Thread Hector

Platform: windows

I want to explore replacing b-tree database (ISAM/Index files) in our
backend RPC server with SQLITE3.   I'm relatively new to SQLITE3 and
just reading up on its multi-thread and pooling framework. I'm not
quite sure how to implement it, but it seems I should be able once
understood.

Some insights would be appreciated. Here is some backend.

We need to maintain API backward compatibility on the client side, for
example, the API had traversal functions such as:

BOOL wcGetFirstFileRec(TFileRecord , DWORD );
BOOL wcGetNextFileRec(TFileRecord , DWORD );
BOOL wcGetPrevFileRec(TFileRecord , DWORD );
BOOL wcGetLastFileRec(TFileRecord , DWORD );

The tid is a handle returned to the last position in the server's
btree page chain.  So as long as its persistent, a loop such as this
can be done:

TFileRecord fr = {0}
DWORD tid = 0;
if (wcGetFirstFileRec(ft,tid)) do {

} while (wcGetNextFileRec(ft,tid));

On the backend, if tid is not zero, then it jumps to the position in
the index page to get the next/prev record position and thus ISAM record.

While the plan is provide new API functions specific for bulk
operations over the wire,  we need to keep compatible with the
existing API.

So my question is, given there are two SQLITE3 objects:

 SQLITE3 *db   <-- pointer to the database
 SQLITE3_STM *stm  <-- pointer to query

what strategy do I look at to provide RPC thread context integrity on
the backend.  Each API function has a context handle so each thread
will have its own instance of context block to work with.

Right now the btree database is globally opened exclusively by the
server and reader/writer locking is used for the server-side API I/O.

This is why it appears I should be able to replace the database with
SQLITE3 and at the very least keep the concurrency integrity.

So my initial thought was to open the database globally as its done
now and then for each thread context, maintain a context handle mapped
list of SQLITE3_STM pointers as they are opened.  They will be created
when GetFirstFileRec() or GetLastFileRec() are called returning tid as
the index/handle to SQLITE3_STM pointer.  It will be passed back in
Next/Prev as the SQLITE3_STM pointer along to continue with a fetch call.

Anyway, I am not sure if this is the wrong approach with SQLITE3, or
will be duplicating work, or there is a workable strategy already for
SQLITE3 for multithread client/server frameworks.

Thanks in Advance

PS: Researching this shows strategy where connection pools are used.
But I didn't see the integrity of this without having a cursor pool.
Does the SQLITE3 library maintain a list of cursors per connection?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE3 in backend RPC client/server environment

2010-03-10 Thread hector

Platform: windows

I want to explore replacing b-tree database (ISAM/Index files) in our 
backend RPC server with SQLITE3.   I'm relatively new to SQLITE3 and 
just reading up on its multi-thread and pooling framework. I'm not 
quite sure how to implement it, but it seems I should be able once 
understood.

Some insights would be appreciated. Here is some backend.

We need to maintain API backward compatibility on the client side, for 
example, the API had traversal functions such as:

BOOL wcGetFirstFileRec(TFileRecord , DWORD );
BOOL wcGetNextFileRec(TFileRecord , DWORD );
BOOL wcGetPrevFileRec(TFileRecord , DWORD );
BOOL wcGetLastFileRec(TFileRecord , DWORD );

The tid is a handle returned to the last position in the server's 
btree page chain.  So as long as its persistent, a loop such as this 
can be done:

TFileRecord fr = {0}
DWORD tid = 0;
if (wcGetFirstFileRec(ft,tid)) do {

} while (wcGetNextFileRec(ft,tid));

On the backend, if tid is not zero, then it jumps to the position in 
the index page to get the next/prev record position and thus ISAM record.

While the plan is provide new API functions specific for bulk 
operations over the wire,  we need to keep compatible with the 
existing API.

So my question is, given there are two SQLITE3 objects:

 SQLITE3 *db   <-- pointer to the database
 SQLITE3_STM *stm  <-- pointer to query

what strategy do I look at to provide RPC thread context integrity on 
the backend.  Each API function has a context handle so each thread 
will have its own instance of context block to work with.

Right now the btree database is globally opened exclusively by the 
server and reader/writer locking is used for the server-side API I/O.

This is why it appears I should be able to replace the database with 
SQLITE3 and at the very least keep the concurrency integrity.

So my initial thought was to open the database globally as its done 
now and then for each thread context, maintain a context handle mapped 
list of SQLITE3_STM pointers as they are opened.  They will be created 
when GetFirstFileRec() or GetLastFileRec() are called returning tid as 
the index/handle to SQLITE3_STM pointer.  It will be passed back in 
Next/Prev as the SQLITE3_STM pointer along to continue with a fetch call.

Anyway, I am not sure if this is the wrong approach with SQLITE3, or 
will be duplicating work, or there is a workable strategy already for 
SQLITE3 for multithread client/server frameworks.

Thanks in Advance

PS: Researching this shows strategy where connection pools are used. 
But I didn't see the integrity of this without having a cursor pool. 
Does the SQLITE3 library maintain a list of cursors per connection?

-- 
HLS



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

[sqlite] SQLITE3 Recompiling for FTS3

2010-03-07 Thread hector

For Windows:

I followed the best of my ability recompiling the most current
SQLITE3.DLL to get the FTS3 incorporated.  I added the compiler directive

SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS

and was able to create a SQLITE3.DLL but it faults when a example test
C/C++ program runs.

No problem compiling with FTS3 disable.  But the DLLs are smaller than
the official sqlite.org website v3.6.22 precompiled binaries For Windows.

My compile:  417,792 sqlite3.dll
Official:511,383 sqlite3.dll

The difference I can only see if the official has a dependency on  std
C++ rtl msvcrt.dllwhere my compiled has no dependency on msvcrt.dll

I am not sure if I have the MS VS2005 project file correct, but I also
recompiled under VC6 too.   All I did to create the project was to add
all the *.C/*.H files to a project and added the defined (for release)

WIN32
NDEBUG
_WINDOWS
_USRDLL
SQLITE3_EXPORTS;
SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_COLUMN_METADATA

I guess, I ideally I would like to able to reproduce the same official
size and then see how to get the FTS3 logic added.

Tips?

Thanks in Advance

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

[sqlite] SQLITE3 Recompiling for FTS3

2010-03-06 Thread Hector

For Windows:

I followed the best of my ability recompiling the most current 
SQLITE3.DLL to get the FTS3 incorporated.  I added the compiler directive

SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS

and was able to create a SQLITE3.DLL but it faults when a example test 
C/C++ program runs.

No problem compiling with FTS3 disable.  But the DLLs are smaller than 
the official sqlite.org website v3.6.22 precompiled binaries For Windows.

My compile:  417,792 sqlite3.dll
Official:511,383 sqlite3.dll

The difference I can only see if the official has a dependency on  std 
C++ rtl msvcrt.dllwhere my compiled has no dependency on msvcrt.dll

I am not sure if I have the MS VS2005 project file correct, but I also 
recompiled under VC6 too.   All I did to create the project was to add 
all the *.C/*.H files to a project and added the defined (for release)

WIN32
NDEBUG
_WINDOWS
_USRDLL
SQLITE3_EXPORTS;
SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_COLUMN_METADATA

I guess, I ideally I would like to able to reproduce the same official 
size and then see how to get the FTS3 logic added.

Tips?

Thanks in Advance

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

Re: [sqlite] cann't work

Re: [sqlite] DELETE only deletes some records, not others

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[sqlite] Load SQLite from InputStream in Java as ReadOnly

[sqlite] SQLITE3 in backend RPC client/server environment

[sqlite] SQLITE3 in backend RPC client/server environment

[sqlite] SQLITE3 Recompiling for FTS3

[sqlite] SQLITE3 Recompiling for FTS3

10 matches

Site Navigation

Mail list logo

Footer information