PlanetSquires Forums

SQLiteningMan - a Database Manager for SQLitening.

The project just is to show how fast and easy database applications can be written with FireFly and SQLitening. The program can work in local or Client / Server mode.

These functions are implemented :
- Connect to a server
- Open a database
- Read and display a recordset in a listview
- Layout - Changing the fontsize of the listview
- Add records
- Edit records
- Delete records
- Show some database info (will be extended)
- A QuickSQL function
- A SQL Query Builder
- Grid sorting (Credit goes to Jean-Piere Leroy)
- Column Auto Sizing (Credit goes to Jean-Piere Leroy)
- Create databases
- Create tables
- Create indeces
- Create triggers
- Print data grid (column width is reflected in printout)
- Helpfile

Something about the speed of SQLitening - I tested this app with the sample database that Fred Meier provided. These are the machines I used for testing:

- Server: AMD Athlon 5000 with 2.6 GHz, 2 GB RAM, Win7 Ultimate
- Dev machine: AMD Athlon 4850e with 2,526 MHz, 4 GB RAM, Win XP Pro SP3
- NetBook: Acer 751, Atom Z520, 1.30 GHz, 2 GB RAM, Win XP Home SP3

Speed on Dev Machine, reading 10,000 records:
- LAN (cable):  0.937 sec
- Local:        0.704 sec

Speed on NetBook, reading 10,000 records:
- Local:        2.781 sec
- WLAN:         3.719 sec
- Internet:     7.516 sec 

(Mobile Internet connection via UMTS Stick, Transfer rate was 68 kbs)

PS: I had created (some just adapted) a couple of icons for the toolbar. They are certainly not the most fantastic. I just wanted something in blue. If anyone has a good source for icons let me know. If anyone wants to use any of mine he can do so, they are Public Domain.

Rolf

A complete setup with all dependencies, helpfile, and sample database can be downloaded here:
http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe (http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe)

Attached are two zip files:
- The project files. (You need to download these only, if you are just updating the project.)
- The runtime files. (The Dlls and the sample database. You need these in the release folder to run the compiled program.)

Latest Update: 2012-04-06 v1.01.148

NICE!!!

You should also include the requirements, for example, I see you're using Jose's includes, make a note of that in case someone doesn't have them.

Include also the compiler you're using, just as a  reference.

You've got a point there, Ivan.
I am using PB 9 and FireFly 3.04.

I should have also made some mention how to use the program. I will start a little helpfile later. Basic steps are:
- Choose a connection to connect or leave it empty to work local.
- Choose a datase (sample.db3)
- Connect and/or open Database.
- In the treeview click on the table you want to use.
- Click Show Data.

Rolf

This really is great,

THANK YOU.

Marc

Rolf,
   Excellent...  I look forward to using it.  I will try it out with that AdBook DB to see how it behaves.

What a great idea, well done Rolf!

I just noticed that I left the German language file (SQLMan.lng) in the Release folder, so that the program has German menues. The file is a normal ini file and can be edited. If you delete or rename the file, then the program will have the original English menues.

Rolf

Rolf,

Fantastic program, this is an excellent tool to show what both SQLite and FF3 can do.

Thanks for giving your effort to the FF community.

Regards

Andrew

SQLMan Update

These are the changes:
- Added Delete Record
- DB Info - Added CopyToClipboard
- Added QuickSQL
- Add / Edit Dialog - Cursor moves now automatic to next field
- Removed some bugs (Might have created a few new ones...)

The download is in the first message of this thread.

Rolf

Rolf,
   It's looking better and better.   When the "Create Database" is implimented, will the user be able to create a Multi-Table database and be able to define the indexes they want to use?  And to define the releationship between the records,  One to One and One to Many relationships ?
 
   I don't know if SQLite databases ever need to be "packed" but will this be possible with your SQL-DBM ?
   Also, if a SQLite database gets dammaged will SQL-DBM have tools to allow for rebuilding/repairing the database? 
   Finially, will SQL-DBM allow the user to re-structure the database.  IE insert or remove fileds, and  add or remove tables?

