Re: [sqlite] exists clause in select

[Martin.Engelschalk]

Hi,

this seems like correct SQL to me. SQLite should execute it.
Doesn't it work?

Martin

Am 08.08.2011 07:50, schrieb William Canfield:
> Hi,
> I am having trouble writing a select statement. What I want to do is this:
>
> SELECT * FROM customers WHERE NOT EXISTS (SELECT * FROM orders WHERE
> customers.id = orders.cust_id);
>
> Is this possible at all in Sqlite?
>
> regards
> W. Canfield
> ___
> 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] Ensure that query acts on PRE-SORTED tables

[Martin.Engelschalk]

Hi,

i apologize beforehand if my post does not answer your question 
directly. It seems to me that you may be missing a basic concept.

Data in an SQL table is never sorted in itself. So, you can not sort a 
table before you query it.

If you select data without an "order by" - clause, the order in which 
you get the data is arbirtary and may also change from time to time. So, 
if you want to retrieve rows in a certain order, you have to add an 
"order by" clause to your select statement.

If you want to speed up such a query, you can create an index.
In your case pos seems to be a candidate for a primary key, because it 
is unique. So, create your table like this:

CREATE TABLE IF NOT EXISTS t_x(
   "pos integer primary key, "
 "txt text NOT NULL"

);

Then, data is indexed automatically by pos, which comes near to your 
intention. However, you must always add an "order by pos" clause to your 
select statement(s).

Perhaps you might want to read up on primary keys and indexes. This is 
not sqlite specific but a feature of SQL.

hth
Martin

Am 01.07.2011 11:16, schrieb e-mail mgbg25171:
> I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
> queried and am not sure of the syntax.
> Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
> (by column pos)  BEFORE I do the SELECT BETWEEN on THEM
> i.e. I purposefully inserted t_d row 1,1,1 to see if it would come out first
> ie as 1. It doesn't any help much appreciated.
> Apologies if my question isn't clear.
>
> #include "stdafx.h"
> #include "stdio.h"
> #include "sqlite3.h"
> #include  //malloc
> #include  //strlen
> #include  //assert
>
> /*i've made these global so...both Create_database AND Query_database can
> SEE them ie it shows what vars need to be common*/
> int res, ind = 0;
> char** sql;
> sqlite3_stmt *stmt;
> sqlite3* db=NULL;
> char* err=0;
>
> static int Open_db(char* flnm){
>  if (!strlen(flnm)){ res=sqlite3_open(":memory:",); }
>  else{ res=sqlite3_open(flnm,); }
>  if (!db){ printf("Open_db() failed\n"); }
>  return res;
> }
>
>
> void Close_db(){
>  res = sqlite3_close(db);
> }
>
>
> int Exec(char * s){
>  res = sqlite3_exec(db,s,0,0,0);
>  if ( res ){
>  printf( "Exec error re %s %s\n", s, sqlite3_errmsg(db)  );
>  }
>  assert(res==0); //so you can concentrate on 1st error
>  return res;
> }
>
>
> int _tmain(int argc, _TCHAR* argv[]){ //default project main
>  Open_db("");
>  //===
>  Exec("CREATE TABLE IF NOT EXISTS t_x("
>  "pos integer UNIQUE NOT NULL,"
>  "txt text NOT NULL"
>  ")"
>  );
>  Exec( "INSERT INTO t_x VALUES(1,'x1')" );
>  Exec( "INSERT INTO t_x VALUES(2,'x2')" );
>  //===
>  Exec("CREATE TABLE IF NOT EXISTS t_y("
>  "pos integer UNIQUE NOT NULL,"
>  "txt text NOT NULL"
>  ")"
>  );
>  Exec( "INSERT INTO t_y VALUES(1,'y1')" );
>  Exec( "INSERT INTO t_y VALUES(2,'y2')" );
>  //===
>  Exec("CREATE TABLE IF NOT EXISTS t_d("
>  "xpos integer NOT NULL,"
>  "ypos integer NOT NULL,"
>  "d float "
>  ")"
>  );
>  /*table layout
>  see onenote thoughts diary me at 30/06/2011 08:42
>  yx->
>  |1,2
>  V3,4  xy data
>VV V  */
>
>  Exec( "INSERT INTO t_d VALUES(1,2,3)" );
>  Exec( "INSERT INTO t_d VALUES(2,1,2)" );
>  Exec( "INSERT INTO t_d VALUES(2,2,4)" );
>  Exec( "INSERT INTO t_d VALUES(1,1,1)" );
>  //===
>
>  //
> http://dcravey.wordpress.com/2011/03/21/using-sqlite-in-a-visual-c-application/
>  //= this block from url albeit modified by me
> ==
>  const char* sqlSelect ="SELECT d FROM t_d "
>  "where xpos in "
>  "(SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND
> 'x2') ";
>  "AND ypos in "
>  "(SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND
> 'y2')";
>
>  char** results = NULL;
>  int rows, columns;
>  res = sqlite3_get_table(db, sqlSelect);
>  if (res){
>  //printf( "error in sqlite3_get_table %s\n", s, sqlite3_errmsg(db)
> );
>  sqlite3_free(err);
>  }
>  else{
>  // Display Table
>  for (int rowCtr = 0; rowCtr<= rows; ++rowCtr){
>  for (int colCtr = 0; colCtr<  columns; ++colCtr){
>  int cellPosition = (rowCtr * columns) + colCtr;
>  printf( "%s\t", results[cellPosition] );
>  }
>  printf( "\n");
>
>  }
>  }
>  sqlite3_free_table(results);
>
> 

Re: [sqlite] Should i upgrade SQLITE for my system?

[Martin.Engelschalk]

Hi,

you can look at the changes doumented here

http://www.sqlite.org/changes.html

and determine if they are beneficial for your application.

Also, my own application runs faster with the newest version (I upgraded 
from version 3.2.5)

Martin

Am 27.06.2011 09:48, schrieb Hoang Linh Duong:
> Hi all,
>
> Currently i'm using SQLite 3.6.22 for my system. I know the latest SQLite is
> version 3.7.7; im just wondering if i should update SQLite in my system. Is
> there any significant change in the this latest SQLite?
>
> Please advise. Thanks.
>
> Linh
> ___
> 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] SQLITE run slow

[Martin.Engelschalk]

Hi,

make sure you have an index on  category and distance, like

create index MyIndex on Location  (category, distance)

Because many records fulfill the category=17 condition, and if database 
size is an issue, an index only on distance might help also.

Martin


Am 14.06.2011 13:01, schrieb Hoang Linh Duong:
> Hi all,
>
> I have one SQL as below:
>
> SELECT name, type, category, x, y, ((x-645529)*(x-645529) +
> (y-1494293)*(y-1494293)) AS distance,
> FROM Location
> WHERE category=17
> ORDER BY distance
> LIMIT 100
>
> I run this SQL to retrieve data from my SQLITE database and it takes more
> than 1 minutes. Noted that my program is running on Win CE 6.0 device (ARM
> 532MHz, 128MB RAM). And there are 60 records in Location table, among
> that 11 records of type=17.
>
> I am just wondering if there is any way to speed up the SQL. I appreciate
> all advices and suggestions. Thank you!
>
> Regards,
> Linh
> ___
> 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] HELP: SQLException getErrorCode vs gerErrorMessage()

[Martin.Engelschalk]

Hello Sridhar,

please tell us more: what interface are you using? what OS?
SQLException is not a part of the sqlite library, so obviously you use 
an additional layer.

Martin

Am 08.06.2011 16:44, schrieb Sridhar Polavarapu:
> Appreciate if anyone can help me.
>
> Thanks
> Sridhar
>
> On 08-06-2011 16:16, Sridhar Polavarapu wrote:
>> Hi
>>
>> I am using getErrorCode() of SQLException to see if the exception is
>> due to any database lock. I all the time get Errorcode to be 0 even
>> though the getMessage is returning different messages. Can anyone let
>> me know if there is some thing that i need to do to get the error
>> codes correctly ?
>>
>> Thanks
>> Sridhar
> ___
> 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] How to know the offset of a rowid inside a table?

