Re: [sqlite] COMMIT in SQLite

[hussainfarzana]

Thanks for your reply Igor.

We have used BEGIN and COMMIT while executing our statements.While executing
the BEGIN the return value is 0,but for COMMIT its returning 1.

Please guide us.

Regards,
Farzana.


Igor Tandetnik wrote:
> 
> "hussainfarzana"
>  wrote in
> message news:20971588.p...@talk.nabble.com
>> We have developed our application in eVC++ 3.0 using SQLite as the
>> database. While insertion or updation of records
>> We have used BEGIN and COMMIT method to insert or update the records.
>> While running the application we are able to insert and update and we
>> are able to check that value also.Once we exit the application and
>> reopen the application again we are not able to see the values and
>> these records are missing in the database also.The records are
>> inserted or updated temporarily.
> 
> You are executing a BEGIN, but you are _not_ executing a COMMIT. Check 
> your code, figure out why. Without COMMIT, your transaction gets rolled 
> back.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/COMMIT-in-SQLite-tp20971588p21009480.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Adding data with periods

[John Stanton]

Single quotes are SQL, as chosen by he designers.  It is good practice 
to stick to the standard rather than rely on extensions which vary from 
implementatiopn tio implementation.

Mohd Radzi Ibrahim wrote:
> It seems to works either way.
>
> I'm just wondering is there any hidden reason that single quote is 
> preferred? Portability?
> Or is double-qoute has some kind of special meaning that we should use it 
> for that special purpose?
>
>
> -radzi-
>
>
> - Original Message - 
> From: "P Kishor" 
> To: "General Discussion of SQLite Database" 
> Sent: Monday, December 15, 2008 1:32 PM
> Subject: Re: [sqlite] Adding data with periods
>
>
>   
>> On 12/14/08, aditya siram  wrote:
>> 
>>> Thanks a lot. The issue has been fixed with:
>>>  INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");
>>>
>>>   
>> Use single quotes to delimit text, not double quotes.
>> ___
>> 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] Adding data with periods

[Mohd Radzi Ibrahim]

It seems to works either way.

I'm just wondering is there any hidden reason that single quote is 
preferred? Portability?
Or is double-qoute has some kind of special meaning that we should use it 
for that special purpose?


-radzi-


- Original Message - 
From: "P Kishor" 
To: "General Discussion of SQLite Database" 
Sent: Monday, December 15, 2008 1:32 PM
Subject: Re: [sqlite] Adding data with periods


> On 12/14/08, aditya siram  wrote:
>> Thanks a lot. The issue has been fixed with:
>>  INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");
>>
>
>
> Use single quotes to delimit text, not double quotes.
> ___
> 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] Adding data with periods

[P Kishor]

On 12/14/08, aditya siram  wrote:
> Thanks a lot. The issue has been fixed with:
>  INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");
>


Use single quotes to delimit text, not double quotes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding data with periods

[aditya siram]

Thanks a lot. The issue has been fixed with:
INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");

I was thrown by the error message that seemed to be complaining about the
periods when I was actually missing the VALUES(...) clause.

Appreciate the quick response ...
deech

On Sun, Dec 14, 2008 at 11:21 PM, John Stanton  wrote:

> Note that literal delimiters in SQL are single quotes, e.g. 'This is an
> SQL literal'.
>
> It is good practice with Sqlite to use bound variables.  You avoid
> possible SQL injection attacks and limit sensitivity to data content.
>
> aditya siram wrote:
> > Hi all,
> > I'm having trouble adding data with period characters in it. I tries to
> > escape the period with a `'` but that didn' t seem to work. Here is an
> > example interaction:
> >
> > sqlite> create table test_table ("Contents" varchar);
> > sqlite> insert into test_table "hello . world";
> > SQL error: near ""hello . world"": syntax error
> > sqlite> insert into test_table "hello '. world";
> > SQL error: near ""hello '. world"": syntax error
> >
> > Thanks ...
> > deech
> > ___
> > 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] Adding data with periods

[John Stanton]

Note that literal delimiters in SQL are single quotes, e.g. 'This is an 
SQL literal'.

It is good practice with Sqlite to use bound variables.  You avoid 
possible SQL injection attacks and limit sensitivity to data content.

aditya siram wrote:
> Hi all,
> I'm having trouble adding data with period characters in it. I tries to
> escape the period with a `'` but that didn' t seem to work. Here is an
> example interaction:
>
> sqlite> create table test_table ("Contents" varchar);
> sqlite> insert into test_table "hello . world";
> SQL error: near ""hello . world"": syntax error
> sqlite> insert into test_table "hello '. world";
> SQL error: near ""hello '. world"": syntax error
>
> Thanks ...
> deech
> ___
> 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] Adding data with periods

[Mohd Radzi Ibrahim]

The syntax is wrong.

INSERT INTO TEST_TABLE(CONTENTS) VALUES ("Hello. World!");

is the correct one.

