SQL Lite and QM

[ken gray]

Does QM support SQL-Lite databases in the Database objects?

[Gintaras]

Probably no.

Tomorrow will post a class for sqlite.

[Gintaras]

For QM 2.3.1 and older. It is available in QM 2.3.2 as Sqlite class.

Attachment 1:
qml file containing CSqlite class. Import it.
sqlite.dll, version 3.6.4. Move to QM folder.

The class is easy to use if you know SQL language.

-------

Attachment 2:
Using CSqlite with QM_Grid control.

-------

[Gintaras]

SQLite database management programs. Many are free. Some have visual SQL query builders.
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

[ken gray]

works awesome!!!
thanks.

[stupomer]

Hi Ken, Kehmu, TheVig, others...(of course, Gintaras!)

I am trying to get a grid control for an excel spreadsheet that I currently use as a way to keep track of interesting medical cases. I fill out new entries via a GUI-Hack of Excel in QMscript. know that there are Excel functions now in QM, but I need to save the final document as an Excel web page for the final user interface so that the user has a very streamlined sortable filterable interface. I use intrinsic Qm excel function to fill out the table but then do a GUIhack to get it to save as a the proper html file type instead of the usual xls. Anyway, I hang the html in a Toolbar Webbrowser). The downside of this approach is the html version is not editable and it is a gui-hack which I hide behind the Curtain function.

Anyway, I wrote a version of the Grid in QM_Grid which displayed nicely but is not sortable. Ideally, I would like to filter and search on it, but sorting columns is probably the most important.

I have seen some of the many shareware MySqlLite options on the link that Gintaras provided: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
For example, something like these screencaptures: http://sqlitebrowser.sourceforge.net/screenshots.html

There are so many, I am kind of intimidated to jump in. Does anyone have a good recommendation for how to go at this: ScGrid, SqlLite, something else. Preferable one with an easy interface or sample scripts I can reverse engineer to my pretty simple needs.

Thanks for any advice from anyone.

Stuart

[Gintaras]

My English is not good, sometimes don't understand.

Does anyone have a good recommendation for how to go at this: ScGrid, SqlLite, something else. Preferable one with an easy interface or sample scripts I can reverse engineer to my pretty simple needs.

Do you need example how to use SQLite with SCGrid?

[stupomer]