[Martin.Engelschalk]

Am 08.06.2011 16:37, schrieb Marco Bambini:
> Thanks Martin and Richard, solution was so simple that I think to need a time 
> break today.
No, an order by does not make any sense here, because the result set 
contains exactly one row.
> Should I add an ORDER BY rowid clause at the end of the SELECT statement or 
> its implicit by the WHERE clause?
>
> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.com
>
>
>
>
>
>
>
>
> On Jun 8, 2011, at 4:27 PM, Richard Hipp wrote:
>
>> On Wed, Jun 8, 2011 at 10:18 AM, Marco Bambini  wrote:
>>
>>> I have a table foo with N rows and I want to know the offset of the row
>>> with rowid X inside that table.
>>> What query/strategy should I perform?
>>>
>>> I assume that a brute force algorithm should be
>>> 1. SELECT * FROM foo ORDER BY rowid;
>>> 2. loop inside the recordset until X is found incrementing a counter by 1
>>> but I am quite sure that should be a more elegant way.
>>>
>>>
>> SELECT count(*) FROM foo WHERE rowid<=X
>>
>> The above gives an answer in linear time.  It is theoretically possible to
>> add a little extra metadata to the btree nodes in order to compute the
>> offset logarithmic time.  But I deliberately decided not to included that
>> metadata when I designed the SQLite btree file format since keeping that
>> metadata current slows down write performance.
>>
>>
>>
>>> Thanks a lot.
>>> --
>>> Marco Bambini
>>> http://www.sqlabs.com
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> -- 
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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] How to know the offset of a rowid inside a table?

[Martin.Engelschalk]

Hi,

select count(*) from foo where rowid < X

Martin

Am 08.06.2011 16:18, schrieb Marco Bambini:
> I have a table foo with N rows and I want to know the offset of the row with 
> rowid X inside that table.
> What query/strategy should I perform?
>
> I assume that a brute force algorithm should be
> 1. SELECT * FROM foo ORDER BY rowid;
> 2. loop inside the recordset until X is found incrementing a counter by 1
> but I am quite sure that should be a more elegant way.
>
> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.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] Unknown parameter for prepare_v2

[Martin.Engelschalk]

Hi,

you cannot bind the name of a table. Bind variables only work for Values 
in the database, like you used in the VALUES('1', ?) - clause.
Names of tables, columns or other items of the schema must be written in 
the sql statement.
You will have to build your statement (using sprintf() or similar) 
before preparig it.

This is not uniqe to sqlite, but to SQL in general.

Martin

Am 19.05.2011 10:04, schrieb Dev_lex:
> Hello,
>
> I've a little question about sqlite3_prepare_v2 :
>
> I would like to do this :
>
>const char   *zSql = "INSERT INTO ?(ID, MyData) VALUES('1',?)";
>
>   if(ppStmt)
>{
>sqlite3_bind_parameter_name(ppStmt, "atest");
>sqlite3_bind_blob(ppStmt, 2,, sizeof(blob), SQLITE_TRANSIENT);
>sqlite3_step(ppStmt);
>sqlite3_finalize(ppStmt);
>}
>
> So then I've just to bind values.. It works great for the second '?' which
> is a blob, so bind_blob(), but for the first one I don't really know what to
> use ?
>
> Maybe the '?' syntax is incorrect? I've not understood everything about it..
>
> Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Joinery

[Martin.Engelschalk]

Hi,

select a.ID
   from a
   join b on b.aID = a.ID
  where b.user = 'MyUser'

The join is an inner join, so that only rowa of table a are selected 
where a rocord in b exists. This seems to be what you want.

Martin

Am 21.01.2011 12:33, schrieb Ian Hardingham:
> Hey guys.
>
> This is just an utterly simple question I know, but I still haven't got
> my head around it.  I have two tables:
>
> Table A
> int ID
>
> Table B
> int user
> int aID
>
> I need a query which selects all elements of Table A which are "owned"
> by a specific user, ie for which there is an entry with user, id in Table B.
>
> Thanks,
> Ian
> ___
> 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] sqlite3 ltrim behaviour bug or feature?

[Martin.Engelschalk]

Hi,

this is the expected behaviour.

See http://www.sqlite.org/lang_corefunc.html

"The ltrim(X,Y) function returns a string formed by removing any and all 
characters that appear in Y from the left side of X. If the Y argument 
is omitted, ltrim(X) removes spaces from the left side of X. "

The second argument is not a string but a set of characters. ltrim() 
strips leading zeros if you include a zero anywhere in your second 
argunent. This is the case in the first two examples.

Martin

Am 13.01.2011 14:41, schrieb Thilo Jeremias:
> Hi,
> the following seems wrong to me:
>
> bash-4.0# sqlite3
> SQLite version 3.6.14.2
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
>
>
> sqlite>  select ltrim("12300567","1230");
> 567
> sqlite>  select ltrim("012300567","0123");
> 567
> sqlite>  select ltrim("12300567","123");
> 00567
> sqlite>
>
>
> Is the stripping of leading 0's intentional?
> (or a bug in my netbsd port?)
>
> How can I workaround this problem?
>
>
> cheers thilo
>
>
> ___
> 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] How to optimize a multi-condition query

[Martin.Engelschalk]

  Hi,

the condition in your query

select achr,bchr from c where achr=bchr

involves 2 columns of the table. In this case, an index is useless.
If you do

select achr,bchr from c where achr='foo'

then the index will be used.

Martin



Am 15.10.2010 15:09, schrieb Black, Michael (IS):
> Ok then... I added 67,600 records like this and still no index use.
>
> SQLite version 3.7.2
> sqlite>  select count(*) from c;
> 67600
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
> sqlite>  create index c_chr on c(achr,bchr);
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Here's my record add:
> #include
> #include
> #include "sqlite3.h"
> int main()
> {
>   sqlite3 *db;
>   char *errmsg=NULL;
>   int rc;
>   int i,j,k;
>   sqlite3_open("test.db",);
>   rc=sqlite3_exec(db, "CREATE TABLE c (achr char, bchr 
> char)",NULL,NULL,);
>   if (rc != SQLITE_OK) {
>puts(errmsg);
>sqlite3_free(errmsg);
>   }
>   sqlite3_exec(db,"BEGIN",NULL,NULL,);
>   for(k=0;k<100;k++) {
>for(i=0;i<26;i++) {
> char sql[4096];
> for(j=0;j<26;j++) {
>  sprintf(sql,"INSERT INTO c VALUES ('%c','%c')",'a'+i,'a'+j);
>  rc=sqlite3_exec(db, sql,NULL,NULL,);
>  if (rc != SQLITE_OK) {
>   puts(sql);
>   puts(errmsg);
>   sqlite3_free(errmsg);
>   exit(-1);
>  }
> }
>}
>   }
>   sqlite3_exec(db,"COMMIT",NULL,NULL,);
>   sqlite3_close(db);
>   return 0;
> }
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of luuk34
> Sent: Fri 10/15/2010 7:40 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query
>
>
>
>
>On 15-10-10 14:34, Black, Michael (IS) wrote:
>> I love simple examples like this can help people with understanding 
>> things...so I tried this which I thought would do what Hilmar wants...but 
>> alaswhat concept am I missing?
>>
>> SQLite version 3.7.2
>> sqlite>   create table c(achr char,bchr char);
>> sqlite>   create index c_chr on c(achr,bchr);
>> sqlite>   explain query plan select achr,bchr from c where achr=bchr;
>> 0|0|TABLE c
>>
>> Why no use of the index in this case?
>>
> because there are no records in the database,
> so its quicker to read just all records,
> than to read all record in the order of the index...
>
> --
> Luuk
> ___
> 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] [C-API] Query returns only 0

[Martin.Engelschalk]

  Hello Gerald,

i think you should tell us more about what you are trying to do and add 
some C code.
what do you mead by "return value"? What functions do you call? Do you 
know that you have to get the selected value by calling a function like 
sqlite3_column_text ?

Martin

Am 30.09.2010 11:41, schrieb Legen Där:
> Hello Gyus,
>
>
>
> ive got problems with my qeuries. After loading a table, i create a statement 
> for every column to prepare the querys. A sample for such a prepared query 
> is..
>
>
>
> SELECT "F_SIZE_M0200_m0_VALUE" FROM fileInfos WHERE FILEINFO_FULLNAME = ?1
>
>
>
>   ...for that query i bind TEXT values like... "C:\Dokumente und 
> Einstellungen\gkaeding\Eigene Dateien\Bilder\Color1000\0.jpg". Unfortunately 
> the returned values are only 0. If i execute this query with the SQLite 
> Database Browser it works. :|
>
>
>
> Any suggestions?
>
>
>
> Thanks in advance!
>
>
>
> Gerald
>   
> ___
> 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] Reserve database pages

[Martin.Engelschalk]

Am 12.08.2010 13:04, schrieb TeDe:
>   Am 12.08.2010 12:16, schrieb Martin.Engelschalk:
>> Am 12.08.2010 12:08, schrieb TeDe:
>>>Hello,
>>>
>>> I want to import a big subset of data from one database to a new one. I
>>> attach the two databases together and use
>>>
>>> insert into customers select * from source.customers where name LIKE 'x%'
>>>
>>> I can approximately calculate, how big the new database will grow. Is
>>> there a way to tell SQLite to reserve an inital space or numer of pages
>>> instead of letting the database file grow again and again? I'm looking
>>> for a way to speed up the import.
>>>
>> Hello Thomas,
>>
>> I create a dummy table with a blob field and fill it with a very large
>> empty blob. Then I drop the table. The empty pages remain behind an can
>> the be used by the followimng inserts.
>>
> Hello Martin,
>
> that sounds like a good idea. Do you use it to have enough space for
> later operations or because you want to speed up the inserts?
> How big is the space you reserve by this and how much faster is it? I
> presume, you have to allocate quite big BLOBs.
>
> Best regards,
>
> Thomas
Hello Thomas,

My primary goal was not a speedy insert but to avoid fragmentation of 
the resulting database file, which slows down later access to the file. 
So, this is not exactly on topic of your post.
I did not measure the changes in speed of the insert. However, later 
selects, which in my case use practically all the data in the database, 
speed up on the order of 20%.
I have to admit that this does not seem like much. However, my customer 
for some reason did not like the fragmentation and insisted on a solution.

I calculate the size of the blobs dynamically based on what I know of 
the following inserts. Also, i watch the result of pragma 
freelist_count. The size varies between 1 MB and 1 GB.

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

Re: [sqlite] Reserve database pages

[Martin.Engelschalk]

Am 12.08.2010 12:08, schrieb TeDe:
>   Hello,
>
> I want to import a big subset of data from one database to a new one. I
> attach the two databases together and use
>
> insert into customers select * from source.customers where name LIKE 'x%'
>
> I can approximately calculate, how big the new database will grow. Is
> there a way to tell SQLite to reserve an inital space or numer of pages
> instead of letting the database file grow again and again? I'm looking
> for a way to speed up the import.
>
> Thanks in advance,
>
> Thomas
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Hello Thomas,

a short while ago there was a thread in this mailing list titled "Coping 
with database growth/fragmentation". Perhaps you want to search for it.
AFAIK, there is no way to tell the Library to allocate a number of pages 
at once. I could use a function like this very well :-)

I create a dummy table with a blob field and fill it with a very large 
empty blob. Then I drop the table. The empty pages remain behind an can 
the be used by the followimng inserts.

Martin



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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Martin.Engelschalk]

Am 09.08.2010 16:57, schrieb Oliver Peters:
> [...]
>
> To my mind the simplified question is:
>
> why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? 
> How
> corresponds this behaviour to the concept of FOREIGN KEYS?
>
> I wrote the code into file.sql (encoding=utf8, if this matters) and started
>   sqlite3 -bail test.db3<  file.sql
>
>
> PRAGMA foreign_keys = ON;
>
> /*
> **success INSERT = yes
> */
>
> CREATE TABLE a(
>  idINTEGER PRIMARY KEY
> AUTOINCREMENT,
>  id_staff_editor   INTEGER NOT NULL,
>  FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
> );
>
> INSERT INTO a(id_staff_editor) VALUES(1);
This insert succeeds, because the relusting record has the value 1 in 
it's field id. Id is autoincrement, therefore the first record will habe 
id == 1, which satisfies your foreign key, because you insert 1 in the 
column id_staff_editor as well.
> /*
> **success INSERT = no
> */
>
> CREATE TABLE b(
>  idINTEGER PRIMARY KEY
> AUTOINCREMENT,
>  id_staff_editor   INTEGER NOT NULL,
>  FOREIGN KEY(id_staff_editor)  REFERENCES b(id)
> );
>
> INSERT INTO b(id_staff_editor) VALUES(2);
>
This insert FAILS, because the relusting record has the value 1 in it's 
field id. Id is autoincrement, therefore the first record will habe id 
== 1, which DOES NOT satisfiy your foreign key, because you insert *2* 
in the column id_staff_editor .

> Oliver
>
>
> ___
> 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] if exist

[Martin.Engelschalk]

Hi,

try this:

select coalesce(min(length), 0) from t where id = ?

Martin

Andrea Galeazzi schrieb:
> Hi All,
> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and 
> INT length.
> I need a statement in order to yield 0 when the key doesn't exist. At 
> this moment the query is too simple:
> SELECT length FROM T WHERE id = ?
> Any idea about it?
> Cheers
> ___
> 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] size control of sqlite database

[Martin.Engelschalk]

Hi,

a sqlite database is a file, you can get its size using OS calls.
It is not possible to create a database with an initial size, because
the file grows dynamically when you insert data.

To avoid fragmentation, I also looked for a way to allocate empty space
inside the database file when creating it. Dr. Hipp proposed to create a
table with a single Blob-column, insert a very large, empty blob, and
then dropping the table. The file will then keep its size and afterwards
reuse the empty pages.
However, i could not find a way to determine when the empty pages are
used up and the file will start to grow again without checking the file
size after every insert.

Martin

gujx schrieb:
> Hi, I’d like to ask some question about the interface of the sqlite
> resource.
>
> Whether there is some interface to control the size of a database, for
> example, if I want to create a database with 5M initialized, how can I do
> that? And when I make change to a database, for example, insert a row to a
> table, then can I get the size of the database now?
>
>  
>
> Looking forward to your answer.
>
>  
>
>  
>
> Gu Jinxiang
>
>  
>
> 以上、よろしくお��いします。
>
>  
>
>   
> 
>
> ___
> 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] a possible bug of select min(id) from...

[Martin.Engelschalk]

Hi,

sqlite works like any other database I know, and afaik conforms to sql 
standard.

Martin


nick huang schrieb:
> Hi Igor,
>
>  
>
> You are absolutely right that its type is NULL and min(id) returns 0 as I use 
> sqlite_column_int64 to retrieve data. However, isn't it confusing that the 
> "sqlite3_step" returns SQLITE_ROW which usually means some dataset is 
> returned? I mean, if it returns one NULL row, why doesn't sqlite simply 
> return "SQLITE_DONE" or something instead of "SQLITE_ROW"? You see, as user I 
> have to double-check its return type even after I get "SQLITE_ROW". In this 
> sense, I think it is a bug.
>
>  
>
> thank you,
>
>  
>
>   
>> To: sqlite-users@sqlite.org
>> From: itandet...@mvps.org
>> Date: Wed, 16 Dec 2009 21:00:43 -0500
>> Subject: Re: [sqlite] a possible bug of select min(id) from...
>>
>> nick huang  wrote:
>> 
>>> Say a table named "url" is created with "id" as primary key. Then a
>>> query like this "select min(id) from url". 
>>>
>>> Quite unexpectedly, the result of query returns "SQLITE_ROW" and the
>>> min(id) is 0. However, my table is still empty which confuses me for
>>> a while.
>>>   
>> This statement should in fact return one row, with the value of NULL. How do 
>> you retrieve the value? If you use sqlite3_column_int, it'll convert NULL to 
>> 0 for you. Check with sqlite3_column_type.
>>
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>
> 
> _
> Windows Live: Make it easier for your friends to see what you’re up to on 
> Facebook.
> http://go.microsoft.com/?linkid=9691816
> ___
> 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] requesting 2 tables about one select

[Martin.Engelschalk]

Hi,

this requires a simple join:

select siterate.name,  sitetype.name
from siterate
join sitetype on sitetype.id = siterate.type
where sitetype.status!=1

This supposes that for every entry in siterate, there exists an entry in 
sitetypes with sitetype.id = siterate.type

It seems to me that you might want to read up on SQL.

Martin

Artur Reilin schrieb:
> I need help with my query.
>
> I have two tables:
>
> sitetypes(
>   id integer primary key not null,
>   name text
> )
>
> siterate(
>   id integer primary key not null,
>   type integer not null,
>   status integer not null,
>   ...
>   ...
> )
>
> This is my currently query:
> $all_waiting_sites = sqlite_array_query('select name,type from
> '.$databasename.'siterate where status!=1 order by name',$db);
>
> And i like that my query, that makes the type integer from siterate into
> the name text from sitetypes.
>
> Thanks forward for helping me.
>
> with best wishes
>
>
> Artur Reilin
> sqlite.yuedream.de
> ___
> 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] BACK API Questions

[Martin.Engelschalk]

Hello Raghu,

it seems to mee that you misunderstand the backup api. This api does not 
"know" or care for your schema and tables, but backups the database 
block for block. In this sense, the api does not know "old" contents and 
can not add "new" content.
The backup api can not help you in your need. You will have to open both 
the in-memory db and the file dm and copy the data you need to back uop 
yourself before deleting it in the in-memory db.
See the attach-command: http://www.sqlite.org/lang_attach.html

Martin

Raghavendra Thodime schrieb:
> Hi,
> I am using in-memory db where I will execute only inserts. Periodically, 
> I am trying to backup my in-memory db to file based db. But as soon as I 
> backup, I want to clear up the in-memory db so that I will have enough memory 
> for subsequent inserts. So next time when I back up I want to back up only 
> the newly written records. Is there a simple way to accomplish it? Right now, 
> I can't execute delete on in-memory because it will reflect on file db when I 
> back up which is what I don't want.
>
>   What I tried was, as soon as I back up, I close the current in-memory db 
> and open a new one for the subsequent inserts. But next time when I tried to 
> back up to the same old file db, backup operation is overwriting all the its 
> old contents. Is there a way to append to old DB instead of overwriting?
>
> Help will be appreciated...
>
>  Thank You
> Raghu
>
> ___
> 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] In-memory database backup

[Martin.Engelschalk]

Hi,

you can use the online backup api described here:
http://www.sqlite.org/backup.html

Martin

Andres Velasco Garcia schrieb:
> Hello,
>
> Do any of you know if it is possible to backup an in-memory database to disk 
> so it can be recovered later from disk.
>
> Thanks
>
> Andres Velasco 
> M: +34 670 40 73 69 
> Skype: newwwave.andres.velasco
>
>
> 
> ___
> 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] Problem with -order by- clause

[Martin.Engelschalk]

Hi,

The sorting used by default in sqlite uses normal strcmp - like comparison.

This problem has been discussed several times in the mailing list: 
perhaps you want to search the archive.
You will have to write your own collation, perhaps using the ICU.
See http://www.sqlite.org/capi3ref.html#sqlite3_create_collation
http://www.mail-archive.com/sqlite-users%40sqlite.org/
http://site.icu-project.org/

Martin

MartinRalf schrieb:
> Hello,
>
>  
>
> I’ve got a problem with sorting german ‚Umlaute’ eg. ’äöü’ (ae,oe,ue)
>
> Usually they are sorted prior to the corresponding Letter: ü before u
>
>  
>
> In SqLite with ‘Collate Locale’ these letters are sorted at the end after
> ‘z’
>
>  
>
> Is there a solution or do I have to ‘live’ with it?
>
>  
>
> Cheers
>
> Ralf
>
> ___
> 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] how to access tables in multiple sqlite databases

[Martin.Engelschalk]

Hi,

see http://www.sqlite.org/lang_attach.html

you attach a second database, giving it a name, and prepend this name to 
the table name.

Martin

Zhenyu Guo schrieb:
> Hi guys,
>
> I have multiple sqlite databases in hand, and I would like to perform the 
> following several tasks:
>
> . two tables with the same schema are in two dbs, and I want to apply a sql 
> query to the two tables efficiently (merge them into one table? Merge cost is 
> also considered as the total cost. Is there a way to logically combine these 
> two tables into one? Other mechanisms?)
>
> . two table with different schemas are in two dbs, and I want to apply a join 
> query to the two tables efficiently (if I can logically have them in one db, 
> that will be better.)
>
> Thanks,
> Zhenyu
>
> ___
> 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] Error is coming in Linux while openings Databse

[Martin.Engelschalk]

Hi,

Does the user under which the application runs have write permission on 
the Directory the database resides in?
Are you sure that the segfault occurs inside sqlite and not in your own 
code?

Martin

Pramoda M. A schrieb:
> Hi All,
>
>I am working on Fedora 10. Using C interfaces, I am successfully opening 
> database. No error while inserting data
> Into database. But after insertion, if I try to get data it is giving 
> segmentation fault error.
> When I try to open using in-built sqlite3 commands, "Select * from File", is 
> it error as
>
> " SQL error: file is encrypted or is not a database"
>
> Please anybody help.
>
> Thanks & Regards
> Pramoda.M.A
>
> ___
> 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] what is most effective way to temporarily disable triggers?

[Martin.Engelschalk]

Hi,

if you are talking about a feature request: Oracle supports sql syntax 
to enable or disable a certain trigger (as opposed to all triggers as 
you suggested):

ALTER TRIGGER  DISABLE
or
ALTER TRIGGER  ENABLE

This would be nice.

Martin

Michal Seliga schrieb:
> hi
>
> attached is patch which will make temporary disable of triggers possible. i
> tried it in my application with current data (many inserts in to various table
> with many triggers on them, which are not meant to be run while importing 
> data).
> it works and it changed running time from 62 seconds to 4, so i guess its 
> worth it
>
> i added new pragma DISABLE_TRIGGERS which can be set to 0 (default, everything
> works) or 1 (function which returns triggers always returns empty list so none
> will be called)
>
> honestly, i didn't spent too much time with learning sqlite sources, i just 
> made
> quick hack which works for me, i hope i diidn't break anything. so i decided 
> to
> share it. maybe one day this feature can get to official version too
>
>
>
>   
> 
>
> ___
> 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] What's the different of the types TEXT and VARCHAR in sqlite3?

[Martin.Engelschalk]

Hi,

type names do not matter in sqlite, see http://www.sqlite.org/datatype3.html

Martin

Kermit Mei schrieb:
> Hello, I'm a newbie for sqlite3, I hope somebody can tell me what's the
> different of TEXT and VARCHAR. Does TEXT can save arbitrary characters,
> but VARCHAR?  Which is better, then?
>
> Thanks.
>
> ___
> 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] how to compose the sql sentence?

[Martin.Engelschalk]

Hi,

what do you mean by "command"? The command line tool takes commands 
entered by the user, such as SQL - statements. These do not have 
returncodes. If an error occurs, the command line tool will print out 
the error message.
The functions of the sqlite3 library have returncodes, but they can not 
be called directly on the command line.

Martin

