:: java.lang.IllegalArgumentException :: ??

Classic Servoy
1

I’ve the following code to make a dynamic valuelist:

	var _date = utils.dateFormat(globals._gbtoday, 'yyyy-MM-dd')	  
																					
//---------------------------
	var _query  = "select DISTINCT h.hora" 
		_query += " 		from horas h"
		_query += "     where h.hora not in" 
		_query += "         (select hora"
		_query += "					from tab_pacientestoday" 
		_query += "						where fecha = to_date('" + _date +"','yyyy-MM-dd')" 
		_query += "							AND hora is not NULL)"
			
		
	application.output(_query);
	var _dataset = databaseManager.getDataSetByQuery('clinica',_query,null,-1)
	application.setValueListItems('hora_db',_dataset);

But, when i select some options of the valuelist show me this errors in console:

Setting dataprovider with name ‘hora’, type ‘DATETIME’ with value of wrong type 08:30:00

java.lang.IllegalArgumentException: Setting dataprovider with name ‘hora’, type ‘DATETIME’ with value of wrong type 08:30:00

in table structure i have:
hora = TIME

in form hora field i have:
hora = COMBOBOX (editable=off / format=hh:mm a)

in tab panel i have
hora = FIELDTEXT (editable=off / format=hh:mm a

What i’m doing wrong?

Thanks for help

2

in your query you give the date a format, which becomes a string!

you can’t set a string to a datetime field.

3

Harjo:
in your query you give the date a format, which becomes a string!

you can’t set a string to a datetime field.

The following function actually gives a date back out of the date-string you pass it. So this works correctly.

to_date('" + _date +"','yyyy-MM-dd')

I don’t think the error is a database error but instead a Servoy error. Servoy doesn’t seem to be able to handle a TIME datatype.
Perhaps you should cast it to a string in your query like so.

SELECT DISTINCT CAST(h.hora AS character varying)

Hope this helps.

4

Same error i got with SELECT DISTINCT CAST(h.hora AS character varying) :(

I’m thinking in change in data base structure directly: hora = TIME to DATETIME

What will be the code in this case, bearing in mind the original query code:

var _query  = "select DISTINCT h.hora" 
      _query += "       from horas h"
      _query += "     where h.hora not in" 
      _query += "         (select hora"
      _query += "               from tab_pacientestoday" 
      _query += "                  where fecha = to_date('" + _date +"','yyyy-MM-dd')" 
      _query += "                     AND hora is not NULL)"

Remember. I’m a complete n00b in this :oops:

thanks

5

Ah!
Reading your original post more carefully it’s not the query nor the valuelist that is the culprit. It’s the value you select and want to store in the DATETIME field.
I believe you can solve this by adding a (time-only) mask on the field (with that valuelist on it).

Hope this helps.

6

I’d make a little change in the query and fix the error :)

	var _query  = "select DISTINCT h.hora " 
		_query += " 		from horas h "
		_query += "     where h.hora not in " 
		_query += "         (select hora  "
		_query += "					from tab_pacientestoday " 
		_query += "						where fecha = to_date('"+utils.dateFormat(globals._gbtoday,'yyyy-MM-dd')+"','yyyy-MM-dd') " 
		_query += "							AND hora is not NULL)"

The Query show the valuelist and work as should be expected, now.

Thanks for yours replays :)