SQL statement fails after migration to MYSQL

[ulihueck]

Hi,
I’m trying to migrate my database from HSQLDB to MYSQL. All data and tables were imported to my new database with same table-names and same column-names.
But when I run my existing Basic script, I get an error:

I did not change the sql-statement:

"INSERT INTO "Rechnungen" ("Rechnungs_ID", "Menge", "Preis","Kunden_ID", "Gesamt", "Datum", "Bezahlt" , "Verschickt", "Nachweis_ID", "Leistungs_ID", "Purchase_Order", "Kundenreferenznummer", "PSG_Bestellnummer")( SELECT 237,  "Leistungsnachweis"."Menge", "Preise"."Preis", "Kunden"."Kunden_ID", "Preis" * "Menge" "Gesamt", "Leistungsnachweis"."Datum", 0, 0, "Leistungsnachweis"."Nachweis_ID", "Leistungsnachweis"."Leistungs_ID", "Leistungsnachweis"."Purchase_Order", "Abweichende_Rechnungsdaten"."Kundenreferenznummer", "Abweichende_Rechnungsdaten"."PSG_Bestellnummer" FROM "Leistungsnachweis" "Leistungsnachweis", "Kunden" "Kunden", "Preise" "Preise", "Leistungen" "Leistungen", "Abweichende_Rechnungsdaten" "Abweichende_Rechnungsdaten" WHERE "Leistungsnachweis"."Kunden_ID" = "Kunden"."Kunden_ID" AND "Preise"."Kunden_ID" = "Kunden"."Kunden_ID" AND "Preise"."Leistungs_ID" = "Leistungen"."Leistungs_ID" AND "Leistungsnachweis"."Datum" = "Leistungsnachweis"."Datum" AND "Preise"."Kunden_ID" = "Leistungsnachweis"."Kunden_ID" AND "Preise"."Leistungs_ID" = "Leistungsnachweis"."Leistungs_ID" AND "Kunden"."Kunden_ID" = "Abweichende_Rechnungsdaten"."Kunden_ID" AND  "Leistungsnachweis"."abrechenbar"= true AND "Kunden"."Kunden_ID" =56 AND "Leistungsnachweis"."Datum" <= {D '2023-11-04'  } AND NOT "Leistungsnachweis"."Nachweis_ID" IN ( SELECT "Rechnungen"."Nachweis_ID" FROM "Rechnungen" ) AND "Abweichende_Rechnungsdaten"."ID" IN ( SELECT MAX( "Abweichende_Rechnungsdaten"."ID" ) FROM "Abweichende_Rechnungsdaten" WHERE "Abweichende_Rechnungsdaten"."Kunden_ID" = 56))"

When I execute the SQL in the SQL-window, it works like it worked before.
Thank you for your help.
Regards,
Uli

[RobertG]

Why do you post it in English? Could better explain it in German…

Error message says: “Rechnungs_ID” not found. Might be there is also a problem to find table “Rechnungen”, because there is no database name for the table.
INSERT INTO "test"."Rechungen" ("Rechnungs_ID",…
might be the code when using direct connection. But direct connection is a connection to MariaDB. Might be it isn’t working well with MySQL. So: Which connection do you use?

[iplaw67]

It might help us to see the relevant part of the macro leading up to the firing of the prepared statement.

The error message points to a JDBC connection to the mysql instance, so you might want to give us the JDBC driver name and driver version as well.

Quite possibly, there is a difference between how the statement is executed in the macro execution environment and how it is executed in the Tools>SQL window.

[ulihueck]

Thank you for your suggestion.
I had to find an other name of the database-connection.