|
|
Rank: Newbie Coder
Groups: Member
Joined: 2/3/2010
Posts: 17
Points: 39
Location: MN
|
Hello.
A search for "weborb prepared statements" on google, and just "prepared" here, reveal no useful hits, so I'll ask you all.
If I want to do something like:
_model = ActiveRecords.Contacts.findBySql("SELECT * from Contacts WHERE name=? AND status=?");
Is such a thing possible? If so, how? If not, should it be?
Thanks!
|
|
Rank: Administration
Groups: Administration
Joined: 8/21/2006
Posts: 679
Points: 1,280
Location: Frisco, TX
|
Hi, You'd need to prepare a complete SQL statement as a string and then make the call. Is the data for the "name" and "status" parameters available when you call the method? Mark
Mark Piller Midnight Coders, Inc. twitter: midnightcoderblog: blog.themidnightcoders.comwebsite: www.themidnightcoders.com
|
|
Rank: Newbie Coder
Groups: Member
Joined: 2/3/2010
Posts: 17
Points: 39
Location: MN
|
yes, they'd be available at that point in time, but I'd definitely rather NOT assemble these types of queries with string concatenation.
What I'm after is the concept of 'bound query parameters' for the purpose of type checking, and the like. Obviously, if I use a stored procedure call, I get that. But for something like the original question, where all I've got is a SQL statement, it seems irresponsible to me, what with the possibilities of SQL-injection and all, not to have that additional layer of protection. It seems to me that we ought to be able to do something like:
_model = ActiveRecords.Contacts.findBySql("SELECT * from Contacts WHERE name=? AND status=?", sName, nStatus);
or
_model = ActiveRecords.Contacts.findBySql("SELECT * from Contacts WHERE name=? AND status=?", aParamList);
or something like that.....
Thanks.
|
|
Rank: Administration
Groups: Administration
Joined: 8/21/2006
Posts: 679
Points: 1,280
Location: Frisco, TX
|
The reason findBySql is there is because we got a lot of requests for that functionality, but personally I would never pass SQL statements from the client to the server (even if they are formatted as prepared statements). What you can do to be able to pass data exactly as you showed is the following: On the server side create a method in ContactsDataMapper which takes the arguments as you defined them on the client: Code:public override QueryResult findBySql(String sqlQuery, String param1, String param2 )
{
// create your final SQL statement here to embed param1 and param2 into the query
// sqlQuery = ......
// then call this:
return base.findBySql(sqlQuery, new Hashtable());
} Compile and deploy your server side code. Now, your calls should work: Code:_model = ActiveRecords.Contacts.findBySql("SELECT * from Contacts WHERE name=? AND status=?", sName, nStatus); Hope this helps. Cheers, Mark
Mark Piller Midnight Coders, Inc. twitter: midnightcoderblog: blog.themidnightcoders.comwebsite: www.themidnightcoders.com
|
|
Rank: Newbie Coder
Groups: Member
Joined: 2/3/2010
Posts: 17
Points: 39
Location: MN
|
Cool, thanks.
But.....won't that code be lost the next time I have to re-generate my database lookup code? Our DB changes pretty frequently (with new product features, etc).
Out of curiosity, would it make more sense to, instead of building a single monolithic data access component, create several, or perhaps one for each table ?
|
|
Rank: Administration
Groups: Administration
Joined: 8/21/2006
Posts: 679
Points: 1,280
Location: Frisco, TX
|
Next time you regenerate the code, simply replace codegen.cs, but leave all the other classes in place. codegen.cs should never be modified. All other classes though are placeholders for your custom code (they all are declared as "partial"). Regards, Mark
Mark Piller Midnight Coders, Inc. twitter: midnightcoderblog: blog.themidnightcoders.comwebsite: www.themidnightcoders.com
|
|
Rank: Newbie Coder
Groups: Member
Joined: 2/3/2010
Posts: 17
Points: 39
Location: MN
|
ooh...Hey, thats pretty clever.
Good to know, Thanks!
|
|
|
Guest |
