Re: [sqlite] Direct PostgreSQL to SQLite connection?

[Vivien Malerba]

Hi!

Using Libgda (http://www.gnome-db.org) you can create virtual connections
which "aggregate" several other connections (meaning all the tables from
all the connections can be used in single SQL queries and you could copy
data using an "INSERT INTO XXX SELECT ... FROM YYY" query), in your case
you could aggregate a PostgreSQL and a SQLite connection.

Hope it helps,
Regards
Vivien


On 25 September 2013 18:45, joe.fis...@tanguaylab.com <
joe.fis...@tanguaylab.com> wrote:

> Looking for a solution to connect directly from a PostgreSQL database to a
> SQLite database. We need PostgreSQL for it's Window/Analytic functions and
> other features that SQLite doesn't have. Yet, SQLite is great for
> collecting data.
>
> Every one of our experiments adds 300MB to the SQLite database. Rather
> than exporting to CSV and then importing to PostgreSQL, we'd like to
> directly access SQLite from PostgreSQL.
>
> Does anyone have good experience using 'Foreign Data Wrapper for sqlite'
> or another method?
> https://github.com/gleu/**sqlite_fdw 
>
>
> Joe Fisher
> Oregon State University
>
> __**_
> 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] Connection between SQLite - Other Databases

[Vivien Malerba]

Hi!

You can check Libgda which allows you to open several connections (e.g. 1
for SQLite and 1 for Oracle), bind them together in different namespaces,
and execute SQL code on all the bound connections at the same time.

Regards,

Vivien


On 20 June 2013 06:34, Reddy C. balaji  wrote:

> Hi All,
> Please let me know the options available to connect to
> other databases ( Oracle, MSSQL , MySQL etc ) from SQLite. For example, to
> connect from Oracle to other databases DBLink can be used. In the same way,
> from MSSQL , Linked Server can be used to connect to other databases.
>
> In the same way are there any features available in SQLite ??
>
> Thank you
> Reddy Balaji C.
>
> DISCLAIMER: This email message and all attachments are confidential and
> may contain information that is Privileged, Confidential or exempt from
> disclosure under applicable law. If you are not the intended recipient, you
> are notified that any dissemination, distribution or copying of this email
> is strictly prohibited.  If you have received this email in error, please
> notify us immediately by return email to mailad...@spanservices.com and
> destroy the original message.  Opinions, conclusions and other information
> in this message that do not relate to the official of SPAN, shall be
> understood to be nether given nor endorsed by SPAN.
> ___
> 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] Building an SQL AST

[Vivien Malerba]

Le 12 juil. 2012 21:40, "Aaron Patterson"  a
écrit :
>
> Hi,
>
> I would like to build an AST of a SQL statement using SQLite3.  I've
> been reading through sqlite3RunParser as well as the grammar.  It
> *looks* like the grammar is building some sort of structure before the
> statement is compiled to byte code, but I'm getting lost.
>
> Is it possible to get an AST out of the parser without major surgery?
> If so, can someone point me in the right direction?
>

I don't know if this is what you are looking for, but Libgda (
http://www.gnome-db.org) includes a parser which takes and SQL string and
builds a abstract tree, see
http://developer-next.gnome.org/libgda/stable/GdaSqlParser.html and
http://developer-next.gnome.org/libgda/stable/libgda-5.0-GdaSqlStatement.html
.

If this is what you're looking for, you can either use Libgda or copy the
bits you want.

Regards,

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 15:31, Black, Michael (IS)  wrote:

> If you have sqlite3.c in your project you can just add your own hook.
>
>
>
> Take a look at  sqlite3TwoPartName if you want to see if before it's
> created.
>
>
>
> Or look at sqlite3StartTable (end of the function) if you want to know
> after it's created.
>
>
>
> That will catch a VIEW creation too.
>

Yes, I had had this idea, but I would like to resort to modifying SQLite's
source code if there is no other solution as I want my program to work with
the standard packaged SQLite (for Linux and ease of update reasons).

Thanks a lot anyway for your suggestion,

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 15:00, kyan  wrote:

> > Is there any possibility to be notified when a table is created (when a
> > "CREATE TABLE XXX" is executed)?
>
> You could try installing a profile callback using sqlite3_profile()
> (see http://www.sqlite.org/c3ref/profile.html). Of course this
> callback will be called for any kind of SQL or DDL statement that gets
> executed in your database so you will have to distinguish CREATE TABLE
> statements by parsing the SQL command text in the profiler callback's
> second argument.
>

This is similar to what I'm doing at the moment (parsing the SQL before
execution).
Thanks for the tip anyway!

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 14:55, Simon Slavin  wrote:

>
> On 26 Jun 2012, at 1:33pm, Vivien Malerba  wrote:
>
> > The code is some SQL entered by the user, I have no control over it.
> There
> > is effectively the possibility to parse the SQL entered, detect the
> CREATE
> > table statement and act accordingly, but I would like to see if SQLite
> has
> > some kind of builtin feature I could use first.
>
> Could you poll 'PRAGMA schema_version' ?
>
> <http://www.sqlite.org/pragma.html#pragma_schema_version>
>
> It changes when something creates or drops a table or index.  Of course,
> it won't tell you the actual CREATE command.  For that you could look at
> the table called "sqlite_master".
>