Hi Marty,

all these features I am planning to implement.

There was a bug in the AddEdit.frm module. Here is the proper working module.
The changes are also reflected in the complete project in the first message of this thread.

Quote from: Rolf Brandt on November 26, 2009, 05:18:56 AM
I just noticed that I left the German language file (SQLMan.lng) in the Release folder, so that the program has German menues. The file is a normal ini file and can be edited. If you delete or rename the file, then the program will have the original English menues.

Rolf

Could you give us the English translation of the menus?  I can guess some of them and maybe figure out most of the rest, but the chances of getting some wrong are pretty high.

I have a future project in mind that might use SQLite and this will be a good introduction for me.  I had a bunch of training in SQL maybe 20 years ago but I've never done much with it after the training and about all I remember about it is that it was too confusing.  I was always a low level programmer and this just seemed so high level it made me dizzy. :)

Thanks,
Barry

Barry,

I will work on the menues in the next days and post the complete menue file in English and German. Like that everybody can make his own file for his language.

If you just want English menues then delete the file 'SQLMan.lng' in the release folder. SQLMan will show the default English menues.

Thank you.  That gives me what I need.

Barry

New Update!

A first version of the SQL Query Builder (see Screenshot) is now included.

Project files are updated in the first message of this thread.

Hi Rolf,

I just downloaded your update and it generates an error (see attached image).

Regards,

David

I'll check it out, David.

David,

I just uploaded the file 'SQLMan_project' to the first message in this thread. This has just all the latest project file. It does not include the sample database and the Dlls. Those you have anyway alread in the release folder.

Let me know it this compiles ok. It does here.

Rolf,
This is an amazing experience for me, as I'm re-learning PB and FF. Now I have an SQLitening project to help me. This is a lot to learn, and I hope to help advance the work.

I just ran the Query Builder (QB), and I have a few questions:
1. Should the FIELDS listbox have MULTI-SELECT enabled? Would be easier than having to select each one individually...

2. In building the WHERE clause, I tried ADAPT and 3COM. ADAPT got quoted, 3COM did not.

3. I built this query, but I get no results:
SELECT ROWID AS RECNO, MANUF, PRODUCT, PRICE FROM PARTS WHERE MANUF LIKE 'ADAPT'

Thanks,
-John

ADDED:
Found one of my mistakes:
In SQLitening:
- the multi-character wildcard is not *, but %
- the single character wildcard is not ?, but _

Hi John,

1. Multi-Select - I see your point. I had it left to single, so that you  can set the order of the fields like that. But with Multi-Select enabled on could set a couple of fields where the order does not matter so much. I'll change that.

2. I simply differentiated between Numeric and String value using the Val() function. Not a good idea for a value like '3COM'. I think I'll better test for occurrence of literals other than 0-9. Thanks for the hint.

3. I check over the LIKE clause.

Hi Rolf,

thanks for the update, it compiles ok now.

David

Hi Rolf,

3. SELECT ROWID AS RECNO, MANUF, PRODUCT, PRICE FROM PARTS WHERE MANUF LIKE 'ADAPT%'

I added the % and it's fine.

new: How would I build a query that contains the keyword: UNIQUE (DISTINCT ?)
-jhm

Hi John,

I put it on ToDo list for the next couple of days to include those features in it. This version of the QueryBuilder I wrote just this morning, so it is still very simple.

But let me know of any features you think that should be included. I will try to add them.

Rolf

Off the top of my head:

- I think LIKE should have the complement: NOT LIKE

- Seems that the Copy to Clipboard button is not working.

- Should the QuickSQL textbox be disabled until a database is opened?

- Could the SQL Builder save the settings, so that next time I can retrieve them and make changes? Maybe save to the .ini file?

Those are the main things I'm thinking of now.

Rolf, this is really cool! Thanks so much for this contribution to the community!
-John

Thanks for your thoughts, John, that is very helpful.

Rolf

