Re: [sqlite] COMMIT in SQLite
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
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
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
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
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
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
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
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
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
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
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
-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
* 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
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
[sqlite] [ANN] Nanoki online demo
Hello, The online demo of Nanoki, a simple wiki engine implemented in Lua, has been updated to version 1.11: http://svr225.stepx.com:3388/a The demo sports content from the 2008/9 Wikipedia Selection, containing about 5500 articles, accessible through full text search, courtesy of SQLite excellent Full Text Search module (FTS): http://svr225.stepx.com:3388/search?q=sql An index, timeline and recent changes navigation is provided to facilitate article retrieval: http://svr225.stepx.com:3388/index/a http://svr225.stepx.com:3388/date/2008 http://svr225.stepx.com:3388/recent The wiki content is accessible as an Atom feed: feed://svr225.stepx.com:3388/search?q=brazil feed://svr225.stepx.com:3388/index/a feed://svr225.stepx.com:3388/date/2008 feed://svr225.stepx.com:3388/recent Or as a WebDAV repository: dav://svr225.stepx.com:3388/ Kind regards, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
