Problem with deleteAllRecords() method

Forum to discuss the new web client version of Servoy.

Problem with deleteAllRecords() method

Postby francisco.garcia » Fri Feb 04, 2022 8:44 pm

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:
Code: Select all
   /** @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:
Code: Select all
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.
francisco.garcia
 
Posts: 16
Joined: Tue Jan 09, 2018 3:40 pm

Re: Problem with deleteAllRecords() method

Postby pitc » Fri Feb 04, 2022 10:51 pm

HI, not sure if you are using Postgresql but I found this link that might help you:
https://stackoverflow.com/questions/27865770/how-long-can-postgresql-table-names-be
Note default is 63 bytes. But if you use non-ASCII (UTF) the limit might be half of that.
Tom
prospect-saas.com
pitc
 
Posts: 115
Joined: Thu Nov 14, 2019 2:22 pm
Location: Ottawa, Ontario, Canada

Re: Problem with deleteAllRecords() method

Postby jcompagner » Mon Feb 07, 2022 2:29 pm

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)
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Problem with deleteAllRecords() method

Postby jcompagner » Tue Feb 08, 2022 10:16 am

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?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Problem with deleteAllRecords() method

Postby francisco.garcia » Thu Feb 10, 2022 1:56 pm

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.
francisco.garcia
 
Posts: 16
Joined: Tue Jan 09, 2018 3:40 pm

Re: Problem with deleteAllRecords() method

Postby jcompagner » Thu Feb 10, 2022 2:17 pm

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 ?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Problem with deleteAllRecords() method

Postby sbutler » Thu Feb 10, 2022 7:22 pm

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:

Code: Select all
delete from <alias> <table> where <alias>.<field>...


but for MySQL it should be

Code: Select all
delete <alias> from <table> <alias> where <alias>.<field>...


Or

Code: Select all
delete from <table> USING '<alias>' AS <table> where <alias>.<field>...
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH


Return to Servoy NGClient

Who is online

Users browsing this forum: Google [Bot] and 9 guests

cron