I know this isn't perfect, but here's my English menu file:

[main]
file=&Database
setcon=Set Connection
setdb=Select Database
connect=Connect to Server
opendb=&Open last used database
quit=&Quit
data=Data
recedit=&Edit record
recadd=&Add record
recdel=&Delete record
tools=&Tools
dbinfo=Database &Information
setlang=Set Language
help=&Help
about=About


I know these will take time, but I offer them anyway:
- For Select Database, there's currently a simple InputBox, and I'm sure that at some future time it will be changed to a full Dialog...

- I think the Main form needs at least this one navigation button: GoToRecord

OK, I'm done for today! Got to get into other work now!
Best regards,
-John

(I have only been tinkering with SQLMan as a user. I have not yet started to explore code, but I expect to start later in the week. I'd like to actually give you code to go along with my suggestions... Soon, I hope!)

Quote(I have only been tinkering with SQLMan as a user. I have not yet started to explore code, but I expect to start later in the week. I'd like to actually give you code to go along with my suggestions... Soon, I hope!)

That will be very much appreciated.

Rolf

New Update!

v1.0.0.42
Query Builder - Added DESC sorting
Query Builder - Added checkbox for differentiating between string / numeric values
Query Builder - Added % param for LIKE
Query Builder - Added Reset button
Query Builder stays alive after executing query
Main Dialog - Added OpenFileDialog for opening local files
Main Dialog - Added 'Close Connection'
Added Connection Info dialog

Download in first message of thread.

Hi Rolf,

I get a compilation error with your updated project (see attached image).

Regards,

David

Sorry David,

I think I forgot to update the project file. Just add the frmConnInfo.frm to the project, then things should work.

Meanwhile I'll upload the correct project. Sorry for the inconvenience.

Hey Rolf! I just installed the latest.
 
I played with Query Builder:
- LIKE with the %  works perfectly!
- variations of Numeric, etc. and found no problems. 
And thanks for fixing the "Copy to clipboard button"!

Problem:
1. open a local database (sample.db3)
2. click on "Connect to Server"
3. enter nothing and click OK (Returns Connection Error -16: cannot connect)
    Note that the local database tree is still showing in the left panel.
4. Click on local database tree. (Returns SQLitening unexpected message: -7 No open database.)
Then the app shuts down without further warning.

I don't have more time today or tomorrow, but I'll be back over the weekend. I've started to look through the source code, and I'm understanding what I see, so soon I can be more helpful.

-John

Thanks for your observations, John. I'll fix those things.

Hi Rolf,

QuoteSorry for the inconvenience.

No problem Rolf, I think it's great that you share your efforts here with the rest of us! ;D

Best Regards,

David

Rolf,

Check your private messages when you get a chance.

Thanks

Update!

v1.0.0.49
Added Form 'Connection Settings' - allows also to test connection
Added Sizing font in grid
Fixed Connection Info dialog

Database Creation will be next.

Download in 1st message of thread.

Ralph,
   It's looking better and better. 
   Would it be possible to have records resorted by the column when a particular column heading is clicked on?

Hi Marty,

yes this also will be one of the next features. Jean-Pierre had put up some nice listview routines for that, and I will implement them.

Rolf

Nice Project !

Do you have plans make here examples for each control that you can used in FireFly SDK Designer v3.00

I like it if some write examples for each control that FireFly SDK Designer used.

- Standard Controls
- Common Controls

Greetings
Stephane

Hello Stephane,

I doubt that my time will allow me to do that. Most of my programming circles around databases.

New Update!

v1.0.0.54
Added some of Jean-Pierre Leroy's Listview functions
(http://www.planetsquires.com/protect/forum/index.php?topic=2026.0 (http://www.planetsquires.com/protect/forum/index.php?topic=2026.0))
- Column Auto Sizing
- Column Sorting
Added a (minimum) help file

Download as always in first message of this thread.

Hello Rolf,

Thank you for SQLMan; I'm sure it will help many of us.

Regarding the "Column Sorting" function, I forgot to mention than to be able to view the down-arrow (or up-arrow) on the columns header, the F3 project has to be compiled with the option "Enable Theme support".

Thanks again for this beautiful piece of software.
Jean-Pierre

Thank you for that hint, Jean-Pierre. It was driving me insane because I did not get the arrows to see.

Rolf

Rolf,
    The Edit routine does not save the record  when the SAVE button is clicked.  Niether does the add routine in SQLMAN seem to be working.  Was this feature not yet implimented?

I am using the latest version.

I'll check and fix!

New Update!

v1.0.0.69
Corrected bug frmAddModify - Editing and Saving records works now properly again
- Note that added records are not reflected in the grid right away but first afer a click
- on the QuickSQL/Refresh button
Changed Layout to Tab based rather then Popup Dialogs
- Thanks to Paul and his help with the AutoResize function
Added NOT LIKE to Query Builder
Still fidgeting with implementation of ESCAPE in Query Builder
frmAddModify - set Cancel property =True for Cancel button

Database Creation is about halfway finished, will be implemented in the next couple of days.

Project download in first message of thread.

Rolf,
   The edit/save is still not working. Here what I am doing.  Where am I going wrong.
1) Open DB
2) Select the Tablet to work with and display the records
3) Click on record to edit
4) Edit record and click the SAVE button

Observation:  Record usally not saved.  If I press enter after the edit and then click SAVE the information in the column changed is saved in the wrong column.

Here's the DB I am working with:

Strange - I'll check it out.

Marty,

I downloaded your database and used it to create new records and modify existing ones. I could not reproduce those errors.

Possibly I am not using the latest version.  The version I have is 1.0.0.70

Here are two picture to illistrate problem:

First picture shows me changing the name  "Welbee"  to  "Welby"
The Second picture is AFTER I clicked the SAVE button.  As you can see the
name did NOT change.   I even tried clicking the "Show/Refresh Data" button. and still the record was not changed.

Where am I going wrong?   Am I using an old version of the SQLman?

Rolf,
   Now look at the following series of pictures:

This time I was trying to edit the street address;  changing "somewhere" to "123 Nowhere Lain".  See picture aaThird.
After making my change I pressed the "Enter" key and notice in Picture aaForth that the active field moved from DrStreet to DrCity  Now I click the save button and change is saved this time but it is saved in the wrong column.  It was saved in the DrCity Column. See picture  aaFifth
Is this the intended behavior? 
Are we using different versions of the program?
Or am I in the "Twilight zone"?

Odd indeed. I am attaching a newer exe. Try if that still happens.

Rolf,
   There must be a rip in the Time-Space continum and I am stuck in another universe.   The program you just posted does the exact same behavior as what I discribed above.
   Maybe I am just dense as a rock.  So, humor me, using the above database,  walk me though the steps to change Markus Welbee  to  Markus Welby  Step by step do a screen capture at each step. 

   If someone here who has also downloaded SQLman could take the database above and edit a record.  I would very much like to know if it is working correctly for you. 

   I find it hard to understand how Rolf and I are getting totally different results doing a simple edit.

    Oh, and the Add-record feature doesn't work on that database either.  Could it be that the database structure is wrong in someway?

   I will try this on a simple one table database and see if it makes any difference.

     

Well, I tried edit with the "Sample.db3" database and the edit did not work on that database either. 

As for the Add record function be aware that I do no update the grid after an Add. You must refresh the recordset by either doubleclicking on the table in the treeview or by a click on the QuickSQL button. This loads the records new.

As for the Edit function I am still not able to reproduce your problem.

