Strange result form SQL command

Classic Servoy
1

Hi,
I have the problem with result from getDataSetByQuery.
The script is:

var maxReturnedRows = 100
var query = 
	"SELECT 'Text1', ' ' "+
    "UNION " +
	"SELECT 'Text2', SUM( IF( stueck <5, stueck, 0 )) AS ' ' "+
	"FROM `vip` "+ 
	"WHERE `vipid` IN ('123') " 
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows)

In the dataset the numbers are like this:

[B@b328e1

This command is not working in Servoy, but I tested it in phpMyAdmin and MySQL Query Browser - result is OK.
What can be wrong?

Regards

Version 3.5.6-build 519
Java version 1.5.0_13-121 (Mac OS X 10.4.11)
MySQL 5.0.51b
mysql-connector-java-5.0.8-bin.jar

2

Hi agis,

What do you do to see the [B@b328e1 value?
Normally you get a value from a dataset using the getValue(row, column) function.

3

Hi Robert,

yes we did it with dataset.getValue(row, column) and dataset.getAsText
allways with these strange results.
Also with getRowAsArray the Result is hte same while the SQL-Statement it self returns the expexted Nummeric results (for example a Number like 32) in SQL-Tools as phpMyAdmin and MySQL Query Browser.

Could you imagine what’s wrong?

4

Hi agis,

Seems the query returns a data type that is neither a numeric, integer, string or a date.
This sure sounds like an issue with the jdbc driver or combination jdbc version and database server version. But from what I see you are using the latest version.
But you do use an older (beta even) MySQL server version. Version 5.0.67 is out, you might want to update it.

What happens when you cast the sum into a signed integer using the following query?

var query = 
   "SELECT 'Text1', ' ' "+
    "UNION " +
   "SELECT 'Text2', Cast( SUM( IF( stueck <5, stueck, 0 )) AS SIGNED ) AS ' ' "+
   "FROM `vip` "+ 
   "WHERE `vipid` IN ('123') " 
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows)

Also why do you use an space as alias-name for the second column in the second query ?

5

Hi,

With CAST() statement:

var query =
   "SELECT 'Text1', ' ' "+
    "UNION " +
   "SELECT 'Text2', Cast( SUM( IF( stueck <5, stueck, 0 )) AS SIGNED ) AS ' ' "+
   "FROM `vip` "+
   "WHERE `vipid` IN ('123') "
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows)

The result is the same:
[attachment=2]Picture 8.png[/attachment] [attachment=0]Picture 11.png[/attachment]

When i remove the first SELECT statement and UNION

var maxReturnedRows = 100
var query =
   "SELECT 'Text2', SUM( IF( stueck <5, stueck, 0 )) AS ' ' "+
   "FROM `vip` "+
   "WHERE `vipid` IN ('123') "
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows)

then result is OK:
[attachment=1]Picture 9.png[/attachment]

Also why do you use an space as alias-name for the second column in the second query ?

Because i want to have empty column name, when i use only second SELECT statement. You are right, makes no sence after UNION.

I check “servoy-admin” page, no errors are shown.
Seems the result is not correct only from Servoy. I try 3 different versions of jdbc driver without success.

Picture 11.png
Picture 11.png149×101 372 Bytes

Picture 9.png

Picture 8.png