Page 1 of 1

How to display data using SQL query

PostPosted: Thu Jul 12, 2012 2:38 pm
by hardina09
Need to display data using with two join statement and substring.
Do I need to create:
1.Foundset and then load query in foundset and display in table view(non editable). I need to display only one field in form that too in formatted String. Thats reason I'm using sbustring in SQL query.
OR
2.Can I join table at design mode itself by creating relations with two table. Then how can I format the string. I don't know is which is the correct method to display data and how can I accomplish it.

Re: How to display data using SQL query

PostPosted: Thu Jul 12, 2012 6:29 pm
by hardina09
Anyways, I got to load foundset using dataset in the query. But it displays incorrect data.
I am getting below message for my SQL Query statement : "select substring(GLDescription,12,58) from GLDescriptionMaster join <further more query>"

I removed "Join" part in above query and executed "select substring(GLDescription,12,58) from GLDescriptionMaster" to check whether its throws an exception message, Yes, it throws : " Conversion failed when converting the varchar value 'K - NATIONAL CITY' to data type int.
Wrapped com.servoy.j2db.dataprocessing.DataException: Conversion failed when converting the varchar value 'K - NATIONAL CITY' to data type int."

Any suggestions why it throws exception.

Re: How to display data using SQL query

PostPosted: Thu Jul 12, 2012 7:16 pm
by ptalbot
When you load records in a foundset, the query you do must return primary keys.
In your case Servoy tries to use the text that is returned as a PK integer and can't convert, which is why this is failing.
If all you want is retrieving some arbitrary values, try databaseManager.getDataSetByQuery() instead.

Re: How to display data using SQL query

PostPosted: Thu Jul 12, 2012 7:24 pm
by hardina09
I did the same created dataset and passed as parameter to foundset. Still throwing same message.

Re: How to display data using SQL query

PostPosted: Thu Jul 12, 2012 7:52 pm
by ptalbot
Of course because your dataset doesn't contain primary keys!
To load a foundset from a dataset, your dataset must contain primary keys.

Alternatively, you can create a datasource from a dataset to use in a form:
Code: Select all
var ds = databaseManager.getDataSetByQuery(theServer, yourQuery, someParameterArray, -1);
var dataSource = ds.createDataSource(aDataSourceName, anArrayOfJSColumnTypes);

then use this on a form, retrieving it or creating it with the solutionModel:
Code: Select all
var jsForm = solutionModel.getForm(formName);
jsForm.dataSource = dataSource;

Re: How to display data using SQL query

PostPosted: Fri Jul 13, 2012 3:45 pm
by hardina09
When I tried code it gives me TypeError: Cannot find function createDataSource.

Re: How to display data using SQL query

PostPosted: Fri Jul 13, 2012 4:27 pm
by ptalbot
createDataSource is a function of JSDataSet:
createDataSource.png
createDataSource.png (27.92 KiB) Viewed 8077 times


So most likely the JavaScript parser didn't recognize that your variable is a dataset.
To make it understand and get rid of the stupid warning, you can do something like this:

Code: Select all
/** @type {JSDataSet} */
var ds = ... however you construct your dataset
var dataSource = ds.createDataSource(name, columns); // should be recognized correctly now.

Re: How to display data using SQL query

PostPosted: Mon Jul 16, 2012 5:38 pm
by hardina09
Thank you Patrick