SQL issue - sqlite3

Databases
1

I have a SQL command that works on SQLite databases fine. However when I try to run it in Xojo, I get database is malformed error. Works ok in anything else?

here is the structure
CREATE TABLE files (
filename TEXT,
filepath TEXT PRIMARY KEY,
filepath_hash TEXT,
filesize INTEGER,
magic TEXT,
modifydate TEXT
)

here is the code:
SELECT
filename,
filepath,
rowid,
CASE
WHEN years > 0 THEN years || ’ years, ’
WHEN months > 0 THEN months || ’ months, ’
WHEN days > 0 THEN days || ’ days, ’
ELSE hours || ’ hours’
END || ’ ago’ AS modification_duration,
CASE
WHEN filesize >= 1099511627776 THEN ROUND(filesize / 1099511627776.0, 2) || ’ TB’
WHEN filesize >= 1073741824 THEN ROUND(filesize / 1073741824.0, 2) || ’ GB’
WHEN filesize >= 1048576 THEN ROUND(filesize / 1048576.0, 2) || ’ MB’
WHEN filesize >= 1024 THEN ROUND(filesize / 1024.0, 2) || ’ KB’
ELSE filesize || ’ bytes’
END AS human_readable_size
FROM (
SELECT
files.filename,
files.filepath,
files.rowid,
files.modifydate,
files.filesize,
CAST((JulianDay(‘now’, ‘+01:00’) - JulianDay(datetime(files.modifydate))) / 365 AS INT) AS years,
CAST(((JulianDay(‘now’, ‘+01:00’) - JulianDay(datetime(files.modifydate))) % 365) / 30 AS INT) AS months,
CAST(((JulianDay(‘now’, ‘+01:00’) - JulianDay(datetime(files.modifydate))) % 30) AS INT) AS days,
CAST((strftime(‘%H’, ‘now’, ‘+01:00’) - strftime(‘%H’, datetime(files.modifydate))) AS INT) AS hours
FROM files
WHERE files.filename LIKE ‘%sound%’ AND files.filename NOT LIKE ‘%afx%’
ORDER BY files.filename ASC
) subquery
ORDER BY datetime(modifydate) DESC;

2

It does not seem related to your query, but because your DB is corrupted.

3

I was thinking that, but it works in all other SQL engines, and I copied the data out and back into a new one, same issue.

4

Dump your file as SQL, let XOJO create it from from 0 to 100%, and try again. This is specially true if a SQLite file was encrypted. When using different cyphers, for those not matching they are just garbage and don’t even open, for those matching they work.

5

I’ve exported all 12 million rows, created a new DB and reading them back in. Fingers crossed.

6

If the SQL is ok, it will be good.

7

Trying
PRAGMA integrity_check(integrity_check_fix);
fingers and toes crossed!
im surprised other sql clients don’t report any corruption with the same query odd?

8

If the database was encrypted, then it’s not really that odd.

9

No encryption. integrity_check just reported no errors.

10

Copied everything to another database file. Same error. Wierd.

11

Even sqlabs from 2018 works ok.

12

Wonder if there is a limit on the sql command length in Xojo, I will start reducing the complex query to a basic one.

13

Again, the above message has no connection with a QUERY

If you make a copy of it and delete lots of rows then VACUUM it, making it just a simple small sample, does it retain the “database malformed” behavior for Xojo? If yes, share a zipped sample.

14

Found the issue, its to do with the Xojo copy files in the build environment. For some reason when it copies a 14GB file from the previous directory it becomes corrupt. Removed it from the copy and pointed directly to the dev environment and it works ok. Will need to build into the installer to copy the data file and not let the IDE do it.
Solved!

15

Kind of. You may have found a bug. A bug Xojo never noticed because no one never had a 14GB resource file before? It could be interesting reporting it.

16

need to make a 14GB data file cleaned from personal info and upload it!

17

It would be interesting to know how it was corrupted though, as in, was it truncated to a particular length, or missing the first few bytes, or something else

18

I own it but have never used it, but SQL Doctor (SQLiteDoctor: The life saver sqlite database repair tool) might help in future, but not if the copying causes the corruption.

Maybe include a Zipped version of the database as unencrypted SQLite DB’s seem to compress quite well.