Liberty BASIC Community Forum
Special Topics and Projects >> Database Applications >> Eye On SQL
http://libertybasic.conforums.com/index.cgi?board=database&action=display&num=1499378065

Eye On SQL
Post by meerkat on Jul 6th, 2017, 4:54pm

Wrote a program that helps in the maintenance of SQLite databases.
Some of the features:
Create new databases.
Load existing databases
Drop a database from the system
Execute SQL commands
Empty Tables
Create Tables
Drop Tables from a Database
Browse Tables
Delete Fields from a Table
Change Fields
Change Field properties
Add fields
Create index
Drop index
Export Schema and data. Including full data
Exports are a great way to backup databases.
Load data and Schema.
Enter SQL commands. A great way to test your commands before you add them to code..
And more....

I'm posting this a little early, because I'm out of town for a couple of weeks and thought people may need it. So expect and report bugs.

Download here http://www.kneware.com/libertyBasic/ionSQL/
There are some docs. Hopefully I can get this all done when I get back in town.

Have fun....


Just to prove the Sql command entry worked I used the following SQL command to search for required assets such as conference room or projector etc. And it did finds all the open slots for that particular asset .
The slots have to have enough time to meet your requirements. It only reports the number of open slots you request..
It uses 2 tables.. asset - what you wanna schedule, schTime - scheduled time already allocated.
I give it the following requirements:
minSlots.. the minimum slot needed in minutes
begSlotDate$.. the beginning date to search for open slots
numSlots.. Report only this many open slots.
schBy$ .. Increments in minutes to search by like 15 minutes intervals
Code:
SELECT  
s.schNum,
s.assetNum,
s.schEnd	as preEnd,
s1.schBeg	as nxtBeg,
((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) as laps,
(strftime('%w',s.schEnd))   as dowBeg,
asset.descr	as aDescr,
asset.assetType
FROM	schTime	as s
JOIN	asset
ON	asset.assetNum = s.assetNum
JOIN	schTime   as s1
ON	s1.assetNum = s.assetNum
AND	s1.schBeg > s.schEnd
AND	((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) >= "+minSlots+"
AND	s1.schBeg = (SELECT min(s2.SchBeg)
FROM	schTime   as s2
WHERE	s2.assetNum = s.assetNum
AND 	s2.schBeg > s.schEnd) "+schBy$+"
AND     s.schBeg >= "+begSlotDate$+" LIMIT "+numSlots
 

Re: Eye On SQL
Post by metro on Jul 8th, 2017, 8:07pm

Thanks for sharing Dan

If I add another field to the ionSQL.db db table then the loading of any more databases fails due to
Code:
INSERT INTO db VALUES('d','sample.db','C:\users\laurie\Application Data\Liberty BASIC v4.5.1\DATABASE\') 

having no data to insert into the new field.
the obvious answer is to not modify the main DB or I modify the SQL$

I now get fields from table before an insert, it would appear that fields with no data will still be saved (empty)
Code:
[LISTFIELDS]

    SQL$ = "Select * From "+Table$ +"  Limit 1"
    calldll #sq3, "SQ3_4_LB_GetRecordset", SQL$ as ptr,DBhnd as long,RS as     struct, ret as long
    FOR n = 0 TO RS.Cols.struct -1
    calldll #sq3, "SQ3_4_LB_GetFieldNameByIndex",n as long, ret as long
    fld$ = Winstring(ret)

    fldNames$= fldNames$;","+fld$
    NEXT n
    fldNames$ =after$( fldNames$,",")
'    print  fldNames$
    RETURN
 


Re: Eye On SQL
Post by meerkat on Jul 9th, 2017, 07:48am

Ya! I could prevevt it from being loaded. But then you could simply use the SQL command section to load it or mess with it. I will have a look at it when I get back home in a few days.

Thanks for the info
Dan
Re: Eye On SQL
Post by sarmednafi on Jul 11th, 2017, 01:29am

I can get nothing from this link:


Download here
http://www.kneware.com/libertyBasic/ionSQL/

The same problem here:

You can get lb_gen.bas here:
http//kneware.com/libertybasic/


Please help.
Regards
Re: Eye On SQL
Post by metro on Jul 11th, 2017, 02:40am

Quote:
I can get nothing from this link:


Give it a try in a couple of days, I'm guessing his server is down and he is away
Quote:
I will have a look at it when I get back home in a few days.

Re: Eye On SQL
Post by sarmednafi on Jul 11th, 2017, 05:54am

I see,
Thanks Metro.
Re: Eye On SQL
Post by meerkat on Jul 11th, 2017, 6:01pm

Server is up!
You know how it works. The server never ever goes down when you are around. But leave town - and it's going down.

Anyone know how to do dynamic page layouts.
When you list a table I have buttons to [A]dd [C]hange [D]elete and [S]ee. These do not work because each table is different and I need to layout the detail page for each table. So it has to be done on the fly. I'm sure there is a simple way. I've even looked at a HTML layout with a JavaScript interface to LB. But LB has got to have a simple way somewhere that I've missed.

Thanks for the help.
Dan
Re: Eye On SQL
Post by metro on Jul 11th, 2017, 7:21pm

Sorry Dan, its not what you've asked for but...
I envisaged something like this, treeview outside my skill set ATM
(E)dit (A)dd or (C)hange via dialog_modal double click ListView to select the row

User Image
full size screen shot here
https://www.dropbox.com/s/s963epvuam3719l/Screenshot%20from%202017-07-12%2007-58-26.png?dl=0
code to get the idea
part1
Code:
   'set some globals
    GlOBAL hwndLV,columnIndex,maxrow
    Global   LVS.REPORT, LVS.NOSORTHEADER, LVS.SINGLESEL,_
        LVM.INSERTCOLUMN, LVCF.WIDTH, LVM.INSERTITEM,_
        LVCF.TEXT, LVM.SETITEM, LVIF.TEXT ,LVS.SHOWSELALWAYS,LVM.DELETEITEM
    
    'set global values
    'constants
    LVS.NOSORTHEADER = 32768
    LVS.REPORT = 1
    LVS.SINGLESEL = 4
    LVS.SHOWSELALWAYS = 8
    LVS.SORTASCENDING = 16
    LVS.SORTDESCENDING = 32
    LVS.NOLABELWRAP = 128
    LVS.AUTOARRANGE = 256
    LVS.NOSCROLL = 8192
    LVS.ALIGNTOP = 0
    LVS.ALIGNLEFT = 2048
    LVS.NOCOLUMNHEADER = 16384
    LVIF.TEXT = 1
    LVIF.STATE = 8
    LVIS.UNSELECTED = 0
    LVIS.FOCUSED = 1
    LVIS.SELECTED = 2
    LVM.FIRST = 4096
    LVM.SETITEM = 4102
    LVM.INSERTITEM = 4103
    LVM.INSERTCOLUMN = 4123
    LVM.GETITEMCOUNT = 4100
    LVM.GETITEMA = 4101
    LVM.GETITEMTEXTA = 4141
    LVM.GETITEMSTATE = 4138
    LVM.SETITEMSTATE = 4139
    LVM.DELETEITEM = 4104
    LVM.DELETEALLITEMS = 4105
    LVCF.WIDTH = 2
    LVCF.TEXT = 4
    'set starting values
    columnIndex=0
    
    
    'create structs
    Struct LVCOLUMN, _
        mask As ulong, _
        fmt As long, _
        cx As long, _
        pszText$ As ptr, _
        cchTextMax As long, _
        iSubItem As long, _
        iImage As long, _
        iOrder As long
    
    Struct LVITEM, _
        mask As ulong, _
        iItem As long, _
        iSubItem As long, _
        state As ulong, _
        stateMask As ulong, _
        pszText$ As ptr, _
        cchTextMax As long, _
        iImage As long, _
        lParam As long, _
        iIndent As long
    
    struct msg,_
        hndl as ulong,_
        message as long,_
        wParam as long,_
        lParam as long,_
        LOWORDwparam as word,_
        HIWORDwparam as word,_
        LOWORDlparam as word,_
        HIWORDlparam as word
    
    'initialize common controls:
    
    calldll #comctl32, "InitCommonControls",re as void
    
    'set style values
    style = _WS_CHILD OR _WS_VISIBLE OR LVS.NOSORTHEADER Or LVS.REPORT Or LVS.SINGLESEL Or LVS.SHOWSELALWAYS
    
    'setup window
   nomainwin
BackgroundColor$="darkcyan"

    WindowWidth = 550 : WindowHeight = 400
    UpperLeftX = INT((DisplayWidth-WindowWidth)/2)
    UpperLeftY = INT((DisplayHeight-WindowHeight)/2)
    Menu        #main, "&File" , "E&xit", [quit]
    button #main.b1,"Edit",[editrow],ul,25,5,30,20
    button #main.b2,"Delete",[delete],ul,60,5,40,20
    button #main.b3,"Save List",[saveList],ul,105,5,60,20
    button #main.b4,"Load List",[loadList],ul,170,5,60,20
    button #main.GetMsgHookCallback, "button", [choice], ul, 230, 5, 60, 20
    Open "List View Example" for Window as #main
    #main "trapclose [quit]"
    
    hWin=hwnd(#main) 'get window handle
    hwndParent = hwnd(#main)
    
    GOSUB [CREATELISTVIEW]
    GOSUB [ADDCOLUMNS]
    
    '**************************************************************************************************
    'add data rows
    LVITEM.mask.struct = LVIF.TEXT
    
    'sample data, i used three spaces to separate each data item
    maxRows=5
    dim rowdata$(maxRows)
    rowdata$(0)="row1   two   three   four   five   six   seven   eight   nine   ten"
    rowdata$(1)="row2   two   three   four   five   six   seven   eight   nine   ten"
    rowdata$(2)="row3   two   three   four   five   six   seven   eight   nine   ten"
    rowdata$(3)="row4   two   three   four   five   six   seven   eight   nine   ten"
    rowdata$(4)="row5   two   three   four   five   six   seven   eight   nine   ten"
    
    maxrow=maxRows-1
    
    'add each row of data
    for row=0 to maxrow
        call addRow row, rowdata$(row)
    next
    '**************************************************************************
    open "MsgHook" for dll as #MsgHook
    hMsgProc = hwnd(#main.GetMsgHookCallback)
    calldll #MsgHook, "TrapMsgFor",hwndLV as ulong, ret as boolean
    calldll #MsgHook, "WatchMsg", hwndLV as ulong, _WM_LBUTTONDBLCLK as long, ret as boolean
    hMsgProc = hwnd(#main.GetMsgHookCallback)
    calldll #user32, "GetWindowLongA",hMsgProc as ulong,_GWL_ID as short,callbackID as long
    calldll #MsgHook, "CreateGetMsgProcHook", hwndParent as ulong, callbackID as long, _
        hMsgProc as ulong, hHook as ulong
    'set timer for checking selected listview row
   
     timer 500, [callsub]
    '*********************************************************************************
   WAIT
    
    ''basic save and load routine.------------------------------------------------
    
[saveList]
    filedialog "Save List","*.txt",saveList$
    if saveList$<>"" then
     open saveList$ for output as #s
     print #s,maxRows
     for s=0 to 5
      print #s,rowdata$(s)
     next s
     close #s
    end if
     notice "List Saved."
    wait
    
[loadList]
    filedialog "Open List View","*.txt",openFile$
    if openFile$<>"" then
        open openFile$ for input as #i
        input #i,maxRows
        redim rowdata$(maxRows) 'max listview rows.
       for i=0 to maxRows
        line input #i,rowdata$(i)
       next i
        close #i
        maxrow=maxRows-1
       'add each row of data
        for row=0 to maxrow
         call addRow row, rowdata$(row)
        next row
    end if
    
    
    '-------------------------------------------------------------------------
    
[loop]
    Wait
    
[callsub]
    
    'call listview1Click ' for future selection using button
    '****************************************************************************************************
    
    
    wait
    
    
    '----------------edit row window
    
    
[editrow]
    timer 0
    WindowWidth = 333 : WindowHeight = 286
    UpperLeftX = INT((DisplayWidth-WindowWidth)/2)
    UpperLeftY = INT((DisplayHeight-WindowHeight)/2)
    
    
    stylebits   #main2.b1, _BS_MULTILINE, 0, 0, 0
    button      #main2.b1, "Save", [saverow], UL, 185, 210, 80, 25
    TextboxColor$ = "White"
    textbox     #main2.t1, 40, 35, 100, 24
    TextboxColor$ = "White"
    textbox     #main2.t2, 40, 70, 100, 24
    TextboxColor$ = "White"
    textbox     #main2.t3, 40, 105, 100, 25
    TextboxColor$ = "White"
    textbox     #main2.t4, 40, 140, 100, 25
    TextboxColor$ = "White"
    textbox     #main2.t5, 40, 175, 100, 25
    TextboxColor$ = "White"
    textbox     #main2.t6, 175, 35, 100, 25
    TextboxColor$ = "White"
    textbox     #main2.t7, 175, 70, 100, 25
    TextboxColor$ = "White"
    textbox     #main2.t8, 175, 105, 100, 25
    TextboxColor$ = "White"
    textbox     #main2.t9, 175, 140, 100, 24
    TextboxColor$ = "White"
    textbox     #main2.t10, 175, 175, 100, 25
    
    Open "Edit Row" for Window as #main2
    #main2 "trapclose [quit2]"
    #main2.t1,word$(rowdata$(LVITEM.iItem.struct),1,"   ")
    #main2.t2,word$(rowdata$(LVITEM.iItem.struct),2,"   ")
    #main2.t3,word$(rowdata$(LVITEM.iItem.struct),3,"   ")
    #main2.t4,word$(rowdata$(LVITEM.iItem.struct),4,"   ")
    #main2.t5,word$(rowdata$(LVITEM.iItem.struct),5,"   ")
    #main2.t6,word$(rowdata$(LVITEM.iItem.struct),6,"   ")
    #main2.t7,word$(rowdata$(LVITEM.iItem.struct),7,"   ")
    #main2.t8,word$(rowdata$(LVITEM.iItem.struct),8,"   ")
    #main2.t9,word$(rowdata$(LVITEM.iItem.struct),9,"   ")
    #main2.t10,word$(rowdata$(LVITEM.iItem.struct),10,"   ")
    
     

Re: Eye On SQL
Post by metro on Jul 11th, 2017, 7:22pm

Part 2
Code:
   
[loop2]
    
    Wait
    
[saverow]
    #main2.t1,"!contents? t1$"
    #main2.t2,"!contents? t2$"
    #main2.t3,"!contents? t3$"
    #main2.t4,"!contents? t4$"
    #main2.t5,"!contents? t5$"
    #main2.t6,"!contents? t6$"
    #main2.t7,"!contents? t7$"
    #main2.t8,"!contents? t8$"
    #main2.t9,"!contents? t9$"
    #main2.t10,"!contents? t10$"
    rowdata$(LVITEM.iItem.struct)=trim$(t1$)+"   "+trim$(t2$)+"   "+trim$(t3$)+"   "+trim$(t4$)+"   "+trim$(t5$)+"   "+trim$(t6$)+"   "+trim$(t7$)+"   "+trim$(t8$)+"   "+trim$(t9$)+"   "+trim$(t10$)
    call updaterow rowdata$(LVITEM.iItem.struct)
    
    goto [quit2]
    wait
    
    
[quit2]
    timer 500, [callsub]
    close #main2
    index=0
    wait
    
[clear]
    wait
    
[delete]
   call deleteRow LVITEM.iItem.struct
   rc=0:r=0:d=0
    while rc<= maxrow
     if rowdata$(rc)<> rowdata$(LVITEM.iItem.struct) then
      rowdata$(r)=rowdata$(rc)
      r=r+1
     else
      d=d+1
     end if
      rc=rc+1
    wend
    maxrow=maxrow-d
   wait
     
[quit]
    timer 0
    calldll #MsgHook, "UnhookMsgHook", hHook as ulong, ret as void
    close #MsgHook
    close #main : END
    
    'subs------------------------------------------------------------------------------------------------
    
sub deleteRow rn
    CallDLL #user32, "SendMessageA", _
      hwndLV As ulong, _
      LVM.DELETEITEM As long, _
      rn As long, _
      LVITEM As struct, _
      r As long
end sub

sub updaterow rd$
    LVITEM.iSubItem.struct = 0
    LVITEM.pszText$.struct =word$(rd$,1,"   ")
    LVITEM.state.struct=1
    calldll #user32, "SendMessageA" , hwndLV as uLong, LVM.SETITEM as Long,_
     0 As Long, LVITEM As struct, re as Long
    for x=1 to columnIndex-1
      LVITEM.iSubItem.struct = x
      LVITEM.pszText$.struct = word$(rd$,x+1,"   ")
      calldll #user32, "SendMessageA" , hwndLV as uLong, LVM.SETITEM as Long,_
      0 As Long, LVITEM As struct, re as Long
    next x
end sub

'------------------------------------
sub addRow row,rd$
    LVITEM.iItem.struct = row
    LVITEM.iSubItem.struct = 0
    LVITEM.pszText$.struct =word$(rd$,1,"   ")
    calldll #user32, "SendMessageA" , hwndLV as uLong, LVM.INSERTITEM as Long,_
     0 As Long, LVITEM As struct, re as Long
   for x=1 to columnIndex-1
     LVITEM.iItem.struct = row
     LVITEM.iSubItem.struct = x
     LVITEM.pszText$.struct = word$(rd$,x+1,"   ")
     calldll #user32, "SendMessageA" ,hwndLV as uLong, LVM.SETITEM as Long,_
     0 As Long, LVITEM As struct, re as Long
    next x
end sub
'--------------------------------------
sub  addColumn name$
    LVCOLUMN.pszText$.struct = name$
    calldll #user32, "SendMessageA" ,hwndLV as uLong, LVM.INSERTCOLUMN as Long,_
    columnIndex as long, LVCOLUMN as Struct, re as Long
    columnIndex=columnIndex+1
end sub


Sub listview1Click
    LVM.GETITEMA = 4101  : LVM.GETITEMCOUNT = 4100
    LVIF.TEXT = 1        : LVM.GETITEMSTATE = 4138
    LVIS.SELECTED = 2    : LVIF.STATE = 8
    'Returns 0-based index of selected item. Call this routine on a timer
    'with resolution of 1000 ms to see which item is selected.
    calldll #user32, "SendMessageA",hwndLV As uLong,  LVM.GETITEMCOUNT as long,_
     0 as long, 0 as long, total As Long
   
    For index = 0 To total-1  'check each row
        LVITEM.mask.struct = LVIF.TEXT Or LVIF.STATE
        LVITEM.iItem.struct = row
        LVITEM.iSubItem.struct = 0  'first column
        LVITEM.cchTextMax.struct = 32
        LVITEM.pszText$.struct = Space$(32)
        LVITEM.stateMask.struct = LVIS.SELECTED
        calldll #user32, "SendMessageA", hwndLV As uLong,  LVM.GETITEMA as long,_
        index as long, LVITEM as struct , re As Long
        state = LVITEM.state.struct 'selected s
        If state And LVIS.SELECTED Then
         Exit For
        End If
    Next
End Sub


[choice] 'determine user selection
       calldll #MsgHook, "GetMsg", msg as struct, ret as void
      'get number of items in list:
    
    CallDLL #user32, "SendMessageA", _
     hwndLV As ulong, _
     LVM.GETITEMCOUNT As long, _
     0 As long, _ 'always 0
     0 As long, _ 'always 0
     total As long
    for index = 0 to total-1 'check each row
     LVITEM.mask.struct = LVIF.TEXT OR LVIF.STATE
     LVITEM.iItem.struct = index 'row
     LVITEM.iSubItem.struct = 0 'first column
     LVITEM.cchTextMax.struct = 32
     LVITEM.pszText$.struct = space$(32)
     LVITEM.stateMask.struct = LVIS.SELECTED
     CallDLL #user32, "SendMessageA", _
      hwndLV As ulong, _
      LVM.GETITEMA As long, _
      index As long, _        'index of row
      LVITEM As struct, _
      r As long
      state = LVITEM.state.struct 'selected state of item
     if state and LVIS.SELECTED then
      txt$=winstring(LVITEM.pszText$.struct)
      goto [editrow]
  '   notice "Selected: ";txt$
     exit for
     end if
    next
      if txt$="" then notice "No selection."
       txt$=""
    wait
    
[CREATELISTVIEW]
    'create listview control
    calldll #comctl32, "InitCommonControls", re as void
    calldll #user32, "GetWindowLongA", hWin as uLong, _GWL_HINSTANCE as long, hInstance as uLong
    
    calldll #user32, "CreateWindowExA", _WS_EX_CLIENTEDGE As long,"SysListView32" as ptr,_
     "" as ptr, style as long,  25 as Long,  25 as Long,  500 as Long,  310 as Long,_
     hWin as uLong, 0 as long, hInstance as uLong,"" as ptr, hwndLV As uLong
    
    calldll #user32, "SendMessageA" , hwndLV as uLong, 4150 as Long,_
     32 As Long, 32 As Long, re as Long    'make listview fullrowselect style
     LVCOLUMN.mask.struct = LVCF.WIDTH OR LVCF.TEXT
     LVCOLUMN.cx.struct = 45 'desired width of column in pixels
 call sendMessage  hwndLV,4150,1,1'SET GRID 
   RETURN
    
[ADDCOLUMNS]
    'add 10 column names, can be changed to match your records comlun names and number of columns

    for c=1 to 10  'add ten columns to the list view
     columnname$="Col";c
    call addColumn columnname$
    next c
    RETURN


function SendMessageLong(hWnd,msg,w,l)
calldll #user32, "SendMessageA", hWnd as ulong, _
msg as long, w as long, l as long,_
SendMessageLong as long
end function

SUB sendMessage  hwndLV , message,wParm ,lParm
    calldll #user32, "SendMessageA",_
        hwndLV as ulong,_
        message as long,_
        wParm as long,_
        lParm as long,_
        re as long
END SUB

 


download for msghook.dll on this post
http://libertybasic.conforums.com/index.cgi?board=api&action=display&num=1497271206
Re: Eye On SQL
Post by metro on Jul 12th, 2017, 01:40am

[ Quote:
These do not work because each table is different and I need to layout the detail page for each table. So it has to be done on the fly. I

Helps if you read the brief properly.
What I added does not help. Sorry
huh
if no other solutions are put forward tsh73 posted this link on another post which could be helpful.

http://justbasic.conforums.com/index.cgi?board=tips&action=display&num=1174689555



Re: Eye On SQL
Post by meerkat on Jul 12th, 2017, 03:35am

Thanks for the info metro.
I didn't realize that laying out forms in LB was so difficult and rigid.
In my mind something that should be so simple!!

I'm looking into your suggestions for an answer.
If I can find a link between the broswer and LB it would make it really simple.

Funny, haven't heard of any bugs yet. Thought there would be a lot since I pushed the code out early..

Thanks again metro..


Re: Eye On SQL
Post by metro on Jul 12th, 2017, 04:28am

Quote:
Funny, haven't heard of any bugs yet. Thought there would be a lot since I pushed the code out early..


One I came across early was ...
I load a new DB, click on it once its displayed in the list, then click on the only table I have in that DB. that will display a list of fields. If I click on any one of the fields
and then select the "change" button the table is deleted.

the DB still appears in the list but it is empty.

there's a start for ya

I'll spend a bit more time putting it through its paces..



Re: Eye On SQL
Post by meerkat on Jul 12th, 2017, 10:33am


Quote:
and then select the "change" button the table is deleted.


Thanks Metro.

Give it a try again.. I think I fixed it. As you know SQLite has no command to Alter Table fields. So the system creates a table backup with the new field. Copies the table stuff to it, Deletes the table, and Renames the backup to the to the table name. Well it did delete the table, but it had a error and didn't rename the backup to the table name.

Re: Eye On SQL
Post by Brandon Parker on Jul 12th, 2017, 6:33pm

I have not tried your application, but there should be no reason why you cannot dynamically create and/ or edit a Windows ListView using Liberty BASIC to create a similar GUI as metro posted in the image above. Sure it takes some time and getting used to, but if you take your time to learn it the endeavor will be well worth it. Then all you will have to do is encapsulate all of the API calls into handy functions and you'll be off to the races for ListView manipulation.

If you set yourself up well you'll find yourself creating more ListView objects in many more projects since they are very useful for displaying all sorts of information.

{:0)

Brandon Parker
Re: Eye On SQL
Post by metro on Jul 12th, 2017, 8:07pm

Hi Brandon
Quote:
but there should be no reason why you cannot dynamically create and/ or edit a Windows ListView using Liberty BASIC

by "edit" do you mean the contents of each cell via "(LVITEM.iItem.struct)", if so
would you have any sample code or point me to an example

thanks in advance

Laurie
Re: Eye On SQL
Post by CarlGundel on Jul 13th, 2017, 07:40am

Maybe this will also be helpful?

http://basic.wikispaces.com/Spreadsheet

-Carl
Re: Eye On SQL
Post by Brandon Parker on Jul 13th, 2017, 10:22pm

Lauri,
I must apologize; as I was looking through old threads I came across a thread where I was supposed to send you some code, but I believe I failed to do so. I am very sorry for that; life must have gotten me caught up with something.

I have mounds of code available for ListView stuff so I'll put something together tomorrow night or Saturday. I will be driving all day tomorrow so it might be late or as I mentioned Saturday, but I will get something together very soon.

Are there any specific requests with managing ListViews from this topic? I'll try to get them into an example if you post the needs for this topic.


{:0)

Brandon Parker
Re: Eye On SQL
Post by metro on Jul 13th, 2017, 11:42pm

Quote:
I must apologize; as I was looking through old threads I came across a thread where I was supposed to send you some code,


to quote the least offensive of the Aussie vernacular "no worries"

don't stress Brandon, there's plenty of info out there for me to digest

All day driving is something we do here a lot, I'm visiting my son next week a leisurely 1600 klm drive (1000 miles in your lingo)

I appreciate you taking the time
Re: Eye On SQL
Post by metro on Jul 14th, 2017, 01:52am

until we get a handle on how to tackle a GridView is something like this an option
it needs a tidy up. and integrating into the ionSql.
Code:
[editrow]
    timer 0
print c
    dim array$(c) ' c was used to create the column count
    for z= 1 to c
    array$(z) =  word$(rowdata$(LVITEM.iItem.struct),z,"   ")
    next z



    WindowWidth = 313
    WindowHeight = 300
    UpperLeftX=int((DisplayWidth-WindowWidth)/2)
    UpperLeftY=int((DisplayHeight-WindowHeight)/2)

    listbox #main2.listbox1, array$(),[listSelect],  1,  1,302,180
    button #main2.default,"Hiden",   [listSelect],ul,-400,-400,  0,  0
    button #main2.BtnSave,"SAVE",   [saverow],ul,100,200,  100, 25
    open "Select Field to Edit..Press Enter" for dialog as #main2
    print #main2, "font Times_New_Roman  10 bold"
    print #main2, "trapclose [quit.main]"
    print  #main2.listbox1, "selectindex 1"
    wait

[listSelect]
    print #main2.listbox1,"selectionindex? index"
  '  notice "You selected - "; array$(index)
    gosub [editfield]
    wait

[quit.main]
    close #main2
wait

[editfield]
    WindowWidth = 313
    WindowHeight = 230
    UpperLeftX=int((DisplayWidth-WindowWidth)/2)
    UpperLeftY=int((DisplayHeight-WindowHeight)/2)

    textbox  #fldEdit.TbEdit ,  10,  30,280,25

     button #fldEdit.default,"Hiden",   [ModFld],ul,-400,-400,  0,  0

    open "Modify Data & Press enter" for dialog as #fldEdit
    print  #fldEdit,"font Times_New_Roman  10 "
    print  #fldEdit, "trapclose [quit.fld.edit]"
     print  #fldEdit.TbEdit, array$(index)
     #fldEdit.TbEdit ,"!setfocus"
' the line below crashes the program conflict somewhere
' rtrn = SendMessageLong(hwnd( #fldEdit.TbEdit),_EM_SETSEL,0,-1)
    wait
[ModFld]
    #fldEdit.TbEdit , "!contents? NewDat$";
    array$(index)=NewDat$
    #main2.listbox1,"reload"
    if index = c-1 then index=0
    print  #main2.listbox1, "selectindex "; index+1

[quit.fld.edit]
    close #fldEdit
    RETURN
[loop2]

    Wait

[saverow]
    for a = 1 to c-1
    RowDat$=RowDat$+array$(a); "   " ' has to be 3 spaces
    next a
    rowdata$(LVITEM.iItem.struct) = RowDat$
    call updaterow rowdata$(LVITEM.iItem.struct)
    RowDat$=""

    goto [quit.main]



[clear]
 


just replace from [editrow] to [clear] in the code I posted in reply #7 & #8 add this function
which isn't working just yet ... appears to be a conflict somewhere (still looking)
Code:

function SendMessageLong(hWnd,msg,w,l)
calldll #user32, "SendMessageA", hWnd as long, _
msg as long, w as long, l as long,_
SendMessageLong as long
end function
 

Re: Eye On SQL
Post by meerkat on Jul 14th, 2017, 09:29am

Thanks Laurie..

I did add something. Don't like it much! Haven't tested it a lot, but it looks like it works. Download it and give it a try..
If you can get something to work, why not replace the stuff I have and see if it works better.

The routine does not take care of TEXT type where you'd expect a edit box or something similar. It only uses "textbox'

I have to admit, I've spent way more time on interfaces like forms and list than I expected. Even though I'm new to LB, and don't understand a lot of stuff yet, I think it could have been a lot easier. But what do I know??

Re: Eye On SQL
Post by metro on Jul 14th, 2017, 6:18pm

One small bug Dan,
boxLabel$(numFields*2) & boxValue$(numFields*2)
throws an error when I try to edit (change) a record. I have changed to a multiple of 4 instead as it appears we have a limit of 10 fields that can be changed.






Re: Eye On SQL
Post by meerkat on Jul 15th, 2017, 05:02am

Thanks Laurie, I only did * 2 for some extra work space. So you are right a smaller number will work..

I curious.. I always wanted to see see how LB and RB compare in a similar program. So I'm going to duplicate this program in RB. For one, RB has no problems with dynamic forms. And putting buttons next to items in a list will work instead of a combo box with buttons at the bottom will also work. Will be interesting to see the difference...


Re: Eye On SQL
Post by Brandon Parker on Jul 21st, 2017, 08:34am

Ok.....

I apologize for the delay, but the weekend turned into a very busy time and unfortunately my wife and I caught a stomach bug during the visit so needless to say I was down for the count during the early part of the week.

That being said I have pulled together a little demonstration this morning that shows a little ListView action. The code does not go into removing/ editing columns, but that's just as easily done as everything else. The biggest challenge would be to keep track of everything.

In the program that uses a lot of this code the list view handles are not passed directly as they are in the example; the handle held in the array element is pushed into a variable that way most of the code gets reused over and over again.

I hope this helps in showing how great ListViews can be once you get a handle on how they work.

The code is contained in this post and the two below as well due to the length.

Code:
NoMainWin
Global False : False = 0
Global True : True = 1
Global currentRow : currentRow = 0

listViewHandle(0) = _NULL


Call initializeListViewConstants

    WindowWidth = 530
    WindowHeight = 335
    Button #Example.btnAddItem, "Add Item", AddItem, UL, 5, (WindowHeight - 65)
    Button #Example.btnEditItem, "Edit Item", EditItem, UL, 202.5, (WindowHeight - 65)
    Button #Example.btnDeleteItem, "Delete Item", DeleteItem, UL, 405, (WindowHeight - 65)

    Open "ListView Example" For Window As #Example
    Call InitCommonControls
    style = _WS_CHILD Or _WS_VISIBLE Or listViewConstants.LVS.NOSORTHEADER.struct _
            Or listViewConstants.LVS.REPORT.struct Or listViewConstants.LVS.SHOWSELALWAYS.struct _
            Or listViewConstants.LVS.SINGLESEL.struct
    styleEx = _WS_EX_CLIENTEDGE Or _WS_EX_DLGMODALFRAME 'Or listViewConstants.LVS.EX.DOUBLEBUFFER.struct
    listViewHandle(0) = CreateListView(style, styleEx, Hwnd(#Example), GetWindowLong(Hwnd(#Example), _GWL_HINSTANCE), 5, 23, 505, 244)
    result = setListViewFullRowSelect(listViewHandle(0))
    result = listViewShowGrid(listViewHandle(0))
    result = createNewListColumn(listViewHandle(0), 0, 100, "  Item 1")
    result = createNewListColumn(listViewHandle(0), 1, 100, "   Item 2")
    result = createNewListColumn(listViewHandle(0), 2, 100, "   Item 3")
    #Example "TrapClose Quit"

    While Hwnd(#Example)
        Scan

    Wend

    Sub Quit handle$
        result = DestroyWindow(listViewHandle(0))
        Close #handle$
        End
    End Sub



    Sub AddItem handle$
        result = AddListViewData(listViewHandle(0), currentRow, 0, "Data1")
        result = EditListViewData(listViewHandle(0), currentRow, 1, "Data2")
        result = EditListViewData(listViewHandle(0), currentRow, 2, "Data3")
        result = SelectListViewRow(listViewHandle(0), currentRow, True)
        currentRow = (currentRow + 1)
    End Sub

    Sub EditItem handle$
        numListViewEntries = GetListViewItemCount(listViewHandle(0))
        If Not(numListViewEntries) Then Exit Sub
        row = GetSelectedListViewRow(listViewHandle(0))
        item1$ = GetListViewText$(listViewHandle(0), row, 0)
        item2$ = GetListViewText$(listViewHandle(0), row, 1)
        item3$ = GetListViewText$(listViewHandle(0), row, 2)

        result = EditListViewData(listViewHandle(0), row, 0, GetListViewText$(listViewHandle(0), row, 0) + " Edited")
        result = EditListViewData(listViewHandle(0), row, 1, GetListViewText$(listViewHandle(0), row, 1) + " Edited")
        result = EditListViewData(listViewHandle(0), row, 2, GetListViewText$(listViewHandle(0), row, 2) + " Edited")

        result = SelectListViewRow(listViewHandle(0), row, True)
    End Sub

    Sub DeleteItem handle$
        numListViewEntries = GetListViewItemCount(listViewHandle(0))
        If Not(numListViewEntries) Then Exit Sub
        row = GetSelectedListViewRow(listViewHandle(0))
        result = SendMessageLVITEMStruct(listViewHandle(0), listViewConstants.LVM.DELETEITEM.struct, row)
        currentRow = (currentRow - 1)
    End Sub

    Sub InitCommonControls
        CallDLL #comctl32, "InitCommonControls", ret As void
    End Sub 



{:0)

Brandon Parker
Re: Eye On SQL
Post by Brandon Parker on Jul 21st, 2017, 08:35am

Here is the second part of the example .....

Code:
    Sub initializeListViewConstants
        'Create the structs for ListViews

        Struct LVITEM, mask       As ulong, _
                       iItem      As long, _
                       iSubItem   As long, _
                       state      As ulong, _
                       stateMask  As ulong,_
                       pszText    As ptr, _
                       cchTextMax As long, _
                       iImage     As long, _
                       lParam     As long, _
                       iIndent    As long

        Struct LVCOLUMN, mask       As ulong, _
                         fmt        As long, _
                         cx         As long, _
                         pszText    As ptr, _
                         cchTextMax As long, _
                         iSubItem   As long, _
                         iImage     As long, _
                         iOrder     As long

        Struct LVFINDINFO, flags        As ulong, _
                           psz          As ptr, _
                           lParam       As ulong, _
                           pt           As ulong, _ 'point structure
                           vkDirection  As ulong

        Struct listViewConstants, LVP.DBLCLK                    As long, _
                                  LVS.REPORT                    As long, _
                                  LVS.SINGLESEL                 As long, _
                                  LVS.SHOWSELALWAYS             As long, _
                                  LVS.NOSORTHEADER              As long, _
                                  LVS.EXDOUBLEBUFFER            As long, _
                                  LVS.EX.FULLROWSELECT          As long, _
                                  LVS.EX.CHECKBOXES             As long, _
                                  LVIS.FOCUSED                  As long, _
                                  LVIS.UNSELECTED               As long, _
                                  LVIS.SELECTED                 As long, _
                                  LVIS.CHECKED                  As long, _
                                  LVIS.UNCHECKED                As long, _
                                  LVIS.STATEIMAGEMASK           As long, _
                                  LVIF.TEXT                     As long, _
                                  LVIF.STATE                    As long, _
                                  LVIR.BOUNDS                   As long, _
                                  LVFI.STRING                   As long, _
                                  LVFI.PARTIAL                  As long, _
                                  LVN.ITEMCHANGED               As long, _
                                  LVM.ENSUREVISIBLE             As long, _
                                  LVM.SETITEMSTATE              As long, _
                                  LVM.GETITEMSTATE              As long, _
                                  LVM.GETITEMTEXT               As long, _
                                  LVM.FINDITEMA                 As long, _
                                  LVM.SETITEM                   As long, _
                                  LVM.GETITEMCOUNT              As long, _
                                  LVM.GETITEMA                  As long, _
                                  LVM.GETSUBITEMRECT            As long, _
                                  LVM.DELETEITEM                As long, _
                                  LVM.INSERTITEM                As long, _
                                  LVM.INSERTCOLUMN              As long, _
                                  LVM.SETEXTENDEDLISTVIEWSTYLE  As long

        'LVM_FIRST = &H1000 or 4096
        listViewConstants.LVP.DBLCLK.struct                     = False
        listViewConstants.LVS.REPORT.struct                     = 1
        listViewConstants.LVS.SINGLESEL.struct                  = 4
        listViewConstants.LVS.SHOWSELALWAYS.struct              = 8
        listViewConstants.LVS.NOSORTHEADER.struct               = 32768
        listViewConstants.LVS.EXDOUBLEBUFFER.struct             = 10000
        listViewConstants.LVS.EX.FULLROWSELECT.struct           = 32
        listViewConstants.LVS.EX.CHECKBOXES.struct              = 4
        listViewConstants.LVIS.FOCUSED.struct                   = 1
        listViewConstants.LVIS.UNSELECTED.struct                = 0
        listViewConstants.LVIS.SELECTED.struct                  = 2
        listViewConstants.LVIS.CHECKED.struct                   = 8192
        listViewConstants.LVIS.UNCHECKED.struct                 = 4096
        listViewConstants.LVIS.STATEIMAGEMASK.struct            = 61440
        listViewConstants.LVIF.TEXT.struct                      = 1
        listViewConstants.LVIF.STATE.struct                     = 8
        listViewConstants.LVIR.BOUNDS.struct                    = 0
        listViewConstants.LVFI.STRING.struct                    = 2
        listViewConstants.LVFI.PARTIAL.struct                   = 8
        listViewConstants.LVN.ITEMCHANGED.struct                = -101
        listViewConstants.LVM.ENSUREVISIBLE.struct              = 4115
        listViewConstants.LVM.SETITEMSTATE.struct               = 4139
        listViewConstants.LVM.GETITEMSTATE.struct               = 4140
        listViewConstants.LVM.GETITEMTEXT.struct                = 4141
        listViewConstants.LVM.FINDITEMA.struct                  = 4109
        listViewConstants.LVM.SETITEM.struct                    = 4102
        listViewConstants.LVM.GETITEMCOUNT.struct               = 4100
        listViewConstants.LVM.GETITEMA.struct                   = 4101
        listViewConstants.LVM.GETSUBITEMRECT.struct             = 4152
        listViewConstants.LVM.DELETEITEM.struct                 = 4104
        listViewConstants.LVM.INSERTITEM.struct                 = 4103
        listViewConstants.LVM.INSERTCOLUMN.struct               = 4123
        listViewConstants.LVM.SETEXTENDEDLISTVIEWSTYLE.struct   = 4150
    End Sub


    Function CreateListView(ByRef style, ByRef styleEx, ByRef hParent, ByRef hInstance, ByRef xloc, ByRef yloc, ByRef width, ByRef height)
        className$ = "SysListView32" + chr$(0)
        CallDLL #user32, "CreateWindowExA", styleEx           As ulong, _  ' extended style
                                            className$        As ptr, _   ' class name
                                            ""                As ptr, _
                                            style             As ulong, _  ' style
                                            xloc              As long, _  ' left x
                                            yloc              As long, _  ' top y
                                            width             As long, _  ' width
                                            height            As long, _  ' height
                                            hParent           As ulong, _ ' parent hWnd
                                            _NULL             As ulong, _
                                            hInstance         As ulong, _ ' hInstance
                                            _NULL             As ulong, _
                                            CreateListView    As ulong    'Hwnd to ListView

        'LVCF.WIDTH = 2 : LVCF.TEXT = 4
        LVCOLUMN.mask.struct = LVCF.WIDTH OR LVCF.TEXT
    End Function

    Function GetWindowLong(hWnd, index)
        CallDLL #user32, "GetWindowLongA", hWnd             As ulong, _
                                           index            As long, _
                                           GetWindowLong    As long
    End Function

    Function GetListViewItemCount(hWndLV)
        GetListViewItemCount = SendMessage(hWndLV, listViewConstants.LVM.GETITEMCOUNT.struct, 0, 0)
    End Function

    Function createNewListColumn(hListView, columnIndex, columnWidth, headerCaption$)
        LVCOLUMN.cx.struct = columnWidth
        LVCOLUMN.pszText.struct = headerCaption$
        LVCF.WIDTH = 2 : LVCF.TEXT = 4
        LVCOLUMN.mask.struct = LVCF.WIDTH OR LVCF.TEXT
        createNewListColumn = SendMessageLVCOLUMNStruct(hListView, listViewConstants.LVM.INSERTCOLUMN.struct, columnIndex)
    End Function

    Function AddListViewData(hWndLV, row, column, myData$)
        LVITEM.mask.struct = listViewConstants.LVIF.TEXT.struct
        LVITEM.iItem.struct = row
        LVITEM.iSubItem.struct = column
        LVITEM.pszText.struct = myData$
        result = SendMessageLVITEMStruct(hWndLV, listViewConstants.LVM.INSERTITEM.struct, 0)
        AddListViewData = SendMessage(hWndLV, listViewConstants.LVM.GETITEMSTATE.struct, 0, 0)
    End Function

    Function EditListViewData(hWndLV, row, column, myData$)
        LVITEM.mask.struct = listViewConstants.LVIF.TEXT.struct
        LVITEM.iItem.struct = row
        LVITEM.iSubItem.struct = column
        LVITEM.pszText.struct = myData$
        EditListViewData = SendMessageLVITEMStruct(hWndLV, listViewConstants.LVM.SETITEM.struct, 0)
    End Function

    Function SelectListViewRow(hWndLV, row, setFocus)
        LVITEM.iItem.struct = row
        LVITEM.iSubItem.struct = 0
        LVITEM.mask.struct = listViewConstants.LVIF.STATE.struct
        LVITEM.state.struct = listViewConstants.LVIS.SELECTED.struct
        If setFocus = True Then
            LVITEM.state.struct = (LVITEM.state.struct Or listViewConstants.LVIS.FOCUSED.struct)
        End If
        'SelectListViewRow = SetFocus(hWndLV)
        SelectListViewRow = SendMessage(hWndLV, listViewConstants.LVM.ENSUREVISIBLE.struct, row, False)
        SelectListViewRow = SendMessageLVITEMStruct(hWndLV, listViewConstants.LVM.SETITEM.struct, 0)
    End Function 



{:0)

Brandon Parker
Re: Eye On SQL
Post by Brandon Parker on Jul 21st, 2017, 08:36am

Here is the third part of the example .....

Code:
Function setListViewFullRowSelect(hListView)
        setListViewFullRowSelect = SendMessage(hListView, 4150, 32, 32)'LVS_EX_FULLROWSELECT
    End Function

    Function listViewShowGrid(hListView)
        listViewShowGrid = SendMessage(hListView, 4150, 1, 1)'LVS_EX_GRIDLINES
    End Function

    Function SendMessage(hWnd, Msg, wParam, lParam)
        CallDLL #user32, "SendMessageA" , hWnd        As ulong, _
                                          Msg         As long, _
                                          wParam      As long, _
                                          lParam      As long, _
                                          SendMessage As long
    End Function

    Function SendMessageLVITEMStruct(hWnd, Msg, wParam)
        CallDLL #user32, "SendMessageA" , hWnd                    As ulong, _
                                          Msg                     As long, _
                                          wParam                  As long, _
                                          LVITEM                  As struct, _
                                          SendMessageLVITEMStruct As long
    End Function

    Function SendMessageLVCOLUMNStruct(hWnd, Msg, wParam)
        CallDLL #user32, "SendMessageA" , hWnd                      As ulong, _
                                          Msg                       As long, _
                                          wParam                    As long, _
                                          LVCOLUMN                  As struct, _
                                          SendMessageLVCOLUMNStruct As long
    End Function

    Function DestroyWindow(hWnd)
        CallDLL #user32, "DestroyWindow", hWnd          As ulong, _
                                          DestroyWindow As long
    End Function

    Function GetSelectedListViewRow(hWndLV)
        GetSelectedListViewRow = -1

            numListViewEntries = GetListViewItemCount(hWndLV)
            For row = 0 To (numListViewEntries - 1)
                LVITEM.mask.struct = listViewConstants.LVIF.STATE.struct
                LVITEM.iItem.struct = row
                LVITEM.iSubItem.struct = 0  'first column
                LVITEM.stateMask.struct = listViewConstants.LVIS.SELECTED.struct
                result = SendMessageLVITEMStruct(hWndLV, listViewConstants.LVM.GETITEMA.struct, 0)
                If LVITEM.state.struct And listViewConstants.LVIS.SELECTED.struct Then
                    GetSelectedListViewRow = row
                    Exit Function
                Else
                    If row = (numListViewEntries - 1) Then
                        GetSelectedListViewRow = -1
                        Exit Function
                    End If
                End If
            Next row

    End Function

    Function GetListViewText$(hWndLV, row, column)
        LVITEM.mask.struct = listViewConstants.LVIF.TEXT.struct
        LVITEM.iSubItem.struct = column
        LVITEM.pszText.struct = Space$(_MAX_PATH) + chr$(0)
        LVITEM.cchTextMax.struct = Len(Winstring(LVITEM.pszText.struct))
        EditListViewData = SendMessageLVITEMStruct(hWndLV, listViewConstants.LVM.GETITEMTEXT.struct, row)
        GetListViewText$ = Trim$(Winstring(LVITEM.pszText.struct))
    End Function 



{:0)

Brandon Parker)
Re: Eye On SQL
Post by meerkat on Jul 21st, 2017, 09:21am

Thanks Brandon
Looks like you did a lot of work on this.

I don't understand it all yet, but I'll have a look at it and see how hard it is to include in the program.

Personally, I wish I could use a browser interface. I'm not smart enough to figure out a link between LB and the browser. You can automatically line up everything up. Plus it has date pickers, time pickers, color pickers, numeric and decimal checks, range checks, scrolled input boxes, canvas and a lot more. But I keep looking..

Dan
Re: Eye On SQL
Post by Brandon Parker on Jul 21st, 2017, 10:33am

The hardest part was pulling everything out of the program it's currently in and making it a usable demonstration.

You can try an ATL control along with HTML to give you what you want.

That being said, everything that you mentioned is doable in LB; it just depends on what you want to do and how you want to go about it. I even have Date/ Time Picker code as well which I use in my program to ensure the user is entering the date/ time in the format that I am expecting to make it easier to insert in the SQLite database.

Obviously looks might be important as well, but you can do anything you want with what Liberty BASIC and Windows makes available.

Let me know if you want to explore anything and I'll lend a hand; start a new thread with any specific request and feel free to PM me and let me know that you done so.

Hopefully I will not take so long to get back to any new requests as I did with Laurie; I feel so terrible about letting that slip through......


{:0)

Brandon Parker

Re: Eye On SQL
Post by Rod on Jul 21st, 2017, 10:34am

Quote:
I wish I could use a browser interface


It is possible to start a browser and to use raw html. I have not read deeply enough to know if this is what you really mean but I just want to be sure you have seen the tutorial here.
Re: Eye On SQL
Post by meerkat on Jul 21st, 2017, 11:05am

Thanks Rod..
No I have not seen the tutorial.
I did a quick look.
If it doesn't take a lot of code I'm interested.
I'll look for the following features;
- Can put stuff on the browser and get it back by reference to the id='value'
- Can list with multiple lines with buttons that have a variable id. Such as for i = 1 to 20: "<input type button value = "Update" id=upd";i ....
If I can reference "upd"+i then I can get to any button.
- Since HTML has <input type=, number, digital, date, time,text, checkbox, radio and others then I should be able to display stuff with pickers and numeric checks and scroll and all the other stuff on browsers.. And get it back via the id reference..

Anyway ... I hope it works..
Thanks again.. Dan


Re: Eye On SQL
Post by metro on Jul 21st, 2017, 7:13pm

Brandon,
Thanks for taking the time out in your busy life. (a heads up retirement is just as busy just no income)
Your coding style is something I am trying to emulate.
I look back and cringe at the spaghetti code I created when i first started.

These functions will come in very handy.

a Bonza effort

Laurie
Re: Eye On SQL
Post by meerkat on Jul 26th, 2017, 07:25am

If anyone is interested I added:
- CSV export
- CSV import
- sorts any field on browsing a table.

More on the way...

Dan..
Re: Eye On SQL
Post by meerkat on Jul 30th, 2017, 10:50am

You can now generate a LB program from sql commands.
When you select Sql you can enter a sql command.
3 options are available;
1. do SQL will execute the sql command.
2. do CSV will produce a CSV file
3. do GEN will create a LB program.
The output LB program has the structure to change the variable types and their sizes.

Tested as follows:
1. downloaded the cal.db file form the site.
2. Loaded it i to the system.
3. Selected it from the Database selection
4. Entered the following sql command
Code:
SELECT  
s.schNum, 
s.assetNum, 
s.schEnd as preEnd, 
s1.schBeg as nxtBeg, 
((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) as laps, 
(strftime('%w',s.schEnd))   as dowBeg, 
asset.descr as aDescr, 
asset.assetType 
FROM  schTime as s 
JOIN  asset 
ON    asset.assetNum = s.assetNum 
JOIN  schTime as s1 
ON    s1.assetNum = s.assetNum 
AND   s1.schBeg > s.schEnd 
AND   ((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) >= 100 
AND   s1.schBeg = (SELECT min(s2.SchBeg) 
FROM  schTime   as s2 
WHERE s2.assetNum = s.assetNum 
AND   s2.schBeg > s.schEnd) 
AND   s.schBeg >= '2009-05' LIMIT 10
 


What does it do.
The cal.db system has "assets" and "schTime" .
Assets are things you want to schedule such as conference rooms, projectors etc.
schTime is a list of time the assets are already scheduled.
This sql command hunts for open slots available for the amount of time you need the asset, starting on a particular time and listing only a certain number of open slots.

You could easily modify the LB program to request from the user what asset they want and when they want it and how long they need it and how many openings to report. This can be done by making the 100 value a variable such as needTime$. Change the limit of 10 to a variable as numSlots$. Change '2009-05' to a variable of begDate$




Re: Eye On SQL
Post by metro on Jul 30th, 2017, 11:11pm

Not sure if its something I've done huh
downloaded cal.db and latest version of ionSQL, the db will load in ionSQL, however the tables are not shown when the db is selected.
Same with 2 previous versions of ionSQL
all other db's will display table schema within ionSQL.

good news though... query result is printed
Re: Eye On SQL
Post by meerkat on Jul 31st, 2017, 07:07am

OOPS!

Thanks for pointing that out.

I was playing and altering cal.db. I thought I was using the one located at the download, but I was using a copy located somewhere else.

The correct one should be there now. If that still has problems let me know.

BTW.. The asset file has a assetNum and a asset type. So you could schedule by type, or a specific asset. For example if you wanted a conference room but didn't care what conference room it was you could ask for all conference rooms by searching the type instead of the asset number.

Thanks again for the heads up..

How is your forecasd for stock picks going?
Re: Eye On SQL
Post by meerkat on Aug 9th, 2017, 5:48pm

Bug Fix.

I'm always surprised by sqlite options.
As many of you know, Firefox maintains information in sqlite tables.
They use a primary key to keep track of their rowid.
So if you load some of their files into the system it will give you an error if you try to delete/change their data. This has been fixed.

You may be interested to see what information Firefox has in their files:
cookies.sqlite
places.sqlite
content-prefs.sqlite
formhistory.sqlite
webappsstore.sqlite


Any problems... Let me know..
Re: Eye On SQL
Post by clausnilsson on Oct 6th, 2017, 10:36am

Apologies from a newbie.

I am trying out the ionSQL.bas.

I have put both SQ3_4_LB.dll and sqlite3.dll in both windows/system32/ and in the directory where I run the .bas. I get the error: General Error - Couldn't open 'sqlite3.dll'.

Why, I wonder?
Re: Eye On SQL
Post by Rod on Oct 6th, 2017, 11:18am

Generally speaking you should not place anything in the windows system directory. This needs to be handled by an installer with the necessary permissions.

Windows fiercely protects its system directories.

So you should be able to access .dlls placed in the project directory. Show us the line of code you use to open the .dll and print of DefaultDir$ just before you try to access the .dll. Is it the directory you expect? Is it a protected windows directory?
Re: Eye On SQL
Post by clausnilsson on Oct 7th, 2017, 1:18pm

I read somewhere that windows/system32 was the place to put dlls. But I also put them in the project directory as it did not help to put them in system32.

By entering
print DefaultDir$
end
it is confirmed that it is the project directory.

Other than that temporary change it is ionSQL.bas:

Open "SQ3_4_LB.dll" for DLL As #sq3 ' open SQ3_4_LB.dll
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long ' was sqlite3.dll loaded by SQ3_4_LB.dll
msg$ = Winstring(result) ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'"
if instr(msg$,"Error") then ' If we have an error
notice msg$ : close #sq3 ' close sqlite and end program
end
end if

Maybe I need to be more clear: DefaultDir$ is as expected, but I get the error eventhough both dll's are in the project directory and a non-edited version of Eye On SQL is in the same directory. Please, can anyone think of a reason? And what to do?

Re: Eye On SQL
Post by meerkat on Oct 12th, 2017, 09:48am

Got a little time and combined the program generator into ionSQL.
You can now generate a LB program to maintain a selected table:
1. list the data in the table with sorts on any field
2. Drill down with wild card searches
3. do Add, Change, and Delete

For those using Run Basic, I wrote a RunBasic ionSQL program. I tried to make them as similar as possible. But because of language differences there are a few differences.

You can download at:
http://kneware.com/libertybasic/
directory:
ionSQL for Liberty basic
RunBasic for RunBasic
RunBasic has some setup and docs at ionSQL.html

Not sure, but maybe this should be sent to the library. Maybe someone has some time to do this??



Re: Eye On SQL
Post by tenochtitlanuk on Oct 16th, 2017, 05:58am

Server or address problem? I can't connect to the address given..
Re: Eye On SQL
Post by meerkat on Oct 16th, 2017, 08:49am

on Oct 16th, 2017, 05:58am, tenochtitlanuk wrote:
Server or address problem? I can't connect to the address given..


Sorry. I have a dual speed modem. Kinda crazy, but I have 2 wifi interfaces plugged into USB ports. One is port 80 (for server functions) and the other gets whatever port it can. Using one for downloads and port 80 for internet. I acidently unplugged port 80.

Should be ok now.. Let me know if there is still a problem. It may be slow since I'm doing a 6hr download.

Good luck.. Dan
Re: Eye On SQL
Post by tenochtitlanuk on Oct 16th, 2017, 11:49am

Thanks! Worked fine. Looking forwars to seeing how it works- I've been meaning to look at 'proper' databases for some time.

Re: Eye On SQL
Post by meerkat on Oct 16th, 2017, 12:24pm

on Oct 16th, 2017, 11:49am, tenochtitlanuk wrote:
Looking forwars to seeing how it works


You're welcome. I tested about everything I can think of. But as you know you can't catch everything.

This was my first LB program, so was surprised that I haven't heard of many buggs. I'm sure there is a better way than they way I wrote it.

The one I wrote in Run Basic to match the LB program, I think is better. LB really isn't set up very well for DB type stuff like forms, grids, and data checking on forms. But it works..

Good luck.. I'm out of town for a week so don't expect bug fixes this week.


Re: Eye On SQL
Post by Chris Iverson on Oct 21st, 2017, 8:08pm

Well, let's start with the basics.

https://basic.wikispaces.com/file/detail/SQ3_4_LB+%281%29.zip


This file is an archive that includes SQLite, the SQ3_4_LB DLL, a test program utilizing both, and the source code for SQ3_4_LB(I believe).


Extract the entire "test program" folder somewhere, double-click the SQ3_4_LB_test.bas file to open it in LB, and try to run it.

Does this run successfully?

It does for me.


If it does for you as well, then there's a problem in your code that's preventing it from loading the SQlite DLL properly.

If this also doesn't run, then there may be issues in your Windows environment that's preventing proper DLL linking.
Re: Eye On SQL
Post by Cor on Oct 23rd, 2017, 08:45am

clausnilsson,

I had the same thing happen to me.

I had played with ionsql for several hours yesterday. Restarted LB several times during that time and it came up with ionsql everytime. When playtime was over I saved the .bas file in the subdir I had been working in all the time (contains both necessary dlls). Then I 'saved as' to another (backup) subdir.

This morning I had your situation. Couldn't get past not loading sqlite. I turned on the default variables and noticed that the CommandLine$ did not have the correct value. Then I checked where my ionsql was loading from. That was from the backup dir. I reloaded ionsql from the save subdir and everything was ok. Perhaps you are loading your ionsql from a non-save directory.

This might just be an error in LB.

Hope this helps you.
Re: Eye On SQL
Post by meerkat on Nov 11th, 2017, 07:55am

I added Speech, movies, music, and photos to ionSQL in RunBasic.
As a complete novice to LB, I couldn't figure out how to do this.

In RB if you have a field beginning with "medi_" it will play or show the media pointed to in the field.
If the field begins with "spek_" it will speak whatever is typed in the field.

I'd like to do the same for the LB version if possible.

How do I do this??

Thanks for the help...
Dan
Re: Eye On SQL
Post by Rod on Nov 11th, 2017, 11:35am

I think you should start with the ATL tutorial that Alyce provided.

This would give you a browser control that may provide some of the functionality you speak of. It depends on how much ionSQL is doing itself or whether it is simply directing the browser to do stuff.

New territory for us, so you will need to experiment.

http://lbpe.wikispaces.com/ATL+Tutorial

Let us know how you get on.

Re: Eye On SQL
Post by meerkat on Nov 12th, 2017, 05:01am

Thanks Rod,

I took a look at your suggestion a couple weeks ago about using the HTML interface.
It would solve a lot like grids, and all the HTML5 functions such as video, photos, voice, calendar popups, color popups and a lot more.

I like it, but still do not know how to get stuff back into LB from the interface.
I tried a simple login screen.
How do I get the username and password into LB
Here is the HTML I tried:
Code:
html$ =         "MSHTML:<html><head></head><body>"
html$ = html$ + "<FORM METHOD='POST'>"
html$ = html$ + "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 BGCOLOR=wheat>"
html$ = html$ + "<TR><TD COLSPAN=2 ALIGN=CENTER><B>Please Log In</TD></TR>"
html$ = html$ + "<TR><TD ALIGN='RIGHT'>User Name</TD>"
html$ = html$ + "<TD><INPUT TYPE='TEXT' NAME='user' SIZE=25 VALUE='guest'></TD></TR>"
html$ = html$ + "<TR><TD ALIGN='RIGHT'>Password</TD>"
html$ = html$ + "<TD><INPUT TYPE='TEXT' NAME='pass' SIZE=25 VALUE='guest'></TD></TR>"
html$ = html$ + "<TR><TD COLSPAN=2 align=center>"
html$ = html$ + "<INPUT TYPE='SUBMIT' NAME='login' VALUE='Login'>"
html$ = html$ + "<INPUT TYPE='SUBMIT' NAME='exit' VALUE='Exot'>"
html$ = html$ + "</TD></TR></TABLE>"
html$ = html$ + "</form>" 


Thanks for the help
Dan
Re: Eye On SQL
Post by Rod on Nov 12th, 2017, 05:50am

Perhaps someone better versed than me will offer advice. I have not played much with html/web code. Would your html code not store the input in the database or on the server? Would you not use httpget$() function to retrieve that info?

There are a few folks that will know the answer, lets hope they contribute.
Re: Eye On SQL
Post by Colin McMurchie on Nov 12th, 2017, 09:31am

Hi,

I am very rusty on this sort of stuff, and I could be wrong, but I think that you may need an 'action' attribute in your form field, for example something like

<form action="/action_page.php" method="get">
First name: <input type="text" name="fname"><br>
Last name: <input type="text" name="lname"><br>
<input type="submit" value="Submit">
</form>

This code is taken from the web site below.

https://www.w3schools.com/tags/att_form_action.asp

Also I am not sure about the first line. I do not recognise the MSHTML: bit in this context.

As Rod asks, are you sending this form (once completed) to a server (even on the same computer) and if so how are you getting your information back? Remember that this form is part of the client side interface. Where is the server side code?

Colin
Re: Eye On SQL
Post by meerkat on Nov 12th, 2017, 5:22pm

Thanks Colin.

You are soo right about <form action=.
I should know better, but I'm new to LB.

I don't know what MSHTML: is either. But without it the DLL does not work.

Ok I set <form action='http:localhost/c:libertyBasic/a.bas'
It finds it, but it don't know what to do with it.. I think I'll have to compile it.

My question is how do I get the passed information from the URL?

Thanks again for the help..
Dan



Re: Eye On SQL
Post by Brandon Parker on Nov 12th, 2017, 7:22pm

Have you attempted to compile the Liberty BASIC .bas file and then execute the renamed runtime engine against your .tkn file? The webpage doesn't know what to do with the .bas file, but the runtime engine should if the webpage can start it up properly

{:0)

Brandon Parker

Re: Eye On SQL
Post by Colin McMurchie on Nov 13th, 2017, 04:25am

Hi Meercat,

There are more questions than answers here I am afraid. The short answer to "how do I get the passed information from the URL?" is that the result is contained in the web page sent back from your program a.bas. But this assumes several things.

1) That a.bas sends a string of well-formed html to STDOUT - which LB does not do by default. Does a.bas do that? As Brandon says, it would have to be compiled and running to work. Can you show the code?

2) That you have an HTML server already running on the local machine, that is correctly linked to a.bas. Do you?

3) That you send your HTML form to the server through a valid browser or browser window, or alternatively use the MESOCK route mentioned earlier in the thread. Are you using such a browser? In other words, what are you trying to do with the html$ you assembled in an earlier post? Again, some more code would be helpful.

4) Finally, saving changes to your database is problematic via web technologies on local machines, for security reasons.

I am sorry of all this sounds complex, but it is. Working with databases is never simple. Leveraging the technologies built into moderm browsers is very appealing but it comes at a price. You could also investigate RUNBASIC personal edition for the server side. It is more suited than LB for a web based approach.

Are you sure you cannot get the results you want by using conventional LB widgets?

Colin
Re: Eye On SQL
Post by Rod on Nov 13th, 2017, 07:39am

Can I just restate the problem. I am getting a little confused. So this is where I think we are. If I have the wrong end of the stick correct me.

We are working with SQLite as the actual database engine. SQLite is designed to work on the local disc or network. It will allow multiple access requests so we are talking a network solution not a web server solution.

ionSQL is a Liberty BASIC wrapper that allows creation and maintenance of a local database.

The reason we are using a browser is that it has been hard to format and display output using standard Liberty controls. The browser seems to offer a way to display text pictures and sounds as SQL might on a web page. So how to interact with an API created ATL Browser?
Re: Eye On SQL
Post by meerkat on Nov 13th, 2017, 10:01am

Thanks Rod.
You state the problem correctly.
The ionSQL has everything working ok. But the interface from LB is very week and cryptic. Even a simple grid is complex. Using html and <table> is simple. It adjust all the columns to size. It left and right adjusts. It allows a button by row that you can assign values. So basically if you loop through a table you only need one html statement to show all the rows, with a button per row to do something. And it knows what record the button wants.

It also validates numeric, decimal, dates. It has popup for dates, colors and whatever. HTML5 will play movies, songs, and display images. Everything is adjusted to size automatically. For example a album with 10 images across and 10 down will adjust the image size to fit in each cell and fit the window. You could click the cell(image) to do something like resize the image.

Anyway, I can live with what I have. I just don't like it much.
If LB had a two way interface to HTML things would look great, and take a lot less code.

I don't need it to be treated as a client/server. So local is fine.

Thanks for the help..
Dan
Re: Eye On SQL
Post by Colin McMurchie on Nov 13th, 2017, 10:57am

Hi Dan, Hi Rod,

If you are confused then so am I. Am I right in assuming we are no longer talking about the program here
http://kneware.com/libertybasic/ionSQL/ionSQL.bas - the link at th top if the thread, but the program here?
http://kneware.com/libertybasic/RunBasic/ionSQL.bas

If so, are we still talking about Liberty Basic or RunBasic?

If we are talking about runbasic and libertybasic together, how , how does it all work?

Colin
Re: Eye On SQL
Post by meerkat on Nov 13th, 2017, 11:14am

Colin..

I'm talking about the liberty Basic version. Actually it really doesn't have anything to do with the ionSQL program but with user interface in general. I only used ionSQL as a reference of how I was doing the user interface. And because I already had it working, it would be a way for me to try a better interface.
Maybe it should be a new thread.

Maybe it's me. I had a lot of problems with the grid and forms. As you can see from the LB ionSQL the grid (or list) and forms are terrible. I know there is a dll for displaying stuff, but not too powerful and not a lot of features. So I was looking for a way to do HTML to get a better interface. I'm beginning to believe it's not possible or as complex as the DLL itself.

I was just reaching out to see if it was possible.

Thanks for the help
Dan.

EDIT..
Something like RFO BASIC interface would be great..
http://rfo-basic.com/manual/#_Toc477208950

Re: Eye On SQL
Post by Rod on Nov 14th, 2017, 03:15am

The idea is that Liberty BASIC accesses SQLite. Carl had something planned for LB5 that allowed native access to a SQLite local database.

That part has been solved by a couple of folks. However without the "Grid" widget planned for LB5 it is quite hard to show structured table data. meerkat wants to take it a step further and allow video, picture, sound and speech data to be presented in a structured way.

As he knows this can be done in Run BASIC using a browser he is trying to find a way to use a browser locally.

As I see it LB sends the browser html code that links to a database and displays info. If the info changes like a password then the html code updates the database. Then LB polls the database to see if there is input. If so it sends the next set of html to the browser. Sounds feasible but I don't have the skills.
Re: Eye On SQL
Post by meerkat on Feb 7th, 2018, 05:35am

on Nov 14th, 2017, 03:15am, Rod wrote:
However without the "Grid" widget planned for LB5 it is quite hard to show structured table data.

As I see it LB sends the browser html code that links to a database and displays info.


You are correct Rod.
In my mind, what would really help is CGI.
https://en.wikipedia.org/wiki/Common_Gateway_Interface
If that were possible it would be a big help to Carl. With HTML you could do grid stuff there. He could eliminate the need to maintain Run Basic since you could do everything in LB5.
And with help for real databases, probably using ODBC, you could do business applications.
Re: Eye On SQL
Post by meerkat on Feb 7th, 2018, 05:37am

m