Bah.Database Query with variables

BlitzMax Forums/Brucey's Modules/Bah.Database Query with variables

wmaass

(Posted 2009) [#1]

Now, I'm certain this has been answered but my brain is cooked and I can't find it.

Anyway, I need to be able to execute queries with variables. For example:

testname = "Alfred"

query = db.executeQuery("SELECT * from person WHERE forename = testname")

...which does not work. Help?

plash

(Posted 2009) [#2]

Are these BlitzMax variables? If so you should try

db.executeQuery("SELECT * from person WHERE forename = " + testname)

Have I misunderstood the question?

wmaass

(Posted 2009) [#3]

You got the question right and testname is a BlitzMax variable. I tried your suggestion before. SQLite gives an error:

(3) Error preparing statement : no such column: Alfred :

I'm looking at the sample code about prepared queries and bindValue. That may be the answer to this but I am having trouble getting my head around it.

Retimer

(Posted 2009) [#4]

I'm thinking you need to put quotes or apostrophes between the name for the query, otherwise it seeks a similar-named table/column, which is why you're getting that result back....

db.executeQuery("SELECT * from person WHERE forename = '" + testname + "'")

wmaass

(Posted 2009) [#5]

Excellent! Thanks for the help. By the way, this works to...

query.prepare("SELECT * from person where forename = ?")
query.bindValue(0, TDBString.Set(testname))
query.execute()

...but your suggestion is MUCH better.

DavidDC

(Posted 2009) [#6]

...but your suggestion is MUCH better.

Well, it depends on what you want out of the code. Prepared statements have their own uses - including being faster once prepared and more secure.

wmaass

(Posted 2009) [#7]

Good to know DavidDC, thanks for the tip.

Brucey

(Posted 2009) [#8]

but your suggestion is MUCH better

No it's not. :-p

What about encoding the string? What if you have a ' in the text you add to the SQL string?
... WHERE surname = 'O'Donnell' ...

Using prepared statements avoids this issue, as the parameters are not inlined with the SQL text itself.
Since my day-job involves working with large databases, it's obvious to me as to the benefits of using prepared statements. If you intend to run the same query over-and-over, it's certainly something worth considering - even if it's a static query, which doesn't have any variable parameters.

Just my two cents... :-)

kenshin

(Posted 2009) [#9]

Just curious, is there any way to use Bah.Database to query information from the WMI database? I've been using a workaround in the form of a purebasic DLL to get this job done. It works well enough, but the ideal solution would be to query WMI direct from BMax without requiring the external DLL.

Brucey

(Posted 2009) [#10]

Interesting... I hadn't heard of it before.

wmaass

(Posted 2009) [#11]

Brucey,

Ok, I'm convinced :)

Fortunately, the data in my database is very small and its contents are completely controlled by me. Still, I will at some point use the prepared statement approach - don't like loose ends.

kenshin

(Posted 2009) [#12]

Interesting...

Yeah, it's pretty handy for finding out all sorts of info about the host Windows PC. You can get volumes of useful stuff from it. I am using it to provide a comprehensive 'system information' window in my program.

Oh well, apologies for the ot interruption wmaass