Re: [sqlite] SQL Lite date / time functions
Hello! On Monday 21 September 2009 05:44:31 D. Richard Hipp wrote: > Is the content of the "date" field either a julian day number or an > ISO-8601 format date string? Please speak is now correct to store 'start of month' dates in julianday format? Does SQLite now uses internal reprezentation of microseconds for the julianday and store it as long integer or float? Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 01:45:07 Fred Williams wrote: > With the background of the never ending drumbeat of "feature, feature, > feature..." on this list as a reason, I wonder if the structure of > SQLite could be "enhanced" to better support the "plug-in" concept, aka: > as with Firefox, IGoogle, and the like for instance. SQLite does have the best extensibility of known to me DBMS. You can bind function or collation from any programming lang. And you can easy write extension with a new functions and collations. You can use SQLite functions in any programming lang and create wrappers. Is it not enought for you?! Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Code problem
Thank you Robert and Simon. I knew it would be something simple, but I'm still a newbie. Hopefully some day I can help you. Jimmy Verner www.vernerlegal.com On Sep 20, 2009, at 6:47 PM, Simon Slavin wrote: > > On 21 Sep 2009, at 12:30am, Jimmy Verner wrote: > >> person's > > You're using single quotes to surround the text, and there's an > apostrophe in the middle of the text. I make this mistake a lot with > PHP and it's really annoying. > > 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] SQL Lite date / time functions
On Sep 18, 2009, at 7:59 PM, Mike Nolting wrote: > Mr Hipp > > I am unable to make any of the date time functions work using your > examples on your web site when applying to > data in a table. > The 'now' variable works great, but there seems to be no way to use > data from a select query from a table / column. > > EG: > > SELECT date('now','start of month','+1 month','-1 day'); produces > real data. > > However, I have a field named "Date" and a field named "StartTime" > in a table named "Reports" > > The following queries produce a null result: > SELECT date(date,'start of month','+1 month','-1 day') from reports > where reportid='123456' > > or (sub query) > > SELECT date(select date from reports where reportid='123456', 'start > of month','+1 month','-1 day') > or > > SELECT date((select date from reports where reportid='123456') , > 'start of month','+1 month','-1 day') > > Is the content of the "date" field either a julian day number or an ISO-8601 format date string? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Code problem
On 21 Sep 2009, at 12:30am, Jimmy Verner wrote: > person's You're using single quotes to surround the text, and there's an apostrophe in the middle of the text. I make this mistake a lot with PHP and it's really annoying. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Code problem
You've got single quotes in the body of your text that need to be doubled: 'Rule 404. Character Evidence not Admissible to Prove Conduct; Exceptions; Other Crimes(A) Character evidence generally. Evidence of a person'[<-- right here]s character . -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jimmy Verner Sent: Sunday, September 20, 2009 4:30 PM To: General Discussion of SQLite Database Subject: [sqlite] Code problem I can't add this record (and several others) to my table. Most of the records did load, both before and after this one. If anyone can let me know what the problem is, I would be most grateful. Here is the table setup: CREATE TABLE "rules" ("number" NUMERIC PRIMARY KEY NOT NULL ,"section" TEXT NOT NULL ,"type" CHAR NOT NULL ,"sub-section" TEXT,"title" TEXT NOT NULL ,"details" TEXT); Here is the record: INSERT INTO rules VALUES(404,'Article4','T','','Character Evidence Not Admissible to Prove Conduct; Exceptions; Other Crimes','Rule 404. Character Evidence not Admissible to Prove Conduct; Exceptions; Other Crimes(A) Character evidence generally. Evidence of a person's character or a trait of character is not admissible for the purpose of proving action in conformity therewith on a particular occasion, subject to the following exceptions:(1) Character of accused. Evidence of a pertinent trait of character offered by an accused, or by the prosecution to rebut the same is admissible; however, in prosecutions for rape, gross sexual imposition, and prostitution, the exceptions provided by statute enacted by the General Assembly are applicable.(2) Character of victim. Evidence of a pertinent trait of character of the victim of the crime offered by an accused, or by the prosecution to rebut the same, or evidence of a character trait of peacefulness of the victim offered by the prosecution in a homicide case to rebut evidence that the victim was the first aggressor is admissible; however, in prosecutions for rape, gross sexual imposition, and prostitution, the exceptions provided by statute enacted by the General Assembly are applicable.(3) Character of witness. Evidence of the character of a witness on the issue of credibility is admissible as provided in Rules 607, 608, and 609.(B) Other crimes, wrongs or acts. Evidence of other crimes, wrongs, or acts is not admissible to prove the character of a person in order to show action in conformity therewith. It may, however, be admissible for other purposes, such as proof of motive, opportunity, intent, preparation, plan, knowledge, identity, or absence of mistake or accident.'); TIA JV Jimmy Verner www.vernerlegal.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
[sqlite] Code problem
I can't add this record (and several others) to my table. Most of the records did load, both before and after this one. If anyone can let me know what the problem is, I would be most grateful. Here is the table setup: CREATE TABLE "rules" ("number" NUMERIC PRIMARY KEY NOT NULL ,"section" TEXT NOT NULL ,"type" CHAR NOT NULL ,"sub-section" TEXT,"title" TEXT NOT NULL ,"details" TEXT); Here is the record: INSERT INTO rules VALUES(404,'Article4','T','','Character Evidence Not Admissible to Prove Conduct; Exceptions; Other Crimes','Rule 404. Character Evidence not Admissible to Prove Conduct; Exceptions; Other Crimes(A) Character evidence generally. Evidence of a person's character or a trait of character is not admissible for the purpose of proving action in conformity therewith on a particular occasion, subject to the following exceptions:(1) Character of accused. Evidence of a pertinent trait of character offered by an accused, or by the prosecution to rebut the same is admissible; however, in prosecutions for rape, gross sexual imposition, and prostitution, the exceptions provided by statute enacted by the General Assembly are applicable.(2) Character of victim. Evidence of a pertinent trait of character of the victim of the crime offered by an accused, or by the prosecution to rebut the same, or evidence of a character trait of peacefulness of the victim offered by the prosecution in a homicide case to rebut evidence that the victim was the first aggressor is admissible; however, in prosecutions for rape, gross sexual imposition, and prostitution, the exceptions provided by statute enacted by the General Assembly are applicable.(3) Character of witness. Evidence of the character of a witness on the issue of credibility is admissible as provided in Rules 607, 608, and 609.(B) Other crimes, wrongs or acts. Evidence of other crimes, wrongs, or acts is not admissible to prove the character of a person in order to show action in conformity therewith. It may, however, be admissible for other purposes, such as proof of motive, opportunity, intent, preparation, plan, knowledge, identity, or absence of mistake or accident.'); TIA JV Jimmy Verner www.vernerlegal.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Can you say Oracle? :-) Along this same line, and based on a few years of using SQLite and following the messages on this list, here's my two cents worth. First a disclaimer: I have no knowledge of the detailed implementation and architecture of SQLite, other than by inference from discussions on this list. I did look at the source once long, long ago, and quickly decided, life is too short. With the background of the never ending drumbeat of "feature, feature, feature..." on this list as a reason, I wonder if the structure of SQLite could be "enhanced" to better support the "plug-in" concept, aka: as with Firefox, IGoogle, and the like for instance. This would allow SQLite (the module) to remain true to its roots (i.e. the "three choices") but better support those who desire to enhance SQLite through the use of both plug-ins and wrappers. This could better address a broader range of applications and allow the developer to selectively "bloat" the final result with only those features required for the project at hand. One thing that would greatly enhance concurrency (the most heard feature request?) would be to implement a finer grained locking structure in the base module. Currently you can either lock or unlock the database, period. All modern database implementations lock to, at a minimum, the the "record" (row) level. The implications of record level locking is pretty obvious related to concurrency. With finer grained locking implmenting multi user "server" wrappers and plug-ins would be better facilitated and allow the base SQLite module to remain true to the three choices. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of sub sk79 Sent: Sunday, September 20, 2009 9:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading "packed" data
On 20 Sep 2009, at 8:06pm, Mohit Sindhwani wrote: > Kees Nuyt wrote: >> On Sun, 20 Sep 2009 12:02:17 +0800, Mohit Sindhwani >> wrote: >> >>> Hi! An embedded SQL-based database that we used earlier had a >>> concept >>> of packed fetches - this would mean that we could create a certain >>> buffer for results, prepare a query, execute it and read back the >>> results in "groups" of 10 or 20 or 1000 (or "n") results per >>> call.. this >>> was significantly faster than reading the results one at a time. You can move the entire table of answers, by using the sqlite function which loads the entire table which is sqlite3_exec(). Or you can write your own routine which uses _prepare, _step, etc.. but parcels up the results of _step in batches. There is no low-level difference in what happens, since all of these solutions come down to calls to _step, and if your own code is reasonably efficient, none of these solutions will be any faster than any other. >> Have a look at: >> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor >> >> It is not exactly what you are looking for, but it may apply >> to your use case. > > I'm just trying to see if there's a way to move more data per request. > But it seems not (for now). By all means write your own routine that calls _step lots of times. But it won't be faster or more efficient. Other database engines have a large overhead for each call to their libraries so fewer calls results in improvements in efficiency and speed. This overhead can be caused by having to do authentication (username/password) or access a server running on another computer for each function call. SQLite does not have to do these things and has almost no overhead for each call to _step, so there's no opportunity for savings. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
StepSqlite PL/SQL compiler for SQLite is certainly not for all. It is simply aimed at what we believe is a majority. http://www.metatranz.com/stepsqlite/ regards, -sk >>But I write stored procedures and triggers for PostgreSQL on Tcl. PL/pgSQL or >>PL/SQL is not the best solution to all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Having to support a dedicated language for stored-preocedures sounds to me like an overkill, PL/SQL or not. IMHO having the ability to store complex queries in the standard TSQL syntax already supported today for queries, plus basic extra stuff only like loops, and have their compiled version executed, would be just enough (since as someone else has mentioned before most reasons to use SPs are irrelevant with most SQLite uses). If that's already an existing feature (afaik it isn't), then I have nothing else to ask for in this subject. Itamar. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alexey Pechnikov Sent: Sunday, September 20, 2009 7:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? Hello! On Sunday 20 September 2009 18:16:19 sub sk79 wrote: > PL/SQL has a very wide user-base and a huge repository of existing > code-base in the world. Using StepSqlite PL/SQL compiler this huge > base can use SQLite by reusing their code as well as reusing their > skills - no learning curve for this set of users. But I write stored procedures and triggers for PostgreSQL on Tcl. You can write it on perl, java, etc. PL/pgSQL or PL/SQL is not the best solution to all. IMHO is more interesting any open source lang than proprietary PL/SQL. Oracle has a lot of a non-standart extensions which are not exists in SQLite. And Oracle ideology is very different. You may not replace Oracle to SQLite with the same application architecture. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ 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] Most wanted features of SQLite ?
As Igor mentioned in a previous post, Hebrew has no capital and lower case versions of a letter. It has only what's called Mantzpach, which are 5 letters which look different if in the end of a word; IIRC this is never an issue since they are ordered right after the original letter, and have their own character value (unlike capital/lower letters in English). Niqqud (the diacritical signs) are considered characters on their own, so you should either strip them (a common solution), or take that into account; here I'm not sure what is the default behavior. Also, since Hebrew letters are only used in Hebrew texts (unlike latin characters), sorting is hardly an issue if you have a Unicode representation of the character (usually a two byte one). The issues ICU is more likely to help you with are logical to visual conversion and the other way around and BiDi stuff. That's my own grasp of things, never had to use ICU myself. Itamar. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Saturday, September 19, 2009 6:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? On 19 Sep 2009, at 3:07am, Igor Tandetnik wrote: > Simon Slavin wrote: >> Thanks to you and Jay for explanations. I hadn't encountered ICU at >> all before. Your descriptions make perfect sense and are very >> interesting since ICU is a good attempt to get around one of the >> fundamental problems of Unicode. > > Out of curiosity - what do you consider a fundamental problem of > Unicode? The fact that different people may prefer their strings > sorted differently? Only in that it's a fundamental problem with the way Unicode was defined. I completely recognise that the question of sorting cannot be answered at the level of characters for the reasons we discussed: different alphabets have different meanings for the same characters, and Unicode has just one entry for the character. It might have made more sense to define two levels of character definitions: one which says what 'c with a hat on' looks like, and another that defines alphabets, character alternatives, and where 'c with a hat on' comes in various alphabets. The problem I was referring to is that there's no consistent way of picking up which characters are variants of other characters. In the Roman alphabet, it would be very useful to be able to look at the codes for 'l' and capital 'L' and realise that they're somehow the same. In Hebrew it would be useful to be able match not only capital and lower-case characters, but also the variants used when a character occurs at the end of a word. ICU is a great way to approach these problems and similar ones. I have no problem with it. On 19 Sep 2009, at 3:17am, Roger Binns wrote: > Errr, this is not the fault of Unicode. Your reaction to my post is amusingly similar to my reaction when people assume that database synchronisation is simple. Sorry to have irritated you. I understand Unicode in more detail than we've discussed here. I do not consider these things to be 'the fault of Unicode' rather, in the words I used, 'problems with Unicode'. And I do consider Unicode to be far superior to the mess of code pages we used to have to implement before it became popular. 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] Reading "packed" data
Kees Nuyt wrote: > On Sun, 20 Sep 2009 12:02:17 +0800, Mohit Sindhwani > wrote: > > >> Hi! An embedded SQL-based database that we used earlier had a concept >> of packed fetches - this would mean that we could create a certain >> buffer for results, prepare a query, execute it and read back the >> results in "groups" of 10 or 20 or 1000 (or "n") results per call.. this >> was significantly faster than reading the results one at a time. >> >> Is there a parallel? Or is it possible to implement such a thing? >> > > Have a look at: > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > It is not exactly what you are looking for, but it may apply > to your use case. > It is not exactly what I am looking for - but the link is very interesting, thanks. I'm just trying to see if there's a way to move more data per request. But it seems not (for now). Cheers, Mohit. 9/21/2009 | 3:06 AM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
P Kishor wrote: > On Sun, Sep 20, 2009 at 4:16 AM, Dan Bishop wrote: > >> Max_wang wrote: >> >>> A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT >>> date(253392451200.0, >>> 'unixepoch');" >>> The result was "-09-09". >>> >>> But now I use SQLite 3.6.18 replace it,this SQL execute result is >>> "-1413-03-01". >>> >>> Is this a Bug? >>> >>> >> Apparently so. And the problem first occurs in 5352. >> >> sqlite> SELECT date(106751991167.30063, 'unixepoch'); >> 5352-11-01 >> >> sqlite> SELECT date(106751991167.30064, 'unixepoch'); >> -1413-03-01 > How on earth did you discover that?! The Bisection Method. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite profiler
Hello, Is there anyone interested in sqlite profiler? If yes what features would you like to see? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 18:16:19 sub sk79 wrote: > PL/SQL has a very wide user-base and a huge repository of existing > code-base in the world. Using StepSqlite PL/SQL compiler this huge > base can use SQLite by reusing their code as well as reusing their > skills - no learning curve for this set of users. But I write stored procedures and triggers for PostgreSQL on Tcl. You can write it on perl, java, etc. PL/pgSQL or PL/SQL is not the best solution to all. IMHO is more interesting any open source lang than proprietary PL/SQL. Oracle has a lot of a non-standart extensions which are not exists in SQLite. And Oracle ideology is very different. You may not replace Oracle to SQLite with the same application architecture. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading "packed" data
Igor Tandetnik wrote: > Mohit Sindhwani wrote: > >> Hi! An embedded SQL-based database that we used earlier had a concept >> of packed fetches - this would mean that we could create a certain >> buffer for results, prepare a query, execute it and read back the >> results in "groups" of 10 or 20 or 1000 (or "n") results per call.. >> this was significantly faster than reading the results one at a time. >> >> Is there a parallel? >> > > No. With SQLite, it wouldn't be benefitial. > > Igor Tandetnik > > > Thanks, Igor. Cheers, Mohit. 9/20/2009 | 11:24 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Can you say Oracle? :-) Along this same line, and based on a few years of using SQLite and following the messages on this list, here's my two cents worth. First a disclaimer: I have no knowledge of the detailed implementation and architecture of SQLite, other than by inference from discussions on this list. I did look at the source once long, long ago, and quickly decided, life is too short. With the background of the never ending drumbeat of "feature, feature, feature..." on this list as a reason, I wonder if the structure of SQLite could be "enhanced" to better support the "plug-in" concept, aka: as with Firefox, IGoogle, and the like for instance. This would allow SQLite (the module) to remain true to its roots (i.e. the "three choices") but better support those who desire to enhance SQLite through the use of both plug-ins and wrappers. This could better address a broader range of applications and allow the developer to selectively "bloat" the final result with only those features required for the project at hand. One thing that would greatly enhance concurrency (the most heard feature request?) would be to implement a finer grained locking structure in the base module. Currently you can either lock or unlock the database, period. All modern database implementations lock to, at a minimum, the the "record" (row) level. The implications of record level locking is pretty obvious related to concurrency. With finer grained locking implmenting multi user "server" wrappers and plug-ins would be better facilitated and allow the base SQLite module to remain true to the three choices. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of sub sk79 Sent: Sunday, September 20, 2009 9:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? >> 1. Generate a loadable SQLite extension and distribute it with DB. > > We can write C extension without any wrapper. Why we need to learn > your extension for getting the same result and possible new bugs? PL/SQL has a very wide user-base and a huge repository of existing code-base in the world. Using StepSqlite PL/SQL compiler this huge base can use SQLite by reusing their code as well as reusing their skills - no learning curve for this set of users. For those who do need to learn PL/SQL, it is still worthwhile to utilize StepSqlite because: PL/SQL is specially designed for and very suitable for handling database operations, while other general purpose languages like TCL, lua, C , C++ etc are very suitable indeed for application programming. StepSqlite enables combining the respective power of these two sets of languages in writing apps with SQLite back-ends by using the concept of Stored Procedures/Functions and Packages. All big databases operate this way as well: for ex, Oracle lets apps written in C++ to call stored procedures written in PL/SQL. StepSqlite is committed to bringing this power and convenience from the big database world to the small database world while preserving the small, fast and reliable. More benefits of StepSqlite described here: http://www.metatranz.com/stepsqlite/benefits.html http://www.metatranz.com/stepsqlite regards, -sk ___ 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 can I do to build this query with a text that contains ' and "?
Thanks, now work fine BestRegards GVS Simon Davies escribió: > 2009/9/20 Guillermo Varona Silupú : > >> Hi >> I want to insert this text: >> >> 1' equivale a 12" >> >> cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a 12"") >> > > INSERT INTO Tabla1 (Code,Equiv) VALUES (10, '1'' equivale a 12"'); > > >> TIA >> Best Regards >> GVS >> >> P.D.: Sorry for the post past and my bad english >> >> >> > > single quotes to delimit string literals. Double them up to escape > them inside a string literal. > See http://www.sqlite.org/lang_expr.html#litvalue > > Regards, > 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] Most wanted features of SQLite ?
Guillermo Varona Silupú wrote: > I want to insert this text: > > 1' equivale a 12" > > cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a > 12"") What language is that? ':=' suggests Pascal, but string literals in Pascal use single quotes. In SQL, string literals are surrounded by single quotes: if you need a single quote as part of the string, you enter two of them in a row. Double quotes don't require any special handling inside a string literal. INSERT INTO Tabla1 (Code,Equiv) VALUES (10, '1'' equivale a 12"') Now that you have a valid SQL statement, figure out how to represent it as a string literal in whatever host language you are using. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> 1. Generate a loadable SQLite extension and distribute it with DB. > > We can write C extension without any wrapper. Why we need to learn > your extension for getting the same result and possible new bugs? PL/SQL has a very wide user-base and a huge repository of existing code-base in the world. Using StepSqlite PL/SQL compiler this huge base can use SQLite by reusing their code as well as reusing their skills - no learning curve for this set of users. For those who do need to learn PL/SQL, it is still worthwhile to utilize StepSqlite because: PL/SQL is specially designed for and very suitable for handling database operations, while other general purpose languages like TCL, lua, C , C++ etc are very suitable indeed for application programming. StepSqlite enables combining the respective power of these two sets of languages in writing apps with SQLite back-ends by using the concept of Stored Procedures/Functions and Packages. All big databases operate this way as well: for ex, Oracle lets apps written in C++ to call stored procedures written in PL/SQL. StepSqlite is committed to bringing this power and convenience from the big database world to the small database world while preserving the small, fast and reliable. More benefits of StepSqlite described here: http://www.metatranz.com/stepsqlite/benefits.html http://www.metatranz.com/stepsqlite regards, -sk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I do to build this query with a text that contains ' and "?
2009/9/20 Guillermo Varona Silupú : > Hi > I want to insert this text: > > 1' equivale a 12" > > cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a 12"") INSERT INTO Tabla1 (Code,Equiv) VALUES (10, '1'' equivale a 12"'); > > TIA > Best Regards > GVS > > P.D.: Sorry for the post past and my bad english > > single quotes to delimit string literals. Double them up to escape them inside a string literal. See http://www.sqlite.org/lang_expr.html#litvalue Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading "packed" data
On Sun, 20 Sep 2009 12:02:17 +0800, Mohit Sindhwani wrote: >Hi! An embedded SQL-based database that we used earlier had a concept >of packed fetches - this would mean that we could create a certain >buffer for results, prepare a query, execute it and read back the >results in "groups" of 10 or 20 or 1000 (or "n") results per call.. this >was significantly faster than reading the results one at a time. > >Is there a parallel? Or is it possible to implement such a thing? Have a look at: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor It is not exactly what you are looking for, but it may apply to your use case. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How can I do to build this query with a text that contains ' and "?
Hi I want to insert this text: 1' equivale a 12" cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a 12"") TIA Best Regards GVS P.D.: Sorry for the post past and my bad english ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hi I want to insert this text: 1' equivale a 12" cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a 12"") TIA Best Regards GVS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Wenbo Zhao wrote: > I was talking about this example by > 2009/9/19 Igor Tandetnik > "Imagine the > classic example, where a transaction first verifies that the balance > in a bank account is sufficient, then performs a withdrawal. If it > relinquishes all locks between these two steps, then somebody else may > record a withdrawal from that account, so that the write operation > would then make the balance negative, thus violating an invariant." > > What I want to say is in this example, there should be only one step, > because the transaction knows it will do 'write'. Not necessarily. It won't do the write if it discovers in the first step that the balance is, in fact, insufficient. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading "packed" data
Mohit Sindhwani wrote: > Hi! An embedded SQL-based database that we used earlier had a concept > of packed fetches - this would mean that we could create a certain > buffer for results, prepare a query, execute it and read back the > results in "groups" of 10 or 20 or 1000 (or "n") results per call.. > this was significantly faster than reading the results one at a time. > > Is there a parallel? No. With SQLite, it wouldn't be benefitial. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
On Sun, Sep 20, 2009 at 4:16 AM, Dan Bishop wrote: > Max_wang wrote: >> A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, >> 'unixepoch');" >> The result was "-09-09". >> >> But now I use SQLite 3.6.18 replace it,this SQL execute result is >> "-1413-03-01". >> >> Is this a Bug? >> > Apparently so. And the problem first occurs in 5352. > > sqlite> SELECT date(106751991167.30063, 'unixepoch'); > 5352-11-01 > > sqlite> SELECT date(106751991167.30064, 'unixepoch'); > -1413-03-01 > How on earth did you discover that?! -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 08:05:04 Darren Duncan wrote: > The more general solution here to the duplicate column name problem is to be > stricter than the SQL standard and treat attempts to return duplicate column > names as a syntax or constraint error. For example, if you had 2 tables > 'foo' > and 'bar' with columns named (a, b) and (b, c), then a plain "select * from > foo > inner join bar on ..." should throw an exception because there would be two > 'b' > in the result. And so, proper NATURAL or USING behavior is one way to say > "select * from foo inner join bar ..." with success, and spelling out the > result > column list rather than using "*" is another way. But you have to deal with > it > explicitly or the SQL will refuse to run, is what the DBMS should do, or the > DBMS should be customizable so it can be thusly strict. It's interesting. The new pragma "unique_column_names" may be helpful for a lot of situations same as the "indexed by" condition for selects. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 02:42:30 Darren Duncan wrote: > As for Tcl, well one solution there is to create a new singleton TCL type and > use its one value to correspond to NULL, and so then empty string will > continue > to just mean empty string, as it should. Not being able to distinguish > known-to-be-an-empty-string from unknown-or-N/A value is a bad thing. In human interface can be used 'not defined' or 'overload' or any other value and all of these together in dependence of the situation. SQLite can store 'undef' and 'overload' and other values into integer or fload fields and this behaviour is fine. Application can operate with correct data by checking a typeof(). For single undefined value we can do more simple. Empty string or 'undefined' values are equal because have the same typeof()=text. I don't see any reasons to use a new singleton TCL type inside of empty string. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Date problem
Max_wang wrote: > A few months ago,I used SQLite 3.5.0 execute SQL:"SELECT date(253392451200.0, > 'unixepoch');" > The result was "-09-09". > > But now I use SQLite 3.6.18 replace it,this SQL execute result is > "-1413-03-01". > > Is this a Bug? > Apparently so. And the problem first occurs in 5352. sqlite> SELECT date(106751991167.30063, 'unixepoch'); 5352-11-01 sqlite> SELECT date(106751991167.30064, 'unixepoch'); -1413-03-01 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 02:45:32 sub sk79 wrote: > 1. Generate a loadable SQLite extension and distribute it with DB. We can write C extension without any wrapper. Why we need to learn your extension for getting the same result and possible new bugs? > 2. If distributing loadable extensions is a concern (security or > otherwise), StepSqlite also has an option to generate a regular C++ > library instead. This library can be linked into the user's > application code And how about Tcl, Python, etc? We need to recompile it? :-) Application code (Tcl, Python, etc.) can be stored into database and executed by application. C code can be compiled once and executed with Tiny C compiler. I'm sorry but I don't see any helpful features of your wrapper. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users