I’m using v5.2.2.
I try to lookup the existence of a certain record by firing a query.
In this particular case I’m 100% sure the combination doesn’t exist (even fired the query from interactiveSql)
So I’ve been looking what this dataset returns and suddenly we get:
BufferedDataSet {Columnnames[k_contact_id]}
row_1[org.mozilla.javascript.UniqueTag@1dcc1de: NOT_FOUN] {rowColumns:array[1],rowIndex:-1,row_1:array[1]}
This means: even with a 0 row result from the database I get 1 row returned by the dataset.
Everywhere in my code where I check for if ($ds.getMaxRowIndex() > 0) I’ll have a problem…
Anyone else experienced this???
Looks strange to me, can you post your code and the sql query?
The SQL just couldn’t be more simple:
SELECT k_contact_id FROM contact WHERE k_organisation_id = ? AND k_person_id = ?
This is queried with a getDataSetByQuery…
I tried to reproduce this, but wasn’t able to, although in this specific method it consistently fails.
Only thing that’s different at this specific moment is that a modal FID is active.
Anyway, I’ll try to reproduce it later on. Meanwhile I changed the SQL to SELECT count(*) as it is just the number I’m interested in…
Marc,
Very strange,
Can you show a bit more of the code calling the getDatasetByQuery?
Rob
Hi Rob,
this is the full code of this particular part:
if(!$pers_rec))
{
var $args = new Array();
var $subQry = 'WHERE k_organisation_id is null';
if($org_id != 'none')
{
$subQry = 'WHERE k_organisation_id = ?';
$args[$args.length] = $org_id;
}
var $query = ''
$query += 'SELECT k_contact_id' + ' ';
$query += 'FROM contact' + ' ';
$query += $subQry + ' ';
$query += 'AND k_person_id = ?';
$args[$args.length] = $pers_id;
var $ds = databaseManager.getDataSetByQuery(globals.current.server,$query,$args,1);
if ($ds.getMaxRowIndex() > 0)
{
var $msg = i18n.getI18NMessage('dlg.person_already_private_person');
if($org_id != 'none') $msg = i18n.getI18NMessage('dlg.person_already_linked_to_org');
plugins.dialogs.showErrorDialog('info', $msg , 'OK');
forms[$form].elements.btn_create.enabled = true;
forms[$form].elements.btn_cancel.enabled = true;
return;
}
}
Hi Marc,
Are you working with PostgreSQL ?
I see that $org_id is of type text ? (if($org_id != ‘none’))
I think field k_organisation_id (foreign key ?) is of type integer ?
PostgreSQL has no auto typecasting as does Sybase has…
Regards,
Marc,
I noticed that you have value ‘org.mozilla.javascript.UniqueTag@1dcc1de: NOT_FOUN’ in the dataset which looks like the toString() of javascript:notfound, but cut-off to 50.
Could it be that your k_contact_id column is a text(50) column?
I suspect that somehow this value was actually stored in the db and that the issue is not with the getDatasetByQuery, but with the insert/update code.
Can you do a query directly in the db and check if there are records with k_contact_id like '%NOT% ?
Rob
Finally back to this issue…
@Lambert/Rob: thnkx for the replies.
Lambert was closest to the issue.
A few weeks ago I replaced the deprecated ‘application.getNewUUID()’ with the new ‘application.getUUID()’.
I didn’t look close enough to the new return type (object). ![Embarassed :oops:]()
So when executing the query, an object was passed on as argument instead of a text-string.
Now I type-casted this new function, so I’m set again! ![Smile :)]()
Hi Marc,
Ok and here the free motto of the week : a pk or foreign key field of type integer is always better then one of type text ![Wink :wink:]()
Regards,