Hello,
Finally had a change to look at this.
I can see the same behaviour as Marty.
Rolf, please try this and take care that you do exactly the same:
first open a database, go to a table lets say doctors (I am using Marty's db but should happen everywhere).
Select a record by clicking once on a row (ex. row 3) -> row gets blue
Click on the icon that says 'Edit record' -> dialog opens
Click on DrStreet in listbox on the left -> data is shown in textbox on the right = '123 anywhere'
Dubbleclick on the 123 part of the text -> 123 gets selected
Change to 999
!!! Press enter !!!
What you now will see is that on the left side (the listbox) the field selected has gone to the next one, namely 'DrCity' while on the right side it will stil show '999anywhere'
When you now press the save button '999anywhere' gets saved in the DrCity and is shown as such in the grid.

Hope this helps.
Marc

OK, Marc, now I can reproduce it.

It has to do with the use of the mouse in the AddModify.frm. My sequence of editing a record is different. I choose the record in the grid, press <ENTER> to open the Edit dialog. Next I use the up/down keys to select the field I want to edit, then I press <ENTER> to move to the textbox. I edit the field contents, the press again <ENTER> to save the changes to the array. Important - otherwise changes will not be recorded. Next I save the record.

You notice I use the keyboard more than the mouse because its faster. Now that I know the problem I'll fix it quickly.

Thanks Marc,  you have released me from Rod Sterling's grasp.  Living in the Twilight Zone is very frustrating.

Rolf, sorry I didn't explane the problem better.  I assumed everyone would edit a record the same way I was doing it.  Guess there's more than one way to skin a cat.

New Update
Version 1.0.0.71

The changes:

v1.0.0.69
Corrected bug frmAddModify
Changed Layout to Tab based rather then Popup Dialogs
Added NOT LIKE to Query Builder
Added Database creation
frmAddModify - set Cancel property  =True for Cancel button

v1.0.0.71
Added DoubleClick event in Grid to open edit dialog
Corrected Click/DblClick behaviour in Treeview
QueryBuilder - Added UpDown  control to change order of selected fields
MainWindow - Added Indices to Treeview
Database Creation - Added Create Index
Database Info - Added Listview for TableDefinition
Database Info - Added Index information

Download in first message of thread.

Rolf,
    Is it possible to modify a Table in an active database?   That is to add or delete a field in a Table.  Or, to add or delete an index   in database filled with infomation?

Rolf,
   I hate to sound like a broken record.  But the Edit record feature still does not work properly. Did you actually test it?
   I have included a screen print.  What the screen print shows,  is that the edited field is ending up being stored in the field next to it.
   Try these steps and see what you get:
1) open database
2) Double click on Tablet to display the rows and columns
3) Click on Row to highlight it
4) Click the  Edit Record button
5) In the edit dialog click the field you want to edit
6) Make a change to the field
7) Press ENTER key
8) Click SAVE button

Notice the changed field got saved in the Dr-Street field instead of the Dr-Name field.

Quote from: Marty Francom on December 16, 2009, 10:08:44 PM
Is it possible to modify a Table in an active database?   That is to add or delete a field in a Table.  Or, to add or delete an index   in database filled with infomation?

As of yet you can only add tables or indices to a current database. More features will be implemented in future updates.

Minor Update!
v1.0.0.72

Changed code in the AddModify.frm.
Cursor will not jump to next field anymore but stay with the same. Seems this was occasionally causing the problem Marty reported.

Download in first message of thread.

Rolf,
    Downloaded this minor  program change.  And now the cursor does stay in the same field after pressing enter.   But that is NOT the problem.  The problem is: the data changed gets saved to the WRONG field.  See screen print.
    I tryed to change the address and when I saved the change the address was put in the DrCity field instead of the DrStreet field.
    So I have to report the Edit function of SQLMAN still does not work correctly.

How do you switch from one field to the next? Mouse or Cursor keys?

BTW I did mention that the cursor does stay in the same field now.

