# How to display data using SQL query

**URL:** <https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184>\
**Category:** Classic Servoy\
**Created:** [July 12, 2012, 12:38pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184 "2012-07-12T12:38:09Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![hardina09](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hardina09](https://forum.servoy.com/u/hardina09)\
**Post date:** [July 12, 2012, 12:38pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/1 "2012-07-12T12:38:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hardina09](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hardina09](https://forum.servoy.com/u/hardina09)\
**Post date:** [July 12, 2012, 4:29pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/2 "2012-07-12T16:29:45Z")

</div>

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 ”

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.

---

<div class="post-metadata">

**Author:** ![ptalbot](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/ptalbot/32/4952_2.png) [@ptalbot](https://forum.servoy.com/u/ptalbot)\
**Post date:** [July 12, 2012, 5:16pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/3 "2012-07-12T17:16:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hardina09](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hardina09](https://forum.servoy.com/u/hardina09)\
**Post date:** [July 12, 2012, 5:24pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/4 "2012-07-12T17:24:45Z")

</div>

I did the same created dataset and passed as parameter to foundset. Still throwing same message.

---

<div class="post-metadata">

**Author:** ![ptalbot](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/ptalbot/32/4952_2.png) [@ptalbot](https://forum.servoy.com/u/ptalbot)\
**Post date:** [July 12, 2012, 5:52pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/5 "2012-07-12T17:52:18Z")

</div>

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:

```auto
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:

```auto
var jsForm = solutionModel.getForm(formName);
jsForm.dataSource = dataSource;

```

---

<div class="post-metadata">

**Author:** ![hardina09](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hardina09](https://forum.servoy.com/u/hardina09)\
**Post date:** [July 13, 2012, 1:45pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/6 "2012-07-13T13:45:26Z")

</div>

When I tried code it gives me TypeError: Cannot find function createDataSource.

---

<div class="post-metadata">

**Author:** ![ptalbot](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/ptalbot/32/4952_2.png) [@ptalbot](https://forum.servoy.com/u/ptalbot)\
**Post date:** [July 13, 2012, 2:27pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/7 "2012-07-13T14:27:05Z")

</div>

createDataSource is a function of JSDataSet:  
[attachment=0]createDataSource.png[/attachment]

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:

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

```

 ![createDataSource.png](https://canada1.discourse-cdn.com/flex010/uploads/servoy/original/1X/85f50b77c97dd51b70cfa4537f850cbc52d3191d.png)

---

<div class="post-metadata">

**Author:** ![hardina09](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hardina09](https://forum.servoy.com/u/hardina09)\
**Post date:** [July 16, 2012, 3:38pm UTC](https://forum.servoy.com/t/how-to-display-data-using-sql-query/16184/8 "2012-07-16T15:38:36Z")

</div>

Thank you Patrick