Is this the right way to do a lookup..

Questions and answers regarding general SQL and backend databases

Is this the right way to do a lookup..

Postby JonathonBevan1342106235 » Fri Aug 10, 2012 4:24 pm

I have looked through the forum and I have found how to conduct an SQL query to do a lookup seems to cause people to be confused - but also people who answer the queries simply say this is very basic stuff look at the documentation. Unfortunately, for me, the documentation has not lead me to the right answer..

I have contemplated doing a Find and Search... but to do this I need to "set autosave on" and that makes me nervous cause I am validating a record being entered and don't want it to be commited to the database. Also, the project_name that I am validating is now a value is bound to the table so find and search may include this new value..

I tried rawSQL but there are heavy warnings on usin this so I suspected this is not the way to go.

Finally, I tried using datasets...

Like below
var vQuery = 'SELECT project_id FROM projects WHERE project_name = ?'
var vArgs = [project_name]
controller.loadRecords(vQuery,vArgs)
application.output(controller.getMaxRecordIndex())

But you cannot do this because as you are validating a new record to the table projects and Servoy tells you that there are updates required to the foundset and it doesn't work...

Please could you tell me if I am barking up the wrong tree - I suspect I am? I didn't come to the forum on this occasion without looking at the docs, tutorials and not having experimented. SO I think I am missing something.

PS I have also been looking at the possibilty of stopping a field having duplicates on the database but I cant seem to find out how to do that.

Thanks in advance. I hope you undertsand what I am trying to say.

Kind Regards
Jonathon
JonathonBevan1342106235
 
Posts: 46
Joined: Thu Jul 12, 2012 5:17 pm

Re: Is this the right way to do a lookup..

Postby ptalbot » Fri Aug 10, 2012 4:31 pm

Code: Select all
var ds = databaseManager.getDataSetByQuery(yourServerName, vQuery, vargs, -1);

Then use the ds DataSet for whatever you need, just don't load records in the controller:
Code: Select all
for(var i = 1; i <= ds.getMaxRowIndex(); i++) {
    ds.rowIndex = i;
    // now ds is pointing to a row, you can get the value you want:
    var id = ds.project_id;
    // and do something with it.
}
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1654
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: Is this the right way to do a lookup..

Postby JonathonBevan1342106235 » Fri Aug 10, 2012 5:12 pm

Really appreciate your feedback. Again :)

Yep, I understand what you are saying and that is very very logical.

Thanks
Jonathon
JonathonBevan1342106235
 
Posts: 46
Joined: Thu Jul 12, 2012 5:17 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 7 guests