datagrid problem with total numbers of records(SOLVED)

[vedus]

i am using the bellow code to show up some data from sqlite file.
The problem i have is with datagrid form.I have 2 fields (lbl1 and lbl2) to show the data i want( is from 2 columns)
The data is about 9500 records and when i try it the LC 7.0.1 rc2 is hang. So i try the dgNumberOfRecords and GetDataForLine from the lesson example http://lessons.runrev.com/s/lessons/m/d ... ts-of-data
still LC hang when i try to show the datagrid.
IF i change the dgNumberOfRecords to 100 i have the data in the datagrid but some cells is empty(i have include the photo from datagrid)


If remove the limit from dgNumberOfRecords and put

Code: Select all

set the dgNumberOfRecords of group "DataGrid1" to sRecordCount

the datagrid is hang.
any help from experts for this problem ?

Code: Select all

global tTitle
Global mydbid
local sCursorID, sRowCursorID
local sRecordFields
local sRecordCount

on preopenCard
     send "ResetControl" to group "DataGrid1"
    uiPopulateListGroup
    ## Initialize the UI
    
    pass preopencard
end preopenCard


on closeCard
     put empty into sRecordFields
    ## Close the database connection as we don't need it any longer
    CloseDatabase
end closeCard



command uiPopulateListGroup
   ## Connect to database and get records
   CloseCursor
   
   
   OpenDatabase
   OpenMoviesCursor
   
   ## Cache number of records so we can display useful info 
   ## to the user
   put revNumberOfRecords(sCursorID) into sRecordCount
   
   ## Track time it takes
   put the seconds into theStart
   
   lock screen
   
   ## Setting the dgNumberOfRecords of a data grid turns on dynamic 
   ## record fetching. Rather than storing the data that is being displayed
   ## in an internal array the data grid fires off the GetDataForLine message
   ## in which you return the data for the appropriate line.
   set the dgNumberOfRecords of group "DataGrid1" to  50//sRecordCount

   unlock screen
end uiPopulateListGroup


## this message is sent to the Data Grid but we handle it here
## so that you can see all handlers in one spot
command GetDataForLine pLine, @pOutData
   ## Revolution 3.5 added revMoveToRecord to revDB. This makes it really
   ## easy to navigate to the proper record for pLine
   revMoveToRecord sCursorID, pLine - 1 -- 0 based record navigation
   put revDatabaseColumnNumbered(sCursorId, 1) into theRowID
   
   ## The rowID is stored in the cursor. Open another cursor that contains all fields for this record.
   --put revQueryDatabase(mydbid,"SELECT * FROM datadrinks WHERE rowid = " & theRowID)  into sRowCursorID
   
   put revQueryDatabase(mydbid,"select title,content from datadrinks where centralcat like 'Cocktails' and rowid = " & theRowID)  into sRowCursorID
   
   
   if sRowCursorID is an integer then
      ## Now convert record in the row cursor to an array for the data grid
      put ConvertCurrentRowToArray() into pOutData
      
      revCloseCursor sRowCursorID
   end if
end GetDataForLine


function ConvertCurrentRowToArray
   local theArray
   
   if sRecordFields is empty then
      ## Cache the fields in the cursor in a script local variable.
      put revDatabaseColumnNames(sRowCursorID) into sRecordFields
   end if
   
   ## Converts current record in the cursor into a single dimensional array.
   ## Note we are using the cached script local sRecordFields
   repeat for each item theField in sRecordFields
      put revDatabaseColumnNamed(sRowCursorID, theField) into theArray[theField]
   end repeat
   
   return theArray
end ConvertCurrentRowToArray


command OpenDatabase
   set the wholematches to true
   
   if mydbid is not an integer OR mydbid is not among the items of revOpenDatabases() then
      put the filename of this stack into thePath
      set the itemdelimiter to slash
      
      put "drinksfull.sqlite" into the last item of thePath
      put revOpenDatabase("sqlite",thePath,,,,) into mydbid
      
      if mydbid is not an integer then
         answer "Error connecting to the database:" && mydbid & "."
         put empty into mydbid
         exit to top
      end if
   end if
