SQL error

[Morley]

In the example below I want SQL to present me with all records where the accountmanager column equals "maarten". Instead I'm being given an error that SQL can't find a column called "maarten".

Note I'm deliberately setting up to search by the contents of a variable derived from an array. I've broken a larger problem so I can debug in parts. A number of contributors to this forum have indicated the way to query sql for instances of a column matching the contents of a variable is the following. Hard coding for '"maarten" also yields the same error.

Test solution called "sqlissue1', using crm server and companies table (form also called companies)

Code: Select all

var a1 = new Array(3);
a1[0] = 'maarten';
a1[1] = 't.banks';
a1[2] = 's.james';
var a1target = a1[0];
var maxReturedRows = 10;
var query = 'select companiesid from companies where accountmanager = ' + a1target;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
controller.loadRecords(dataset);

Error message is:

dataset com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -143: Column 'maarten' not found ASA Error -143: Column 'maarten' not found

Servoy Developer
Version R2 2.1.2-build 315
Java version 1.4.2_05-b04 (Windows XP)

What am I missing?

[rkrservoy]

var SQLquery = "SELECT companiesid FROM companies WHERE accountmanager = ' " + a1target + "' " ;

The quotes are important

[Morley]

Thanks, that worked! That's a lot of quotation marks, obviously necessary.

[Riccardino]

Thanks, that worked! That's a lot of quotation marks, obviously necessary.

A little tip: never test your SQL in the method editor.
Use a tool like Interactive SQL or similar: if your code works there and you're getting errors in Servoy, you can be pretty sure is something related to quotes or typos.