liubin liu schrieb:
> and I think of another question:
> how to know the return value of a command IN the command-line mode of
> sqlite3?
>
>
>
> Martin Engelschalk wrote:
>   
>> Hi,
>>
>> First, you have to declare the index as unique:
>>
>> CREATE UNIQUE INDEX i_data ON data (num, di, time1);
>>
>> or - depending on your database design, declare a primary key with these 
>> three fields.
>>
>> Second, the error message says it all: You supplied three column - 
>> names, but 6 values.
>>
>> Martin
>>
>> liubin liu schrieb:
>> 
>>> Thank you a lot!
>>>
>>>
>>> I created a table:
>>> CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1
>>> INTEGER,
>>> time2 INTEGER, format CHAR(1) );
>>>
>>> and create a index:
>>> CREATE INDEX i_data ON data (num, di, time1);
>>>
>>> I want to do:
>>> first tell whether there is a record in the table "data" according to the
>>> index "i_data".
>>> to update the record if there is a record;
>>> to insert the record if there isn't any record.
>>>
>>>
>>>
>>> when I run the sql:
>>> INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
>>> '732e4a39', 8323000, 8323255, 22);
>>>
>>> the sqlite3 report a error:
>>> SQL error: 6 values for 3 columns
>>>
>>> Does It mean the method isn't the right way?
>>>
>>>
>>>
>>>
>>> Simon Slavin-2 wrote:
>>>   
>>>   
 On 3 Jun 2009, at 7:05am, liubin liu wrote:

 
 
> the first step is to tell if there is the data in the table.
> if the answer is not, I want to insert a row of data into the table
> if the answer is yes, I need to update the row of data acccording to  
> the
> data inputting from me.
>   
>   
 INSERT OR REPLACE INTO table (columns) VALUES (values)

 This will use the columns and indices you have already defined as  
 UNIQUE to decide whether it should INSERT a new row or REPLACE an  
 existing one.  So take care in creating UNIQUE columns or a UNIQUE  
 index that does what you want.

 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] how to compose the sql sentence?

[Martin.Engelschalk]

Hi,

First, you have to declare the index as unique:

CREATE UNIQUE INDEX i_data ON data (num, di, time1);

or - depending on your database design, declare a primary key with these 
three fields.

Second, the error message says it all: You supplied three column - 
names, but 6 values.

Martin

liubin liu schrieb:
> Thank you a lot!
>
>
> I created a table:
> CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER,
> time2 INTEGER, format CHAR(1) );
>
> and create a index:
> CREATE INDEX i_data ON data (num, di, time1);
>
> I want to do:
> first tell whether there is a record in the table "data" according to the
> index "i_data".
> to update the record if there is a record;
> to insert the record if there isn't any record.
>
>
>
> when I run the sql:
> INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
> '732e4a39', 8323000, 8323255, 22);
>
> the sqlite3 report a error:
> SQL error: 6 values for 3 columns
>
> Does It mean the method isn't the right way?
>
>
>
>
> Simon Slavin-2 wrote:
>   
>> On 3 Jun 2009, at 7:05am, liubin liu wrote:
>>
>> 
>>> the first step is to tell if there is the data in the table.
>>> if the answer is not, I want to insert a row of data into the table
>>> if the answer is yes, I need to update the row of data acccording to  
>>> the
>>> data inputting from me.
>>>   
>> INSERT OR REPLACE INTO table (columns) VALUES (values)
>>
>> This will use the columns and indices you have already defined as  
>> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
>> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
>> index that does what you want.
>>
>> 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

Re: [sqlite] accessing a sqlite db with multiple java processes.

[Martin.Engelschalk]

Hi,

i imagine that your problem has nothing to do with your driver.
Have you looked at http://www.sqlite.org/faq.html#q5 ?

Martin

Laurent Burgy schrieb:
> Hi,
> I was using sqlite with only one java process (using the sqlitejdbc driver)
> and everything was ok... now i'm trying to run several instances of the same
> program on the same db...and i've a bunch of "database is locked"
> exceptions.
>
> i was wandering if it was possible to access the same db with several jobs
> ?
>
> If so (and maybe this is not the right place to ask), what are the best
> practices using sqlitejdbc ? (or any other jdbc driver)...
>
>
> thanks
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Martin.Engelschalk]

Hi,

select * from sqlite_master;

Martin

PS.: Please provide a subject which summarises your question.

Manasi Save schrieb:
> Hi All,
>
> Can anyone help me out with the command to see the SQLite table defination
> on command-line SQLite application.
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert into with select not working

[Martin.Engelschalk]

Hello Brian,

you do not need a VALUE keyword in this case.

INSERT INTO "subscribers_new"
--VALUES  <- remove this
SELECT id, ip_address, added, 'aa:bb:cc:etc' from subscribers;

See the syntax diagram for the insert command: 
http://www.sqlite.org/lang_insert.html

Martin


Brian Zambrano schrieb:
> Can anyone explain to me why an insert with select statement isn't working?
> I know in my my example below I just have an extra column which I could have
> added, but my actual tables are a bit more complex and this below is just to
> demonstrate the problem.
>
> -- Original table
> CREATE TABLE "subscribers" (
> "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "ip_address" char(15) NOT NULL UNIQUE,
> "added" integer NOT NULL
> );
>
> -- New table
> CREATE TABLE "subscribers_new" (
> "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "ip_address" char(15) NOT NULL UNIQUE,
> "added" integer NOT NULL,
> "mac_address" char(32) NOT NULL
> );
> SQL error: near ""mac_address"": syntax error
>
> -- insert a couple of rows into the original
> INSERT INTO "subscribers" VALUES (1, '1.2.3.4', 123456);
> INSERT INTO "subscribers" VALUES (2, '11.22.33.44', 111);
>
> -- Now try to insert into new table with select, and see failure
> INSERT INTO "subscribers_new"
> VALUES
> SELECT id, ip_address, added, 'aa:bb:cc:etc' from subscribers;
> SQL error: near "SELECT": syntax error
>
> -- Note, the select statement by itself works just fine:
>
> SELECT id, ip_address, added, 'aa:bb:cc:etc' from subscribers;
> id|ip_address|added|'aa:bb:cc:etc'
> 1|1.2.3.4|123456|aa:bb:cc:etc
> 2|11.22.33.44|111|aa:bb:cc:etc
> ___
> 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] Binary Format

[Martin.Engelschalk]

Hi,

from the website http://www.sqlite.org/oldnews.html:

The file format for version 3.3.0 has changed slightly to support 
descending indices and a more efficient encoding of boolean values. 
SQLite 3.3.0 will read and write legacy databases created with any prior 
version of SQLite 3. But databases created by version 3.3.0 will not be 
readable or writable by earlier versions of the SQLite. The older file 
format can be specified at compile-time for those rare cases where it is 
needed.

This semms the only change since 3.0

Martin

Martin Pfeifle schrieb:
> Hi,
> we do use SQLite in a standardisation initiative and have to state 
> which binary file-format of sqlite is used.
> Up to now, I was of the opinion that all sqlite versions 3.x use the same 
> binary sqlite file
> format but only differ in the library functionality. 
> Can somebody confirm that the binary disk format does not change in 3.x or 
> can it change and
> we have to say, we use the binary format of sqlite 3.5.4 for instance, or is 
> it enough to say
> that we use 3.x as binary file format?
> Best Martin
>
>
>   
> ___
> 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] is primary key already indexed ?

[Martin.Engelschalk]

Hi,.

baxy77bax schrieb:
> hi my question is : if i create table that contains primary key like;
>
> create table TEST (field1 varchar not null primary key);
>
> do i need to create index on it or not?
>   
Yes, the primary key is indexed
> and is it better to create table with a primary key and then import data in
> it or create table without a key , import data and then just create index on
> the table. (which is faster- or should i ask which is the fastest way to
> import data in the table?)
>
> then if i create proper table , is it advisable to order data by column with
> primary key on it or data with index on it
>   
My experience says that this does not matter much. However, you could 
try and see for yourself.
If you have a Primary Key which is a single integer, you should declare 
the column as "integer primary key"  (see 
http://www.sqlite.org/lang_createtable.html#rowid).
> thank you
>   

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

Re: [sqlite] nullable select fields

[Martin.Engelschalk]

Hi,

see sqlite3_bind_null:

http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob

Martin

Andrea Galeazzi schrieb:
> Hi All,
> I'm developing an application which relies on sqllite as  back-end. Now 
> I face to this problem: I've got a form that allows the user to fill a 
> lot of fields,  obliviously only a little part of them will actually be 
> filled, the others isn't gonna be in the search criteria. So I prepare a 
> parameterized query containing the whole possible fields like this:
> SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
> How can I bind the unrequested fields? Does a trivial solution exist?
> Thanks
> ___
> 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] Error message This program cannot be run in DOS