end OpenDatabase


command CloseDatabase
    try
        revCloseDatabase mydbid
    catch e
    end try
end CloseDatabase


command OpenMoviesCursor
   ## Selecting 50,000 records can take a bit of time. The method used in this 
   ## example selects the rowid of the 50,000 records and stores that in a cursor.
   ## In GetDataForLine the rowid is used to fetch the entire record. This is much faster.
 
    put revQueryDatabase(mydbid,"SELECT rowid FROM datadrinks") into sCursorID

    
    
    if sCursorID is not an integer then
        answer "Error opening cursor:" && sCursorID & "."
        exit to top
    end if
end OpenMoviesCursor


command CloseCursor
    try
        revCloseCursor sCursorID
    catch e
    end try
end CloseCursor


command uiViewRecord pID
    put "SELECT * from datadrinks WHERE ID = " & pID into theSQL
    put revDataFromQuery(tab, cr, mydbid, theSQL) into theData
    
end uiViewRecord

[Traxgeek]

Hi vedus,

Im', no expert - far from it but I do use datagrids all the time - and in quite a complex manner... So, my 2 cents worth :

[1] DGs are incredibly complex and, when broken in some way, do very bizarre things. A tip : if your DG doesn't behave the way you think it should, save your work, exit LC, re-start LC and re-load your project. Does the DG work now / does it work any differently ?
[2] Cut down your data file (make a shortened version of your sqlite DB), say 50 or 100 entries and prove the DG is functional. When your DG works the way it ought with this reduced dataset, then revert to your original, full file/dataset. If the DG works correctly with your shortened dataset and fails with the full dataset then try gradually increasing the dataset size. This should help you determine if you have a problem with LC (maybe it has a problem with the number of entries you are giving it) or you have a problem within a given range of entries (maybe one (or more) of your data entries has a data format you have not handled properly - probably because you were not expecting such an entry - in which case the issue may not be within LC at all but withing your data - which means you'll have to handle / check your data more thoroughly before parsing it to you DG).

I don't know if this will help at all. Try a reduced dataset and post your results. There are minds fare better than mine who can help and the more info you give us, the easier it is to help.
What system are you running on - Mac or Windows (version/type).
You should also try a non RC (release CANDIDATE) version of LiveCode - version 6.6.3 - I'm, not sure of the latest solid release (maybe there's one above this... - check the LC/RR site)

HTH (at least a little bit)

Trax.

[vedus]

i am on Imac i5 16gb memory and ssd HD
i am using 6.7.0 - 7.0(stable) and 7.0.1 rc2
from that i have understand is that the above code give me all the database records based on the row id right?
How is possible to filter with query and show specific records from rows? like bellow with the above code?

select title,content from datadrinks where centralcat like 'Cocktails'

the reason is because i don't want all the database records to show up but specific,and the specific records is about 9000 from 2 columns...

[Traxgeek]

Hi vedus,

I'm really not too sure how far you've got with displaying your DG. Does your DG display correctly in the first instance and then 'go wrong' when you try to update the records in it or does it not display correctly at all ?

Let me assume that it doesn't display correctly at all (and try to answer your last question) :

Code: Select all

put "SELECT * FROM MyDBName WHERE aKey = '" & someValue & "'" into sDBQuery
put revDataFromQuery (, , nDBConnectionID, sDBQuery) into sDBData
   
[b]--Error[/b]
if sDBData begins with "revdberr" then
[b]#sHandle the error ![/b]
      
[b]--Display the data[/b]
else
[b]#Handle your data[/b]

end if

The above snippet :
Line 1 : creates the query for the DB with :
SELECT everything/all records (that's the star / * ) FROM the DataBase called 'MyDBName' WHERE 'aKey' (one of the column names/titles/keys) = someValue (a specific value you are looking for)
Remember, BEFORE you can select and undertake an operation on a DB you MUST open it/connect to it with something like :

Code: Select all

put revOpenDatabase("sqlite", thePathToAndNameOfMyDataBase, , , , ) into nDBConnectionID

If your DB was found and opened successfully, then nDBConnectionID should hold a unique DB ID as a number greater than 0. You can check for this

Line 2 : physically reads the DB and puts the result of your query into the variable sDBData

Line 3 checks the result for an error and the rest of the lines either handle the error or handle the data...

I think that, generally, you've come this far.

You do confuse me a little though when you state that you have only two columns/keys in your DB (seemingly called 'title' and 'content') but then select to filter that DB with a key called 'centralcat'. The filter key MUST exist in the DB... and it looks like yours doesn't. So, in your case valid filter keys would be 'title' or 'content' - 'centralcat' would be invalid/non-existant...
On the other hand maybe you do have a third column/key, called 'centralcat', that you forgot to tell us about... or maybe this was a typo...

I still recommend, you temporarily trim your DB size to, say, 50-100 entries; it'll make your life, during this test and debug stage, a lot simpler. Revert to the full sized DB later when everything else is working or when you really need to...

OK. Onto the empty / missing data. Like I said earlier, this would either be because the data in the field for that particular record is indeed empty, or because the data in the field for that particular record is in a format you're not expecting/handling or because the original filter / method of handling the received data is incorrect... or...

Look at and try the above (check your 'Select' syntax, check any filter key is indeed an existing key in the DB and shorten your data file). If you're still having problems then post your (cut down) project here and I'll look at it when I'm back in later on this afternoon - or, I'm sure someone else will be able to in the interim...
BTW, to look at my raw SQLite file and 'play' with it outside of LC on a Mac, I often use 'SQLite DataBase Browser' - brilliant and it's free - acailable from http://sqlitebrowser.sourceforge.net )

HTH.

Trax

[vedus]

hi Traxgeek thx for the help.
Is no my first time that i use DG and the most of the times i use dblib from andre because is more simple to show some data.
now about the problem..
Database have successful connection..
Yes my database have 8 columns but in the Query i need the data from 2 columns with name (title-content)
table=datadrinks
column1=centralcat
column2=content
column 1 have the data i want to filter like the word >cocktails.If i filter this word in my database clients (sqlite pro,valentina and others) with the bellow query

Code: Select all

select  centralcat, count(*) from datadrinks where centralcat = 'Cocktails' ;

i get 7000 records,and when i do the simple steps to show the data in DG i have hang.
And now i try the method from the lesson with GetDataForLine
if i put the bellow code i get empty cells because i have put (where centralcat like 'Cocktails')

Code: Select all

put revQueryDatabase(mydbid,"select title,content from datadrinks where centralcat like 'Cocktails' and rowid = " & theRowID)  into sRowCursorID

If i remove it and put

Code: Select all

put revQueryDatabase(mydbid,"select title,content from datadrinks where rowid = " & theRowID)  into sRowCursorID

then i get all the records from database and no empty cells,but i don't have the filter word (cocktails) and i have all the fields from database columns (title,content)about 22000 records

[vedus]

ok i have find the problem was the Query in wrong line.
line i have change is

Code: Select all

put revQueryDatabase(mydbid,"SELECT rowid FROM datadrinks") into sCursorID

with this

Code: Select all

 put revQueryDatabase(mydbid,"SELECT rowid FROM datadrinks where centralcat like 'cock%' ") into sCursorID

this is code complete and working

Code: Select all

Global mydbid
local sCursorID, sRowCursorID
local sRecordFields
local sRecordCount

on preopenCard
     resetgrid
    uiPopulateListGroup
    ## Initialize the UI
    
    pass preopencard
end preopenCard


on closeCard
     put empty into sRecordFields
    ## Close the database connection as we don't need it any longer
    CloseDatabase
end closeCard



command uiPopulateListGroup
   ## Connect to database and get records
   CloseCursor
   
   
   OpenDatabase
   OpenMoviesCursor
   
   ## Cache number of records so we can display useful info 
   ## to the user
   put revNumberOfRecords(sCursorID) into sRecordCount
   
   
   ## Track time it takes
   put the seconds into theStart
   
   lock screen
   
   ## Setting the dgNumberOfRecords of a data grid turns on dynamic 
   ## record fetching. Rather than storing the data that is being displayed
   ## in an internal array the data grid fires off the GetDataForLine message
   ## in which you return the data for the appropriate line.
   set the dgNumberOfRecords of group "DataGrid1" to sRecordCount

   unlock screen
end uiPopulateListGroup


## this message is sent to the Data Grid but we handle it here
## so that you can see all handlers in one spot
command GetDataForLine pLine, @pOutData
   ## Revolution 3.5 added revMoveToRecord to revDB. This makes it really
   ## easy to navigate to the proper record for pLine
     revMoveToRecord sCursorID, pLine - 1 -- 0 based record navigation
     
  put revDatabaseColumnNumbered(sCursorId, 1) into theRowID
   
   ## The rowID is stored in the cursor. Open another cursor that contains all fields for this record.
   --put revQueryDatabase(mydbid,"SELECT * FROM datadrinks WHERE rowid = " & theRowID)  into sRowCursorID
   
   put revQueryDatabase(mydbid,"select title,content from datadrinks where rowid = " & theRowID)  into sRowCursorID
   
   
   if sRowCursorID is an integer then
      ## Now convert record in the row cursor to an array for the data grid
      put ConvertCurrentRowToArray() into pOutData
      
      revCloseCursor sRowCursorID
   end if
end GetDataForLine


function ConvertCurrentRowToArray
   local theArray
   
   if sRecordFields is empty then
      ## Cache the fields in the cursor in a script local variable.
      put revDatabaseColumnNames(sRowCursorID) into sRecordFields
   end if
   
   ## Converts current record in the cursor into a single dimensional array.
   ## Note we are using the cached script local sRecordFields
   repeat for each item theField in sRecordFields
      put revDatabaseColumnNamed(sRowCursorID, theField) into theArray[theField]
   end repeat
   
   return theArray
end ConvertCurrentRowToArray


command OpenDatabase
   set the wholematches to true
   
   if mydbid is not an integer OR mydbid is not among the items of revOpenDatabases() then
      put the filename of this stack into thePath
      set the itemdelimiter to slash
      
      put "drinksfull.sqlite" into the last item of thePath
      put revOpenDatabase("sqlite",thePath,,,,) into mydbid
      
      if mydbid is not an integer then
         answer "Error connecting to the database:" && mydbid & "."
         put empty into mydbid
         exit to top
      end if
   end if
end OpenDatabase


command CloseDatabase
    try
        revCloseDatabase mydbid
    catch e
    end try
end CloseDatabase


command OpenMoviesCursor
   ## Selecting 50,000 records can take a bit of time. The method used in this 
   ## example selects the rowid of the 50,000 records and stores that in a cursor.
   ## In GetDataForLine the rowid is used to fetch the entire record. This is much faster.
 
     //put revQueryDatabase(mydbid,"SELECT rowid FROM datadrinks") into sCursorID
     put revQueryDatabase(mydbid,"SELECT rowid FROM datadrinks where centralcat like 'cock%' ") into sCursorID
      
    
    if sCursorID is not an integer then
        answer "Error opening cursor:" && sCursorID & "."
        exit to top
    end if
end OpenMoviesCursor


command CloseCursor
    try
        revCloseCursor sCursorID
    catch e
    end try
end CloseCursor


command uiViewRecord pID
     put "SELECT * from datadrinks WHERE ID = " & pID into theSQL
     put revDataFromQuery(tab, cr, mydbid, theSQL) into theData
     
end uiViewRecord

on resetgrid
     send "ResetControl" to group "DataGrid1"
     put empty into sRecordFields
     put empty into sRowCursorID
     put empty into sCursorID
     put empty into sRecordCount
end resetgrid

[Traxgeek]

Great stuff, vedus. Glad it's fixed. Well done.