Show related info. on each form using buttons

Classic Servoy
21

Try running the SQL query inside your database outside of Servoy

You can check the results there to validate the structure

Also, in case of mismatch, check your DB schema through an admin tool to enusre that it has consistent data types

Cheers
Harry

22

I checked the sql in the backend and it works and the data types are correct.
TableA.pk1 is an integer. TableB.pk1 is an integer. Table B.pk2 is serial.

I have this narrowed down to the variable or its syntax inside the sQuery.
It must be some syntax error because I can’t get this to work.

var v_table_a_pk = Forms.TableA.pk1;
var sQuery = "SELECT TableB.pk2, TableB.pk1, TableA.pk1 FROM TableB, TableA WHERE TableA.pk1 = TableB.pk1 AND TableA.pk1 =' + vaPk + ' ";
sQuery += " AND TableB.Field in ('Code1','Code2') ORDER BY TableA.pk1, TableB.pk1";
forms.FormB.controller.loadRecords(sQuery); 
forms.FormB.controller.show();

The error I get is:

Error loading primary key data,
org.apache.commons…
Character to numeric conversion error
com.seroy.j2db.persistence.RepositoryEception:
org.apache.commons…
Character to numeric conversion error java.sql.SQLECEPTION:
Character to numeric conversion error

If set the variable to an integer var v_tableApk1 = 555 and use this in sQuery:
WHERE tableA.pk1 = ’ + v_tableApk1 + ’
It still gives the same character to numeric conversion error.

But setting the TableA.pk1 manually to an integer works. Ex. WHERE TableA.pk1 = ‘555’ it works inside the sQuery. the FormB appears with the data From Table B. Also if I set the tableA.pk1 = ‘555.0’, it also works correctly.

This leads me to believe that this ’ + v_tableApk1 +’ syntax is wrong. All I have to do to get it to show Form B with Form A’s pk1 is to take out the variable and replace it with an integer or number 555 or 555.0 inside the sQuery statement. I just can’t put a variable statement inside the sQuery!

I really need to be able to do this. There has to be a way.
Please Help! :?

23

Hi,

You can use the following syntax to use variables in a query:

var v_table_a_pk = Forms.TableA.pk1; 
var sQuery = "SELECT TableB.pk2, TableB.pk1, TableA.pk1 FROM TableB, TableA WHERE TableA.pk1 = TableB.pk1 AND TableA.pk1 =" + vaPk; 
sQuery += " AND TableB.Field in ('Code1','Code2') ORDER BY TableA.pk1, TableB.pk1"; 
forms.FormB.controller.loadRecords(sQuery); 
forms.FormB.controller.show();

Or when vaPK is a string then use the following syntax:

var v_table_a_pk = Forms.TableA.pk1; 
var sQuery = "SELECT TableB.pk2, TableB.pk1, TableA.pk1 FROM TableB, TableA WHERE TableA.pk1 = TableB.pk1 AND TableA.pk1 ='" + vaPk + "'"; 
sQuery += " AND TableB.Field in ('Code1','Code2') ORDER BY TableA.pk1, TableB.pk1"; 
forms.FormB.controller.loadRecords(sQuery); 
forms.FormB.controller.show();

Or even easier use the prepared statement approach using “?” placeholders and an array with values/variables:

var v_table_a_pk = Forms.TableA.pk1; 
var sQuery = "SELECT TableB.pk2, TableB.pk1, TableA.pk1 FROM TableB, TableA WHERE TableA.pk1 = TableB.pk1 AND TableA.pk1 =?"; 
sQuery += " AND TableB.Field in ('Code1','Code2') ORDER BY TableA.pk1, TableB.pk1"; 
forms.FormB.controller.loadRecords(sQuery. [vaPK]); 
forms.FormB.controller.show();

Hope this helps.

24

That did it! :D

It was the syntax. Thank you so much.

I tried both your first suggestion and third suggestion, because the data type was integer, and they both worked great! The place holders are easy to use.

The extra information on variables will come in handy as well. I had looked in the books and on the forum and I could not find the information.
Again, thanks for the help! :)