var serverName = "foto";
var sql = "DESCRIBE foto";
application.output(sql);
var dataset = databaseManager.getDataSetByQuery(serverName, sql, null, -1);
var myField = dataset.getValue(1,1);
In the debugger JSdataset: size:0
and so the getValue failed…
If I try to do a “SELECT id …” from the same table and all works fine…
I use mysql 4.1.21 with mysql-connector-java-5.0.4-bin.jar
the table collation is utf8_general_ci
Any suggestion?
describe is a MySQL function and not standard SQL.
What do you want actually? If you want a list of all the tables, you can use:
databaseManager.getTableNames(controller.getServerName());
I want the type of an ENUM field of a table for create a valuelist to bind to a combobox …
It’s a new bug (or feature?) introduced with Servoy 3.1.
I used to get infos about the DB using MySQL functions like ‘SHOW PROCESSLIST;’, ‘SHOW MUTEX STATUS;’, SHOW TABLE STATUS;', etc.
All those functions returns a dataset, example:
mysql> SHOW PROCESSLIST;
+-----+--------+-----------------+--------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------------------+---------+------+-------+------------------+
| 220 | root | localhost | sprint | Query | 0 | NULL | SHOW PROCESSLIST |
| 221 | servoy | localhost:63336 | servoy3_repository | Sleep | 150 | | NULL |
| 222 | servoy | localhost:63341 | servoy3_repository | Sleep | 150 | | NULL |
| 223 | root | localhost:63342 | sprint | Sleep | 157 | | NULL |
| 224 | root | localhost:63343 | sintpro_mods | Sleep | 149 | | NULL |
| 225 | root | localhost:63344 | sprint_batch | Sleep | 156 | | NULL |
| 226 | root | localhost:63345 | sprint_allegati | Sleep | 156 | | NULL |
| 227 | servoy | localhost:63346 | netmanager | Sleep | 156 | | NULL |
| 228 | servoy | localhost:63348 | sintpro_files_mod | Sleep | 156 | | NULL |
| 229 | servoy | localhost:63349 | servoy_log_server | Sleep | 156 | | NULL |
| 230 | root | localhost:63350 | asp_sintpro | Sleep | 156 | | NULL |
+-----+--------+-----------------+--------------------+---------+------+-------+------------------+
11 rows in set (0.00 sec)
Or:
mysql> SHOW TABLE STATUS;
+------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
| articoli | InnoDB | 10 | Compact | 4233 | 623 | 2637824 | 0 | 1982464 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| bancali | InnoDB | 10 | Compact | 30 | 546 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| catalogo | InnoDB | 10 | Compact | 37 | 9299 | 344064 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| colli | InnoDB | 10 | Compact | 793 | 103 | 81920 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| customqueries | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| ddt | InnoDB | 10 | Compact | 335 | 293 | 98304 | 0 | 16384 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| ddt_contenuti | InnoDB | 10 | Compact | 265 | 61 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| difetti | InnoDB | 10 | Compact | 3043 | 134 | 409600 | 0 | 81920 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| disposizioni | InnoDB | 10 | Compact | 80 | 204 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| disposizioni_elenco | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| disposizioni_righe | InnoDB | 10 | Compact | 126 | 130 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| etichette | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| grades | InnoDB | 10 | Compact | 439 | 149 | 65536 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| liste_valori | InnoDB | 10 | Compact | 108 | 151 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| listini_acquisto | InnoDB | 10 | Compact | 20287 | 78 | 1589248 | 0 | 2506752 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| listini_vendita | InnoDB | 10 | Compact | 1170 | 112 | 131072 | 0 | 147456 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| messages | InnoDB | 10 | Compact | 8 | 2048 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| off_vendita | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| off_vendita_righe | InnoDB | 10 | Compact | 18 | 910 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| offerte_acquisto | InnoDB | 10 | Compact | 317 | 310 | 98304 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| offerte_acquisto_righe | InnoDB | 10 | Compact | 3131 | 88 | 278528 | 0 | 229376 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| opzioni | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| partners | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| procedure_sprint | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| processing_costs | InnoDB | 10 | Compact | 64 | 256 | 16384 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| tests | InnoDB | 10 | Compact | 1017 | 177 | 180224 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
| ubicazione | InnoDB | 10 | Compact | 1447 | 67 | 98304 | 0 | 0 | 0 | NULL | 2007-01-11 16:27:39 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 5120 kB |
+------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
27 rows in set (0.16 sec)
They worked fine until 3.1 and in conjuction with the datagrid it was also easy to present the results:
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), "SHOW PROCESSLIST;",null,1000)
globals.g_mysql_title = 'MySQL Connections ('+ utils.dateFormat(application.getTimeStamp(), 'dd-MM-yyyy hh:mm:ss')+')'
elements.bean_mysqlConnections.setModel(dataset.getAsTableModel())
Servoyans: is there something you can do to bring this functionality back?
Hi Nicola, I met you in Verona abount 1 month ago (I’m the young boy)
Do you remember?
No other way to extract a string like this: “enum(‘type1’,‘type2’,‘type3’)” ?
axterics:
Hi Nicola, I met you in Verona abount 1 month ago (I’m the young boy)
Do you remember?No other way to extract a string like this: “enum(‘type1’,‘type2’,‘type3’)” ?
Hi! I hope you are doing well with Servoy.
So you want to manage the value list on the backend. I suppose you have a reason for that (maybe another app using the backend?).
If the team doesn’t bring back that functionality I guess there are two ways to accomplish this:
var done = plugins.rawSQL.executeSQL("example_data","tmp_table","insert into tmp_table values ('1', (describe mytable;));")
if (done)
{
var dataset = databaseManager.getDataSetByQuery(example_data, "select describe_column from tmp_table where tmp_table_id = 1;",null,1)
var describe_output = dataset.getValue(1,1)
}
Then you can parse describe_output using the text functions in the Utils node or RegExp to extract your ENUM list.
Hope this helps.
So you want to manage the value list on the backend. I suppose you have a reason for that (maybe another app using the backend?).
In the actual DB structure some table have enum fields…
From frontend I can make relations or select all values from a table but I can’t bring the enum options and assign them to a combobox…
(or maybe I don’t find this functionality…)
In other cases (state/province for example but there is a table for each combobox in this case) I use the frontend…
The alternative is set a custom valuelist corrisponding to the enum options but, in the case of the enum, a change in the backend don’t reflect in frontend (if I add an option of an enum for example…)
axterics:
The alternative is set a custom valuelist corrisponding to the enum options but, in the case of the enum, a change in the backend don’t reflect in frontend (if I add an option of an enum for example…)
You could do the opposite: define the valuelist in Servoy and update the ENUM definition in the backend each time the Servoy valuelist changes.
For this you can use the RawSQL plugin to fire an “ALTER TABLE” query and modify the ENUM definition every time that is needed.
You just need to figure out wich side (Servoy or MySQL) should be the Master and wich the Slave and take decisions accordingly.