SQL Error when trying to copy a table

[LSemmens]

I have a split database and have copied data and structure from this particular table numerous times. Now I cannot perform this simple task without getting the following

SQL Status: HYC00

The driver does not support the functionality for 'XParameters::setClob'. It is not implemented.

my procedure is always

1 Open source table for editing

2 Create table in design view

3 copy the structure across

4 ensure the primary field is set to auto increment

4 save and close both tables

5 drag source table over new table to append data

6 ensure that the fields all correlate correctly

8 click on create

It worked every time until tonight. I do suspect that it may have something to do with the corrupt database that I had to restore - (the subject of my question about data backup in another thread)

It has occured to me that there might be a duplicate record in a supposedly unique field, Is there a SQL command to detect duplicates?

[Ratslinger]

Hello,

First a note. When posting a question, please include OS, specific LO version, and in the case of Base the database type used. This can help resolving problems. The OS & LO versions may have bug issues either currently opened or resolved in a version you are not using. The database is important because of differences in SQL and functionality.

This is helpful to have in each question asked. Even if posted in a previous question, then the person trying to help must search for this info. Did find you are on windows 10 in one question and I believe LO 6.0.1.x. But even finding this, you may have changed since those question(s) were asked.

Please provide now and all future questions.

[Ratslinger]

In addition to the info of the previous comment needed, a split DB can use a variety of HSQLDB (or Firebird since that is a possibility) versions. Please include the DB version used.

Now your question deals with copying a table. You do not state the ‘from’ and ‘to’ databases. This can certainly be different. Even embedded to say PostgreSQL. Please specify yours. Also do not know the field types involved. Your error seems to indicate use of a type ‘Clob’ field. This can vary in different databases. Please provide.

There is no specific SQL function to detect duplicates. An SQL statement would need to be constructed comparing records against each other. Constructing would require specifics of the table structure.

[LSemmens]

My apologies, I did not think that there were any replies to this thread not having looked at e-mail for a couple of days,

 LO : Version: 6.0.7.3   `Build ID: 1:6.0.7-0   ubuntu0.18.04.10   CPU threads: 4;   OS: Linux 4.15;   UI render: default;   VCL: gtk3;    Locale: en-AU (en_AU.UTF-8);   Calc: group

How do I determine the version of HSQLDB I am using?

All I am doing is as per my first post all within the same database by creating an identical table and copying the data across by using the “Copy Table” tool and selecting append data.

[Ratslinger]

Based upon your just duplicating the table with the same database, I can’t imagine any way to determine what is causing your problem without the actual DB. Can’t even find a reference to the error you received.

I understand your approach (not why - that is not a concern) to the method of copying you use. Here would be my attempt to find the cause. All this is using a copy of the database. First see if you can simply copy and paste the entire table - not the step-by-step. If that works there is something in you steps gone awry.

Another measure, create new table definition and using just a few records from original table (delete others - this is testing) and see if successful. If OK see about increasing number of records tried.

As stated, not sure what is specific problem without looking at everything.

[Ratslinger]

Just a note about your not checking emails. You commented another post earlier. At that time you should have seen there were other notifications. This is noted by the red envelope to the right of your user name after signing in. Clicking on that takes you directly to notifications.

If you mark all notifications as viewed (or delete them) the red envelope is no longer displayed.

[LSemmens]

Thanks Ratslinger, I did not know about the envelope bit. The only reason for copying the table is to modify it (usually adding more fields) and because it is a split database the table is locked from editing, this is the only way I have found to modify the structure of an existing table. If there is a better way, I would love to find out.

I tried the copy & paste method and that failed too.

I suspect that there may be a duplicate record in there that (by the structure of the indexes) should not be permitted, but a scan through the table did not find any (there are only about 5000 records so far). Is there an SQL statement that could find duplicates?

It has suddenly started working again. I started by deleting records to try and identify a recalcitrant record and narrowed it down to 250 records. I restored the data yet again to narrow it down further and thought I’d check to see if the problem still occurs and it doesn’t Stuffed if I know why- but, for now problem solved!

[LSemmens]

I found a more recent copy of the data so i played with that, for some reason the last 50 records were corrupted. Nothing obvious, but if I attempted to modify any one of those records It would throw up a similar message to the one above. Deleting those records seem to solve the problem.

[Ratslinger]

@LSemmens,

Sounds like things are working. You don’t usually need to create & and copy even though editing can’t be done on existing. For existing fields SQL can be used to modify. They can also be deleted and new fields can be added. Please see this recent post → Split Base DB. Field is Decimal. Decimal places was 2, now it’s 0??? How to fix it!!!

[LSemmens]

Thanks mate, I have learnt a heap over this issue. I’m now almost back to “normal”. Silly me, did not realise how much I had modified the structure and the data in the two days since the backup. I’ve now got a few forms to modify and I’ll be back up and running again. I’ll flag this as solved now.