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

[aterbo]

Hello,

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
https://ask.libreoffice.org/en/question ... 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

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: incompatible data type in conversion

This line of code is throwing the error:

Code: Select all

RunSQL = oQuery.executeUpdate(SQLQuery)

The string SQLQuery is as follows, where I believe the error is originating:

Code: Select all

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
viewtopic.php?f=40&t=95149&p=454227&hil ... se#p454227

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.

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.

[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.

[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?

Thanks,
Andy

[Villeroy]

Your script file shows that 2 fields representing a time are DOUBLE.

Code: Select all

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)

[Villeroy]

Copy the table icon.
Open a new spreadsheet.
Paste
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.
[Next]
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.

[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!

[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.