DBKit Beta 9 is now available

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.
9 Likes

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.

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

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.

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

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.

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

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

1 Like

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

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

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