Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Sep 25, 2018 11:57 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 35 posts ]  Go to page 1, 2, 3  Next
Author Message
 Post subject: Database.ParameterName() Method
PostPosted: Mon Oct 17, 2005 4:59 pm 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
I would like to leverage RealBasic's feature whereby databases can be accessed the same way regardless of implementation. For example I can call Database.TableSchema() the same way regardless of whether the database is SQLite or MSSqlServer or Oracle, etc.

What is the best way to handle parameter names with this in mind? For example the statement...

SQLite: select * from users where name = $name
MSSQL: select * from users where name = @name
Oracle: select * from users where name = <i dunno how oracle names paramaters>

Each implementation has a difference way of naming parameters. It would be nice if there was a method on the Database object called ParameterName() or somesuch, that would take a string and return a string with the proper character appended to it. That way I could write a complete statement, using parameters, and know that it would continue to work no matter what back-end database I am talking to.

I am new to RealBasic but would appreciate any insight you might have.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Oct 17, 2005 5:16 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
write a method that checks the type of database and then fills in the blanks for you.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Oct 17, 2005 5:34 pm 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
That'd work, sure, but it's not very useful when a database comes along I haven't coded for. I get schema data in a database-agnostic way from the Database base class. Why not parameter names too?


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Oct 17, 2005 5:42 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
That too was programmed by someone, if a database comes along you haven't coded for then chances are you don't have the plugin installed anyway so you wouldn't be able to connect to the database.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Oct 17, 2005 6:08 pm 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
I recognize that someone will have to code this. I just think it should be done in the database provider, where other features are implemented that hide the underlying provider-specific details with generic functionality that is the same regardless of provider. We already have a place where provider-specific details are being wrapped by provider-generic code. Why not do this in the same place? Why do it in another object when there's already an obvious place to do it in?


Top
 Profile  
Reply with quote  
 Post subject: Re: Database.ParameterName() Method
PostPosted: Tue Oct 18, 2005 7:24 am 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
johndehope3 wrote:
SQLite: select * from users where name = $name


You are kind of in luck with SQLite because you don't have to worry about named parameters. There isn't any way for you to set their values in the current version of the REAL SQL Database, thought that would be an interesting feature to add at some point.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 18, 2005 7:35 am 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
Are you telling me that there's no way to use named parameters in an sql statement? Is that just with the SQLite built-in database? Or does this apply to all databases?


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 18, 2005 7:41 am 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
It depends on the database engine and what it uses named parameters for. SQLite uses named parameters as placeholders to be filled in later with the sqlite3_bind function. But that's a C function. You'd have to be using SQLite with C to call it. The REAL SQL Database could expose sqlite3_bind as a method, and then you'd be able to call it in REALbasic code. I think that might actually be a nifty idea. But it doesn't exist yet.

I believe other database engines use named parameters for other things. For example, I think MS SQL uses them with stored procedures, something that the REAL SQL Database doesn't (yet) support.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 18, 2005 8:15 am 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
Wow okay, this is good information. Thanks! I thought RealBasic's database support was more mature. I can live without stored procedures (I've had to with MySql in the past and I survived). But I can't live without being able to pass named parameters in a generic way. Alas.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Oct 19, 2005 4:57 am 
Offline

Joined: Fri Sep 30, 2005 8:28 am
Posts: 34
Location: Dunfermline, UK
Umm, obviously I'm being really dumb here, but why do you need named parameters?

Wouldn't this kind of thing work? ...

dim strSQL, strName As String

strName = "ian" // This could obviously be passed into the function or be global etc

strSQL = "select * from users where name = '" + strName + "'"
rs = db.SQLSelect(strSQL)

// Now do stuff with recordset

I'm lost.

_________________
Ian M. Jones
IMiJ Ltd
http://www.imij.co.uk
http://www.ianmjones.com
http://twitter.com/ianmjones


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Wed Oct 19, 2005 7:13 am 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
Well yes you're right, named parameters don't allow you to do anything that you can't already do yourself. But they have a few nice features.