Sorry I didn't explain well but I really appreciate how hard you try to help everybody.
I just saw so many different applications on that website, I simply wanted to know whether anyone had a good experience with one particular grid package. For my application, the database part is not complex (just simple rows of diferent patients with each column having exam information (Name, medical record number, exam date, disease type, study type, etc). The challenge is on the presentation side - i.e. editable grid (editable, sortable, filter, search, etc).

I don't know SQl, and ScGrid looked harder to figure out than I thought though I was able to get your ScGrid example working, but I don't know how to work with ScGrid to create something more complex (sort, search, etc).

If qmGrid was sortable, I wouldn't need another software but I don't know how to do the sorting outside the grid and then re-loading the sorted data. I know some of those other packages do that within the grid so I was looking for other's experience and recommendations. I know I can learn the scripting for a new package but I spent a long time becoming productive in QM so I am nervous to spend a lot of time learning something new unless I know people had good results - especially integrating it with QM.

Thanks again for any thoughts!

Stuart

[ken gray]

I haven't used anything like that. I just wanted to hit my Ditto db via QM.

[stupomer]

Thanks...
I eventually will learn one of those grids software but hoping for something easy - a template from one of those packages that I can do QMscripting around it.
I will look through the packages options and report anything I find to the forum.
Stuart

[Gintaras]

I did't work with these, but tested some. Liked SQLite2008 Pro Enterprise Manager.

[stupomer]

Thanks, will give it a look.

Stuart

[Gintaras]

Using sqlite databases with QM_Grid control. Functions to set/get data and sort columns.

Attachment moved to 3-rd post.

[stupomer]

WoW!!!!!!
I couldn't ask for more.
Really phenomenal!!!


Thanks so much!!!

Stuart

[stupomer]

Hi Gintaras,
First, I can't thank you enough for writing the code to enable Sqlite in conjunction with QM. I like working with QM because it's native to the rest of my QM code and is quick. It has allowed me to get rid of installing Excel and then doing a very long GUI-hack behind a Curtain....I couldn't be more pleased.

My issue now is understanding how to get selected row information out of the grid and how to search to find rows with specific text in one of the fields in that row.

I saw in qmgrid this help text

Function qmgrid

Code:

Copy      Help

;C. Getting text from cells.
;Use LVM_GETITEMTEXT to get text from each cell you need. Or, use LVM_QG_GETALLCELLS to get all cells. Or use ICsv.FromQmGrid.

but I am not sure how to incorporate it.

For Select, I am trying to select some rows (using the qmgrid functions already in place), and then extract the contents of one of the columns from the selected rows and put them into an array. Once I get the selected rows out and know how to find a column item, I can do the string/array manipulation.

For Find, I am trying to code one of those "Find/Find Next functions found in many applications. I think you mentioned that it would only show (select) the row, but that's fine.

Sorry for not posting an example but if you have the time (sorry for so many questions), even a simple example based on the dlg_QM_Grid2 example would get me very far.

Thanks so much again.

Stuart

[Gintaras]

Added functions to get list view selected items and item text.
CSqlite.FromQmGrid can create temporary table from existing.
Added code that gets selected rows in grid control.
Added code that searches grid control using sqlite (SELECT).

Attachment moved to 3-rd post.

[stupomer]

Hi Gintaras,
You never cease to amaze me and this is just the functionality that I was looking for. Thank you!

I was looking for select row function - like in the LB and CB functions but it turns out, the normal acc functions work! I assume I can substitute a str for the Namewhich can be filled based on search results but I wonder if it will be a problem with duplicates. Is this a good strategy or is there a way of having direct row selection through another function?

Function Function10

Code:

Copy      Help

Acc a=acc("a" "LISTITEM" win("QM_Grid" "#32770") "QM_Grid" "" 0x1001)
a.Select(SELFLAG_TAKEFOCUS|SELFLAG_TAKESELECTION)

2.0
Acc v=acc("k" "LISTITEM" win("QM_Grid" "#32770") "QM_Grid" "" 0x1001)
;v.Select(SELFLAG_ADDSELECTION)
v.Select(SELFLAG_EXTENDSELECTION)

In case it might be useful for others, I created a function From2dimArraytoSingleColum which helps me take the output from a specified column from all the selected rows (in my case, a list of exan numbers to be entered serially into another application to create a worlist in that application).

Member function str.From2dimArrayToSingleColumn

Code:

Copy      Help

function ARRAY(str)&a

fix(0)
int c r; str s
c=1;;set to desired column (0-based)
for r 0 a.len(2)
,addline(a[1 r])

I'm sure there is a direct way to make it without going through 2 dim array but this was easy to reverse-engineer from your work!

Thanks again,

Stuart

[Gintaras]

Normally listview items in current process are selected using LVM_SETITEMSTATE message, but accessible objects also is OK (also can use a.elem to select by index). I added LvSelect function.

But I don't know is it possible to get row indices in SELECT results.

You can create table that has a column that cannot have duplicate values. The new code adds column A as PRIMARY KEY (cannot have duplicates). Also added better error handling.

Because this is SQLite topic, I used it in code, where it is rational. In your case maybe better to get all cell values into array and search. Then you will have row indices and can select items using them. Added function LvGetAll.

[stupomer]

Thanks Gintaras,
I got it so the columns are properly formatted EXCEPT for the ones that have data entered into them via the dbl.Exec method

Function CaseLogDisplay_FromEntryDialog

Code:

Copy      Help

db1.Open(CaseLogUserFile)
str+ InsertDemogs;;.format("INSERT INTO 'CaseLog' VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" FollowUp DxFindings PtName PtMRN ACCN ExamDate ExamName DC1 DC2 DC3 DC4 cb4 PathStatus PathStatus cb15 cb15)
db1.Exec(InsertDemogs)

I format the qmgrid columns in the case WM_INITDIALOG step, like this:

Function CaseLogDisplay_FromEntryDialog

Code:

Copy      Help

CSqlite- t_db
sel message
,case WM_INITDIALOG
,t_db.Open(CaseLogUserFile)        
,t_db.ToQmGrid(id(3 hDlg) "SELECT * FROM CaseLog" 0 "FU[]FINDINGS[]NAME[]MRN[]ACCN[]DATE[]EXAM[]Dz Category 1[]Dz Category 2[]Custom Tag 1[]Custom Tag 2[]Interest Type[]Path Pending?[]Path Dx[]SharedFrom[]SharedWith" "50[]200[]150[]75[]75[]100[]125[]125[]125[]125[]125[]125[]85[]150[]150[]150")
,int+ g=id(3 hDlg)
,SendMessage g LVM_QG_SETCOLUMNTYPE 0 QG_CHECK
,SendMessage g LVM_QG_SETCOLUMNTYPE 1 QG_EDIT|QG_EDIT_MULTILINE
,SendMessage g LVM_QG_SETCOLUMNTYPE 6 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 7 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 8 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 9 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 10 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 11 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 12 QG_CHECK    
,case WM_DESTROY
,case WM_COMMAND goto messages2
,case WM_NOTIFY goto messages3
ret

Am I doing the formatting in the wrong place? It's strange that everything tht already has something entered into it, doesn't respond to the formatting (acts like text edit only i.e. gridNotify2 just registeres begin and end edits).
But fields in blank rows, do respond (i.e. trigger to gridNotify2 properly)

Any ideas where I might be messing up the code?

Thanks, Stuart

[Gintaras]

Probably because grid initialization order is incorrect.

Mut be: 1. add columns. 2 set column types. 3 add data.
Is: 1. add columns. 2. add data. 3. set column types.

Columns cannot be added in ToQmGrid if you want to set column types. I'll create correct functions.

[stupomer]

Thanks so much!!! I look forward to that functionality.

STuart

[Gintaras]

Updated. Look in 3-rd post.

Changed: dlg_QM_Grid2, CSqlite.ToQmGrid, LvAddCol2.
Added: QgAddColumns.

Now CSqlite.ToQmGrid cannot add columns. At first call QgAddColumns, then add data.

Fixed CSqlite.ToQmGrid bug when it fails if table is empty.

[stupomer]

You are the best...as always!!!

Stuart

[stupomer]

Just to let you know I got everything working properly with your new update from the previous post - all the column type designations work correctly from QgAddColumns and I even have CB lists filling from different nodes of an XML file depending on the user. Very cool.

The only thing I can't seem to get working is the LVSelect function - it doesn't seem to select any row on the screen. My goal eventually is to use the LVGetAll, then parse the 2dim array for the row index of a text search query, then multi-select those results. Alternative choice for the user is to populate a temporary qmgrid with just the row indices from the search query (which then can be saved as unique new sqlite db's. I geuss this is wht you mean by TempTable in CSqlite.TempTable?
Anyway, once I get the select part worked out and write the code for those functionalities, I will post my examples for the forum.

Anyways, phenomenal work - thanks so much again.

Stuart

[Gintaras]

You don't see what you select because grid control is not focused. It loses focus when you click a button.

Testing:
Add button with id 12.
Add this code:
Function dlg_QM_Grid2

Code:

Copy      Help

,
,case 12 ;;Select 2 rows
,SetFocus id(3 hDlg)
,LvSelect id(3 hDlg) 0 2|4 ;;sel first row
,LvSelect id(3 hDlg) 1 1 ;;and next row

To see listview selection when it is not focused, add LVS_SHOWSELALWAYS style.

3 QM_Grid 0x54210009 0x200 0 0 232 140 ""

[stupomer]

Hi Gintaras,
I said I would post the search query addition to dlg_QM_Grid2
This seems to work really well-

Function
Trigger     

Code:

Copy      Help

,case 9;; Selct Results of FIND query
,,ARRAY(str) WholeGrid
,,hlv=id(3 hDlg)
,,LvSelect hlv -1;;deselect previously selected items
,,LvGetAll hlv WholeGrid
,,str Query.getwintext((id(11 hDlg)));;a text input box in the dialog
,,str RxSearchTarget
,,int SearchQueryRowMatchesCounter = 0
,,ARRAY(int) SearchQueryRowMatches.create(100);;maximum 100 results
,,int i j
,,for i 0 WholeGrid.len(2) ;;enumerate dimensions  FOR EACH ROW
,,,for j 0 WholeGrid.len(1) ;;enumerate dimension FOR EACH COLUMN
,,,,RxSearchTarget = WholeGrid[j i]
,,,,if(!empty(RxSearchTarget))
,,,,,Query.format("(?i)%s" Query);;makes search query case insensitive
,,,,,if(findrx(RxSearchTarget Query)>-1);; a search hit
,,,,,,SearchQueryRowMatches[SearchQueryRowMatchesCounter] = i;; place the row number of the hit into the cumulative hit array depository
,,,,,,SearchQueryRowMatchesCounter=SearchQueryRowMatchesCounter+1
,,int SearchQueryRowCounter         
,,for SearchQueryRowCounter 0 SearchQueryRowMatches.len    
,,,if SearchQueryRowMatches[SearchQueryRowCounter] > 0
,,,,hlv=id(3 hDlg)
,,,,SetFocus hlv
,,,,LvSelect hlv SearchQueryRowMatches[SearchQueryRowCounter] 1;; don't deselect previous
,,SearchQueryRowMatches=0;;reset
,,WholeGrid=0;;reset

This selects the rows with a match. I am using it on a grid with 15 columns and currently ~50 rows (patients) but I expect there eventually to be 100's (maybe 1000's if I combine many users lists).

I was trying to populate a new qmgrid with the selected results. I know there is a LvGetSelectedItems function but is there a way to populate a qmgrid with the 2 dimentional array easily. I could make a csv first by parsing out the 2dim array and then using ICsv to qmgrid but thought there might be an easier way. If you could just point me in the right direction.
Thanks so much - qmrid + sqlite is awesome....
Stuart

[Gintaras]

Can use 3 other ways.

1. sqlite. Create temporary table (CQlite.TempTable), add rows (CQlite.Exec), put into grid (CQlite.ToQmGrid). Not the most efficient way, unless you already have sqlite table with query results.

2. LVM_QG_SETALLCELLS message. Need to format multistring.

3. LVM_INSERTITEM and LVM_SETITEMTEXT messages. There is a private function TO_LvAdd in System that simplifies it. Copy it. If needed, add more arguments to support more columns.

[stupomer]

Hi Gintaras,

I have added Find and Filter functions to dlg_QM_Grid2 --> now called dlg_QM_Grid2_Find_Filter.

Thanks so much for all your help in getting me here and the new version of QM looks great!
Stuart



Function dlg_QM_Grid2_Find_Filter

Code:

Copy      Help

\Dialog_Editor
function# hDlg message wParam lParam
if(hDlg) goto messages

#compile "qmgrid"
#compile CSqlite

;create database for testing. Normally you would create it in a database management program or in another macro.
if(dir("$desktop$\test80.db3")) goto g1
CSqlite db1.Open("$desktop$\test80.db3")
;db1.Exec("DROP TABLE table1"); err
;db1.Exec("CREATE TABLE IF NOT EXISTS table1 (A,B,C,D)")
db1.Exec("CREATE TABLE IF NOT EXISTS table1 (A PRIMARY KEY, B,C,D)")
db1.Exec("INSERT INTO table1 VALUES ('a','z[]x','Yes',3)")
db1.Exec("INSERT INTO table1 VALUES ('z','a',null,1)")
db1.Exec("INSERT INTO table1 VALUES ('k','k','Yes',8)")
db1.Close
;g1

if(!ShowDialog("dlg_QM_Grid2_Find_Filter" &dlg_QM_Grid2_Find_Filter 0)) ret

;BEGIN DIALOG
;0 "" 0x90C80A44 0x100 0 0 231 228 "QM_Grid"
;3 QM_Grid 0x54210009 0x200 0 0 232 140 ""
;12 Button 0x54032000 0x0 2 142 52 14 "Select 2 rows"
;5 Button 0x54032000 0x0 56 142 52 14 "Get Selected"
;13 Button 0x54032000 0x0 182 142 48 14 "Focus grid"
;6 Button 0x54032000 0x0 2 158 52 14 "Find rows"
;7 Static 0x54000000 0x0 56 160 54 12 "where in column"
;8 Edit 0x54030080 0x200 112 158 26 14 ""
;9 Static 0x54000000 0x0 140 160 20 12 "cell is"
;10 Edit 0x54030080 0x200 162 158 68 14 ""
;1 Button 0x54030001 0x0 82 184 48 14 "OK"
;4 Button 0x54032000 0x0 132 184 48 14 "Apply"
;2 Button 0x54030000 0x0 182 184 48 14 "Cancel"
;14 Button 0x54032000 0x0 0 202 40 20 "Find"
;15 Edit 0x54030080 0x200 44 202 54 20 ""
;16 Button 0x54032000 0x0 100 202 64 20 "Display Only Selected in Table"
;17 Button 0x54032000 0x0 166 202 64 20 "Return to Original"
;11 Static 0x54000010 0x20000 2 178 220 1 ""
;END DIALOG
;DIALOG EDITOR: "" 0x2030002 "" "" ""

ret
;messages
CSqlite- t_db
sel message
,case WM_INITDIALOG
,QgAddColumns id(3 hDlg) "Edit[]Multiline[]Check[]Combo" "80[]80[]80[]80" "0[]8[]2[]1"
,t_db.Open("$desktop$\test80.db3")
,t_db.ToQmGrid(id(3 hDlg) "SELECT * FROM table1")
,
,case WM_DESTROY
,case WM_COMMAND goto messages2
,case WM_NOTIFY goto messages3
ret
;messages2
str ss ExecLine SelectedResultsTable
ARRAY(str) ar
int c r hgrid=id(3 hDlg)
err-
sel wParam
,case [IDOK,4]
,t_db.FromQmGrid(id(3 hDlg) "table1")
,case IDCANCEL
,
,case 13 ;;Focus grid
,SetFocus hgrid
,
,case 12 ;;Select 2 rows
,SetFocus hgrid
,LvSelect hgrid 0 2|4 ;;sel first row
,LvSelect hgrid 1 1 ;;and next row
,
,case 5 ;;Get selected
,ARRAY(int) a; if(!LvGetSelectedItems(hgrid a)) ret ;;get selected indices
,ar.create(LvGetColumnCount(hgrid) a.len)
,for(r 0 ar.len(2)) for(c 0 ar.len(1)) LvGetItemText(hgrid a[r] c ar[c r])
,ss.From2dimArray(ar)
,mes ss
,
,case 6 ;;Find rows
,t_db.FromQmGrid(hgrid "temp" 0 "table1") ;;get all into temporary table
,str s1.getwintext(id(8 hDlg)) s2.getwintext(id(10 hDlg))
,_s.format("SELECT * FROM temp WHERE %s='%s' COLLATE NOCASE" s1 s2)
,t_db.Exec(_s ar)
,ss.From2dimArray(ar)
,mes ss
;
,case 14;;Find
,,ARRAY(str) WholeGrid
,,LvSelect hgrid -1;;deselect previously selected items
,,LvGetAll hgrid WholeGrid
,,str Query.getwintext((id(15 hDlg)));;a text input box in the dialog
,,str RxSearchTarget
,,int SearchQueryRowMatchesCounter = 0
,,ARRAY(int) SearchQueryRowMatches.create(100);;maximum 100 results
,,int i j Hit=0;;
,,for i 0 WholeGrid.len(2) ;;enumerate dimensions  FOR EACH ROW
,,,for j 0 WholeGrid.len(1) ;;enumerate dimension FOR EACH COLUMN
,,,,RxSearchTarget = WholeGrid[j i]
,,,,if(!empty(RxSearchTarget))                     
,,,,,Query.format("(?i)%s" Query);;makes search query case insensitive
,,,,,if(findrx(RxSearchTarget Query)>-1);; a search hit
,,,,,,Hit=1
,,,if Hit=1;;at least one hit in the row
,,,,SetFocus hgrid
,,,,LvSelect hgrid i 1;; don't deselect previous
,,,Hit=0;;reset before next row test
,,WholeGrid=0;;reset
,,
,case 16;;Display Selected    
,,t_db.FromQmGrid(id(3 hDlg) "temp2" 0 "table1") ;;get all into temporary table temp2    
,,t_db.TempTable("SelectedResultsTable" "table1")
,,hgrid=id(3 hDlg)
,,ARRAY(int) SelectedIndices; if(!LvGetSelectedItems(hgrid SelectedIndices)) ret ;;get selected indices
,,ar.create(4 SelectedIndices.len);;2 dimensions 2 elements in first dim, a.len = the number of selected items in the second
,,for(r 0 SelectedIndices.len)
,,,ExecLine = ""
,,,;out "Row: %i" r
,,,for(c 0 4)
,,,,LvGetItemText(hgrid SelectedIndices[r] c ar[c r])
,,,,;out ar[c r]
,,,,ExecLine.formata("'%s'," ar[c r])
,,,ExecLine.rtrim(",")
,,,;out ExecLine
,,,str ExecLineRowCol.format("INSERT INTO ''SelectedResultsTable'' VALUES (%s)" ExecLine)
,,,out ExecLineRowCol
,,,t_db.Exec(ExecLineRowCol)
,,t_db.ToQmGrid(hgrid "SELECT * FROM SelectedResultsTable")    
,
,case 17 ;;Return to Original
,,,t_db.ToQmGrid(hgrid "SELECT * FROM temp2" 0)
err+ mes _error.description; ret
ret 1
;messages3
NMHDR* nh=+lParam
if(nh.idFrom=3) ret DT_Ret(hDlg gridNotify2(nh))

[stupomer]

Hi,

I have successfully implemented a dialog which fills and displays a QM grid (also part of the dialog) by writing to and from a SQLite database, as above. I have implemented the search function, as in the previous post.

My question is about extending the search query and display to multiple different user's database. Currently each user has their own database to which they write to and display from, depending on their logon user id.

For searching across ALL user databases, I see a few options:

One is to serially search through each individual user's .db3 file and fill the Quick Macros Grid sequentially with those items that are marked Sharable and, of course, meet the search criteria.

The other option is to make a single mega-database that all users write-to (instead of each accessing their own individual one on the network). Thus at time of a user writing-to or calling up their own database, they will really just be seeing a filtered version of the mega-database.

A third option would be a hybrid. Keep the individuals ones but on a continual basis dump to the mega-database which would be just for mega-searching.

Obviously, the tradeoffs would be speed but I can't predict the speed hits of searches. I foresee users having in excess of 2000-3000+ entires (rows with ~15 columns of very short text entries) the total number of users might be over a 100 eventually.

I just don't have a sense yet of numbers for database dynamics.

Also not sure what the "server" side impact of multiple users writing and reading from the same network file.

Any thoughts would be great. Once this is robust, I foresee the option of adding an image column to the SQLite db3.

Apparently, this can be done using this inexpensive software: Image2db

http://www.vive.net/products/image2db.htm

Image2db is a utility for uploading large binary and text data to the database. It allows you to insert any number of files into the database. Image2db works with BLOB columns. A SQL BLOB is a built-in type that stores a Binary Large Object as a column value in a row of a database table. You can use them to store binary files such as images, pdf documents, Microsoft Word documents, AutoCAD files or, also, plain text data.

Today database driven websites became a standard and more and more webmasters are using databases to store images and other binary data. It is easy, convenient and secure way for everybody who wants to manage large amount of images and/or documents on the website.

Image2db supports Oracle, PostgreSQL, Microsoft SQL Server, Microsoft Access, MySql and Sqlite databases.

If I do this with images (expect 1-5 jpg images of ~ < 200 kb) and maybe a few journal articles pdfs (1-2 Mb average). Probably users would add images in only a small fraction of the cases (at most a 1/3), the db file could get quite large. Does this suggest that I should keep individual users db's?

Thanks for anybody's thoughts on this who has experiences with databases.
Much appreciated !!!

Stuart