Problem with deleteAllRecords() method

Questions
1

Hi.
I’m facing an issue when i call the deleteAllRecords() method over a foundset that points to a table whose name has over tan 30 characters. It started to happen in 2021.12.1 update and it keeps the same in 2021.12.2.
The exact case is the following. I have a Mariadb table named factc_ind_calidad_rechazados_lotes and i execute the following code:

	/** @type {JSFoundset<db:/qdw/factc_ind_calidad_rechazados_lotes>}*/
	var fs_factc_ind_calidad_rechazados_lotes = databaseManager.getFoundSet('qdw','factc_ind_calidad_rechazados_lotes')
	fs_factc_ind_calidad_rechazados_lotes.find()
	fs_factc_ind_calidad_rechazados_lotes.elab_dc_aprob_calidad_fec = utils.dateFormat(tmp_fecha_inicial,'yyyy-MM-dd') + ' 00:00:00...' + utils.dateFormat(tmp_fecha_final,'yyyy-MM-dd') + ' 23:59:59|yyyy-MM-dd HH:mm:ss'
	fs_factc_ind_calidad_rechazados_lotes.search()
	
	if (fs_factc_ind_calidad_rechazados_lotes.getSize() > 0) {
		fs_factc_ind_calidad_rechazados_lotes.deleteAllRecords()
	}

After that i get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'factc_calidad_rechazados_lotes where factc_calidad_rechazados_lotes.elab_dc_a...' at line 1
Query is: delete from factc_ind_calidad_rechazados_lotes factc_calidad_rechazados_lotes where factc_calidad_rechazados_lotes.elab_dc_aprob_calidad_fec between ? and ?, parameters ['2019-01-01 00:00:00.0','2022-02-04 23:59:59.0']

As you can notice the query is wrongly typed. This is one example but it happens me with all the tables whose name has over tan 30 characters.
Please help.

2

HI, not sure if you are using Postgresql but I found this link that might help you:

Note default is 63 bytes. But if you use non-ASCII (UTF) the limit might be half of that.

3

what happens if you are in a another sql tool send this:

delete from factc_ind_calidad_rechazados_lotes factc_calidad_rechazados_lotes where factc_calidad_rechazados_lotes.elab_dc_aprob_calidad_fec between ? and ?

to the database as is? (filling in the question marks)

does the database wants quotes? (around the column names)

4

you say it started to happen with 12.1 but i guess you didn’t test it with 12.0 ?

can you make a case for this?

5

Hi Johan.
Thanks for the reply.
I haven’t tested it on 12.0 so i cannot tell. If i execute the exact same query on Mysql Workbench it gives me the same error. It’s like the query gets malformed when the table name is more than 30 characters long.
I’ll make a case.
Thanks.

6

but what do you need to do to make that query work in Mysql Workbench?
do you need to quote the column names?

delete from “factc_ind_calidad_rechazados_lotes” “factc_calidad_rechazados_lotes” where “factc_calidad_rechazados_lotes.elab_dc_aprob_calidad_fec” between ? and ?

or is it that we generate that alias?
so does this work:

delete from factc_ind_calidad_rechazados_lotes where factc_calidad_rechazados_lotes.elab_dc_aprob_calidad_fec between ? and ?

7

Johan, Just a guess, but I think its using the wrong syntax for MySQL: https://stackoverflow.com/questions/104 … 9#26935749

Looks like its doing this:

delete from <alias> <table> where <alias>.<field>...

but for MySQL it should be

delete <alias> from <table> <alias> where <alias>.<field>...

Or

delete from <table> USING '<alias>' AS <table> where <alias>.<field>...