[Martin.Engelschalk]

Hi,

what is your "DOS Prompt"? There are "command.com" and "cmd.exe". Try 
using "cmd.exe".

Martin

Richard Hardwick schrieb:
>> selecting the UNBLOCK button from the window displayed,  and unzip and
>> 
> Well I dont seem to have an unblock button
> Here is what I did
>
> I downloaded
> sqlite-3_6_10.zip  245,575  bytes
> which unzipped to
> sqlite3.exe501,456
>
> The start of file sqlite3.exe looks like this
> 4D 5A 90 00 03 00 00 00 04 00 00 00 FF FF 00 00
> B8 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00
> 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
> 00 00 00 00 00 00 00 00 00 00 00 00 80 00 00 00
> 0E 1F BA 0E 00 B4 09 CD 21 B8 01 4C CD 21 54 68
> 69 73 20 70 72 6F 67 72 61 6D 20 63 61 6E 6E 6F
> 74 20 62 65 20 72 75 6E 20 69 6E 20 44 4F 53 20
> 6D 6F 64 65 2E 0D 0D 0A 24 00 00 00 00 00 00 00
>
> That is
>
> MZ..
> .
> .
> .
> ...Th
> is program canno
> t be run in DOS
> mode
>
> And indeed "sqlite3.exeEX1.DB" at the dos prompt returns
> "This program cannot be run in DOS mode".
> I'm still stuck
>
> Richard H
>
>   
>> Message: 4
>> Date: Wed, 4 Feb 2009 17:28:35 +0530
>> From: Rajesh Nair 
>> Subject: Re: [sqlite] Error message This program cannot be run in DOS
>> mode
>> To: General Discussion of SQLite Database 
>> Message-ID:
>> 
>> Content-Type: text/plain; charset=ISO-8859-1
>>
>> Is it sqlite3.ex1 or sqlite3.exe ?
>>
>> Before unziping, Unlock the downloaded file by righ-clicking it an
>> selecting the UNBLOCK button from the window displayed,  and unzip and
>> then try. Unblock the EXE and the DLL file also, if needed.
>>
>> Rajesh Nair.
>>
>> On 2/4/09, Richard Hardwick  wrote:
>> 
>>> Stupid Newbie problem. I'm running WinXP.
>>> I want to make a little database.
>>> So at http://www.sqlite.org/download.html
>>> (Precompiled Binaries For Windows)
>>> I downloaded  sqlite-3_6_10.zip
>>>
>>> Unzipped it to sqlite3.exe.
>>> At DOS prompt I did
>>> sqlite3 ex1
>>> And I got
>>> "This program cannot be run in DOS mode".
>>>
>>> Googled for help and found
>>> http://jroller.com/obie/entry/installing_sqlite_3_on_windows
>>> Followed their suggestion.
>>> Downloaded sqlitedll-3_6_10.zip and unzipped to
>>> sqlite3.def and sqlite3.dll
>>>
>>> But still 'sqlite3 ex1' gives me "This program cannot be run in DOS mode".
>>> I'm stuck.
>>> Your help much appreciated
>>> rch
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>   
>> --
>> Regards
>> Rajesh Nair
>>
>>
>>
>>
>> 
>
>
>
>   
> ___
> 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] sqlite3_bind_int returns SQLITE_RANGE

[Martin.Engelschalk]

Hi,

no, you habe 13 placeholders and one string constant '?' on position 9.
You do not need to include the ? in quotes if the value you want to bind 
is a string.

Martin

hussainfarzana schrieb:
> Yes,the SQL prepared statement has got 14 placeholders.
>
> The statement is "INSERT INTO CollDataNum
> values(?,?,?,?,?,?,?,?,'?',?,?,?,?,?)"
>
> Regards,
> Farzana.
>
>
> SimonDavies wrote:
>   
>> 2009/2/2 hussainfarzana :
>> 
>>> Dear All,
>>>
>>> We are working with SQLite Version 3.6.10.
>>> We tried to insert or update the records in the database using
>>> sqlite3_prepare and binding the values using sqlite3_bind functions.We
>>> have
>>> started with the index 1.We have a table with 14 columns and when we use
>>> sqlite3_bind_int,for the first 13 columns its returning 0 and for the
>>> last
>>> 14th column which is also an integer datatype, but the function is
>>> returning
>>> value 25(SQLITE_RANGE) error.
>>>   
>> Your table may have 14 columns, but has the SQL for your prepared
>> statement got 14 parameter placeholders?
>>
>> 
>>> Please help us how to proceed further.
>>>
>>> Regards,
>>> Farzana.
>>>   
>> Rgds,
>> Simon
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> 
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] need a CURRENT_USER() function

[Martin.Engelschalk]

Hi Jeffrey,

use something like this:

struct passwd *who;
if ((who = getpwuid(getuid ())) != NULL)
{
oUserName = who->pw_name;
}   

where oUserName is the desired result.

See http://www.sqlite.org/capi3ref.html#sqlite3_create_function how to 
define your function.

Martin

Hoover, Jeffrey schrieb:
> Can anyone tell me set-by-step how to add a CURRENT_USER() function to
> SQLLITE that will return the current linux login?
>
>  
>
> I'm not a C programmer and I have JUST picked up SQLite.
>
> I am trying to convert a Sybase schema to SQLite.  The schema has
> triggers triggers but so far they convert in a pretty straightforward
> manner...that is, util I ran into a reference to "current_user" in
> Sybase...
>
>  
>
> Please help.
>
>  
>
> Jeff.
>
>  
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] Collation not used

[Martin.Engelschalk]

Hello Dan,

yes, you are right, Thank you.
Is there a reason for this?

martin

Dan schrieb:
> On Jan 9, 2009, at 9:43 PM, Martin.Engelschalk wrote:
>
>   
>> Hello list,
>>
>> I definied a collation and used it in the order by - clauses of  
>> queries.
>> In one query, sqlite calls the collation function, and in the other
>> query, it does not (i checked by inserting a printf inside the  
>> collation
>> function).
>> The queries differ only in the order by - clause.
>>
>> This query Works OK:
>>
>> select d1._recno,
>>   d1._source_id,
>>   d1._source_position,
>>   d1._source_len,
>>   d1._source_lfd,
>>   d1._list_id,
>>   d1._list_lfd,
>>   d1._country,
>>   coalesce(d2._skip,d1._skip) _skip ,
>>   coalesce(d1._skip,d2._skip) _state_num ,
>>   d2.f_mail2Group f_mail2Group,
>>   d2.f_mail2Code f_mail2Code,
>>   d2.f_mail2Stat f_mail2Stat,
>>   d1.f_FirstName f_FirstName,
>>   d1.f_LastName f_LastName,
>>   d1.f_StreetLine f_StreetLine,
>>   d1.f_Zip f_Zip,
>>   d1.f_CityName f_CityName,
>>   d2.f_Bewertung f_Bewertung,
>>   d2.f_Dublettengruppennummer f_Dublettengruppennummer,
>>   d2.f_Dublettentyp f_Dublettentyp
>>  from data1 d1 left outer join data2 d2 on d2._recno = d1._recno
>> order by 11 collate DQS_NUM_ASC, 13 collate DQS_NUM_ASC, 12 collate
>> DQS_NUM_ASC
>>
>> If I change the oder by - clause to the following, the collation
>> function ist not called, and the result list is sorted in the standard
>> order.
>>
>> order by 20 collate DQS_NUM_ASC, 21 collate DQS_NUM_ASC, 19 collate
>> DQS_NUM_ASC
>>
>> All fields of the table "data2 d2" are defined as type "integer"
>> My version is 3.2.5.
>>
>> Does anyone have an idea? Might it be a good idea to upgrade to the
>> newest version? I want do do this only if really necessary.
>> 
>
> The collation function is only called for sorting (or comparing) text
> values. Is it possible that the second set of columns are entirely
> populated with numbers, blobs and nulls?
>
>
> ___
> 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] Collation not used

