How to display data using SQL query

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

How to display data using SQL query

Postby hardina09 » Thu Jul 12, 2012 2:38 pm

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.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to display data using SQL query

Postby hardina09 » Thu Jul 12, 2012 6:29 pm

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.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to display data using SQL query

Postby ptalbot » Thu Jul 12, 2012 7:16 pm

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.
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
http://www.servoy-stuff.net
https://www.servoyforge.net
--------------------------------------------
Servoy 5.2.16 / 6.0.9 / 6.1.6 / 7.3.1
All OSes / Java 5 & 6 & 7
User avatar
ptalbot
 
Posts: 1612
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: How to display data using SQL query

Postby hardina09 » Thu Jul 12, 2012 7:24 pm

I did the same created dataset and passed as parameter to foundset. Still throwing same message.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to display data using SQL query

Postby ptalbot » Thu Jul 12, 2012 7:52 pm

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;
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
http://www.servoy-stuff.net
https://www.servoyforge.net
--------------------------------------------
Servoy 5.2.16 / 6.0.9 / 6.1.6 / 7.3.1
All OSes / Java 5 & 6 & 7
User avatar
ptalbot
 
Posts: 1612
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: How to display data using SQL query

Postby hardina09 » Fri Jul 13, 2012 3:45 pm

When I tried code it gives me TypeError: Cannot find function createDataSource.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: How to display data using SQL query

Postby ptalbot » Fri Jul 13, 2012 4:27 pm

createDataSource is a function of JSDataSet:
createDataSource.png
createDataSource.png (27.92 KiB) Viewed 2055 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.
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
http://www.servoy-stuff.net
https://www.servoyforge.net
--------------------------------------------
Servoy 5.2.16 / 6.0.9 / 6.1.6 / 7.3.1
All OSes / Java 5 & 6 & 7
User avatar
ptalbot
 
Posts: 1612
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: How to display data using SQL query

Postby hardina09 » Mon Jul 16, 2012 5:38 pm

Thank you Patrick
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm


Return to How To

Who is online

Users browsing this forum: No registered users and 2 guests