I need a notification _before_ the command is executed, so this won't do.


>
> > Also, for example if the user executes a query as "CREATE TABLE xxx AS
> > SELECT ..." then it's much more difficult to know how to define the XXX
> > table as I would have to parse the SELECT query part as well. It's doable
> > of course but can become a bit complicated.
>
> I think this will be your problem.  Even if you could set up notifications
> for changes, trying to convert what they did to what you want to do instead
> looks complicated.


Still, it seems it's the easiest way to go (as a matter of fact this is
already partially done for the soft I work on which is Libgda).

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 14:21, Igor Tandetnik  wrote:

> Vivien Malerba  wrote:
> > Is there any possibility to be notified when a table is created (when a
> > "CREATE TABLE XXX" is executed)?
>
> How is it executed? Isn't it your program that executes the statement? How
> come you don't know when that happens?
>

The code is some SQL entered by the user, I have no control over it. There
is effectively the possibility to parse the SQL entered, detect the CREATE
table statement and act accordingly, but I would like to see if SQLite has
some kind of builtin feature I could use first.

Also, for example if the user executes a query as "CREATE TABLE xxx AS
SELECT ..." then it's much more difficult to know how to define the XXX
table as I would have to parse the SELECT query part as well. It's doable
of course but can become a bit complicated.

Regards,

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

[sqlite] Detecting when a table is created

[Vivien Malerba]

Hi!

Is there any possibility to be notified when a table is created (when a
"CREATE TABLE XXX" is executed)?

The context here is that I have an SQLite connection with only virtual
tables and when the user manually creates a table, the table is created
either in memory or in the temporary file (depending on how the SQLite
connection was opened in the first place), whereas I would like to create a
virtual table instead.

Thanks for any answer!

Regards,

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

Re: [sqlite] savepoint

[Vivien Malerba]

2012/1/16 Csaba Jeney 

> Is there any way to query the valid savepoints? At least their names?
> Many thanks.
>
>
AFAIK, the only way to do this is to keep track of each begin, rollback,
add savepoint, ... executed, to always know where you are.
(Anyway this is what's done in Libgda).

Regards,

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

Re: [sqlite] SQLite C - APIs to insert data in to a table.

[Vivien Malerba]

On 12 January 2012 12:22, bhaskarReddy  wrote:

>
> Hi Friends,
>
>  I am using SQLite. This is the first time. And i am creating,
> accessing and retrieving data from the table.
>
> I am using sqlite3_exec() function to insert and retrieve data.
> Instead using sqlite3_exec(), is there any other APIs which help inserting
> data into tables.
>
>
>
You can also have a look at the Libgda library, see http://www.gnome-db.org

Vivien

PS: to be completely honest I have to tell that I'm Libgda's maintainer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Vivien Malerba]

On 8 December 2010 20:44, Richard Hipp  wrote:
> On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille 
> wrote:
>
>> Hello,
>>
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>>
>> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>>
>> Any reason for such deprecation?
>>
>
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?

Libgda uses that information a lot. If you remove this pragma, I'll
have to add a lot of code to parse the table create statement. Please
don't remove that pragma!

Regards,

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

Re: [sqlite] virtual tables

[Vivien Malerba]

