[sqlite] [newbie] SQLite and VB.Net?
Hello I don't know anything about .Net, and I'd like to build a quick app with Visual Studio 2005 or 2008 to check how well it performs with SQLite. If performance and deployment prove to be good enough, we'll use VB.Net for new projects and finally dump VB6. AFAIK, the default option is to program through ADO.Net, but it seems slow, and a lot of bagage considering that we have the luxury of choosing the DB the apps will talk to. Does someone know if there are good wrappers to SQLite that don't require ADO.Net, how well they perform, and if there's a HOWTO on how to get from installing the VS CD to a running SQLite? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I manually Lock a database?
From reading the documentation I see that sqlite seems to have a very smart locking mechanism going on under the hood, regulating concurrent access by multiple applications. Unfortunately, the designers of another application with which I share a database have decided to cache data internally, so that the only safe access is when that other app is not running. That's easy enough to detect with API from the OS, but a conflict can still occur if they launch and start reading while I am in the middle of writing something I don't want to stop. I need to lock the database so that they get SQLITE_BUSY until I'm done. How can I manually lock the database using the C API? I can't find any "lock" function. Thanks, Jerry Krinock ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how do I know for sure that my data hit the disk?
"Adam Megacz" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have an application that absolutely must not return from a certain > call until the results of an update are safely committed to disk. The > situation above would be considered "not safe". How can I perform an > update and then wait until I am completely certain that the data is on > the disk, regardless of whether or not other selects are still in > progress? Blocking for a long time is okay, returning early is not. Open a separate connection to the same database, perform the update on this connection. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how do I know for sure that my data hit the disk?
>From http://www.sqlite.org/lockingv3.html If multiple commands are being executed against the same SQLite database connection at the same time, the autocommit is deferred until the very last command completes. For example, if a SELECT statement is being executed, the execution of the command will pause as each row of the result is returned. During this pause other INSERT, UPDATE, or DELETE commands can be executed against other tables in the database. But none of these changes will commit until the original SELECT statement finishes. I have an application that absolutely must not return from a certain call until the results of an update are safely committed to disk. The situation above would be considered "not safe". How can I perform an update and then wait until I am completely certain that the data is on the disk, regardless of whether or not other selects are still in progress? Blocking for a long time is okay, returning early is not. I've recently had problems with the above situation occurring, my application declaring "the data is safely on the disk", and then the process gets killed. When the process comes back, the data is gone. Thanks, - a ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 01 Mar 2008 18:50:56 +0100, Gilles Ganault
<[EMAIL PROTECTED]> wrote:
>This doesn't work as intended, because it returns all the rows,
>effectively ignoring the WHERE part:
Thanks everyone for the help. Problem solved:
$dbh = new PDO("sqlite:test.sqlite");
$sql = "SELECT Table1.*,Table2.name FROM Table1,Table2 WHERE
Table1.table2id=1 AND Table2.id=1";
$rows = $dbh->query($sql);
echo "";
while($row = $rows->fetch(PDO::FETCH_NUM) ) {
echo "\n";
for($i=0;$i$row[$i]\n";
}
echo "\n";
}
echo "";
$dbh = null;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 18:23:43 -0500, "Stephen Oberholtzer" <[EMAIL PROTECTED]> wrote: >I have to ask: Why is it that you expected a condition applying to one >column on one table, to also apply to a differently named column in a >differently named table? Because I'm not clear about how joins work :-) Thanks for the help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, Mar 1, 2008 at 6:13 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sat, 1 Mar 2008 18:04:12 -0500, "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote: > >> INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); > >> INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); > >> = > >> INSERT INTO Table1 VALUES (NULL,"John Doe",1); > >> INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); > >> = > >> SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; > >> = > >> 1|John Doe|1|1|Some text in Table2 > >> 1|John Doe|1|2|Some other text in Table2 > >> = > > > > >I'm confused. Which one of those rows does not have table1.table2id=1? > > Sorry for the imprecision: It's the same record, but why do I get two > rows instead of one? I expected only the first one, since "Some text > in Table2" has its ID = 1. Then you need to specify that: SELECT * FROM Table1,Table2 WHERE Table1.table2id=1 AND Table2.ID=1; I have to ask: Why is it that you expected a condition applying to one column on one table, to also apply to a differently named column in a differently named table? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 18:04:12 -0500, "Stephen Oberholtzer" <[EMAIL PROTECTED]> wrote: >> INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); >> INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); >> = >> INSERT INTO Table1 VALUES (NULL,"John Doe",1); >> INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); >> = >> SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; >> = >> 1|John Doe|1|1|Some text in Table2 >> 1|John Doe|1|2|Some other text in Table2 >> = > >I'm confused. Which one of those rows does not have table1.table2id=1? Sorry for the imprecision: It's the same record, but why do I get two rows instead of one? I expected only the first one, since "Some text in Table2" has its ID = 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling sources for Coldfire embedded platform
On Fri, Feb 29, 2008 at 11:01 PM, <[EMAIL PROTECTED]> wrote: > > > I've downloaded and built the sources for the standard Intel Linux > platform but I want to run this on Linux on a Coldfire (MCF5484) platform. > How do I modify the compiler the build uses (short of just modifying the > Makefile)? I've looked at the Makefile.in but there's nothing obvious > about how to change it. > > Didn't see any docs on this. Any help appreciated. > I don't know that specifically, but the usual method for that stuff is CC=compilername make -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 15:35:01 -0500, "P Kishor"
<[EMAIL PROTECTED]> wrote:
>SELECT table1.*, table2.col1
>FROM table1 JOIN table2 ON table1.field10 = table2.field1
Thanks for the tip, but...
1. I want to SELECT all the columns from Table1 and one column from
Table2, but only rows WHERE table1.field10 = 1. The above SELECT
returns all the rows from Table1.
I put the SQL commands in my other reply, so it makes more sense.
2. I can't use the "." notation because PHP doesn't allow this as
column names in associative arrays.
For instance, this doesn't work:
===
$dbh = new PDO("sqlite:test.sqlite");
$sql = "SELECT Table1.*, Table2.name FROM Table1 JOIN Table2 ON
Table1.table2id = Table2.id";
$rows = $dbh->query($sql);
while($row = $rows->fetch(PDO::FETCH_ASSOC) ) {
//Nothing shown...
echo sprintf("%s",$row['Table1.name']);
}
$dbh = null;
===
So the solution I found is use absolute names for all columns, eg.
Table1_table2id. I should have specified this in the orignal post.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
> = > CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, > table2id INTEGER); > CREATE TABLE Table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); > = > INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); > INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); > = > INSERT INTO Table1 VALUES (NULL,"John Doe",1); > INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); > = > SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; > = > 1|John Doe|1|1|Some text in Table2 > 1|John Doe|1|2|Some other text in Table2 > = > > I expected only the first row, but I got two :-/ I'm confused. Which one of those rows does not have table1.table2id=1? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On Sat, 1 Mar 2008 15:27:19 -0500, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >What do you mean, all the rows? Are you saying the resultset contains >some rows where table1_field10 is not equal to table2_field1? With all >due respect, I find it very hard to believe. That's what happens, though. FWIW, I'm using 3.5.6: = CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, table2id INTEGER); CREATE TABLE Table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); = INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); INSERT INTO Table2 VALUES (NULL,"Some other text in Table2"); = INSERT INTO Table1 VALUES (NULL,"John Doe",1); INSERT INTO Table1 VALUES (NULL,"JaneDoe",2); = SELECT * FROM Table1,Table2 WHERE Table1.table2id=1; = 1|John Doe|1|1|Some text in Table2 1|John Doe|1|2|Some other text in Table2 = I expected only the first row, but I got two :-/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Crashes
Hey all, Over at Mozilla we've been seeing a large amount of crashes in sqlite3_enable_shared_cache. The stack frames don't make a whole lot of sense to me, so I thought I'd inform you and hope that you might have a better idea as to what is going on. If you have any questions, feel free to ask. If I don't know the answer, I'll get the people who should know involved. We'd really like to try and resolve this issue, so insight on this matter would be greatly appreciated. http://tinyurl.com/2393qs We are presently using the latest version of sqlite. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
On 3/1/08, Gilles Ganault <[EMAIL PROTECTED]> wrote: > Hello > > I have two tables: Table1 has about 10 columns, Table2 has 2. I need > to get all the columns of Table1 and only one column in Table2 where > some field in Table1 is equal to field1 in Table2. > > This doesn't work as intended, because it returns all the rows, > effectively ignoring the WHERE part: > > SELECT * FROM table1,table2 WHERE table1_field10=table2_field1; > > => Is there a smarter way to solve the problem than replacing "*" with > every single column, ie. > > SELECT table1_field1, table1_field2... table1_field10,table2_field1, > table2_field2 FROM table1,table2 WHERE table1.field10=table2.field1; > SELECT table1.*, table2.col1 FROM table1 JOIN table2 ON table1.field10 = table2.field1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Alternative to * to SELECT most columns?
"Gilles Ganault" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have two tables: Table1 has about 10 columns, Table2 has 2. I need > to get all the columns of Table1 and only one column in Table2 where > some field in Table1 is equal to field1 in Table2. > > This doesn't work as intended, because it returns all the rows, > effectively ignoring the WHERE part: > > SELECT * FROM table1,table2 WHERE table1_field10=table2_field1; What do you mean, all the rows? Are you saying the resultset contains some rows where table1_field10 is not equal to table2_field1? With all due respect, I find it very hard to believe. > => Is there a smarter way to solve the problem than replacing "*" with > every single column, ie. Realize that the list of columns in the SELECT clause has no effect on the number of rows in the resultset. It only limits how much of each row you can see. If you feel there are rows in the resultset that shouln't be there, changing the column list won't help you solve that. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ API callback problem
Thanks Igor and Teg, I think I know were I was going wrong now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ API callback problem
"Toby Roworth" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Looking at the API reference. it would apear you can send an extra > "custom" argument to the callback fro sqlite3_exec, using the 4th > parameter - how does this work, and inperticular, could I pass an > object through to the call back, and if so, how? You can pass anything that fits into void* - typically a pointer to some structure or object you define. SQLite will simply pass it through to the callback as-is, without any modifications. The callback will then simply cast the void* pointer back to the original object pointer. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ API callback problem
Hello Toby, You can pass in anything you want, a pointer, a number. As long as it fits in the native size of the parameter. You can pass the ADDRESS of an object as long as it doesn't go out of scope between the call and when the processing finishes. I tend to pass the "this" pointer to the class that owns the processing so, I can cast a pointer to the class from within the callback and operate on my class. You should seriously consider NOT using the callbacks at all. Using the "prepare" API. I find I seldom need them. Pretty much the only reason I'll use them now is for displaying progress on some long term process. C Saturday, March 1, 2008, 8:03:00 AM, you wrote: TR> Hello all TR> Looking at the API reference. it would apear you can send an extra TR> "custom" argument to the callback fro sqlite3_exec, using the 4th TR> parameter - how does this work, and inperticular, could I pass an object TR> through to the call back, and if so, how? TR> Thanks TR> Toby TR> ___ TR> sqlite-users mailing list TR> sqlite-users@sqlite.org TR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Alternative to * to SELECT most columns?
Hello I have two tables: Table1 has about 10 columns, Table2 has 2. I need to get all the columns of Table1 and only one column in Table2 where some field in Table1 is equal to field1 in Table2. This doesn't work as intended, because it returns all the rows, effectively ignoring the WHERE part: SELECT * FROM table1,table2 WHERE table1_field10=table2_field1; => Is there a smarter way to solve the problem than replacing "*" with every single column, ie. SELECT table1_field1, table1_field2... table1_field10,table2_field1, table2_field2 FROM table1,table2 WHERE table1.field10=table2.field1; Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing an insert/update
Hi Michael,
> I have a table with two columns, the first with a string and the
> second with
> an integer.
> Given a set of input strings, I want to perform this operation
> 50,000+ times
So maybe something like:
create table InputTable
(
InputString text collate nocase
)
;
with 50,000+ rows such as:
insert into InputTable values ('Mickey');
insert into InputTable values ('Donald');
insert into InputTable values ('Mickey');
insert into InputTable values ('Goofy');
insert into InputTable values ('Minnie');
> preferably in a single transaction: "If the string doesn't exist in
> the
> table, create a new row with the string in the first column and 1 in
> the
> second column. If the string does exist in the table, increment the
> second
> column by 1"
Here's one simple solution:
create table Summary
as
select InputString, count(*)
from InputTable
group by InputString
;
which gives:
Donald 1
Goofy1
Mickey 2
Minnie 1
Tom
BareFeet
--
Cheapest ADSL1 and ADSL2 in Australia:
http://www.tandb.com.au/broadband/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Statement
"Mahalakshmi.m" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Can I bind the unsigned short value [ie., like 0x0065 for English and > 0x3045 > for Japanese] to its corresponding string value.is it possible. > > Unsigned short temp; > For eg, > If temp = 0x0065 then its corresponding english string 'a' should > come while > binding.It works out by using sprintf();But If temp = 0x30E4 then its > corresponding Japanese string should come.For this sprintf() is not > working. ... but wsprintf should. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C++ API callback problem
Hello all Looking at the API reference. it would apear you can send an extra "custom" argument to the callback fro sqlite3_exec, using the 4th parameter - how does this work, and inperticular, could I pass an object through to the call back, and if so, how? Thanks Toby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C++ api - callbacks problem
Hello all Looking at the API reference. it would apear you can send an extra "custom" argument to the callback fro sqlite3_exec, using the 4th parameter - how does this work, and inperticular, could I pass an object through to the call back, and if so, how? Thanks Toby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
Well I'm very new to SQLite but shouldn't:
UPDATE table SET Value=12.3 WHERE Address=7 and Port=1
be:
UPDATE table SET Value='12.3' WHERE Address='7' and Port='1';
sqlite3_vmprintf() is the recommended method to build SQL with
parameters.
Saturday, March 1, 2008, 6:31:50 PM, you wrote:
tti> The code is very long, I'll try to put here the core of my application.
tti> I'm using a C++ Class where one function is "sqlraw" that I use to
tti> execute a SQL statement:
tti> CLASS DEFINITION
tti> sqlite3 *db;
tti> int expander::
tti> open_db(char * pDbName)
tti> {
tti>int rc;
tti>rc = sqlite3_open(pDbName, &db);
tti> if( rc )
tti>{
tti> fprintf(stderr, "Can't open database: %s\n",
tti> sqlite3_errmsg(db));
tti> sqlite3_close(db);
tti> exit(1);
tti>}
tti>return(0);
tti> }
tti> int expander::sqlraw(char *pSql)
tti> {
tti>int rc;
tti>char *zErrMsg = 0;
tti> printf("SQLRAW: SQL=%s\n",pSql);
tti>printf("Database %d\n",db);
tti>rc =
tti> sqlite3_exec(db,pSql, NULL, NULL, &zErrMsg );
tti>printf("SQLRAW: Stato=%d
tti> - OK=%d\n",rc, SQLITE_OK);
tti>if( rc!=SQLITE_OK )
tti>{
tti>fprintf(stderr,
tti> "SQL error: %s\n", zErrMsg);
tti>sqlite3_free(zErrMsg);
tti>return(-1);
tti>}
tti> return(0);
tti> }
tti> int main(int argc ,char *argv[])
tti> {
tti>expander expio;
tti> char sPre[2048[;
tti>expio.open("test.db");
tti>strcpy(sPre,"UPDATE
tti> table SET Value=12.3 WHERE Address=7 and Port=1");
tti>if (expio.sqlraw
tti> (sPre) == 0)
tti>{
tti>/ / Action for no error
tti>}
tti>else
tti>{
tti> // Manage error conditions
tti>}
tti> When I execute the code, sqlraw
tti> function print the pSql string, and this is the same I pass.
tti> The
tti> Database descriptor is the same returned from open function, and status
tti> code is OK!!!
tti> But table value isn't updated.
tti> I don't understand
tti> what's matter, and because i haven't any error message I can't debug
tti> it.
tti> Any suggestion is VERY VERY appreciate
tti> Pierluigi Bucolo
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C++ api - callbacks problem
Hello all Looking at the API reference. it would apear you can send an extra "custom" argument to the callback fro sqlite3_exec, using the 4th parameter - how does this work, and inperticular, could I pass an object through to the call back, and if so, how? Thanks Toby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C++ api - callbacks problem
Hello all Looking at the API reference. it would apear you can send an extra "custom" argument to the callback fro sqlite3_exec, using the 4th parameter - how does this work, and inperticular, could I pass an object through to the call back, and if so, how? Thanks Toby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.5.6 and readline
Hi, > I am trying to compile sqlite in a /custom/directory and keep the ability > to use arrows to get previously entered commands in the sqlite3 > executable. I read the wiki about that topic > http://www.sqlite.org/cvstrac/wiki?p=ReadLine > but I am still failing. It is advised to find the variable READLINE_FLAGS > and LIBREADLINE in the Makefile, but I can only find: > READLINE_LIBS > I am not sure what I should enter there as a value and whether that would > be enough to solve my problem Note that the above page refers to the case where readline is installed in an unusual location, not SQLite itself. This page is outdated anyway, as it refers to SQLite 2.7.x and a bug that seems to have been fixed in more recent releases. Setting CPPFLAGS and LDFLAGS does work. See below. If readline is already installed in a default location where it can be found by the compiler and linker, you don't have to mess with the Makefile. On my Linux workstation for example: $ cd /tmp $ wget -q http://www.sqlite.org/sqlite-amalgamation-3.5.6.tar.gz $ tar xzf sqlite-amalgamation-3.5.6.tar.gz $ cd sqlite-3.5.6 $ configure --prefix=/tmp/install [...] checking for library containing readline... -lreadline checking for readline... yes [...] config.status: creating Makefile config.status: executing depfiles commands $ make [...] $ make install [...] $ Now if readline is not installed on your Unix system and you have installed it in an unusual location, it's a different issue. I have simulated it by removing the readline development package on my Linux workstation: # rpm -e readline-devel # and then installing readline in /tmp/install: $ cd /tmp $ wget -q ftp://ftp.cwru.edu/pub/bash/readline-5.2.tar.gz $ tar xzf readline-5.2.tar.gz $ cd readline-5.2 $ ./configure --prefix=/tmp/install [...] $ make [...] $ make install [...] $ export LD_LIBRARY_PATH=/tmp/install/lib: $ I was then able to build SQlite using the readline library in /tmp/install: $ cd /tmp $ wget -q http://www.sqlite.org/sqlite-amalgamation-3.5.6.tar.gz $ tar xzf sqlite-amalgamation-3.5.6.tar.gz $ cd sqlite-3.5.6 $ $ ./configure --prefix=/tmp/install [...] checking for library containing readline... no checking for readline... no [...] $ $ export CPPFLAGS=-I/tmp/install/include $ export LDFLAGS=-L/tmp/install/lib $ ./configure --prefix=/tmp/install [...] checking for library containing readline... -lreadline checking for readline... yes [...] $ make [...] $ make install [...] $ $ ldd /tmp/install/bin/sqlite3 [...] libsqlite3.so.0 => /tmp/install/lib/libsqlite3.so.0 [...] libreadline.so.5 => /tmp/install/lib/libreadline.so.5 [...] [...] $ Regards, -- Dimitri ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export to SQL insert statements with column names
[EMAIL PROTECTED] wrote:
>
> Hi,
> Is there a way to export/dump SQLite data into INSERT statements which
> also have column names?
>
> As of now, a sqlite dump looks like this
>
> INSERT INTO "ric_tb_language" VALUES('ENG','English');
> INSERT INTO "ric_tb_language" VALUES('SPN','Spanish');
> INSERT INTO "ric_tb_language" VALUES('GER','German');
>
> Is it possible to have it like below(column names included)?
>
> INSERT INTO "ric_tb_language" (ID, language) VALUES('ENG','English');
> INSERT INTO "ric_tb_language" (ID, language) VALUES('SPN','Spanish');
> INSERT INTO "ric_tb_language" (ID, language) VALUES('GER','German' );
You might find the impexp.c source file in the SQLite ODBC
driver on http://www.ch-werner.de/sqliteodbc useful for this purpose.
It implements some SQLite 3 extension functions. The export_sql()
function should produce your desired output format if invoked as
select export_sql('filename', 1, 'ric_tb_language');
HTH,
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
The code is very long, I'll try to put here the core of my application.
I'm using a C++ Class where one function is "sqlraw" that I use to
execute a SQL statement:
CLASS DEFINITION
sqlite3 *db;
int expander::
open_db(char * pDbName)
{
int rc;
rc = sqlite3_open(pDbName, &db);
if( rc )
{
fprintf(stderr, "Can't open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
return(0);
}
int expander::sqlraw(char *pSql)
{
int rc;
char *zErrMsg = 0;
printf("SQLRAW: SQL=%s\n",pSql);
printf("Database %d\n",db);
rc =
sqlite3_exec(db,pSql, NULL, NULL, &zErrMsg );
printf("SQLRAW: Stato=%d
- OK=%d\n",rc, SQLITE_OK);
if( rc!=SQLITE_OK )
{
fprintf(stderr,
"SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
return(-1);
}
return(0);
}
int main(int argc ,char *argv[])
{
expander expio;
char sPre[2048[;
expio.open("test.db");
strcpy(sPre,"UPDATE
table SET Value=12.3 WHERE Address=7 and Port=1");
if (expio.sqlraw
(sPre) == 0)
{
/ / Action for no error
}
else
{
// Manage error conditions
}
When I execute the code, sqlraw
function print the pSql string, and this is the same I pass.
The
Database descriptor is the same returned from open function, and status
code is OK!!!
But table value isn't updated.
I don't understand
what's matter, and because i haven't any error message I can't debug
it.
Any suggestion is VERY VERY appreciate
Pierluigi Bucolo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prepare Statement
If my Table is as follows:
create table Music ( id integer not null primary key,
classificationCode integer, input text) <<
Table:
id classificationCode input
-- -- -
1 1 aaa
2 0 1345
3 1 asdf
At this point,
sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE input
>= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0);
Can I bind the unsigned short value [ie., like 0x0065 for English and 0x3045
for Japanese] to its corresponding string value.is it possible.
Unsigned short temp;
For eg,
If temp = 0x0065 then its corresponding english string 'a' should come while
binding.It works out by using sprintf();But If temp = 0x30E4 then its
corresponding Japanese string should come.For this sprintf() is not working.
Can anyone please help to solve this.
Regards,
Mahalakshmi
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen Oberholtzer
Sent: Friday, February 29, 2008 2:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Prepare Statement
On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m
<[EMAIL PROTECTED]> wrote:
>
>
> Hi,
> My table looks like:
> IdName
> 1 1aaa
> 2 01345
> 3 1asdf
>
> I want to bind unsigned short as text. i.e, If the Unsighed short is
> 0x0061 I want to bind it as 'a'.
>
> My Prepare statement is as follows:
>
> Unsigned char u8_ClassificationCode=1;
>
> Unsigned short u16_Input=0x0061;
>
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC
> WHERE Name >= '%d%c' LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!=
> SQLITE_OK)
>
> {
>
> return SQLITE_DB_ERROR;
>
> }
>
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
>
> }
>
Since nobody else mentioned it: there's something seriously wrong with your
database design.
But first: Your usage of sqlite3_bind_text16 is incorrect. The
fourth argument, -1, means "My string is NUL-terminated. Use strlen() to
figure out how long my string is and use that.".
However, for that to always work correctly, u16_input needs to be an array
with a NUL terminator:
>> unsigned short u16_input[] = { 'a', '\0' }; <<
Anyway, back to what I was saying: your database design needs rethinking.
1NF (http://en.wikipedia.org/wiki/First_normal_form)
states that a column should only have one value. However, you seem
to be combining *two* values (Classification Code and Input) into one column
(Name). Therefore, you should be doing this:
>> create table Music ( id integer not null primary key,
classificationCode integer, input text) <<
Table:
id classificationCode input
-- -- -
1 1 aaa
2 0 1345
3 1 asdf
At this point, you would do this:
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE classificationCode = ? AND input
>= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); << Note that, if you you want
the original form, you can do
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode
|| input as Name FROM MUSIC WHERE classificationCode = ? AND input >=
? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); << This will convert
classificationCode to a string and join it against the 'input' column to
return your original Name.
>> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC); <<
This also means you can index the string portion of your Name column
separately, and quickly search for something with a specific name without
knowing its classification.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
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] Retrieve Rownumber in sqlite
Hi Kalyani, There is a column named ROWID which gives you the internal row number. Saturday, March 1, 2008, 9:32:44 AM, you wrote: KP> In SQL Server2005, Row_number() function is used to retrieve the KP> sequential number of a row within a partition of a result set, starting KP> at 1 for the first row in each partition. Which is very useful when KP> implementing paging through a large number records in Table. Is there KP> any function available in SQLite similar to this. KP> Thanks KP> -Kalyani -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling sources for Coldfire embedded platform
I've downloaded and built the sources for the standard Intel Linux platform but I want to run this on Linux on a Coldfire (MCF5484) platform. How do I modify the compiler the build uses (short of just modifying the Makefile)? I've looked at the Makefile.in but there's nothing obvious about how to change it. Didn't see any docs on this. Any help appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