They remove the need for you to format the data yourself. In your example you had to know that strings have single quotes. What if strings have double quotes in some other database? Access uses # to delimit dates whereas ms sql server uses single quotes, for example. What if there are single quotes embedded in strName? Now you have to make sure you embed all the special characters you need to watch out for. Named parameters help remove these concerns.

Many database engines can cache some of the processing needed to perform an sql statement, so that executing it many times with different parameters goes much faster. So if you need to execute your example once, for a single strName, then yeah that is fine. But if you needed to execute it many times (say 1000) then you could pre-compile the statement with named parameters, and when you provide the parameters, it'll run much faster.

Your code will be much much cleaner. Imagine if every method in your code was only allowed to take a single string as a parameter! You'd have to format and concat all your data parameters into the string just to make a function call. What a pain that'd be! Instead with named parameters the code will be much cleaner because instead of all the concatenation, and all the extra quote marks, and all the method calls to embed quote marks, etc.

Those are the reasons off the top of my head. Again, you're right, named parameters are not neccessary to do anything (that I know of) they're just much nicer. And they've been around for I don't even know how long, decades? It's not like I'm asking for the moon or anything.


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 20, 2005 9:47 am 
Offline

Joined: Fri Sep 30, 2005 9:31 am
Posts: 28
johndehope3 wrote:
Wow okay, this is good information. Thanks! I thought RealBasic's database support was more mature. I can live without stored procedures (I've had to with MySql in the past and I survived). But I can't live without being able to pass named parameters in a generic way. Alas.


Can you be more specific about exactly how you would use this? Other than binding binary data, how would you like to use named parameters?


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 20, 2005 11:05 am 
Offline

Joined: Sat Oct 08, 2005 2:15 pm
Posts: 75
>Well yes you're right, named parameters don't allow you to do anything that you can't already do yourself. But they have a few nice features.

It’s easy to write a little method for SQLite to accomplish all this except for compiling the statements. I haven’t hand any speed problems with SQLite, so that’s not an issue.

Parameters come in handy for some database drivers that don’t allow you to use a DatabaseRecord class to do inserts. Not a problem with RB. In a lot of cases, parameters are just a hack to get around the limitations of the driver.

>Your code will be much much cleaner.
Too me, it’s a lot cleaner and less error prone to use the DatabaseRecord class for inserts, and RecordSet for edits when more than 3 or 4 columns are involved.

johndehope3, I think you have a solution looking for a problem:)


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 20, 2005 11:29 am 
Offline

Joined: Mon Oct 17, 2005 4:50 pm
Posts: 17
Well the folks who wrote SQLite obviously thought it was worthwhile, because they included it in the functionality of the database. When RB picked SQLite for their built-in database, why didn't they implement this?

If you don't think I know what I'm talking about, at least notice that every database implementation you've probably ever used has included this feature. Just those that I've used personally that offered parameters: ms access, dao as in early VB, ado as in later VB, ado.net, mysql, ms sql server, sqlite (rb's built in database), and oracle. I'm probably missing something I've used over the years. Nothing I've ever used has not allowed parameters, except RB, at least not that I can think of.

Maybe you think I don't know what I'm talking about, which is fine, I'm just some dude in a web forum, what the hell do I know? But are you gonna tell me that all these database implementers also don't know what they're doing? That they've all wasted their time including this functionality?


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Thu Oct 20, 2005 1:13 pm 
Offline

Joined: Sat Oct 08, 2005 2:15 pm
Posts: 75
>If you don't think I know what I'm talking about

Hold on there, johndehope3.
I apologize if I came across that way.

This is only my opinion and not meant to reflect on you in any way.
The lack of parameters in RB is covered for the most part by the DatabaseRecord, and RecordSet classes.

File a feature request.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 35 posts ]  Go to page 1, 2, 3  Next

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group