DBKit Beta 9 is now available

Databases
1

Changes

  • DBKit now supports using any column type for the primary key column (not just numbers as had been the case).

Resolved Issues

  • Fixed a bug when using DBKit with PostgreSQL that caused the PrimaryKeyColumn function to fail if the table name included the name of the schema.
  • DBKit.DatePicker (web) now stores null in the database column when there is no date selected.
  • WebDialogs and WebContainers are now supported in addition to WebPages for web projects. Note that any setup you are going to do for a TableConnection on a WebDialog must be done in the Shown event rather than the Opening event.
  • Fixed a bug that caused a NOE when uploading a picture to a DBKit.ImageViewer.

New Features

  • DBKit.SearchField - Provides a Search UI that automatically works with your QueryRowsListBox without any additional code. If you leave the Table property blank, it will find the first TableConnection then bind with its QueryRowsListBox. Otherwise, it will look for a TableConnection on the layout whose Table property matches its own. If you provide columns for it to search it will use them. If you don’t, it will use the columns of the QueryRowsListBox to which it is bound. String and Number type columns are supported. For String values, a begins with search is performed. For numeric values, the search is for an exact match. You can use >, < or 
 for a range. For example, >10 finds rows use searched numeric column has a value greater than 10. Entering 10
20 will find rows whose searched numeric column has a value between 10 and 20. The Show All Rows By Default property if true will display all rows when the SearchField is blank.
  • DBKit.Toolbar - New control that provides a toolbar with options for New, Delete, Edit and Undo buttons. No code needed. The buttons you wish displayed can be indicated via properties in the Inspector. (For web projects, you’ll will need to add the new backicon.png file to your icons folder)
  • DBKit controls now have a Mandatory property that can be set via code or in the Inspector. Mandatory controls must have a value for the record to be able to be saved. For Slider, the value must be greater than the minimum value. For CheckBox, the checkbox must be checked.
  • DBKit.NumberField - Handles numeric entry. Choose the type (Currency, Number, Percentage and Custom) and it will handle the formatting and entry masking for you. It includes properties to indicate if you want a thousands separator and the number of decimals you wish to show. It’s also locale-aware. Choose Custom if you plan to assign a format to the Format property. It also has MinimumValue and MaximumValue properties. It will only allow entry of values between these two numbers provided that they are not the same number. In other words, if MinimumValue and MaximumValue are both 0 then no range checking will be done.
  • All controls that display database data now have a DisplayName property. If this property has a value, it will be used in any messages (except those displayed as a result of database errors) to the user such as when they have not filled in a mandatory field. If it does not have a value, the name of the control will be used.
11 Likes
2

It’s great to see you’re still working on this. I beleive it will greatly simplify DB interaction.

Just one question - how can I connect to a PostgreSQL database? There are absolutely 0 examples for anything except SQLite.

I think i have to create a property of type DBKit
 and then define it’s properties before connecting, but I can’t work out what goes where.

Thanks.

3

Eventually got there after much trial & error


New property in ‘Session’ db of type DBKit.TableConnection
Following lines added to opening event of session:

dbAdmin = New DBKit.TableConnection
if dbAdmin.Connected(DBKit.TableConnection.DatabaseServer.Postgres, “192.168.1.54”, 5432, “mydatabase”, “myuser”, “mypassword”) then
MessageBox(“connected”)
End

4

Yes for a web project it would go on Session. In fact, the web example of DBKit and the web tutorial for DBKit show this. As for PG, If you look at the example projects, there are buttons on the main desktop window and webpage that show connecting to MySQL and PostgreSQL.

Regardless, I’m glad you worked it out! There will be more updates coming. I have endless ideas both to make DBKit easier to use and more powerful at the same time.

5

Found an issue with method LoadSelectedRowContinue when using Postgres. Changed line 22 from

CurrentRow = Connection.SelectSQL("SELECT * FROM " + Table + " WHERE " + PrimaryKeyColumn + " = ?", PrimaryKeyValue)

to

CurrentRow = Connection.SelectSQL("SELECT * FROM " + Table + " WHERE " + PrimaryKeyColumn + " = $1", PrimaryKeyValue)

Hope this helps

6

Good catch Jon. Too bad PostgreSQLDatabase uses $1, $2, etc. instead of ? like MySQL, ODBC and SQLite.

I guess @Geoff_Perlman needs to update the code to have this difference working in DBKit without the need to manual change by the user.

7

Yes indeed. There some subtle differences that distinguishes PostgreSQL from the rest.

8

Yep, I missed that one in the last update but it’s fixed already for the next beta.

1 Like
9

Is there any way to cancel a ‘new’ entry if incorrectly selected? Undo is disabled.

10

Yeah Undo should work. That’s a bug. I’ll fix it for the next beta.

11

And it’s now fixed. I’ll see about getting another beta out this coming week.

12

Does this also fix the problem when I have pressed the ‘New’ button but I don’t enter anything and select something else in the listbox? I get the message “You have unsaved changes”, when I click Continue it crashes with “cannot rollback - no transaction is active”.
Do I always have to press Undo first, even if nothing has been entered?

13

The right code to maintain the use of different databases will be :

if Connection isa PostgreSQLDatabase then
  CurrentRow = Connection.SelectSQL("SELECT * FROM " + Table + " WHERE " + PrimaryKeyColumn + " = $1", PrimaryKeyValue)
Else
  CurrentRow = Connection.SelectSQL("SELECT * FROM " + Table + " WHERE " + PrimaryKeyColumn + " = ?", PrimaryKeyValue)
end if
14

Yes, it fixes that.

1 Like
15

@Geoff_Perlman : Is it possible to localise the button’s caption ?

16

It is. The only one that is problematic is the Edit button because DBKit toggles it between Edit and Done. I’m working on a fairly major update (which will be AFTER the next update) that moves all the localization outside of the internal implementation of DBKit so that you can localize the UI without having to alter DBKit at all.

1 Like
17

PostgreSQL allows positional placement and parameter reuse. That’s a good design. So some people could say the other way around <<too bad others use just “?”>>.

Connection.ExecuteSQL("UPDATE my_table SET special_val = $2  WHERE items = $1 AND special_val <> $2 ;" , groupID, specialValue)
2 Likes
18

Yeah, too bad that not all use the same advanced options.

1 Like
19

Hello @Geoff_Perlman ,

I took your DBKit from the web example , put it in my web project with Copy/Paste, It asks for those icons, and it should not, i put those as well.

Created a Property in App called DB, Put in the App.Opening event DB = New DBKit.TableConnection and the app crashes with the following error.

Screenshot 2024-07-23 at 12.33.49
Screenshot 2024-07-23 at 12.33.491473×932 97.9 KB

Any idea why is that and why it crashes in the first place ? Should i do something else in particular before running that ?

So far it seems that it is crashing due to Nil for target, and it seems that you designed this to run in Session Only, but in my case i do need the DB to be connected in the App side for some Thread tasks that are to be done unattended and separate from sessions .

Is there a quick fix for this ? or you can push another update for this in the examples to adapt the case ?

I guess same case could apply in the case where you want to use this as an API server without any interface, but in this case the kit could be little bit overkill but still.

Thanks

20

As a quick change i check if target is NIl and if so then i put as target = App and that did the job

Var target As Object
#If TargetDesktop Then
  target = app
#ElseIf TargetWeb Then
  target = Session
  
  If target = Nil Then
    target = App
  End If
  
#EndIf

If you have a better way please let me know , otherwise this works for me .

Thanks