Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Simon Slavin]

On 26 Dec 2012, at 4:28pm, genliu777  wrote:

> the reason that i talk about how to get ROWID of record is that i want to
> access specific record by the FASTEST way!
> 
> i read the document of sqlite again, and find if i define a field(e.g.
> social_num) with the feature of  "*integer primary key*" , i can access
> record by the value of the field in the fastest way. 
> 
> is it true!?

No.  There is no big difference in speed between accessing one field or another 
just because it's an integer primary key.  Just keep doing your SELECT commands 
for fields.  SQLite is very well written and it is always fast.

My advice to you is not to worry about speed at this point.  Just make a 
program that works.  Once you learn more about SQL you will learn about 
creating INDEXes so that each SELECT always has a good way to find the rows it 
wants.  But you don't need to worry about that here because there's already an 
INDEX that makes finding the rowid column fast.

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

Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Simon Slavin]

On 26 Dec 2012, at 1:36pm, genliu777  wrote:

> as you sayed, knowing the ROWID means i can deal with the SQL query which
> you mention to move back or move forwark through the table! but,  i  do not
> get the ROWID really yet and  further do not  know how to get the ROWID by
> any API funtion supplied by sqlite3!!!

rowid is a special column especially made by SQLite.  Suppose you define a 
table like

CREATE TABLE myTable (a INTEGER, b TEXT)

even though you did not ask for a column called rowid, SQLite will create one 
anyway.  It's a special secret column !  It will contain a numeric value which 
automatically increments for each new row: 1, 2, 3 ...

If you ask for

SELECT * FROM myTable

you will not get a value for rowid, because it's a secret column.  But if you 
explicitly ask for it:

SELECT rowid FROM myTable
SELECT b,rowid FROM myTable
SELECT rowid,* FROM myTable

then SQLite will return its value.  Try it out !

SQLite has been making this column for every table you've made, unless you 
define a column called rowid yourself.  So you can use this column on tables in 
old databases you already have.  You don't need to start a new one.

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

Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Marcus Grimm]

please be aware that you will run into quite some programming effort
if you attempt to convert the MFC CRecordSet methods like MovePrev(),
into an sqlite equivalent. In MFC they often use statements like

myTable.Open();
while( !myTable.IsEOF() )
{
  // do something ...

  myTable.MoveNext();
}

I tend to argue that it is impossible to add a MovePrev(), inside such a
loop without the need to restart the hole processing - IF you intend to
maintain the MFC style.

Some time ago I created CRecordSet wrapper for sqlite while porting an
application to use sqlite instead - but I skipped the MovePrev() method - 
Usually
they can be avoided with a little restructuring of the MFC style of
coding - you might consider this as well.

Anyway, back to your question - It is not clear what you are asking since
you already quote the relevant chapters from the sqlite doc. So the answer
is yes, the ROWID can be accessed obviously faster compared to a standard
column. But I think this is not really your problem here...

Marcus


On 26.12.2012 17:28, genliu777 wrote:

the reason that i talk about how to get ROWID of record is that i want to
access specific record by the FASTEST way!

i read the document of sqlite again, and find if i define a field(e.g.
social_num) with the feature of  "*integer primary key*" , i can access
record by the value of the field in the fastest way.

is it true!?

the orignal descripiton follows,
"Searching for a record with a specific rowid, or for all records with
rowids within a specified range is around twice as fast as a similar search
made by specifying any other PRIMARY KEY or indexed value."

"*With one exception*, if a table has a primary key that consists of a
single column, and the declared type of that column is "INTEGER" in any
mixture of upper and lower case, then the column becomes an alias for the
rowid."

"Rowid values may be modified using an UPDATE statement in the same way as
any other column value can, either using one of the built-in aliases
("rowid", "oid" or "_rowid_") or *by using an alias* created by an integer
primary key. "



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/how-to-move-around-records-in-sqlite-as-the-way-as-in-CDaoRecordset-of-MFC-tp66191p66276.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



--
Marcus Grimm
MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: +49(0)6151-95147-10
Fax: +49(0)6151-95147-20
web: www.medcom-online.de
--
MedCom slogans of the month:
"Vacation ? -- Every day at MedCom is a paid vacation!"
"Friday I have monday in my mind."
"MedCom -- Every week a vacation, every day an event, every hour a cliffhanger,
   every minute a climax."
"Damned, it's weekend again!"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[genliu777]

the reason that i talk about how to get ROWID of record is that i want to
access specific record by the FASTEST way!

i read the document of sqlite again, and find if i define a field(e.g.
social_num) with the feature of  "*integer primary key*" , i can access
record by the value of the field in the fastest way. 

is it true!?

the orignal descripiton follows,
"Searching for a record with a specific rowid, or for all records with
rowids within a specified range is around twice as fast as a similar search
made by specifying any other PRIMARY KEY or indexed value."

"*With one exception*, if a table has a primary key that consists of a
single column, and the declared type of that column is "INTEGER" in any
mixture of upper and lower case, then the column becomes an alias for the
rowid."

"Rowid values may be modified using an UPDATE statement in the same way as
any other column value can, either using one of the built-in aliases
("rowid", "oid" or "_rowid_") or *by using an alias* created by an integer
primary key. "



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/how-to-move-around-records-in-sqlite-as-the-way-as-in-CDaoRecordset-of-MFC-tp66191p66276.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 to move around records in sqlite as the way as in CDaoRecordset of MFC??

[genliu777]

thanks!!

as you sayed, knowing the ROWID means i can deal with the SQL query which
you mention to move back or move forwark through the table! but,  i  do not
get the ROWID really yet and  further do not  know how to get the ROWID by
any API funtion supplied by sqlite3!!!




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/how-to-move-around-records-in-sqlite-as-the-way-as-in-CDaoRecordset-of-MFC-tp66191p66275.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 to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Simon Slavin]

On 25 Dec 2012, at 3:22pm, genliu777  wrote:

>   // sql query: "SELECT rowid FROM myTable WHERE rowid = rowiddd".

If you already know that rowid = rowiddd then there's no point in doing the 
SELECT.  You already know the answer.

If you want to find the line before or the line after this one just use

SELECT rowid FROM myTable WHERE rowid > rowiddd ORDER BY rowid LIMIT 1
SELECT rowid FROM myTable WHERE rowid < rowiddd ORDER BY rowid DESC LIMIT 1

if you want more than one row, change the LIMIT clause.  Once you know which 
rows you're interested in, you can use something like

SELECT * FROM myTable WHERE rowid = rowiddd

to find the values you're going to want to print or show on the display.

> i am not familar with SQL query

I think this may be your problem.  You could spend a couple of hours learning 
how to use SQL just from reading random SQL tutorial web pages.  This may help 
you figure out which questions to ask or you may not need to ask any.

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

Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[genliu777]

thanks, Simon Slavin-3!

it is good idea to use " SELECT rowid FROM myTable WHERE rowid >
*[current_rowid]* ORDER BY rowid LIMIT 1 " . but the key question is about
the " current_rowid":  how to get the value of rowid as the way as
extracting other field value!!

for example, 
... ...
int rowiddd = -1; // to store rowid of the specific record
lpctstr sql = _T("select * from student"); // deal with all fields in the
talbe
sqlite3_prepare16_v2(&db, sql, -1, &stmt, Null);
rc = sqlite3_step(stemt);
while(SQLITE_ROW == rc)
{
   // get to a record in the table, maybe the first, or maybe the other
   // here get the ROWID of this record and store and mark it  in rowidd
   // move on and do sth with record
   // something happens and it is necessary to move to the record marked
by rowiddd. may be with the
   // sql query: "SELECT rowid FROM myTable WHERE rowid = rowiddd".
   // here we again get to the marked record and do sth with it. 
}
... ...

i am not familar with SQL query, so bother you to give some idea! thanks!



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/how-to-move-around-records-in-sqlite-as-the-way-as-in-CDaoRecordset-of-MFC-tp66191p66268.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 to move around records in sqlite as the way as in CDaoRecordset of MFC??

[genliu777]

thanks for consideration!!

your reply gives some kind of view of solution. but how to  get bookmark and
set bookmark of record in table when "cursor" moves to the marked record
directly from some other place in the table?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/how-to-move-around-records-in-sqlite-as-the-way-as-in-CDaoRecordset-of-MFC-tp66191p66264.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 to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Larry Brasfield]

tigeryth wrote:

i used CDaoRecordset of MFC to handle records in ACCESS, whick is convenient to 
me move around records in table and edit certain record.

according to my habit, i wish sqlite has some kind of functions like, move(int) 
to get to cerctain record, getbookmark(...) to the record marked record and 
setbookmark() to mark the record, moveprov() to move one record back and 
movenext() to the record forward, IsEOF and IsBOF to know we are not out of 
table domain. all in one word, not only move forwardly but also backwardly 
through records in table.

i know sqlite3_get_table( )  ,  sqlite3_step() function and struct sqlite3_stmt 
 archive some goal mentioned above, but not all!!
how ?


SQLite does not provide any equivalent to a backward-moving cursor.

If you insist on using the SQLite C API, (when it appears you are using 
a library offering a putatively higher level of abstraction), you will 
need to look at the 'limit' and 'offset' qualifiers for 'select' 
queries.  These can be inefficient for otherwise large datasets, so be 
careful and consider incorporating similar result subsetting criteria 
into the 'where' clause.


Now, some unsolicited advice: While MFC's database interface was useful 
in its time, that time is past except for old projects in maintenance. 
You should be using the ADO.NET SQLite adapter on the .NET platform.  (See
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki .)  I 
won't elaborate on this here, (as it is off-topic), but I doubt any sane 
developer could regret making that transition.


--
Larry Brasfield

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

Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Simon Slavin]

On 18 Dec 2012, at 3:03pm, tigeryth  wrote:

>   i use sqlite in my project. it is good because of its capacity bigger than 
> ACCESS.  i used CDaoRecordset of MFC to handle records in ACCESS, whick is 
> convenient to me move around records in table and edit certain record.
> 
> according to my habit, i wish sqlite has some kind of functions like, 
> move(int) to get to cerctain record, getbookmark(...) to the record marked 
> record and setbookmark() to mark the record, moveprov() to move one record 
> back and movenext() to the record forward, IsEOF and IsBOF to know we are not 
> out of table domain. all in one word, not only move forwardly but also 
> backwardly through records in table.
> 
> i know sqlite3_get_table( )  ,  sqlite3_step() function and struct 
> sqlite3_stmt  archive some goal mentioned above, but not all!!

Every table has a secret column which can be addressed as id or rowid.  There's 
an index on this column, so you can find values very quickly.  You can use this 
column to uniquely identify a row in the table.  Move forward by looking for 
the next bigger rowid value.  Move backwards by looking for the next smaller 
rowid value.  So you can write your own 'movenext()' code that does something 
like

SELECT rowid FROM myTable WHERE rowid > [current_rowid] ORDER BY rowid LIMIT 1

You might want to read



and get back to us if you still have questions.

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