Bugfix (Can't really call it an update!)
v1.0.0.73

Changed code in the AddModify.frm again.
Cursor still will not jump to next field but stay with the same. But the problem should be fixed.

Download in first message of thread.

Hello:
I don't know why, but I am getting a dialog from windows that says "memory xxx could not be read" and firefly shuts down. This is when I compile and run the version dated today. I tried a new installation and same thing happened. I tried another sample application and there was no problem, so I don't think it is my machine, but you never know. I use a different machine every day at the computer lab, use a fresh install of firefly. If nobody else complains, you can probably ignore this. I like the program and hope you keep improving it.
thanks,
Paul Breen

Paul,

I sometimes experience this too. I think it might have to do with the tab control. Try to open all forms in designer view. That sometimes works for me.

Update!
v1.0.0.74

Changes:
Implemented Creation of Triggers
frmAddModify again - jumps to next field after edit (finally)

Changed name of executable to SQLiteningMan to reflect it's real purpose.

A complete setup with all dependencies, helpfile, and sample database can be downloaded here:
http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe (http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe)

Rolf,
    I am happy to report that fields being saved are now saved correctly.

    I would like to request a couple of things:

    * Would it be possible that if the user double clicks a field, that record opens the edit dialog and moves directly to the field the user double clicked (as that is likely the field they wanted to edit).

    * Would it be possible to SAVE the record as soon as the ENTER button is pressed.  I am particulary keyboard centric and it seems to me to be a waist of energy to have to stop and grab the mouse so I can save the record.   I know some people may want to edit several fields before they save.  But because after pressing enter you can not see the fields you have previously changed.   To me it just makes more sense to save the change as soon as the ENTER key is pressed.

   * Another posibility would be to be able on the listing screen to just clickon the field the user wants to edit and edit it right there.  No real need to open a special edit dialog.

   Your project is coming along nicely.  Good work!

Hello Marty,

thank you for your thoughts. I'll see what I can implement into the project.

QuoteWould it be possible to SAVE the record as soon as the ENTER button is pressed.   

ENTER just saves the field content to the array. You could use ALT-S to save the record without using the mouse. Of course we also could save the record also after each field being edited, but this really would create a lot of unnecessary traffic to the server.

Quote
I am particulary keyboard centric and it seems to me to be a waist of energy to have to stop and grab the mouse so I can save the record.

I fully understand that, I don't like either always to change between mouse and keyboard. That's why my commercial taxi programs are keyboard oriented. Though many of my customers complain and want more mouse capabilities like Drag and Drop etc.

The Add / Edit form I am using here is an idea borrowed from the Cheetah Pet program. It allows to have an universal small dialog even for records with lots of fields. It is not the most ideal form to work with but you do not have to create special forms for each table. In an application with a set range of tables I would use a different approach.

Right now I am implementing altering table definitions (which is a bit limited in SQLite).

Update!
v1.0.0.75

Changes:
RecordBrowser - right mouseclick opens Edit dialog with selected column
ToolsMenu - Export to CSV
Updated helpfile

Project download in first message of thread.

Complete setup with all dependencies, helpfile, and sample database:
http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe (http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe)

Rolf,
   Just wonderiing how you are coming with this project?

Hi Marty,

I have been very busy with my dispatch project the last few weeks, so I did not have much time to work on the SQLiteningman. But it will continue.

Update!
v1.0.0 Release 85

Changes:
New Layout - Selected record displayed in 2nd Listview (see image)
New Feature - Print data grid (column width is reflected in printout)

Project download in first message of thread.

Complete setup with all dependencies, helpfile, and sample database:
http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe (http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe)

Hallo Herr Brandt!

Would it be possible to extend the program that I can open a database on a server with a password?
Like: 'slOpen(msDbName & $BS & $PwrdDB, sMode)'

Thanks

Rudolf Fürstauer

Hallo Rudolf,

I hadn't done anything for ages on that project - never got really finished. I should reallly continue it.

Since I use this option on my other SQLitening based programs I can (and will) add that feature in the next few days.

Rolf

Thank you for your great help!   :)


Rudolf

Update!
v1.0.1 Release 148

Changes:
Supports password protected database files now.
Some quirks fixed.
Updated to PB10

Complete setup with all dependencies, helpfile, and sample database:
http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe (http://sqlitening.rbsoft.eu/SQLiteningMan_Setup.exe)

Rolf

PS:
Can't get the project files uploaded. Will try later again.