[Martin.Engelschalk]

Hello list,

I definied a collation and used it in the order by - clauses of queries. 
In one query, sqlite calls the collation function, and in the other 
query, it does not (i checked by inserting a printf inside the collation 
function).
The queries differ only in the order by - clause.

This query Works OK:

select d1._recno,
   d1._source_id,
   d1._source_position,
   d1._source_len,
   d1._source_lfd,
   d1._list_id,
   d1._list_lfd,
   d1._country,
   coalesce(d2._skip,d1._skip) _skip ,
   coalesce(d1._skip,d2._skip) _state_num ,
   d2.f_mail2Group f_mail2Group,
   d2.f_mail2Code f_mail2Code,
   d2.f_mail2Stat f_mail2Stat,
   d1.f_FirstName f_FirstName,
   d1.f_LastName f_LastName,
   d1.f_StreetLine f_StreetLine,
   d1.f_Zip f_Zip,
   d1.f_CityName f_CityName,
   d2.f_Bewertung f_Bewertung,
   d2.f_Dublettengruppennummer f_Dublettengruppennummer,
   d2.f_Dublettentyp f_Dublettentyp
  from data1 d1 left outer join data2 d2 on d2._recno = d1._recno
order by 11 collate DQS_NUM_ASC, 13 collate DQS_NUM_ASC, 12 collate 
DQS_NUM_ASC

If I change the oder by - clause to the following, the collation 
function ist not called, and the result list is sorted in the standard 
order.

order by 20 collate DQS_NUM_ASC, 21 collate DQS_NUM_ASC, 19 collate 
DQS_NUM_ASC

All fields of the table "data2 d2" are defined as type "integer"
My version is 3.2.5.

Does anyone have an idea? Might it be a good idea to upgrade to the 
newest version? I want do do this only if really necessary.

Thanks,
Martin


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

Re: [sqlite] Transfer data between databases

[Martin.Engelschalk]

Hi Pierre,

you can open both database files at the same time and using the same 
connection: Look at the attach - Command:
http://www.sqlite.org/lang_attach.html
You can then use both databases in the same statement (insert into 
MyTable (... columns ...) select ... columns ... from 
MyOtherDatabase.MyTable where ...)

Martin

Pierre Chatelier schrieb:
> Hello,
>
> This may be a question with a very short answer...
> I have two separate SQLite database files, but containing the same  
> kind of tables. Is there a quick way to copy rows from one table of a  
> file to the same table of the other file ?
> I suppose "no", and I will have to perform SELECT on one side and  
> INSERT on the other. But since one of my column is a blob type, it is  
> a little more pain than a simple string copy, because for performance  
> I should have to handle the blob with the read/write functions. Right ?
>
> Anyway, are there tricks to know to make such a row transfert simple  
> and efficient ?
>
> Regards,
>
> Pierre Chatelier
> ___
> 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] Drop Trigger with Select

[Martin.Engelschalk]

Hi,

My idea would be a to define a function which drops the trigger and 
returns some dummy value and call ist like this:

SELECT MyDropFunction(name) from trigger_status where status = 1

However, i do not know if this will work.

Martin




timdbu...@gmail.com schrieb:
> Hi,
> Is there any way that I could drop a trigger by providing the results of a  
> query? Something similar to the following?:
>
> DROP TRIGGER (SELECT name from trigger_status where status = 1);
>
> Thanks!
> ___
> 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] Maximum Size of Record

[Martin.Engelschalk]

Yes, i think so.

Satish schrieb:
> Hi,
>   I have seen the link which you sent.I didn't found any thing in
> which you sent regarding the size of number or text
> Field.In the link which u sent there mentioned the size limit of blob I
> didn't found any size limit regarding text and number.Can I assume the size
> of number and text as unlimited.
>
> Regards,
> G.Satish.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Martin.Engelschalk
> Sent: Wednesday, December 10, 2008 5:45 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Maximum Size of Record
>
> Hi,
>
> See http://www.sqlite.org/limits.html
>
> Martin
> Satish schrieb:
>   
>> Hi Igor,
>>
>> Can I know the Maximum size of Record in sqlite or Can I
>> know the maximum size of NUMBER,TEXT and BLOB Data types.
>>
>>  
>>
>> Regards,
>>
>> G.Satish.
>>
>> ___
>> 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
>
>   

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

Re: [sqlite] Maximum Size of Record

[Martin.Engelschalk]

Hi,

See http://www.sqlite.org/limits.html

Martin
Satish schrieb:
> Hi Igor,
>
> Can I know the Maximum size of Record in sqlite or Can I
> know the maximum size of NUMBER,TEXT and BLOB Data types.
>
>  
>
> Regards,
>
> G.Satish.
>
> ___
> 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] Valgrind, writeJournalHdr and Syscall param write(buf) points to uninitialised byte(s).

[Martin.Engelschalk]

Hi,

i remember the posts you mention, and I know these error reports from 
valgrind. I get them outside of sqlite also, and have learned to ignore 
them.

Martin

Kent Dahl schrieb:
> Hi.
>
> After running valgrind on my program that is using sqlite (3.6.6.2,
> statically linked on Linux, Ubuntu 8.10) for a while, carving away all
> the problems caused by my own code, I was left with the "Syscall param
> write(buf) points to uninitialised byte(s)" error reported within the
> call from writeJournalHdr.
>
> I saw a similar post in the archives, which may be the same issue:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg38091.html
> But the replies don't explain on _why_ it isn't a problem, hence my
> continued concern and this email. 
>
> Is this covered in a bug issue? The old #536 ticket looks similar, but
> as far as I can tell, the resolution mentioned is more on the PRNG code.
> (And the PRNG fix for valgrind is in 3.6.6.2.)
>   http://www.sqlite.org/cvstrac/tktview?tn=536
>
> Output from valgrind, using the example code at the bottom of
>   http://www.sqlite.org/quickstart.html 
> and run as:
>   valgrind ./testsql test.db "create table testtable(id INTEGER)"
>
> ==
> ==18100== Syscall param write(buf) points to uninitialised byte(s)
> ==18100==at 0x40007D2: (within /lib/ld-2.8.90.so)
> ==18100==by 0x8066B08: writeJournalHdr (sqlite3.c:12420)
> ==18100==by 0x8066D5F: pager_open_journal (sqlite3.c:31731)
> ==18100==by 0x80700A3: sqlite3BtreeBeginTrans (sqlite3.c:35905)
> ==18100==by 0x809956E: sqlite3VdbeExec (sqlite3.c:48875)
> ==18100==by 0x80885D7: sqlite3_step (sqlite3.c:45476)
> ==18100==by 0x8088F31: sqlite3_exec (sqlite3.c:66489)
> ==18100==  Address 0x42eb004 is 36 bytes inside a block of size 1,032
> alloc'd
> ==18100==at 0x4025D2E: malloc (vg_replace_malloc.c:207)
> ==18100==by 0x805AE7A: sqlite3MemMalloc (sqlite3.c:12830)
> ==18100==by 0x8049CC8: mallocWithAlarm (sqlite3.c:15992)
> ==18100==by 0x8049D96: sqlite3Malloc (sqlite3.c:16015)
> ==18100==by 0x804AF34: pcache1Alloc (sqlite3.c:28155)
> ==18100==by 0x804B05C: sqlite3PageMalloc (sqlite3.c:28219)
> ==18100==by 0x80685AB: sqlite3BtreeFactory (sqlite3.c:30603)
> ==18100==by 0x806EED0: openDatabase (sqlite3.c:85463)
> ==
>
> Could it be something like the journal header only is partly filled, but
> needs to be of certain block-size on disk, leaving trailing
> uninitialized bytes? 
>
> I'm not necessarily hoping for a solution as such. A pointer to a bug or
> code comment that explains why it is safe would suffice nicely.
>
> (Sorry to be mentioning 'valgrind', which seems to be akin to a
> four-letter word, on this list... ;)
>
> TIA.
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how sqlite use index

