Page 1 of 1

[SOLVED]Macro/Query Failure after updating to split database

PostPosted: Tue Oct 22, 2019 10:49 pm
by aterbo

I created a small inventory management system to locate material in a warehouse by row and column. It utilizes a barcode scanner to act as a keyboard, and manages the input through a series of macros. It might not be the most elegant solution, but it works and was robust enough for our needs. I have a link to a Google Drive shared version of the folder at the bottom of this email.

However, I am having issues with data corruption/disappearance using the normal LibreOffice embedded Base set up, which I probably shouldn't have used in the first place.

I just converted to a Split Database by following the instructions here:
viewtopic.php?f=83&t=61183 ... tructions/

Once I updated the files, I am getting errors with my macros, I believe relating to the queries and possibly the WHERE clause.

When I try to update the database information using my form, I get the following error:
Code: Select all   Expand viewCollapse view
BASIC runtime error.
An exception occurred
Message: incompatible data type in conversion

This line of code is throwing the error:
Code: Select all   Expand viewCollapse view
RunSQL = oQuery.executeUpdate(SQLQuery)

The string SQLQuery is as follows, where I believe the error is originating:
Code: Select all   Expand viewCollapse view
SQL = "UPDATE ""PackLocationsTable"" SET ""Previous Location"" = ""Location"", ""PreviousTimestamp"" = """ &_
"Timestamp"", ""Location"" = '" & sLocationScan & _
"', ""Timestamp"" = CURRENT_TIMESTAMP " & _
" WHERE ""PackNumber"" = '" & sPackNumber &"'"

Based on the information in this link, I see that there are concerns with using the WHERE clause in the new split database format, however I seem to be unable to find an implementation that will work with my database and macros after trying a few approaches

Could someone help me out with this update, or point me in the right direction? I built the original version of this database last year, and I am a bit rusty with my SQL. :crazy: :crazy:

EDIT - Villeroy pointed out the error - Dates and Timestamps are treated differently in HSQLDB 2.4.x, and I had the columns set up as Doubles, which didn't work after the update. See posts below.

Re: Macro/Query Failure after updating to split database

PostPosted: Wed Oct 23, 2019 3:47 pm
by Villeroy
Upload the database files or upload a Base document with an embedded version of that database. Confidentials removed of course. I guess, it's the time stamps.HSQLL 2.4 introduced timestamps with zone info. Try HSQL 2.3.4 and see.

Re: Macro/Query Failure after updating to split database

PostPosted: Wed Oct 23, 2019 3:55 pm
by aterbo
Hey Villeroy,

I will look into dates and see if that is causing the problem, thank you!

In my original post, I have a link at the bottom to a Google Drive folder that should have all of the files in it. Is it accessable to you?


Re: Macro/Query Failure after updating to split database

PostPosted: Wed Oct 23, 2019 4:28 pm
by Villeroy
Your script file shows that 2 fields representing a time are DOUBLE.
Code: Select all   Expand viewCollapse view
CREATE CACHED TABLE PUBLIC."PackLocationsTable"("PackNumber" CHARACTER(6) NOT NULL PRIMARY KEY,"Location" CHARACTER(8) NOT NULL,"Timestamp" DOUBLE,"Previous Location" CHARACTER(8),"PreviousTimestamp" DOUBLE)

Re: Macro/Query Failure after updating to split database

PostPosted: Wed Oct 23, 2019 8:46 pm
by Villeroy
Copy the table icon.
Open a new spreadsheet.
Format the numbers as YYYY-MM-DD HH:MM:SS
Copy the cells.
Delete the database table.
Paste the spreadsheet cells to the database tables.
A wizard pops up where you enter the table name and choose to import data and structure.
First row has column headers.
Assign proper data types to the columns.
Don't let the wizard add a primary key.
Right-click>Edit the new table and mark the PackageNumber as primary key.

Re: [SOLVED]Macro/Query Failure after updating to split data

PostPosted: Thu Oct 24, 2019 3:14 am
by aterbo
Villeroy, thank you!

It was the dates. As you pointed out, the original pre-split database version had a "Timestamp" column as a double, which I populated using CURRENT_TIMESTAMP. This worked fine in the previous version of HSQLDB (1.8?), but threw an error in the updated version using HSQLDB 2.4.x.

I rebuilt the table using a separate DATE and TIME column (probably unnecessary, but it seemed easier to use two) as proper DATE and TIME types. I made a few modifications to the macro and form to point to the new columns, and it worked perfectly.

The problem was not related to the WHERE clause or aliases.

As an aside, I am pretty sure I built the original using Double instead of Timestamp following some SQL advice I found online that promoted storing dates/times as such. I remember it being an active decision to use Double. Going forward, is it more appropriate to use the built in TIMESTAMP, DATE, and TIME types?

Thank you again!

Re: [SOLVED]Macro/Query Failure after updating to split data

PostPosted: Thu Oct 24, 2019 1:06 pm
by Villeroy
I can not confirm that embedded HSQL dynamically converts between times and doubles.
UPDATE "Table" SET "DblField"=CURRENT_DATETIME raises the same error about wrong data types.
With Base you can enter times into double fields. Base treats the doubles like spreadsheet cells and passes over the required type to the database.
You can enter 2009-7-6 18:00 and Base passes 40,000.75 to the double field (day #40,000 plus 3/4 of a day). This works with ebedded HSQL and with stand-alone HSQL 2x. But you can not tell HSQL directly to enter a date/time into a double field.