On 23 November 2010 17:39, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 11/23/2010 02:04 AM, Vivien Malerba wrote:
>> The Libgda library (http://www.gnome-db.org) uses virtual tables
>
> Are you sure?  It looks like an abstraction layer that sits above several
> databases, with similar functionality to ODBC/JDBC.

Yes, it is, but with some more features.

Basically here is how one uses it to execute statements involving data
stored in two different databases:
* open a connection (let's name it c1) to, for example, a PostgreSQL db,
* open a connection (c2) to, for example, a MySQL db,
* open a connection (c3) binding c1 and c2 and execute the SELECT,
UPDATE,... statements in c3 using tables from c1 and c2

c3 is actually an SQLite (in memory) connection which takes all the
tables in c1 and makes them appear as tables in c3 using SQLite's
virtual tables (and the same for c2's tables). The SQL used in c3 is
parsed and executed by SQLite (internally it creates some other
statements to access data from c1 and c2's tables).

It is also possible to add individual tables in c3 (for example from a
CVS file), or add other connections (c4, c5,...) to c3.

Regards,

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

Re: [sqlite] virtual tables

[Vivien Malerba]

The Libgda library (http://www.gnome-db.org) uses virtual tables to
enable one to execute statements on several tables from several
database backends (SQlite, PostgreSQL, MySQL, Oracle, Jdbc, SqlCipher,
MDB) and CSV files.

Regards,

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

Re: [sqlite] gui for data entry

[Vivien Malerba]

On 12 October 2010 17:51, Graham Smith  wrote:
> There are several GUI editors for SQLite, but is there one that allows
> creation of simple forms to allow data entry.  Although some allow
> adding data to a single table, none that I have looked at seem to
> allow a new record to be added when it involves more than one table.
>
> It needs to be cross platform (Linux, Widows and Mac) and ideally
> free, but a commercial product would still be considered.
>

Libgda: http://www.gnome-db.org

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

Re: [sqlite] SQLite 3.7.0 coming soon....

[Vivien Malerba]

For what it's worth, I've just tested Libgda with the latest dev.
version of the 3.7.0 and found no problem at all with the NR tests
(note that Libgda uses a lot the virtual tables features).

Thanks a lot for your very good work in SQlite!

Regards,

Vivien

On 30 June 2010 19:21, D. Richard Hipp  wrote:
> We are in the final phases of development for SQLite version 3.7.0.
> The key enhancement over version 3.6.23.1 is support for the use of
> write-ahead logs for transaction control.  See 
> http://www.sqlite.org/draft/wal.html
>  for additional information on how this might enhance your  use of
> SQLite.
>
> The transition from 3.6.23.1 to 3.7.0 is a large one, and we would
> appreciate beta testing.  Recent snapshots of SQLite can be found at 
> http://www.sqlite.org/draft/download.html
>  and those snapshots pass all of our extensive tests.  But testing
> can only prove the existence of bugs, not their absence and so we are
> eager for others to try out the latest code changes, and in particular
> the new write-ahead log feature, and let us know your experiences,
> before we make the next release.
>
> Please provide feedback - positive, negative, or indifferent - to this
> mailing list.
>
> We are currently aiming to release 3.7.0 sometime during July.  Thank
> you in advance for your help in testing this important new release.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] copy data from one db to another

[Vivien Malerba]

On 9 June 2010 20:44, Rich Shepard  wrote:
> On Wed, 9 Jun 2010, Vivien Malerba wrote:
>
>>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>>> there's no source file to copy.  Though a backup might be
>>> interesting ...
>
>> You can use Libgda's gda-sql tool in which you can:
>> * open a connection to the PostgreSQL db (for example named db1)
>> * open a connection to the SQLite db (for example named db2)
>> * bind those 2 connections into a 3rd one, and execute statements like
>> "insert into db2.table_one_name select * from db1.table_one_name ;"
>
>   Why not do a database dump from postgres, then read the .sql file into
> SQlite? As long as you use standard SQL in the data development language
> (DDL) you'll get ASCII SQL files for each table's schema with INSERT
> statements for each row of each table.

Because it's easier to set up, you won't have any problem with dates,
and you can do much more complicated statements (for example to do
comparisons, partial updates, ...)

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

Re: [sqlite] copy data from one db to another

[Vivien Malerba]

On 9 June 2010 18:58, Scott Frankel  wrote:
>
> On Jun 9, 2010, at 12:22 AM, Vivien Malerba wrote:
>
>> On 8 June 2010 22:02, Scott Frankel  wrote:
>>>
>>> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>>>
>>>>
>>>>> What's the best way to copy data from one db to another?
>>>>>
>>>>> Given 2 databases with identical schemas, one full of data and the
>>>>> other empty, the brute force way would be to perform selects on the
>>>>> source db, then for each row, perform an insert into the
>>>>> destination
>>>>> db.  Is there a more efficient way?
>>>>
>>>> The easiest is either to simply copy the file as Igor suggested or
>>>> use
>>>> the backup API (very easy too).
>>>
>>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>>> there's no source file to copy.  Though a backup might be
>>> interesting ...
>>
>> You can use Libgda's gda-sql tool in which you can:
>> * open a connection to the PostgreSQL db (for example named db1)
>> * open a connection to the SQLite db (for example named db2)
>> * bind those 2 connections into a 3rd one, and execute statements like
>> "insert into db2.table_one_name select * from db1.table_one_name ;"
>
> While Libgda looks very interesting, I need a solution that's
> accessible from common Linux, OSX, and Windows base installs.

Libgda (and associated tools) is available for those 3 OSes.

Regards,

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

Re: [sqlite] copy data from one db to another

[Vivien Malerba]

On 8 June 2010 22:02, Scott Frankel  wrote:
>
> On Jun 8, 2010, at 12:51 PM, Jean-Christophe Deschamps wrote:
>
>>
>>> What's the best way to copy data from one db to another?
>>>
>>> Given 2 databases with identical schemas, one full of data and the
>>> other empty, the brute force way would be to perform selects on the
>>> source db, then for each row, perform an insert into the destination
>>> db.  Is there a more efficient way?
>>
>> The easiest is either to simply copy the file as Igor suggested or use
>> the backup API (very easy too).
>
> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
> there's no source file to copy.  Though a backup might be
> interesting ...

You can use Libgda's gda-sql tool in which you can:
* open a connection to the PostgreSQL db (for example named db1)
* open a connection to the SQLite db (for example named db2)
* bind those 2 connections into a 3rd one, and execute statements like
"insert into db2.table_one_name select * from db1.table_one_name ;"

If you want more info, tell me.

Regards,

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

Re: [sqlite] Export sqlite database to H2

[Vivien Malerba]

On 9 April 2010 13:37, Andreas Henningsson
 wrote:
> Hi
>
> I am about to change database from SQLite to H2 in a project of mine.
>
> Is there any tools I can use to do i it? What is the best way to export data
> from an SQLite database?
>

The easiest is probably that you export your data from SQLite as an
SQL dump, adapt it to the H2 dialect and use it to import your data
into your H2 database.

There is another solution however using the gda-sql tool from Libgda
(I'm the Libgda's maintainer) where you can have more control over
what get migrated from SQLite to H2 as the migration operation is done
by executing some "INSERT into  SELECT ... FROM " statements.

The idea is to use the gda-sql command to open 2 connections: one to
the SQLite database, and one to the H2 database and then "bind" them
together into a 3rd virtual connection in which you execute the
"INSERT into..." statements. If you want to explore this way, I can
give you more details.

Regards,

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

Re: [sqlite] Parsing create statements

[Vivien Malerba]

> BareFeet wrote:
>> At the moment I am resorting to developing regular expressions to do the 
>> parsing. They work, but it seems to be re-inventing the wheel.
>
> You won't be able to do parsing completely with regular expressions.  Create
> statements let you specify default values for a column and that can be any
> arbitrary SQL expression.
>

You can use SQLite's own SQL parser
(http://www.hwaci.com/sw/lemon/lemon.html) with SQLite's own grammar
(to ba found in SQLite's sources) and adapt it to you needs.

Regards,

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

Re: [sqlite] Sqlite3 is not compiling in Linux

[Vivien Malerba]

2009/4/14 Pramoda M. A 

> What I have done is,
>
> Cc -lpthread sqlite3.o ourApplication.o -o main
>
> Then it is giving linker error. Is it right?


try instead:
cc -lpthread -ldl sqlite3.o ourApplication.o -o main

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

Re: [sqlite] Sqlite3 is not compiling in Linux

[Vivien Malerba]

2009/4/14 Pramoda M. A 

> Hi All,
>
>
>
> I tried to compile in Linux. But it is giving error at linking stage as
> 'unreferenced dlopne dlclose...
>
> Can anybody help me?


did you add the -ldl linker flag?

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

[sqlite] Unsigned int 64 in WHERE

[Vivien Malerba]

Hi!

I have a table with a timestamp column which I use to insert sqlite_uint64
values using sqlite3_bind_int64()
(values are retreived using sqlite3_column_int64() with a cast to
sqlite_uint64). This works fine with the C API.

The problem is that when I try to use the sqlite3 command line, if I use:
SELECT data FROM mytable WHERE ts=18446744072622041475;
I get no result at all (the 18446744072622041475 value is too big to fit
into a signed 64 bit integer, but it fits in an unsigned 64 bits integer),
so I have to use
SELECT data FROM mytable WHERE ts=-1087510141;

In other terms, I have to use the signed 64 bits integer in my WHERE clause
(2^64-1087510141 = 1844674407262204147).

Is there any other way of storing an unsigned 64 bits integer in the
database than storing it as a signed value and casting its binary value to
unsigned, for example if there was a sqlite3_bind_Uint64() and a
sqlite3_column_Uint64()?

Thanks a lot,

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

Re: [sqlite] journal_mode = off crash in 3.6.10

[Vivien Malerba]

This has already been fixed, see
http://www.sqlite.org/cvstrac/tktview?tn=3603

Regards,

Vivien

2009/2/4 Brodie Thiesfield 

> Hi,
>
> I know that there has been a number of crashes involving
> journal_mode = off. This problem continues for me in
> sqlite 3.6.10 almalgamation though. A trigger seems to
> be the cause.
>
> The following test program crashes on the last line
> (sqlite3_step) with a NULL pointer dereference of
> id->pMethods when compiled on Windows with VC2003.
>
> sqlite.c line 12576
>
> SQLITE_PRIVATE int sqlite3OsWrite(sqlite3_file *id, const void *pBuf, int
> amt, i64 offset){
>  DO_OS_MALLOC_TEST;
>  return id->pMethods->xWrite(id, pBuf, amt, offset);
> }
>
> If the prop table and associated triggers are removed,
> the crash doesn't occur.
>
> Regards,
> Brodie
>
>
> The stack trace is:
>
> >   testsqlite.exe!sqlite3OsWrite(sqlite3_file * id=0x00510b58, const
> void * pBuf=0x0012e3e4, int amt=4, __int64 offset=0)  Line 12576 + 0x1b
>   C
>testsqlite.exe!write32bits(sqlite3_file * fd=0x00510b58, __int64
> offset=0, unsigned int val=4)  Line 31128 + 0x17   C
>testsqlite.exe!subjournalPage(PgHdr * pPg=0x00388a64)  Line 33450 +
> 0x1bC
>testsqlite.exe!pager_write(PgHdr * pPg=0x00388a64)  Line 34311 + 0x9
>C
>testsqlite.exe!sqlite3PagerWrite(PgHdr * pDbPage=0x00388a64)  Line
> 34420 + 0x9  C
>testsqlite.exe!insertCell(MemPage * pPage=0x00388e88, int i=0,
> unsigned char * pCell=0x00388f20, int sz=9, unsigned char *
> pTemp=0x, unsigned char nSkip=0)  Line 40971 + 0xc   C
>testsqlite.exe!sqlite3BtreeInsert(BtCursor * pCur=0x00385760, const
> void * pKey=0x0051d698, __int64 nKey=8, const void * pData=0x004f6c22, int
> nData=0, int nZero=0, int appendBias=0)  Line 42175 + 0x19   C
>testsqlite.exe!sqlite3VdbeExec(Vdbe * p=0x00386808)  Line 52819 +
> 0x2c  C
>testsqlite.exe!sqlite3Step(Vdbe * p=0x00386808)  Line 47849 + 0x9
> C
>testsqlite.exe!sqlite3_step(sqlite3_stmt * pStmt=0x00386808)  Line
> 47916 + 0x9  C
>testsqlite.exe!main(int argc=1, unsigned short * * argv=0x00381d88)
>  Line 48 + 0x9  C++
>testsqlite.exe!mainCRTStartup()  Line 259 + 0x19C
>kernel32.dll!7c817067()
>ntdll.dll!7c915d27()
>
>
> The test program is:
>
> #include 
> #include 
> #include 
> #include 
> #include "sqlite3.h"
>
> int main(int argc, TCHAR * argv[])
> {
>sqlite3 * pDatabase = NULL;
>char * pszError = NULL;
>sqlite3_stmt * pStatement = NULL;
>
>unlink("test.sqlite");
>unlink("test.sqlite-journal");
>
>assert(SQLITE_OK == sqlite3_open_v2("test.sqlite",
>&pDatabase, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL));
>
>assert(SQLITE_OK == sqlite3_exec(pDatabase,
>"CREATE TABLE dic ( entryid INTEGER PRIMARY KEY AUTOINCREMENT NOT
> NULL, headword TEXT NOT NULL, source TEXT NOT NULL ); "
>"CREATE INDEX dic_headword_idx ON dic ( headword ); "
>"CREATE TABLE prop ( pkey TEXT PRIMARY KEY NOT NULL, pval TEXT NOT
> NULL  ); "
>"INSERT INTO prop(pkey, pval) VALUES ('total_count', 0); "
>"CREATE TRIGGER count_insert_trigger AFTER INSERT ON [dic] FOR EACH
> ROW BEGIN"
>"UPDATE prop SET pval = pval + 1 WHERE pkey = 'total_count'; "
>"END; "
>"CREATE TRIGGER count_delete_trigger AFTER DELETE ON [dic] FOR EACH
> ROW BEGIN"
>"UPDATE prop SET pval = pval - 1 WHERE pkey = 'total_count'; "
>"END; ",
>NULL, NULL, &pszError));
>
>assert(SQLITE_OK == sqlite3_close(pDatabase));
>
>assert(SQLITE_OK == sqlite3_open_v2("test.sqlite", &pDatabase,
> SQLITE_OPEN_READWRITE, NULL));
>
>assert(SQLITE_OK == sqlite3_exec(pDatabase,
>"PRAGMA journal_mode = OFF; "
>"BEGIN IMMEDIATE TRANSACTION; ",
>NULL, NULL, &pszError));
>
>assert(SQLITE_OK == sqlite3_prepare_v2(pDatabase,
>"INSERT INTO dic (entryid, headword, source) VALUES (?, ?, ?);", -1,
> &pStatement, NULL));
>
>assert(SQLITE_OK == sqlite3_bind_null(pStatement, 1));
>assert(SQLITE_OK == sqlite3_bind_text16(pStatement, 2, "foo", -1,
> NULL));
>assert(SQLITE_OK == sqlite3_bind_text16(pStatement, 3, "bar", -1,
> NULL));
>
>assert(SQLITE_DONE == sqlite3_step(pStatement)); // crash here
>
>return 0;
> }
>
> ___
> 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] Question about sqlite3_reset

[Vivien Malerba]

Hi!

I have the following situation:
* a prepared statement (for a SELECT) on which sqlite3_step has been called
successfully (meaning having returned SQLITE_ROW or SQLITE_DONE each time)
* I call sqlite3_reset() on it
* I start using sqlite3_step again on it

The question is: will the rows I read on the 2nd pass be the same as the
ones I've read on the 1st pass, or will the statement be executed again (and
may be different if the database has changed in the meanwhile)?

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

Re: [sqlite] Sqlite Server

[Vivien Malerba]

On 5/27/07, noname <[EMAIL PROTECTED]> wrote:


I am using SQL Server as a back end in my vb6 application i want to switch
over to sqlite server but terrainformatica.com site has not provided rates
for server. so i am confused that should i use it or not i enquired on sites
also but i am getting proper answer from them. My application process huge
data of hospital. please suggest me that what i should do may i can waint
for SQlite server or suggest me other server like SQLite server on which
Developer licence are available. My client should not need to purchase back
end.



See http://www.it77.de/sqlite/sqlite_sockets.htm for an implementation
(I don't know about the status) of a SQLite server.

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

Re: [sqlite] file structure issue

[Vivien Malerba]

On 5/23/07, weiyang wang <[EMAIL PROTECTED]> wrote:

hi,

i can see the source file structure has been changed from 65 (sqliteint.h,
os_common.h,. )files before to only 2 files (sqlite3.h&sqlite3.c)now.
and this change bring me a problem when i try to integrate sqlite with other
platforms.

in my sqlite integration, i have to put my porting layer os_xxx into a
seperate 'cpp' file and to include related defines (defined in
sqliteint.hand os_common.h in early file structure).

and due to compiler issues, i cant include sqlite3.c in my 'cpp' file. i am
trapped here.

does anyone know how can i get the source codes with the early file
structure? (65 seperate files)? thanks in advance.


You can extract individual files from the sqlite3.c using for example:
sed -n -e '/Begin file sqliteInt.h/,/End of sqliteInt.h/p' sqlite3.c >
sqliteInt.h

However I don't know if it is safe to use those files to do some
compilation then.

Regards,

Vivien

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

Re: [sqlite] Multiple connections to the same database and CREATE TABLE command

[Vivien Malerba]

On 5/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Vivien Malerba" <[EMAIL PROTECTED]> wrote:
> Hi!
>
> In a single process, I open two connections (C1 and C2) to the same
> database (this is actually a corner case which could happen) and the
> following sequence of operations fail:
> 1- on C1 execute "CREATE table actor (...)" => Ok
> 2- on C1 execute "SELECT * FROM actor" => Ok
> 3- on C2 execute "SELECT * FROM actor" => error because table "actor"
> does not exist.
>

When C2 goes to parse the SQL in statement 3, it does not know
that the database schema has changed because it has not attempted
to access the database file.  Thus it does not know that the new
table exists.

To fix this, you have to get C2 to access the database so that
it will reread and reparse the schema and thus discover the new
table.  Perhaps something like this:

   2.5- on C2 execute "SELECT 1 FROM sqlite_master LIMIT 1"



This seems to work, see test case (uncomment line 68, 69 to make it
work, remove the Test.db before each run).

Thanks!

Vivien
#include 
#include 
#include 

static int
execute_sql (sqlite3 *db, const char *sql)
{
	int status;
	sqlite3_stmt *stmt = NULL;
	int retval = 0;

	printf ("== %p SQL: %s\n", db, sql);
	status = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL);
	if (status != SQLITE_OK) 
		printf ("   ERROR preparing statement: %s\n", sqlite3_errmsg (db));
	else {
		status = sqlite3_step (stmt);
		if ((status != SQLITE_OK) && (status != SQLITE_DONE) && (status != SQLITE_ROW))
			printf ("   ERROR executing statement: %s\n", sqlite3_errmsg (db));
		else {
			retval = 1;
			printf ("   Ok\n");
		}
	}

	if (stmt)
		sqlite3_finalize (stmt);
	
	return retval;
}

int 
main(int argc, char **argv){
	char *dbname = "Test.db";
	sqlite3 *db1, *db2;
	int rc;

 	rc = sqlite3_open(dbname, &db1);
	if (rc) {
		printf ("Can't open database: %s\n", sqlite3_errmsg(db1));
		exit(1);
	}
	rc = sqlite3_open(dbname, &db2);
	if (rc) {
		printf ("Can't open database: %s\n", sqlite3_errmsg(db2));
		exit(1);
	}

	printf ("DB1: %p\nDB2: %p\n", db1, db2);

	if (! execute_sql (db1, " CREATE TABLE if not exists actor (\
  actor_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
  last_update TIMESTAMP NOT NULL);"))
		exit (1);

	if (! execute_sql (db1, "SELECT * FROM actor"))
		exit (1);
	if (! execute_sql (db2, "SELECT * FROM actor"))
		exit (1);

	if (! execute_sql (db1, " CREATE TABLE if not exists othertable (\
  othertable_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
  last_update TIMESTAMP NOT NULL);"))
		exit (1);

	if (! execute_sql (db1, "SELECT * FROM othertable"))
		exit (1);
	/*if (! execute_sql (db2, "SELECT 1 FROM sqlite_master LIMIT 1"))
	  exit (1);*/
	if (! execute_sql (db2, "SELECT * FROM othertable"))
		exit (1);
	
	sqlite3_close(db1);
	sqlite3_close(db2);
	return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Multiple connections to the same database and CREATE TABLE command

[Vivien Malerba]

Hi!

In a single process, I open two connections (C1 and C2) to the same
database (this is actually a corner case which could happen) and the
following sequence of operations fail:
1- on C1 execute "CREATE table actor (...)" => Ok
2- on C1 execute "SELECT * FROM actor" => Ok
3- on C2 execute "SELECT * FROM actor" => error because table "actor"
does not exist.

Is there a way to make this sequence of operations work, or should I
make sure one can never open two connections on the same DB file in
the same process?

Thanks,

Vivien

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

Re: [sqlite] Error reporting problem

[Vivien Malerba]

On 3/26/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:

Vivien Malerba wrote:
> Hi!
>
> I've got an error reporting problem when trying to insert a row which
> breaks a UNIQUE constraint in a table in a C program, I get the
> following error with sqlite3_errmsg():
> "SQL logic error or missing database"
>
> If I fire the sqlite3 program and run the same SQL query, I get the
> following error:
> "SQL error: column name is not unique"
> which is much more obvious to understand.
>
> Any idea how I could get that error message calling sqlite3_errmsg()?

The pysqlite wrapper has a bug which reports the same error message if
you try to nest transactions. I fixed the wrapper (locally) by adding an
sqlite_reset(). I think there's a new API (sqlite_prepare_v2()?) which
has the same effect. If you can't search (say) gmane then post a tiny
code snippet and someone who uses the C API more than I do will spot the
problem straight away.


Here is a sample test case, just uncompress, run "make" and "./test".
Here is what I get using SQLite 3.3.13 (On Linux):
SQL error (step): SQL logic error or missing database
SQL error (step): column name is not unique

It shows that calling sqlite3_reset() seems to be required (contrary
to what the doc says), or maybe I got something wrong...

Thanks,

Vivien


test_case.tgz
Description: GNU Zip compressed data
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Error reporting problem

[Vivien Malerba]

Hi!

I've got an error reporting problem when trying to insert a row which
breaks a UNIQUE constraint in a table in a C program, I get the
following error with sqlite3_errmsg():
"SQL logic error or missing database"

If I fire the sqlite3 program and run the same SQL query, I get the
following error:
"SQL error: column name is not unique"
which is much more obvious to understand.

Any idea how I could get that error message calling sqlite3_errmsg()?

Thanks,

Vivien

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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Vivien Malerba]

On 3/13/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:

Vivien Malerba wrote:
> I've already sent a proposal along with a patch some time ago about
> that, but nobody seemed to care, see
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html

Vivien, I can't see any patch attached to that post. Perhaps you should
resubmit it? And I'll guess the odds of any patch being accepted are
significantly improved if the patch both passes and extends the test suite.



The patch was not archived. I guess I'll work on it to follow your
advice and make sure it passes the test suite. I can send it to you if
you want to have a look at it, though.

Cheers,

Vivien

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

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Vivien Malerba]

On 3/12/07, Stef Mientki <[EMAIL PROTECTED]> wrote:

If ask the table sturcture, with pragma table_info()

I get of course the basic fields, like:
   CID,Name,Type,

And also SOME special values, like
  Null, DefaultValue, PrimaryKey

But NOT the following special values (and probably a lot more)
  Unique, AutoIncrement



I've already sent a proposal along with a patch some time ago about
that, but nobody seemed to care, see
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html

Cheers,

Vivien

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

[sqlite] Need for new pragma directives

[Vivien Malerba]

Hi!

My project (Libgda/Libgnomedb) needs to be able to get more
information about a database structure, and I've made a patch which
defines 3 new pragma directives:
* "table_info_long()" which adds a column to the "table_info"
pragma to tell if a column is auto incremented (see ticket #1464)
* "proc_list" which returns a list of functions and aggregates
* "table_types_list" which returns a list of all the column types of
all the tables.

Please review the attached patch for inclusion in a future version and
tell me if there are problems so I can improve the patch if needed.

Thanks,

Vivien

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

Re: [sqlite] Handling null characters in blob data

[Vivien Malerba]

On 11/15/06, John Stanton <[EMAIL PROTECTED]> wrote:

Vivien Malerba wrote:
> On 11/13/06, Shivshankar Subramani - TLS , Chennai <[EMAIL PROTECTED]>
> wrote:
>
>> Hi all,
>>
>> > SQLite version 2.8 and earlier could not (easily) store binary
>> > data - data with embedded \000 characters.  Thus the encode/decode
>> > routines were provide to transform data so that it contained no
>> > \000 characters.
>> >
>> > SQLite version 3.0 can store binary data without difficulty.
>>
>> This is what  I read in the site
>> <http://www.mail-archive.com/sqlite-users@sqlite.org/msg04332.html>
>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg04332.html
>> . but
>> i am having difficulty in storing data with null characters in it.Is
>> there
>> any specific method in which i can solve this problem?
>>
>
> I personally use the X'AABBCCDD' syntax to store BLOBS where AABBCCDD
> is the hexadecimal representation of my binary data (one byte
> translated into ist 2 digits hexa equivalent). for example:
> "insert into mytable values (1, X'AABBCCDD');"
>
> Regards,
>
> Vivien
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
That is exapnsion, not compression!



It's just a notation, I believe SQLite does the conversion the other
way and stores the BLOB as a binary chunck.

Vivien

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

Re: [sqlite] Handling null characters in blob data

[Vivien Malerba]

On 11/13/06, Shivshankar Subramani - TLS , Chennai <[EMAIL PROTECTED]> wrote:

Hi all,

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

This is what  I read in the site

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



I personally use the X'AABBCCDD' syntax to store BLOBS where AABBCCDD
is the hexadecimal representation of my binary data (one byte
translated into ist 2 digits hexa equivalent). for example:
"insert into mytable values (1, X'AABBCCDD');"

Regards,

Vivien

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

Re: [sqlite] Wish to store a C structure in sqlite column

[Vivien Malerba]

On 9/22/06, AJ <[EMAIL PROTECTED]> wrote:

I hackish method might be something like this:


struct s
{
  ...
};

struct s  myS;

char buf[sizeof(s)*2];   // *2 as base64 encoding will be approx 33% bigger.
base64_encode( &myS, buf, sizeof(s) );

INSERT INTO table ( myTextField ) VALUES ( 'buf' );


then retrieval is the opposite.



Beware however that for this to work you must read and write from
machines with the same endian order, otherwise you'll end up with
corrupted data in your structure (for example intel/AMD processors are
little endian while powerPC processors are big endian). Also beware
about the 32/64 bits.

Vivien

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

Re: [sqlite] HexValues

[Vivien Malerba]

On 9/12/06, guy12 <[EMAIL PROTECTED]> wrote:


hi,

i need to insert hex values in the data base and after that i have to select
them again ...

what's the best way for doing that ??

i first used INTERGER for that but i think that is the wrong way...

p.s. i need to accress the db through my c-code


You can use the notation descrobid at
http://www.sqlite.org/datatype3.html for the BLOB data type.

Cheers,

Vivien

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

Re: [sqlite] need to form an frame work for database independent API

[Vivien Malerba]

On 8/18/06, Narendran <[EMAIL PROTECTED]> wrote:


I wish to form an database independant API for sqlite and BerkeleyDB . I need
to now wht factors  should consider .

i am new to both . I have problems in running an simple program in sqlite .
its say can't find the sqlite3_open(  . If i paste the source code for that
funstion ,it say that some other is so there . please help to resolve this
problem . I am really struck

thanks in advance .

B. Narendran(  Indian)


I'm working on libgda and libgnomedb which are abstraction libraries
for SQlite, postgreSQL, MySQL, Oracle, ... There is a BDB adaptator
but it needs some work.
Please wisit http://www.gnome-db.org (the website is very outdated but
development is still going on), or download the latest versions from
CVS.

Regards,

Vivien

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

[sqlite] Re: Patch proposal

[Vivien Malerba]

2005/9/28, Vivien Malerba <[EMAIL PROTECTED]>:
> Hi!
>
> Here is a small patch which modifies the returned data set of the
> PRAGMA table_info() to add a new column named "auto_inc" which
> contains 1 if the corresponding field has the AUTO INCREMENT in its
> definition and 0 otherwise.
>
> Could it be included in a future version of SQLite?
>
> Regards,
>
> Vivien
>

Forgot the patch!

[sqlite] Patch proposal

[Vivien Malerba]

Hi!

Here is a small patch which modifies the returned data set of the
PRAGMA table_info() to add a new column named "auto_inc" which
contains 1 if the corresponding field has the AUTO INCREMENT in its
definition and 0 otherwise.

Could it be included in a future version of SQLite?

Regards,

Vivien

Re: [sqlite] Schemas

[Vivien Malerba]

2005/9/28, Firman Wandayandi <[EMAIL PROTECTED]>:
> On 9/27/05, Vivien Malerba <[EMAIL PROTECTED]> wrote:
> > Hi!
> >
> > I'm working on the SQLite interface between the Libgda library and the
> > SQLite database. For the job, I need to know information about the
> > database schema, and I'm using the PRAGMA statements regarding
> > schemas.
> >
> > However I can't find the information about the AUTO INCREMENT
> > attribute of a column. Can anyone help me?
> >
>
> here they are,
>
> http://sqlite.org/datatypes.html
> http://sqlite.org/datatype3.html
>

I don't mean finding information about how to create AUTO INCREMENT
fields but about how to get that information for each field of an
already existing table in the database (like when using PRAGMA
table_info('table')).

Thanks,

Vivien

[sqlite] Schemas

[Vivien Malerba]

Hi!

I'm working on the SQLite interface between the Libgda library and the
SQLite database. For the job, I need to know information about the
database schema, and I'm using the PRAGMA statements regarding
schemas.

However I can't find the information about the AUTO INCREMENT
attribute of a column. Can anyone help me?

Thanks a lot,

Vivien