-radzi-
- Original Message - 
From: "aditya siram" 
To: 
Sent: Monday, December 15, 2008 1:04 PM
Subject: [sqlite] Adding data with periods


> Hi all,
> I'm having trouble adding data with period characters in it. I tries to
> escape the period with a `'` but that didn' t seem to work. Here is an
> example interaction:
> 
> sqlite> create table test_table ("Contents" varchar);
> sqlite> insert into test_table "hello . world";
> SQL error: near ""hello . world"": syntax error
> sqlite> insert into test_table "hello '. world";
> SQL error: near ""hello '. world"": syntax error
> 
> Thanks ...
> deech
> ___
> 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] Adding data with periods

[P Kishor]

On 12/14/08, aditya siram  wrote:
> Hi all,
>  I'm having trouble adding data with period characters in it. I tries to
>  escape the period with a `'` but that didn' t seem to work. Here is an
>  example interaction:
>
>  sqlite> create table test_table ("Contents" varchar);
>  sqlite> insert into test_table "hello . world";
>  SQL error: near ""hello . world"": syntax error
>  sqlite> insert into test_table "hello '. world";
>  SQL error: near ""hello '. world"": syntax error
>


many errors in the above. The correct syntax would be

sqlite> INSERT INTO test_table VALUES ('hello . world');

note the keyword VALUES, and the text string delimited with single
quotes, not double quotes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding data with periods

[kirrthana]

Hi,

Sqlite takes the period as a special character. Wherever you get a period '
make it as ''. This will make it as a normal character and will work fine.
Hope the solution solves your problem.

Regards,
Kirrthana

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of aditya siram
Sent: Monday, December 15, 2008 10:35 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Adding data with periods


Hi all,
I'm having trouble adding data with period characters in it. I tries to
escape the period with a `'` but that didn' t seem to work. Here is an
example interaction:

sqlite> create table test_table ("Contents" varchar);
sqlite> insert into test_table "hello . world";
SQL error: near ""hello . world"": syntax error
sqlite> insert into test_table "hello '. world";
SQL error: near ""hello '. world"": syntax error

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


The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments contained in it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Adding data with periods

[aditya siram]

Hi all,
I'm having trouble adding data with period characters in it. I tries to
escape the period with a `'` but that didn' t seem to work. Here is an
example interaction:

sqlite> create table test_table ("Contents" varchar);
sqlite> insert into test_table "hello . world";
SQL error: near ""hello . world"": syntax error
sqlite> insert into test_table "hello '. world";
SQL error: near ""hello '. world"": syntax error

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

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

[Jay A. Kreibich]

On Sun, Dec 14, 2008 at 08:25:02PM +, Nathan Catlow scratched on the wall:

> I am trying to use libdbi + sqlite for my project. It is impossible  
> for libdbi to determine the PRIMARY KEY type (64bit) using PRAGMA  
> table_info(). 

  Why impossible?  The type in INTEGER, just as returned.

> As the PRIMARY KEY is an alias to ROWID (64bit), 

  The PRIMARY KEY is *ONLY* an alias for ROWID if it is defined
  as "INTEGER PRIMARY KEY".  You can define any arbitrary PRIMARY KEY,
  including a multi-column key.

> the data should be returned as a 64bit integer.

  And it is.  All INTEGER valued types in SQLite are capable of holding
  a 64 bit value.

> Unfortunately the PRAGMA table_info()  
> command returns a type of INTEGER (32 bit in at least mysql).

  Yes.  It returns INTEGER because that's what it is-- at least if
  you're doing a ROWID alias with AUTOINCREMENT.

  What MySQL might or might not define INTEGER to be is irrelevant.
  The SQL standard doesn't have specs for how many bits different types
  of numbers take.  If you, or any libraries or software you are using
  makes such assumptions, you're going to have a lot of headaches.

  "INTEGER" in Oracle, for example, defines a 38 digit (base-10)
  integer.  That's a bit more than 120 bits.

  On the flip side, I *wouldn't* assume a ROWID is a 64 bit integer. 
  That happens to be true in SQLite, but there is nothing that says
  that has to be true.  Again, it isn't true in Oracle.

> It is  
> also impossible to help by defining primary key as BIGINT PRIMARY KEY,  
> as the field does not then autoincrement in sqlite.

  Right.  The docs are quite specific.  You can define a BIGINT
  PRIMARY KEY, but since it is not "INTEGER PRIMARY KEY" it won't
  become a ROWID alias.  AUTOINCREMENT only works on ROWID columns.

> This would then  
> mean it will be returned via the PRAGMA statement as BIGINT and DB  
> independant libraries can return the correct datatype and also be SQL  
> compatible with other DB engines.

  The fault is in the libraries for making rash and incorrect
  assumptions about types and sizes, not in anything SQLite is doing.
  Anything that assumes "INTEGER" in SQL is a 32 bit number (or that
  any column marked ROWID must be a 64 bit integer) is making
  poor and incorrect assumptions.

