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

Creating tables and queries

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

Postby aterbo » Tue Oct 22, 2019 10:49 pm

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   Expand viewCollapse view
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   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
viewtopic.php?f=40&t=95149&p=454227&hilit=split+database#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. :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.
Last edited by robleyd on Thu Oct 24, 2019 3:37 am, edited 2 times in total.
Reason: Add green tick
Windows 10
LibreOffice Version: 6.3.2.2 (x64)
aterbo
 
Posts: 3
Joined: Tue Oct 22, 2019 10:33 pm

Re: Macro/Query Failure after updating to split database

Postby Villeroy » Wed Oct 23, 2019 3:47 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro/Query Failure after updating to split database

Postby aterbo » Wed Oct 23, 2019 3:55 pm

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
Windows 10
LibreOffice Version: 6.3.2.2 (x64)
aterbo
 
Posts: 3
Joined: Tue Oct 22, 2019 10:33 pm

Re: Macro/Query Failure after updating to split database

Postby Villeroy » Wed Oct 23, 2019 4:28 pm

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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro/Query Failure after updating to split database

Postby Villeroy » Wed Oct 23, 2019 8:46 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby aterbo » Thu Oct 24, 2019 3:14 am

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!
Windows 10
LibreOffice Version: 6.3.2.2 (x64)
aterbo
 
Posts: 3
Joined: Tue Oct 22, 2019 10:33 pm

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

Postby Villeroy » Thu Oct 24, 2019 1:06 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests