getDataSetByQuery -> Query has to start with SELECT ?

Classic Servoy
1

Hi,

im using the following code to make a query on a MySQL-Server :

var vQuery = "select vorname,nachname from kunden where nummer = 'abc123'";
var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(), vQuery, null, 500);
forms.suchen.controller.loadRecords(vDataset);

Im getting the error : "Error loading primary key data, Die Abfrage muss mit ‘SELECT’ beginnen’.

Any idea whats wrong in my code ?

Thanks in advance,
Andre

2

Hi Andre,

You need to select only the PK column. Servoy will load the rest for you.
So your code should look something like this:

var vQuery = "select myIDField from kunden where nummer = 'abc123'"; 
var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(), vQuery, null, 500); 
forms.suchen.controller.loadRecords(vDataset);

Of course you need to put the correct PK fieldname in there.

3

Okay, the pk-field is “nummer” (as shown in Dataproviders). So i changed the code to :

var vQuery = "select nummer from kunden where nummer = 'abc123'";
var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(), vQuery, null, 500);
forms.suchen.controller.loadRecords(vDataset);

The result is the same error.

4

Hi Andre,

It’s a bit of a strange error you get because your select statement begins with a SELECT as it requires. Maybe you have any invisible characters in that string? Did you copy paste it from another app?

Also do you get the error on the 2nd line of code or on the 3rd (loadRecords) ?
You can see this by enabling the debugger.

Hope this helps.

5

Hi Robert,

the string is okay, i modfied it some hundred times :-). The error is shown passing the third line.

Andre

6

This sounds a bit strange. “nummer” sounds like a number and you search for a string?! BTW: you don’t need to fill a dataset. You can simply call

var vQuery = "select kunden.nummer from kunden where kunden.nummer = 'abc123' ORDER BY kunden.nummer";
forms.suchen.controller.loadRecords(vQuery);

What happens if you try that?

7

Whem im using your code, im getting the following :

“Error loading primary key data, Unknown table ‘kunden’ in field list”.

The name of the table is absolutly correct. It is the tableName of the form.

:x

8

I’d start with the basics and look at the output of that query in the debugger. What is the count of rows found? Look at the contents of that column output.

9

No i found the reason :

The table in the MySQL-DB is named “Kunden” with a big “K”. I dont know why, but the dataprovider shows it with small “k”. Normally this seems to work fine. But when i use this “query_by_hand” i have to use the big “K”. :lol:

Thanks for all your help, wish you a good night.

10

dont know why, but the dataprovider shows it with small “k”.

It is the way Servoy shows and uses the dataprovider names. When I am correct this has something to do with the way some databases work.