[sqlite] How do you access a specific row of a resultset?
Hi, I want to know how to go about getting a specific row from a resultset (or if there is a different way to do it Im all for that too) My code goes something like this, stat being a statement variable already initialised previously. I thought perhaps absolute sounds like it might give the required row but it doesnt seem to have. s is a string declared already previously and picPath is a column of the table. I hope I have made what I am trying to do clear and would appreciate any help. ResultSet rs = stat.executeQuery(select * from table1;); rs.absolute(index); s = rs.getString(picPath); -- View this message in context: http://old.nabble.com/How-do-you-access-a-specific-row-of-a-resultset--tp33856419p33856419.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] How do you access a specific row of a resultset?
On 16 May 2012, at 9:04am, Goatjugsoup goatjugs...@hotmail.com wrote: Hi, I want to know how to go about getting a specific row from a resultset (or if there is a different way to do it Im all for that too) My code goes something like this, stat being a statement variable already initialised previously. I thought perhaps absolute sounds like it might give the required row but it doesnt seem to have. s is a string declared already previously and picPath is a column of the table. I hope I have made what I am trying to do clear and would appreciate any help. ResultSet rs = stat.executeQuery(select * from table1;); rs.absolute(index); s = rs.getString(picPath); What programming language and API are you using ? My guess is that you can't do what you want. You have to iterate through the result set using .next() . The results of the query aren't all loaded up in an array waiting for you, they're not ready for you until you have used .next() to fetch them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] import problem
I have problem when import with versione superior to 3071000. No problem with 3071000. ..\sqlite3 -echo aa_VUOTO.db 0aa.script .separator \t .import aa.txt aa Error: aa line 1744: expected 5 columns of data but found 2 .import aa aa Error: aa.txt line 82886: expected 9 columns of data but found 5 .exit -- Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f Sponsor: Offerta Hotel + parchi con l'hotel Europa di Misano Adriatico, vendita biglietti in prevendita per un soggiorno tutto compreso in un hotel per famiglie Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12351d=16-5 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Escape table and column names
Hello, I would like to receive a definitive answer about what is the recommended way to escape table and column names. SQLite supports single quotes, double quotes and square brackets (for mySQL compatibility) … but I haven't yet found the official or recommended way. Anyone have an answer? What about if table name or column name contains that escape character? Do I need to escape it in another way? Please let me know. 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] sqlite3_column_count vs. sqlite3_data_count
sqlite3_column_count sqlite3_data_count What is the difference between the two? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
On May 16, 2012, at 12:28 PM, ma...@sqlabs.net ma...@sqlabs.net wrote: Anyone have an answer? http://www.sqlite.org/lang_keywords.html What about if table name or column name contains that escape character? Do I need to escape it in another way? http://sqlite.org/faq.html#q14 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_count vs. sqlite3_data_count
On May 16, 2012, at 12:49 PM, Baruch Burstein wrote: sqlite3_column_count Number of columns. http://www.sqlite.org/c3ref/column_count.html sqlite3_data_count Number of rows. http://www.sqlite.org/c3ref/data_count.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_count vs. sqlite3_data_count
On 05/16/2012 05:49 PM, Baruch Burstein wrote: sqlite3_column_count sqlite3_data_count What is the difference between the two? sqlite3_data_count() will return zero if the statement handle does not currently point to a row of data. For example if it has just been prepared or reset, or if the last call to sqlite3_step() returned something other than SQLITE_ROW. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_count vs. sqlite3_data_count
On 05/16/2012 05:57 PM, Petite Abeille wrote: On May 16, 2012, at 12:49 PM, Baruch Burstein wrote: sqlite3_column_count Number of columns. http://www.sqlite.org/c3ref/column_count.html sqlite3_data_count Number of rows. http://www.sqlite.org/c3ref/data_count.html Not so. It's the number of columns of data currently available. Which is sometimes the same as sqlite3_column_count() and sometimes zero. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
What about if table name or column name contains that escape character? Do I need to escape it in another way? http://sqlite.org/faq.html#q14 This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. Given that we have 3 ways to quote a schema name (abc def, [abc def] and `abc def`) it's unlikely that a user would select a real-world name including all 3 [ ` as significant characters. So that solves the issue for user entried schema names. OTOH I agree with Marco in that it would be nice to have a solid rule that could be implemented in software to properly quote and escape schema names in the general case. JcD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] NULL sqlite3_stmt in function calls
Are all API functions that accept an sqlite3_stmt* safe (=NO-OPs) to call with a NULL pointer? I mean things like bind, column, etc. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
On 16 May 2012, at 11:56am, Petite Abeille petite.abei...@gmail.com wrote: On May 16, 2012, at 12:28 PM, ma...@sqlabs.net ma...@sqlabs.net wrote: Anyone have an answer? http://www.sqlite.org/lang_keywords.html What about if table name or column name contains that escape character? Do I need to escape it in another way? http://sqlite.org/faq.html#q14 Doesn't answer the question of what to do if your table name includes a double-quote character. And nor can I, except to say that if I saw such a thing I'd run away. SImon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
On Wed, May 16, 2012 at 2:25 PM, Simon Slavin slav...@bigfraud.org wrote: On 16 May 2012, at 11:56am, Petite Abeille petite.abei...@gmail.com wrote: On May 16, 2012, at 12:28 PM, ma...@sqlabs.net ma...@sqlabs.net wrote: Anyone have an answer? http://www.sqlite.org/lang_keywords.html What about if table name or column name contains that escape character? Do I need to escape it in another way? http://sqlite.org/faq.html#q14 Doesn't answer the question of what to do if your table name includes a double-quote character. And nor can I, except to say that if I saw such a thing I'd run away. A quick testing shows it works the same for double-quoted column names, e.g. sqlite create table qw (t); sqlite .tables qw -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
On May 16, 2012, at 1:15 PM, Jean-Christophe Deschamps wrote: This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. create table foobar ( baz ); select * from onetwo; select * from sqlite_master; table|foobar|foobar|9|CREATE TABLE foobar ( baz ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. create table foobar ( baz ); select * from onetwo; select * from sqlite_master; table|foobar|foobar|9|CREATE TABLE foobar ( baz ) I knew this particular escape worked, granted, but this isn't documented. But even then: SQLite version 3.7.11 2012-03-20 11:35:50 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table [foo [[bar]] ?]; Error: unrecognized token: ] sqlite create table `foo ``bar`` ?`; Error: near ;: syntax error sqlite create table `foo ``bar ?`; Error: near ;: syntax error So some questions remain: what are the definitive rules? JcD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_count vs. sqlite3_data_count
On 05/16/2012 06:04 PM, Stephan Beal wrote: On Wed, May 16, 2012 at 1:02 PM, Dan Kennedydanielk1...@gmail.com wrote: Not so. It's the number of columns of data currently available. Which is sometimes the same as sqlite3_column_count() and sometimes zero. Can sqlite3_data_count() be used to determine if calling sqlite3_colum_xxx() is legal, i.e. if sqlite3_step() has succeeded? Yes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
On Wed, May 16, 2012 at 2:47 PM, Jean-Christophe Deschamps j...@antichoc.net wrote: This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. create table foobar ( baz ); select * from onetwo; select * from sqlite_master; table|foobar|foobar|9|CREATE TABLE foobar ( baz ) I knew this particular escape worked, granted, but this isn't documented. But even then: SQLite version 3.7.11 2012-03-20 11:35:50 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table [foo [[bar]] ?]; Error: unrecognized token: ] sqlite create table `foo ``bar`` ?`; Error: near ;: syntax error sqlite create table `foo ``bar ?`; Error: near ;: syntax error So some questions remain: what are the definitive rules? Those all work. You are getting an error because you didn't supply any column in the table -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escape table and column names
On Wed, May 16, 2012 at 01:47:01PM +0200, Jean-Christophe Deschamps scratched on the wall: This FAQ topic doesn't apply here. We're talking about schema names or keywords, while FAQ#14 is literal escaping a single quote. create table foobar ( baz ); select * from onetwo; select * from sqlite_master; table|foobar|foobar|9|CREATE TABLE foobar ( baz ) I knew this particular escape worked, granted, but this isn't documented. All three of these are part of the SQL standard: A) The use of single quotes to define string literals B) The use of double quotes to define identifiers**. C) The use of repeat characters as an escape. ** An identifer in SQL is an object name, such as a database name, table name, or column name. So some questions remain: what are the definitive rules? Unless otherwise documented, the SQL standard. See above three points. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite max query parameters differs on Snow Leopard?
All - I posted this query on stackoverflow, but I'm probably more likely to get an answer here. http://stackoverflow.com/questions/10332668/sqlite-max-query-parameters-differs-on-snow-leopard An overgeneralization in some code I'm writing revealed some differing behavior between the Python sqlite bindings on Windows and on MacOS X Snow Leopard. The issue relates to the maximum permitted number of query parameters. sqlite has a hard limit on the number of query parameters, of 999. This is set in headers which are not part of the public header file, and the runtime gives you the ability to lower the limit, but not exceed the hard limit. The maximum value in the Python sqlite3 module on Snow Leopard (and Lion as well, apparently) is not 999, but 500,000, and I can't figure out for the life of me how this is possible. I considered the possibility that the Python wrapper is doing the parameter substitution itself and passing full statements to sqlite3; but as far as I can tell from the source code, that's not happening. I also considered the possibility that the implementation of sqlite3 on Snow Leopard was compiled from slightly different source code; but it turns out that Apple publishes its open-source modifications (see http://opensource.apple.com/source/SQLite/SQLite-74.11/) and the parameter limit in their source is identical to the default. Does anyone have any ideas about how this might be happening? As an added bonus, if any of the sqlite developers are listening, it would be excellent if these max limits were programmatically inspectable (e.g., sqlite3_get_hard_parameter_limit() or something like that). Thanks in advance - Sam Bayer The MITRE Corporation s...@mitre.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query by License -Consulta por Licencia
Mr. Representative of SQLite: Hello, my name is Guillermo G. Soland, I live in the city of Villa Constitución, Santa Fe, Argentina, I graduated as a Computer Systems Analyst, but now I do not practice my profession for profit because my job is totally alien to what I studied , he wanted to see if I am allowed to include a file SQLite database in an application that I developed, and if so if I'm allowed to include with the application to apply for registration under any license, either GPL or Copyright (preferably the latter). Motivates this reason that I like to develop applications (as amateur) to users or public organizations, usually non profit with the sale of software (do not sell, are free), but I would ever write a history that I have I performed. From already thank you very much. Signed: Guillermo G. Soland (Texto Original en Español) Sr. Representante de SQLite: Hola, me llamo Guillermo G. Soland, vivo en la ciudad de Villa Constitución, provincia de Santa Fe, Argentina, soy graduado como Analista en Sistemas de Computación, pero actualmente no ejerzo mi profesión con fines de lucro dado que mi trabajo consiste en algo totalmente ajeno a lo que he estudiado; le quería consultar si me encuentro autorizado a incluir un archivo de Base de Datos SQLite en una aplicación que yo desarrolle, y en su caso si me encuentro autorizado a incluirla con la aplicación para solicitar la registración bajo cualquier licencia, ya sea GPL o Copyright (de preferencia esta última) . Motiva la presente la razón que me gusta desarrollar aplicaciones (en forma amateur) para usuarios u organizaciones públicas, normalmente no lucro con venta de software (no las vendo, son gratuitas), pero me gustaría alguna vez dejar un antecedente de que yo las he realizado. Desde ya muchas gracias. Firmado: Guillermo G. Soland ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accented chars in Android embeded Sqlite.
Hi everyone! Im Brazilian, also, Android Dev. Im in a struggle with Sqlite and accented chars such as á or é. What Im trying to do is a select with where accent insensitive. For months I have having research over and over the internet to accomplish that, but, unfortunately unsuccessful. What I need is something like this: select * from table where column like a% and result be: agora água através and so on Its possible? Or better to commit suicide ;-) Thanks in advance Youre all aces in my book. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] import problem
I have problem when import with versione superior to 3071000. No problem with 3071000. ..\sqlite3 -echo aa_VUOTO.db 0aa.script .separator \t .import aa.txt aa Error: aa line 1744: expected 5 columns of data but found 2 .import aa aa Error: aa.txt line 82886: expected 9 columns of data but found 5 .exit -- Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f Sponsor: Fino al 2 Giugno una settimana Euro 310 nel Family Hotel King di Milano Marittima. Speciale settimana corta con arrivo la domenica e partenza al sabato Euro 270. Pensione completa, bevande ai pasti, terzo letto gratis, quarto scontato 50%, servizio spiaggia Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12340d=16-5 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite max query parameters differs on Snow Leopard?
On Tue, May 15, 2012 at 4:27 PM, Samuel Bayer s...@mitre.org wrote: All - I posted this query on stackoverflow, but I'm probably more likely to get an answer here. http://stackoverflow.com/**questions/10332668/sqlite-max-** query-parameters-differs-on-**snow-leopardhttp://stackoverflow.com/questions/10332668/sqlite-max-query-parameters-differs-on-snow-leopard An overgeneralization in some code I'm writing revealed some differing behavior between the Python sqlite bindings on Windows and on MacOS X Snow Leopard. The issue relates to the maximum permitted number of query parameters. sqlite has a hard limit on the number of query parameters, of 999. This is set in headers which are not part of the public header file, and the runtime gives you the ability to lower the limit, but not exceed the hard limit. The maximum value in the Python sqlite3 module on Snow Leopard (and Lion as well, apparently) is not 999, but 500,000, and I can't figure out for the life of me how this is possible. The upper limit on the maximum number of query parameters is a compile-time parameter that defaults to 999. Apple raises the limit to 500,000 in their builds of SQLite. SQLite allocates an array to hold query parameters. The size of the array is the size of the largest parameter. So you really don't want to be using ?50 as that requires a large allocation, even if it is the only query parameter used. The engineers at Apple are aware of this issue but still choose to increase the limit to 50 since apparently there exist legacy applications on their platform that require this large limit. I considered the possibility that the Python wrapper is doing the parameter substitution itself and passing full statements to sqlite3; but as far as I can tell from the source code, that's not happening. I also considered the possibility that the implementation of sqlite3 on Snow Leopard was compiled from slightly different source code; but it turns out that Apple publishes its open-source modifications (see http://opensource.apple.com/**source/SQLite/SQLite-74.11/http://opensource.apple.com/source/SQLite/SQLite-74.11/) and the parameter limit in their source is identical to the default. Does anyone have any ideas about how this might be happening? As an added bonus, if any of the sqlite developers are listening, it would be excellent if these max limits were programmatically inspectable (e.g., sqlite3_get_hard_parameter_**limit() or something like that). To find the upper bound use: sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, 0x7fff); int upperBound = sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, -1); Thanks in advance - Sam Bayer The MITRE Corporation s...@mitre.org __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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
Re: [sqlite] Accented chars in Android embeded Sqlite.
On Tue, May 15, 2012 at 9:23 PM, Yahoo bossco...@yahoo.com.br wrote:
Hi everyone!
I’m Brazilian, also, Android Dev.
I’m in a struggle with Sqlite and accented chars such as ‘á’ or ‘é’.
What I’m trying to do is a select with where accent insensitive.
For months I have having research over and over the internet to accomplish
that, but, unfortunately unsuccessful.
What I need is something like this:
select * from table where column like ‘a%’
and result be:
agora
água
através
and so on
There is a file in the latest source tree named test_spellfix1.c which
includes an application-defined SQL function named
spellfix1_translit(X). See
http://www.sqlite.org/src/artifact/495535f3eb57a?ln=1152-1171 for the
implementation. The spellfix1_translit(X) function attempts to convert its
input string X into a pure ASCII transliteration. In other words, it
removes the accented characters, replacing them with ASCII equivalents.
Example:
SELECT spellfix1_translit('água'); -- returns agua
You could, perhaps, add such a function to your application, then use it to
accomplish what you describe above:
SELECT * FROM table WHERE spellfix1_tanslit(column) LIKE 'a%';
Note that spellfix1 is not currently a part of any build and is
unsupported. But you can use it as a guide to your own implementation.
It’s possible? Or better to commit suicide ;-)
Thanks in advance
You’re all aces in my book.
___
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
Re: [sqlite] Escape table and column names
At 13:55 16/05/2012, you wrote: ´¯¯¯ Those all work. You are getting an error because you didn't supply any column in the table `--- Oops, that's what happens when you try doing too many things at the same time. Nonetheless, SQLite version 3.7.11 2012-03-20 11:35:50 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table [foo [[bar]] ?] (a); Error: unrecognized token: ] It would be logical to have ]] represent a single ] (standard escape by doubling), while [[ would be left as [[ since there's ne need to escape the opening bracket [. But yes, this one works: sqlite create table `foo ``bar`` ?` (a); sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query by License -Consulta por Licencia
2012/5/16 Guillermo Soland ggsol...@gmail.com: Mr. Representative of SQLite: Hello, my name is Guillermo G. Soland, I live in the city of Villa Constitución, Santa Fe, Argentina, I graduated as a Computer Systems Analyst, but now I do not practice my profession for profit because my job is totally alien to what I studied , he wanted to see if I am allowed to include a file SQLite database in an application that I developed, and if so if I'm allowed to include with the application to apply for registration under any license, either GPL or Copyright (preferably the latter). http://www.sqlite.org/copyright.html Motivates this reason that I like to develop applications (as amateur) to users or public organizations, usually non profit with the sale of software (do not sell, are free), but I would ever write a history that I have I performed. From already thank you very much. Signed: Guillermo G. Soland Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] NULL sqlite3_stmt in function calls
On Wed, May 16, 2012 at 7:16 AM, Baruch Burstein bmburst...@gmail.comwrote: Are all API functions that accept an sqlite3_stmt* safe (=NO-OPs) to call with a NULL pointer? I mean things like bind, column, etc. No. sqlite3_finalize() is, but most others are not. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ 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
Re: [sqlite] import problem
Donald Griggs ha scritto quanto segue il 16/05/2012 13.24 Hello, Spora, Regarding: *I have problem when import with versione superior to 3071000. No problem with 3071000.* ..\sqlite3 -echo aa_VUOTO.db 0aa.script .separator \t .import aa.txt aa Error: aa line 1744: expected 5 columns of data but found 2 .import aa aa Error: aa.txt line 82886: expected 9 columns of data but found 5 .exit == What did you find when you looked closely at lines 1744 and 82886? 1744: i have the last line as the previous. I tryed with and without the carriage return but i have this error. 82886: i have many unclosed. do it is possible to bypass the congruity check and import as in previous version in special cases? Possibly, you might find some unescaped double quote characters. My email to the list from 6 days ago describes improvements checked in on 16 January that may be of help. If this is not relevant, you might want to post a copy of the problem lines to this list (denoting the tab characters and disguising anything private, obviously.) A lot of strange things call themselves csv, but the change attempts to make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180. http://tools.ietf.org/html/rfc4180 http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization In particular, during CSV mode import: -- Allow any field to be surrounded by double quotecharacters without those characters being considered part of the field data. -- Allow fields to contain embedded commas (or other separators) when the field is surrounded by double quotecharacters. -- Allow fields to span multiple lines if they are surrounded by double quote characters. -- Allow the double quote character to be escaped by having two adjacent double quote characters. (But note that a field consisting solely of two double quote characters still represents an empty string field.) -- On output in CSV mode, surround text fields with double quotes when needed. See check-in [93aa17d866] http://www.sqlite.org/src/info/93aa17d866 (By the way, I believe the sqlite3 command line utility (CLI) was intended to be more of a debug tool than a production component -- but it surely is useful!) For an example of CSV import, if I have file MyStuff.csv whose data is shown below between the barred lines below (words in square brackets [] are just my comments and were not present in the import file): == 1,cat 2,rat[quotes are optional unless separator(s) embedded] 3 ,grey fox [extra whitespace will be handled differently when affinity is numeric] 4, spacedog[There's a space before and after spacedog -- trust me] 5,o'possum 6,big, bad, wolf 7,two-lined [Fields can span lines] zebra 8, [Second field empty. (Maybe I forgot to type Missing lynx)] 9,imperial (laughing) loon == Now I create a test database. C:\utilsqlite3 test.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter .help for instructions Enter SQL statements terminated with a ; sqlite /* Define a simple table t, comprised of an integer column and a text column */ sqlite Create table t ( id integer, animal); sqlite /* import the data above using csv mode */ sqlite .mode csv sqlite .import MyStuff.csv t sqlite /* Show the table in CSV mode sqlite select * from t; 1,cat 2,rat 3,grey fox 4, spacedog 5,o'possum 6,big, bad, wolf 7,two-lined zebra 8, 9,imperial (laughing) loon sqlite sqlite sqlite sqlite /* Try changing the separator and show it again in LIST mode */ sqlite .separator | sqlite .mode list sqlite select * from t; 1|cat 2|rat 3|grey fox 4| spacedog 5|o'possum 6|big, bad, wolf 7|two-lined zebra 8| 9|imperial (laughing) loon sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f Sponsor: Speciale ponte del 2 Giugno a Cesenatico all'Hotel Gallia: Pacchetto 2 giorni: Euro 154; Pacchetto 3 giorni: Euro 231. Pensione completa, servizio spiaggia, bevande ai pasti Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12343d=16-5 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you access a specific row of a resultset?
Goatjugsoup goatjugs...@hotmail.com wrote: Hi, I want to know how to go about getting a specific row from a resultset (or if there is a different way to do it Im all for that too) That rather depends on what it is. What's the point of the exercise? Are you looking to implement something like this, by any chance: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
Did you check out http://www.sqlite.org/inmemorydb.html Could you use an in-memory db to act as a db for a save point? When you are ready to commit, do so from 1 in memory db, while accumulating into another in preparation for that save point. Adam On Tue, May 15, 2012 at 1:55 PM, Charles Samuels char...@cariden.comwrote: I'm using sqlite in addition to another database (otherdb) storing data in a specific manner. I'm trying to keep atomicity of my disk commits. It can take several minutes for otherdb to commit, and while it commits it can already start accumulating data for a future transaction. Some of the data coming into this application also goes into the sqlite database. But I'd like to keep what's on the oxide between sqlite and otherdb consistent with eachother. Let's accept that otherdb At some point, we get a checkpoint; at this instant, what is in otherdb and what is in sqlite is what we want committed to sqlite, if either of them fails, we can rollback both of them and both databases return to a consistent state of a previous checkpoint. The problem is that in the time between checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving. The question here is: where can I put that more data so that it won't be part of checkpoint 1, but is still accessable by sqlite select statements? (Accept that otherdb allows asychronous commits such that I can add more data to it that doesn't wind up on disk). There's a few possibilities with some serious disadvantages: * When otherdb completes its checkpoint, I commit sqlite; until otherdb and sqlite finish their commits, any data going into sqlite instead goes into a mirror sqlite that I can do queries against meanwhile (but then I have to replay *all* of those modifications against the primary sqlite). This can cost huge amounts of memory because the sqlite database can get big: 3GiB or more. It's also slow because all of a sudden I have to do a whole bunch of sqlite statements. It's even slower because now any update I do *normally* has to be cloned. * I could write a virtual filesystem layer for sqlite that somehow accumulates changes that I can merge in with insert statements. So it's like the previous solution but I use some arm waving in combination with smoke and mirrors to at least not make me have two total copies of the database. The problem with this one is I don't know how to do it, and even if I did, I wouldn't know how reliable it was. * If sqlite had a commit transaction to savepoint X, then sqlite commits to the oxide everything up to a specific savepoint, keeping the savepoints after those committed still as active and uncommitted savepoints. The only disadvantage I can think of to this is that sqlite has no such feature. So how could I do this? Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you access a specific row of a resultset?
The other thing you are not thinking about is that you never know the order of data in a set. You may think that you want the 5th record that was ever inserted into the table, but you have no guarantee that a select statement will return records in the order in which they were inserted. The 5th oldest record could come up first in the result set, or last, or anywhere else. So, you either have to walk through the set from the beginning, checking every record to see if it's the one you want, or you're going to have to build a more precise select statement. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Wednesday, May 16, 2012 10:00:37 f.h. Adam DeVita wrote: Did you check out http://www.sqlite.org/inmemorydb.html Could you use an in-memory db to act as a db for a save point? Yes, but this is incredibly hard: I have to maintain the schema twice, I can't allow the user to enter their own SQL statements, I still have to copy all the accumulated stuff over, and schema changes would be very difficult to handle. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query by License -Consulta por Licencia
Guillermo: Due to generous copyright conditions of its author DRH, you can use SQLite in any application you want and for any purpose. HTH -- -- Adolfo J. Millan Zator Systems. Mensaje original De: Guillermo Soland ggsol...@gmail.com Para: sqlite-users@sqlite.org Fecha: Wed, 16 May 2012 14:07:55 +0200 Asunto: [sqlite] Query by License -Consulta por Licencia Mr. Representative of SQLite: Hello, my name is Guillermo G. Soland, I live in the city of Villa Constituci=F3n, Santa Fe, Argentina, I graduated as a Computer Systems Anal= yst, but now I do not practice my profession for profit because my job is totally alien to what I studied , he wanted to see if I am allowed to include a file SQLite database in an application that I developed, and if so if I'm allowed to include with the application to apply for registration under any license, either GPL or Copyright (preferably the latter). Motivates this reason that I like to develop applications (as amateur) to users or public organizations, usually non profit with the sale of software (do not sell, are free), but I would ever write a history that I have I performed. From already thank you very much. Signed: Guillermo G. Soland ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On 5/15/2012 1:55 PM, Charles Samuels wrote: The question here is: where can I put that more data so that it won't be part of checkpoint 1, but is still accessable by sqlite select statements? (Accept that otherdb allows asychronous commits such that I can add more data to it that doesn't wind up on disk). Well, SQLite doesn't. Basically, you want SQLite to maintain multiple not-yet-committed versions of the same record. I don't believe there's any way to get it to do that automatically - you'll have to come up with an explicit versioning scheme (like your idea of creating a temporary shadow database). * If sqlite had a commit transaction to savepoint X, then sqlite commits to the oxide everything up to a specific savepoint, keeping the savepoints after those committed still as active and uncommitted savepoints. The only disadvantage I can think of to this is that sqlite has no such feature. Oh yes, I thought of something... but unfortunately, it rather involved being on the other side of this airtight hatchway. -- The Hitchhiker's Guide to the Galaxy. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Wednesday, May 16, 2012 11:33:02 f.h. Igor Tandetnik wrote: Well, SQLite doesn't. Basically, you want SQLite to maintain multiple not-yet-committed versions of the same record. Well, yes, but it already does; you can rollback a savepoint. If I could rollback a savepoint and then unrollback it after doing an commit, that'd be the equivalent, if I could trick the paging module. I don't believe there's any way to get it to do that automatically - you'll have to come up with an explicit versioning scheme (like your idea of creating a temporary shadow database). I'm trying that now, it involves temporary tables and views; but the bookkeeping for it is looking to be increasingly difficult and it is also going to make my own code unmaintainable. I'm willing to pay the sqlite team to add this feature to a future of sqlite. Charles p.s. please excuse my Subject line. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Tue, May 15, 2012 at 1:55 PM, Charles Samuels char...@cariden.comwrote: At some point, we get a checkpoint; at this instant, what is in otherdb and what is in sqlite is what we want committed to sqlite, if either of them fails, we can rollback both of them and both databases return to a consistent state of a previous checkpoint. The problem is that in the time between checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving. You will find that in almost all situations where either of them failed, whatever method you're using to protect the integrity of your data will fail too, because it will depend on the same thing. Not only that, but that your method of assuring synchrony is likely to add lots of complication which will make the whole system fail more than a simple one-SQL-engine implementation would. For data integrity of the type you describe, there's nothing much you can do short of using duplicate servers talking to RAIDs with redundancy. On 16 May 2012, at 7:40pm, Charles Samuels char...@cariden.com wrote: I'm trying that now, it involves temporary tables and views; but the bookkeeping for it is looking to be increasingly difficult and it is also going to make my own code unmaintainable. You are inventing a versioning system for database rows. This has been discussed a few times on this list, but never with any useful outcome in the form of code. There's no reason it couldn't be done, but it can turn complicated fast. Especially if you are going to deal correctly with multi-thread, multi-process or multi-user access. Two standard approaches are savepoints and an endless undo mechanism. Each have their advantages and disadvantages. The fact that there is no well-known way to do this should give you an idea how difficult or useful it will end up being. To reduce the complication you have already noted, I recommend you try to divorce the versioning system from the code of your project. Try to write yourself a database library which would be useful for many projects, rather than have any one routine which includes both knowledge about your project and knowledge about how versioning works. Another way to do it is simply to use ROLLBACK points as defined in the SQL standard and implemented by SQLite. We already know that they work correctly and there is a lot of documentation on their use and drawbacks. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Wednesday, May 16, 2012 1:28:17 e.h. Simon Slavin wrote: On Tue, May 15, 2012 at 1:55 PM, Charles Samuels char...@cariden.comwrote: At some point, we get a checkpoint; at this instant, what is in otherdb and what is in sqlite is what we want committed to sqlite, if either of them fails, we can rollback both of them and both databases return to a consistent state of a previous checkpoint. The problem is that in the time between checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving. You will find that in almost all situations where either of them failed, whatever method you're using to protect the integrity of your data will fail too, because it will depend on the same thing. Not only that, but that your method of assuring synchrony is likely to add lots of complication which will make the whole system fail more than a simple one-SQL-engine implementation would. For data integrity of the type you describe, there's nothing much you can do short of using duplicate servers talking to RAIDs with redundancy. Sorry, I misspoke here a little bit and I think it's confusing you to the actual problem I'm having. To clarify: After I do a commit to otherdb - I'm *still* able to atomically roll it back, simply because I keep history of its previous versions in the sqlite db. I have it such that if the sqlite database itself rolls back, then the data in otherdb automatically does as well (it involves lots of COW pages and such). This is a solved problem. The real problem is that while I'm waiting for otherdb to synchronize, more data is still coming in to this entire solution. Otherdb is able to take that data and put it in a future version, but sqlite can't. After otherdb synchronizes (i.e., with fsync), I can commit the sqlite db and even if otherdb has more stuff of a future version added to it, the sqlite database causes that future data to be safely ignored. However, while I can safely add to otherdb's future data, there's no place to add it into SQLite, as I still haven't committed sqlite's present version - I can't do that until otherdb reports that it's committed its entire present version. [...] To reduce the complication you have already noted, I recommend you try to divorce the versioning system from the code of your project. Try to write yourself a database library which would be useful for many projects, rather than have any one routine which includes both knowledge about your project and knowledge about how versioning works. I have already done that; I have SQL-friendly data stored in SQLite, and non- SQL data stored in otherdb, but either I can't accept new data while waiting for otherdb to do its full fsync (because I have nowhere to put the sqlite- side of things), or I need a way to have sqlite not commit the stuff from between the start of otherdb's checkpoint and the point that it finishes its fsync. Another way to do it is simply to use ROLLBACK points as defined in the SQL standard and implemented by SQLite. We already know that they work correctly and there is a lot of documentation on their use and drawbacks. If by rollback points, you mean savepoints; then they're simply not useful if they discard (as opposed to giving me back the data) the data that is being rolled-back. To keep that data, I would need a shadow-database of some sort, which would of course require me to have many many routines that include knowledge of my project and knowledge of how versioning works. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you access a specific row of a resultset?
Well, a new thought just came to me, if I use the same sql statement will the resultset be in the same order each time because if that is the case I imagine I could use a for statement and an index to move through it the way I want to with next inside of that as many times as it takes to get to where I need. -- View this message in context: http://old.nabble.com/How-do-you-access-a-specific-row-of-a-resultset--tp33856419p33861287.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] How do you access a specific row of a resultset?
if I use the same sql statement will the resultset be in the same order each time Only if you use ORDER BY clause, and the set of columns in this clause have unique values for each row, and all these values don't change between query executions. Pavel On Wed, May 16, 2012 at 6:14 PM, Goatjugsoup goatjugs...@hotmail.com wrote: Well, a new thought just came to me, if I use the same sql statement will the resultset be in the same order each time because if that is the case I imagine I could use a for statement and an index to move through it the way I want to with next inside of that as many times as it takes to get to where I need. -- View this message in context: http://old.nabble.com/How-do-you-access-a-specific-row-of-a-resultset--tp33856419p33861287.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you access a specific row of a resultset?
On 16 May 2012, at 11:14pm, Goatjugsoup goatjugs...@hotmail.com wrote: Well, a new thought just came to me, if I use the same sql statement will the resultset be in the same order each time because if that is the case I imagine I could use a for statement and an index to move through it the way I want to with next inside of that as many times as it takes to get to where I need. SQLite is free to return rows in any order which satisfies your ORDER BY clause. It is possible for the same SELECT statement, run twice in a row, with the same data in the table, to return its rows in a different order. Two ways I can think of to make this happen are to create a new index, and to do an ANALYZE, but there are probably others which I haven't thought of. If you want your rows to be returned in the same order every time, use an ORDER BY clause for your SELECT statement, and make sure the order you've asked for the rows gives every row a unique value. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to write \n\r each line in the output txt file?
when I use : .output akk.txt select * from dhq where qph0; .output stdout command to write a txt file,I found no \n\r in the each line, when I use ultraedit to open the txt file, I found end of each line is 0x0D, How to write 0x0D 0x0A each line? may be should change the sqlite3.c or shell.c? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite max query parameters differs on Snow Leopard?
sqlite has a hard limit on the number of query parameters, of 999. This is set in headers which are not part of the public header file, and the runtime gives you the ability to lower the limit, but not exceed the hard limit. The maximum value in the Python sqlite3 module on Snow Leopard (and Lion as well, apparently) is not 999, but 500,000, and I can't figure out for the life of me how this is possible. Define SQLITE_MAX_VARIABLE_NUMBER to the max you want when compiling the amalgamation code ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to write \n\r each line in the output txt file?
On Wed, May 16, 2012 at 9:30 PM, YAN HONG YE yanhong...@mpsa.com wrote: when I use : .output akk.txt select * from dhq where qph0; .output stdout command to write a txt file,I found no \n\r in the each line, when I use ultraedit to open the txt file, I found end of each line is 0x0D, How to write 0x0D 0x0A each line? may be should change the sqlite3.c or shell.c? ___ Hello, I believe you may want: select *, x'0d' from dhq where qph 0; though this will add a field separator, depending on the mode you're using. You may instead need something like (assume columns are named c1 and c2): select c1 || '|' || c2 || x'0d' from dhq where qph 0; /*A bit confusing, but the '|' is the actual separator, and the x'0d' provides the RETURN and sqlite provides the LINEFEED */ Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