> Another example would be to move data from sqlite to mysql, because  
> the datatype INTEGER PRIMARY KEY is only 32bits on mysql, if I were to  
> dump the database from sqlite the CREATE TABLE test(id INTEGER PRIMARY  
> KEY) would only be able to hold a 32bit integer in mysql where the  
> data in sqlite is actually 64bits (ROWID) resulting in an overflow.

  Yes.  You'll find similar problems with nearly any other numeric
  type in SQL.  This is not C or C++ (and even C does not define a
  specific size for "int"...).

  Welcome to the wonderful world of cross-platform SQL.

> This is a real showstopper for me, I want to use sqlite, but have an  
> compatible way of supporting other db engines.

  If compatibility is the issue, you might have a look at the SQL
  standards and what assumptions you can actually make about types,
  sizes, and value domains.  It seems you, or the software you're using,
  has a lot of misconceptions about the type systems used in different
  flavors of SQL.   In short, you can't assume much of anything.

  You definitely can't assume something as generic as "INTEGER" has a
  specific domain, like a 32-bit number, or that "ROWID" is a 64-bit
  integer.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Nathan Catlow wrote:
> ... INTEGER (32 bit in at least mysql) ...
> ... (id BIGINT ...

You seem to be under the impression that SQLite has multiple variable
sized integers.  It doesn't.  The SQLite integer type is 64 bit signed.
 (It will use less than all 64 bits when stored in the file as a storage
space optimisation but that implementation detail is not user visible).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAklFnZAACgkQmOOfHg372QRNbgCeNGO7SOggRVbBFHJCZivXOfAo
ae8AnRrP2OB1idT2nbJs9SclDgzmVpan
=sKtV
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] General index information

[Florian Weimer]

* Igor Tandetnik:

> L B  wrote:
>> The explanation you made is only valid for sqlite or
>> for SQL in general?
>
> For SQL in general.

True, but there are some database engines that support skip-index
scans, so if there are only few different values of X, say x_1,
... x_k, you've got an index on (X, Y) and want to find all y, the
query is executed as lookups for (x_1, y), ..., (x_k, y).

(Obviously, this is only beneficial if k is not too big.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Impossible to declare field type BIGINT PRIMARY KEY

[Nathan Catlow]

I am trying to use libdbi + sqlite for my project. It is impossible  
for libdbi to determine the PRIMARY KEY type (64bit) using PRAGMA  
table_info(). This has completely crippled my project that relies on  
autoincrement.

As the PRIMARY KEY is an alias to ROWID (64bit), the data should be  
returned as a 64bit integer. Unfortunately the PRAGMA table_info()  
command returns a type of INTEGER (32 bit in at least mysql). It is  
also impossible to help by defining primary key as BIGINT PRIMARY KEY,  
as the field does not then autoincrement in sqlite.

sqlite> CREATE TABLE test(id BIGINT PRIMARY KEY AUTOINCREMENT); SQL  
error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

As the PRIMARY KEY is really ROWID (64bit), shouldn't I be able to  
define it as BIGINT and still have it autoincrement? This would then  
mean it will be returned via the PRAGMA statement as BIGINT and DB  
independant libraries can return the correct datatype and also be SQL  
compatible with other DB engines.

Another example would be to move data from sqlite to mysql, because  
the datatype INTEGER PRIMARY KEY is only 32bits on mysql, if I were to  
dump the database from sqlite the CREATE TABLE test(id INTEGER PRIMARY  
KEY) would only be able to hold a 32bit integer in mysql where the  
data in sqlite is actually 64bits (ROWID) resulting in an overflow.

The correct definition is actually CREATE TABLE test(id BIGINT PRIMARY  
KEY AUTOINCREMENT), this would be a simple patch to sqlite allow that  
statement and everything would be groovy.

So to summarise:

CREATE TABLE test(id BIGINT PRIMARY KEY) - Should internally work  
exactly the same as INTEGER PRIMARY KEY where rowid == id and can be  
reused.

CREATE TABLE test(id BIGINT PRIMARY KEY AUTOINCREMENT) - Should  
internally work exactly the same as INTEGER PRIMARY KEY but rowid ==  
id and cannot be re-used.

And the output from PRAGMA table_info(test) would be completely correct;

PRAGMA table_info(test);
0|id|BIGINT|0||1

By amending sqlite3.c (sqlite-3.6.6.2) line 59780 to read:

   if( zType && (sqlite3StrICmp(zType, "INTEGER")==0 ||  
sqlite3StrICmp(zType, "BIGINT")==0)
 && sortOrder==SQLITE_SO_ASC ){

I was able to get sqlite to accept the AUTOINCREMENT command on  
BIGINT, but I don't know how to make it work for re-usable ROWIDS.

This is a real showstopper for me, I want to use sqlite, but have an  
compatible way of supporting other db engines.

I appreciate your comments.

regards

nat




This message was sent using IMP, the Internet Messaging Program.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users