[sqlite] Reset the cursor
Hi, All,
After executing the following:
int res = sqlite3_prepare_v2( ... stmt );
while( ; ; )
{
res = sqlite3_step( stmt );
if( res == SQLITE_ROW )
{
// process the record
}
else if( res == SQLITE_DONE )
break;
else
{
// error procressing
}
}
Now I'd like the cursor in the recordset of the "stmt" to go to the record 1
so I can process those records again.
I thought that this will be a job of sqlite_reset(), but when I called
it and started re-processing the recordset I got SQLITE_DONE on the
very first iteration.
So, how do I reset the cursor to the first record?
Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subject: Re: SQL Date Import
> SQLite doesn't have a DATE type. You can store dates in a SQLite > database as text, or integers or floating point numbers (e.g. "20180602", > a number of days, a number of seconds). But when you ask for a value, > that's what you'll get back. Any interpretation of that value as a > date is up to you or your software. > Simon. That is the real crux of the situation, I have a handle on how to display DATEs whether they are NUMERIC or TEXT. Users really gather no meaning from temporal values as numbers. The real issue I suppose after some thought is the export/import of a DATE, TIME, TIMESTAMP. Most Databases for both SQL, INSERT example, and CSV is done with TEXT. Example being standard SQL DATE, -MM-DD. I think that the case is to preserve the users data in the type they have chosen for SQL export, have to test each entry for NUMERIC or TEXT. The import of the SQL INSERT statements will return the database entries to the same data. I have no control on those since they are passed directly to SQLite. CSV export will be TEXT, since that is the most likely way a spreadsheet will recognize the data. I will have to test probably import of CSV for NUMERIC or TEXT and not just assume TEXT. A determination can be made of the typeof() for the DATE field as NUMERIC or TEXT then transition the data accordingly if need be. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subject: Re: SQL Date Import
The problem not having a DATETIME field is, however, very simple: When reading a foreign database which stores date values as a number, I have to guess on how to get back the correct date. The datatype used is irrelevant w.r.t. this issue. Unless fully qualified with convention used and possibly location on Earth, you have no way to say it's local time (requires location), Zulu time, Hebrew calendar (requires location), islamic calendar (which version?), fiscal (which version/country?), TAI, whatever. There exist a large number of conventions and variants for denoting datetime. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Subject: Re: SQL Date Import
Not even Microsoft Excel has a dedicated datetime/timestamp type. It is just a presentation layer attribute of a floating point value. Also, you get to choose the way you want calendar data to be stored. So why? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Kurz Gesendet: Samstag, 02. Juni 2018 21:56 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] Subject: Re: SQL Date Import Are there any plans to implement a DATETIME and/or TIMESTAMP field types? - Original Message - From: Simon Slavin To: SQLite mailing list Sent: Saturday, June 2, 2018, 21:04:10 Subject: [sqlite] Subject: Re: SQL Date Import On 2 Jun 2018, at 7:32pm, dmp wrote: > By the way, most databases give exactly that INSERT when dumping data > for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred > type for storage here. I think your proposed programme of experimentation is the right way to pursue this. But I wanted to save you some time. SQLite doesn't have a DATE type. You can store dates in a SQLite database as text, or integers or floating point numbers (e.g. "20180602", a number of days, a number of seconds). But when you ask for a value, that's what you'll get back. Any interpretation of that value as a date is up to you or your software. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "cursored" queries and total rows
On 2018/06/03 1:13 PM, Wout Mertens wrote: Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column values and construct a query that will get the next item in a query by augmenting the query like SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 To know how many rows there are in the query, I do SELECT COUNT(*) FROM t WHERE b=? Are there any efficiency tricks here? Is it better to run the count before the query or vice versa? I assume b is not the Primary Key, (since the use case suggests that it repeats), but hopefully it is at least an Index. - If it is not an Index, the entire system is inefficient. - If it is an Index, then it doesn't matter which comes first[*], the queries are dissimilar enough to not have differing caching advantages based on order of execution, except... - If it is an Index, /and/ the Key repeats magnificently much (Imagine adding an "Age" column to a phone-book and then filtering on Age, there will be thousands of people who are all 34, for instance) then you are better off extracting the set of records to a TEMP table and then paginating through the temp table's complete dataset and COUNT(*) its rows. This will be extremely fast, especially if the DB is otherwise quite hefty, and will allow using the new table's rowids (invisible in the query) as pagination pegs. Be sure to use a memory-oriented journal mode and cache settings for this, or if not possible, perhaps even a second attached in-memory or memory-oriented DB. [*] - The above assumes there are no FTS tables (or other special virtual tables) involved, nor any computed Keys - all of which may need more specific considerations. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "cursored" queries and total rows
Wout Mertens wrote: > To do paged queries on a query like > > SELECT colVal FROM t WHERE b=? LIMIT 10 This does not make sense without an ORDER BY. > To know how many rows there are in the query, I do > > SELECT COUNT(*) FROM t WHERE b=? > > Are there any efficiency tricks here? No. SQLite computes rows on demand, so without the COUNT(*) query, it never even goes to the last rows. > Is it better to run the count before the query or vice versa? There is unlikely to be any noticable difference. (If there might be concurrent queries that modify the data, you should use a transaction to get consistent results.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "cursored" queries and total rows
Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column values and construct a query that will get the next item in a query by augmenting the query like SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 To know how many rows there are in the query, I do SELECT COUNT(*) FROM t WHERE b=? Are there any efficiency tricks here? Is it better to run the count before the query or vice versa? Thanks, Wout. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subject: Re: SQL Date Import
On 3 Jun 2018, at 9:48am, Thomas Kurz wrote: > he problem not having a DATETIME field is, however, very simple: When reading > a foreign database which stores date values as a number, I have to guess on > how to get back the correct date. People and companies are very creative in > that regard: Is it unix-based? Is it like Excel? Is it days, minutes, > seconds, or even milliseconds? Since when? [snip] You get that from the same place you get the units for numeric units. Is "length" inches or centimetres ? Is "audit" TRUE when it has been audited or when it needs auditing ? > Import and export from/to text formats shouldn't be a problem either as other > DBSs already support DATETIME and TIMESTAMP and obviously have found a > representation of such values in SQL (it's probably defined in the SQL > standard anyway). There are indeed data types about date and time in section 4.5 of SQL95. They're a weird by modern standards but they are there. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subject: Re: SQL Date Import
> One problem with having an actual internal date format is how to dump it into > a text file or to a text interface. You end up turning it into a number or a > string anyway, so you might was well store it that way. The problem not having a DATETIME field is, however, very simple: When reading a foreign database which stores date values as a number, I have to guess on how to get back the correct date. People and companies are very creative in that regard: Is it unix-based? Is it like Excel? Is it days, minutes, seconds, or even milliseconds? Since when? Compatibility shouldn't be affected imho. You can always continue to use INTEGER or FLOAT, and databases using INTEGER or FLOAT continue to be fully compatible. But newly created databases could store date values in a properly defined way that is readable for others as well. Import and export from/to text formats shouldn't be a problem either as other DBSs already support DATETIME and TIMESTAMP and obviously have found a representation of such values in SQL (it's probably defined in the SQL standard anyway). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