[Martin.Engelschalk]

Hello Rachmat,

by creating one and using a fitting where- or order-by - clause.
See http://www.sqlite.org/lang_createindex.html

perhaps you would like to ask you question with a litte bit morte detail?

Martin

Rachmat Febfauza schrieb:
> how to make sqlite use index? 
>
>
>
>   
> ___
> 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] Syntax for sql 'insert' for text with comma

[Martin.Engelschalk]

Hi,

text constants have to be set between single quotes:

insert into sometable values ( 5 , 'text' )

If the text contains a single quote, double it:

insert into sometable values ( 5 , 'Don''t do this' )


Martin

Mauricio schrieb:
> Hi,
>
> I would like to
>
> insert into sometable values ( someid , text ) ;
>
> but 'text' contains a few commas, and then it
> would look like
>
> (...) values ( someid , text , text2 , text3 ) ;
>
> Is there a syntax to do that properly, maybe
> like  C string constants ("text,\"text\",text\n")?
>
> Thanks,
> Maurício
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] Request to check UNICODE support

[Martin.Engelschalk]

Hi,

sqlite supports unicode. Internally, thext is stored in UTF-8 or UTF-16, 
depending on sqlite3_open or sqlite3_open16, respectively.
See http://www.sqlite.org/c3ref/funclist.html,
There functions named sqlite3_xxx16 are described , which take UTF-16 as 
arguments.

Martin

[EMAIL PROTECTED] schrieb:
>  Hi all
>
> This is krishnakumar i am using SQLite as data base in my application as meta 
> data storage i want use it to store CString how can i deal with UNICODE. Is 
> SQLite support UNICODE Character set
>
>
>  
>
>
> Krishna Kumar T M
> 9886454481
>
> 
> Don't let your email address define you - Define yourself at 
> http://www.tunome.com today! 
> ___
> 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] test error

[Martin.Engelschalk]

hi,

attachments do not reach this list, please specify the error.

Martin

AVINASH MITTAL schrieb:
>   
> Hi,
>
> while executing tests for amalgamation for version 3.6.5 i got this error, 
> can somebody help me in this regard
>
> Regards
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] How to install SQLite on a shared linux hosting

[Martin.Engelschalk]

Hello Luigi

sqlite cannot be installed or run, but only compiled and inked into an 
application. There is no server.
See http://www.sqlite.org/about.html

Martin

[EMAIL PROTECTED] schrieb:
> I have been trying to understand if and how it is it possible for me
> to install SQLite, but due to my little knowledge I find no way.
> Also, no information at all is given on the official site, apart from
> a general "installation is trivial: just copy the sqlite or sqlite.exe
> executable to the target machine and run it".[1]
>
> I have my own site hosted on a shared linux server[2][3] (of which I
> have no direct control) which I can access with ftp.
> Where exactly should I place the SQLite file, and which file should I
> grab. please?
> Then, how do I "run" it?
>
> Another tutorial[4] says: "Get a copy of the prebuilt binaries for
> your machine, or get a copy of the sources and compile them yourself."
> As I do not exactly know what compiling is, I targeted the
> "Precompiled Binaries for Linux"[5], but there I found 4 different
> things.
> Which of them would be the right one in order to try and use for my
> own php site, please?
>
> I understand that most users here are already super-experts, who do
> not need any advice.
> Is there a real (as opposed to [4]) tutorial for beginners, please?
>
>
> Luigi
>
> 
> [1] http://www.sqlite.org/whentouse.html
> [2] http://webx18.aruba.it/ver.php
> [3] 
> http://translate.google.com/translate?u=http%3A%2F%2Fassistenza.aruba.it%2Fkb%2Fidx%2F45%2F142%2Farticle%2FLinguaggi-supportati-su-Hosting-Linux.html=it=UTF-8=it=en
>
> [4] http://www.sqlite.org/quickstart.html
> [5] http://www.sqlite.org/download.html
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] problem with sqlite3_exec() and select sql statemant

[Martin.Engelschalk]

Hello Hariom,

- use single quotes around text constants: select * from my_table where 
Primarykey='google.com'
- use sqlite3_prepare and sqlite3_step to select data. First call 
sqlite3_prepare for your statement and then sqlite3_step in al loop 
until it returns SQLITE_DONE
- It is not an error if a select statement returns no rows because of a 
where clause. In this case, the first call to sqlite3_stepreturns 
SQLITE_DONE

Martin

Hari schrieb:
> Hi as i am new to sqlite.
> I have problem when i am using sqlite3_exec() function with select
> as i am using sqlite3_open() to opening database
> then creating table and inserting some information using sqlite3_exec()
> and 'create table' and 'insert into'
> then again if i use sqlite3_exec() with select like
> my sql statement is : select * from my_table where
> Primarykey="google.com"
> where google.com primary key and its related information is not already
> in my table but it is returning
> SQLITE_OK...even it must be return some error as that primary key is not
> in my table.
> .how should i get the error...when using select and if primary key will
> not be there ..after creating table with using sqlite3_exec() function +
> select sql statemant.
>
> Thanks in advance for any ideas
> Harioum
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

[sqlite] How does the database file grow?

[Martin.Engelschalk]

Hi all,

i create and fill database files which reach quite a large size after a 
while, because i only add data and never remove it.
The database files themselves become quite fragmented on the disk.
Because I can in many cases calculate in advance the size to which the 
file will grow, i would like to reserve the disk space before inserting 
all the data. Can this be done?

Thanks,
Martin

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

Re: [sqlite] REQ: How do I change one character in a field value for another

[Martin.Engelschalk]

Hi,

look at the replace() - Function at 
http://www.sqlite.org/lang_expr.html#corefunctions
and use a statement like

update Aircraft set OperatorFlagCode  = replace(OperatorFlagCode, 
'~','-') where ... OperatorFlagCode like '%~%'

Martin
> Hi All,
>
> I need to change all occurances of a character in a field to another 
> character, as I dont use sqlite very often, I can select the records but 
> cannot work out how to code the "change character" part. Can anyone help?
>
> I need to cange all occurences of  "~" (tilde) to "-" (dash)
>
> the select statement is thus:
>
> select OperatorFlagCode from Aircraft where OperatorFlagCode like '%~%'
>
>
> thanks in anticipation
>
>
>   

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

Re: [sqlite] selecting uncommited rows

[Martin.Engelschalk]

Hi,

there is a pragma: PRAGMA read_uncommitted = 1;
You can select the uncommitted data and show ist before commit.

Have a look here: http://www.sqlite.org/pragma.html

Martin

Alex Katebi schrieb:
> Hi All,
>
> Let's say I start a transaction and do bunch of insertions etc. Before my
> commit I like to show (select) what I have configured.
> How can I accompilish this?
>
> Thanks,
> -Alex
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] How to use function in binding parameters ?

[Martin.Engelschalk]

Hello Marten,

do this:

insert into persons(name, birthday) values( ?, date(?))

And bind the string '1964-04-01'

Martin

Marten Feldtmann schrieb:
> I'm not sure how to use a function call in a prepared statement:
>
> insert into persons(name, birthday) values( ?,?)
>
> how to I bind the value of date('1964-04-01') to one of the
> parameters ? I want to store not the string, but the value of
> the internal date-function-call into that column ?
>
> Somehow I do not get it how I could do that using the API !?
>
> Thanks,
>
> Marten
>
>
>
>
> ___
> 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