We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

base crash query parameters

asked 2020-12-25 21:25:42 +0200

BrianStew gravatar image

updated 2021-05-27 13:48:19 +0200

Alex Kemp gravatar image

I am having problems with base crashing very frequently. It mostly happens when running a query which requires 2 date parameters and generally happens when the 1st digit of the 2nd date is entered. It doesn't happen every time and generally the first time the query is run everything works OK. This usually happens the 2nd or 3rd time the query is run and almost always happens with the 1st digit of the second date parameter. Recovery always seems to work but heart drops everytime it crashes. I always make a backup copy of the database file before running as it virtually always crashes. Any help would be really appreciated. Is there a way to access any error report generated by the crash to find the cause?

Version: 6.4.6.2 Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded

Running embedded HSQLDB. Tried with embedded firebird and same thing happens.

Removed all Libreoffice suite and re-installed with snap 7.0.2.2 and still happens with HSQLDB and firebird. The problem area is probably associated in the WHERE section as this is where the date parameters are requested.

The full HSQLDB Query is :-

SELECT "Bookings"."Booking_Ref" AS "Booking_Ref", "Bookings"."Agent" AS "Agent", "Bookings"."CustomerID" AS "CustomerID", "Bookings"."StartDate" AS "StartDate", "Bookings"."EndDate" AS "EndDate", "Bookings"."Comments" AS "Comments", "Bookings"."RecvdDate" AS "RecvdDate", "Bookings"."ElecDayStart" AS "ElecDayStart", "Bookings"."ElecNightStart" AS "ElecNightStart", "Bookings"."ElecDayEnd" AS "ElecDayEnd", "Bookings"."ElecNightEnd" AS "ElecNightEnd", "Bookings"."Status", "Customers"."Name" FROM "Bookings", "Customers" WHERE "Bookings"."CustomerID" = "Customers"."CustomerID" AND ( "Bookings"."StartDate" >= :Start_Date_From AND "Bookings"."StartDate" <= :Start_Date_To OR "Bookings"."StartDate" >= :Start_Date_From AND :Start_Date_To IS NULL OR "Bookings"."StartDate" <= :Start_Date_To AND :Start_Date_From IS NULL OR :Start_Date_To IS NULL AND :Start_Date_From IS NULL ) ORDER BY "StartDate" ASC

This query wont work with firebird and had to change to use CAST format for NULL's. ie CAST( :Start_Date_From AS DATE ) IS NULL.

@Ratslinger This happens to be the query which I run inside another query which I reported as failing due to the ORDER BY a few days ago when I also mentioned the frequent crashing under "LIBRE BASE Query won't run" where I mistakenly added as an answer rather than a comment.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-12-25 22:15:48 +0200

Ratslinger gravatar image

Hello,

When I set the second test, I got Base to crash. However, maybe did something incorrect. Have since tried dozens of times and no problems. Tried many different & same entries and no further crashes (at least a few dozen tests).

Here is my test file -> ForumQuery.odb

edit flag offensive delete link more

Comments

Hi @Ratslinger I can't believe you looked at this and put in effort today Christmas day. Thank you lots.

I tried with your odb file and yes. It crashed on entering the first digit of the 2nd date field for me on the first attempt, but was OK on the next 4 attempts. I closed the database and re-opened it and the same thing happened.

At least you have been able to duplicate the crash I'm having, it's always when entering the 1st digit of the second date parameter.

Is there a way to view the crash report to figure out what's causing this? It's happening on 2 versions of Base and seems to be independent of the embedded database engine.

There may be a better way to SQL the date checking I'm doing. I want to enter date_from, date_to or leave either or ...(plus)

BrianStew gravatar imageBrianStew ( 2020-12-25 23:26:19 +0200 )edit

@BrianStew,

You're welcome but I believe you may have not understood. The ONE and ONLY crash I got was on the second execution. Have done dozens of runs since without a crash. Even closed Base & re-opened multiple times without a problem. This on Ubuntu 20.04 Mate with LO 7.0.4.x from TDF.

If you continue with problems, first try resetting the user profile -> LibreOffice user profile. If further problems, file a bug report -> Bugzilla.

Firebird does take some adjusting to. There are many post here. Just search on Firebird. Once past initial hurdles it gets easier. Firebird should fix your Query calling Query situation.

Ratslinger gravatar imageRatslinger ( 2020-12-25 23:56:04 +0200 )edit

@BrianStew,

Was not fond of your Where portion of the SQL. Try this:

WHERE ("Bookings"."CustomerID" = "Customers"."CustomerID")
AND   (("Bookings"."StartDate" >= :Start_Date_From AND "Bookings"."StartDate" <= :Start_Date_To)
      OR ("Bookings"."StartDate" >= :Start_Date_From AND :Start_Date_To IS NULL)
      OR ("Bookings"."StartDate" <= :Start_Date_To AND :Start_Date_From IS NULL)
      OR (:Start_Date_To IS NULL AND :Start_Date_From IS NULL))
ORDER BY "StartDate" ASC
Ratslinger gravatar imageRatslinger ( 2020-12-26 02:56:08 +0200 )edit

@Ratslinger, Sorry for any misunderstanding. I did understand that it crashed for you only once. Initially when it crashed for me I thought i'd made an input mistake but i've discovered that it always happens at the same point and incorrect date entry is caught anyway. Sadly this seems to be very intermittent and intermittent issues are always difficult.

I totally agree with you on not being fond of the Where portion of the SQL, adding parenthesis definitely makes it more readable. Sadly if you then enter graphical edit view (rather than SQL view) and save any changes, it removes them reverting to the original.

I still can't find where any error logs are. Does Libreoffice create log files?

BrianStew gravatar imageBrianStew ( 2020-12-27 14:33:28 +0200 )edit

@BrianStew,

Only know of crash reports and have not tried to do anything with them, so no help in that regard.

Still has not crashed again with that SQL. Runs each time I try it.

I also avoid the graphical view. It is limited, it has a number of problems and certainly does not work with many of the databases I use. Stick with SQL view as you can do more with this and is easier to transfer knowledge to other tools.

Ratslinger gravatar imageRatslinger ( 2020-12-27 18:15:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-12-25 21:25:42 +0200

Seen: 60 times

Last updated: Dec 25 '20