rowindex equivalent for sqlite tables?

[colinmac]

I was wondering is there an equivalent to the rowindex variable for tables rendered from sqlite query objects? I can create column links but cannot determine from which row a link was activated. EventKey$ will tell me the contents of the cell clicked, but not the row number itself.

I can get around this by reading the query object into an array and making an ordinary table from this array (and then checking rowindex on the array), but it is hardly elegent and I wondered if there was anything more direct?

thanks, Colin

[kokenge]

rowid may be what you are looking for.

www.sqlite.org/autoinc.html

use something like this to get your data and the rowid from the DB

SELECT *, rowid FROM file.....

[colinmac]

Thanks Kokenge,

rowid was not quite what I was looking for (which was more related to the displayed query result than the underlying data), but I will now use rowid it for keeping track of records.

I now realise that for my purposes I must copy the query result into a table in any case rather than rendering it directly.

Colin

[kokenge]

Colin,
Not sure this will work.
You might give the LIMIT parameter a try.

sql$ = "SELECT something FROM something JOIN stuff......"
#sql	execute(sql$)
rowindex	= 0
WHILE  #sql hasanswer()
 	rowindex = rowindex + 1
        #row = #sql #nextrow()
	....
	button  #chg, "Change", [chgRecord]
                    #chg setkey(str$(rowindex))
WEND
[chgRecord]
limit$	= " LIMIT " ; EventKey$ ; ",1"    ' limit to 1 record starting at rowindex
sql$ = "SELECT something FROM something JOIN stuff......";limit$
...

Hope this helps..
Dan

[colinmac]

Hello Dan,

thanks again for posting. I have not tried the full code, but the idea of indexed buttons gave me the clue I needed to solve my problem. I am now using list boxes to provide a scrollable display of the database query rows. Each row is identified by and index as you did for each button.

Thanks, Colin

[metro]

Hi Colin

any chance of posting a small piece of your code for the benefit of us Noobs

thanks

Laurie

[colinmac]

Of course Laurie,

I will work on it later.

Colin

[kokenge]

Not sure, but I think RBgen will do what you are trying to do.
If it works, it can save you lots of work..

Dan

[colinmac]

Hello,

I would like to respond to Metro with some example code, but this will only work in combination with a very short SQLite database file. I can see on way of attaching a file to this post. Where are files like this usually placed on this forum?

Thanks, Colin

[colinmac]

Hello

I will post my code anyhow. I have remmed out the loading of a database file, and hard coded an array to work with instead. Just copy this code into a fresh project and run it.

Like Metro, I am very much a newbie with RunBasic, although I have use Liberty Basic a few years ago. this code is very rough around the edges, but to might be of value to someone.
' scrolling a database query result

global blank$

cssid #toppane, "{ font-family: courier ; }"
cssid #authorbox, "{ font-family: courier ;  background-color: cyan ;}"
' it is important to use a monospace font for parts of the display

dummy$ = buildblank$(50)
' build a string of hard spaces for later use in formatting the list box

div toppane
' to label columns in returned dataset
print
html " <b>Author Records</b><br /><br /> "
print "     First Name          Last Name         "
end div

' sqliteconnect #records, "projects\whatever_project\authors.s3db"
' fairly standard procedure for reading a query result into an array
' there is no checking ofr existance of the sqlite file
' indexing for the array is effectively from 1 not zero - I am a programming wimp
'  query$ = "select * from authors"
'  #records execute(query$)
'  if #records hasanswer() then
'  totalrecords=#records rowcount()
'  dim authordata$(3, totalrecords)
 ' this is the actual query result array
'  dim displayarray$(totalrecords)
 ' this is the array that will fill the list box
'      for x = 1 to totalrecords
'      thisrow$ =#records nextrow$(",")
' for y = 1 to 2
'      authordata$(y,x) =word$(thisrow$ ,y,",")
'     next y
' '     next x
'   end if
'  #records disconnect()

' we have hard coded values into our table, instead of reading the database
totalrecords = 12
dim authordata$(3,totalrecords)
dim displayarray$(totalrecords)

authordata$(1,0) ="NA"   : authordata$(2,0)="NA"
authordata$(1,1) ="Henry"   : authordata$(2,1)="Fielding"
authordata$(1,2) = "Henry" : authordata$(2,2)= "James"
authordata$(1,3) =  "James": authordata$(2,3)="Joyce"
authordata$(1,4) = "Jane" : authordata$(2,4)="Austin"
authordata$(1,5) = "Alyce" : authordata$(2,5)="Watson"
authordata$(1,6) = "John" : authordata$(2,6)="Steinbeck"
authordata$(1,7) =  "Charles": authordata$(2,7)="Darwin"
authordata$(1,8) = "Carl" : authordata$(2,8)="Gundel"
authordata$(1,9) =  "Elizabeth": authordata$(2,9)="Rather"
authordata$(1,10) = "Leon": authordata$(2,10)="Brodie"
authordata$(1,11) = "Chalmond": authordata$(2,11)="Lui"


' now we build the second single dimentioned array
' that fills the list box
for x=1 to totalrecords  -1
 temp$=str$(x)
' we start by making a row index within the row that we will display
temp$=temp$+hspace$(5-len(temp$))
' we then add some hard spaces for formatting neat columns
' my experience is that ordinary spaces are condensed by the list box
temp$=temp$ + authordata$(1,x)+ hspace$(20 - len(authordata$(1,x)))
 temp$=temp$ + authordata$(2,x)+ hspace$(20 - len(authordata$(2,x)))
' these two rows add actual data
 displayarray$(x)=temp$
' now we store our completed row in the display array
 next x
' now we get the nxt row
 listbox #authorlistbox, displayarray$(),8
' link the listbox to the array
  #authorlistbox setid("authorbox")
 #authorlistbox select(1)
 button #fixdata , "print selected entry", [showselect]
' this button must be pressed to show the row we have selected
' from here do what you like with the data
' perhaps loading it into seperate text boxes for editing
html "<br />"
wait

[showselect]
 currentrecord$= #authorlistbox selection$()
' this obtains the selected row from authorlistbox, complete with our index
currentrecord = val(currentrecord$)
' this obtains the index from the front of the selected row
print "the author's first name was " ; authordata$(1,currentrecord)
print "the author's last name was " ; authordata$(2,currentrecord)
wait

function buildblank$(anumber)
' builds a string of hard spaces for formatting in the list box
temp$ = ""
for x= 1 to anumber
temp$=temp$+chr$(160)
next x
blank$=temp$
end function

function hspace$(anumber)
' returns the required number of hard spaces
hspace$ = left$(blank$, anumber)
end function





[metro]

thanks for taking the time Colin

I will spend some time digesting your code

appreciate it

regards

Laurie