Page 1 of 1
How to display data using SQL query
Posted:
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
Posted:
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
Posted:
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
Posted:
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
Posted:
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
Posted:
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
Posted:
Fri Jul 13, 2012 4:27 pm
by ptalbot
createDataSource is a function of JSDataSet:
- createDataSource.png (27.92 KiB) Viewed 8190 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
Posted:
Mon Jul 16, 2012 5:38 pm
by hardina09
Thank you Patrick