1
fjardon
Support for SQL Parameterized queries ?
  • 2007/10/27 7:54

  • fjardon

  • Just popping in

  • Posts: 2

  • Since: 2007/10/27


Hi
Is there any plan to support SQL parameterized queries ?

One could do something like:
1) prepare query: SELECT * FROM table WHERE Id=?
2) loop
a) bind(1, <some Id value>)
b) execute()
c) get next Id value
3) next loop

The major advantage over custom SQL query crafting is the SQL server can optimize the query once and for all. Another advantage is that most SQL layer will take care of parameter formatting i.e. you won't have to escape characters manually, add quotes for strings and not for numbers and all that stuff everyone do before executing the query.

All in all this leads to less code, less errors and better efficiency.

2
nekro
Re: Support for SQL Parameterized queries ?
  • 2007/10/27 8:27

  • nekro

  • Quite a regular

  • Posts: 213

  • Since: 2005/11/9


I really like your idea... i will try to do something like that... in other non XOOPS projects i do something like that...

I will try to merge ideas... thanks for collaborating!

3
Dave_L
Re: Support for SQL Parameterized queries ?
  • 2007/10/27 10:52

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


Don't the XOOPS object and criteria classes already accomplish this?

4
suico
Re: Support for SQL Parameterized queries ?
  • 2007/10/27 11:18

  • suico

  • Friend of XOOPS

  • Posts: 374

  • Since: 2003/7/24


Maybe I am misunderstanding the point but yes i think Dave_L is right. Criteria was created for this :)
Yogurt Social Network Service
Visit: http://www.marcellobrandao.eti.br/

5
Garrath
Re: Support for SQL Parameterized queries ?
  • 2007/10/27 11:41

  • Garrath

  • Just popping in

  • Posts: 23

  • Since: 2007/6/11


Sorry for my bad english
Quote:

Dave_L wrote:
Don't the XOOPS object and criteria classes already accomplish this?

No

Actually all query is construct when we need it.
All query are a constructed string.
We don't have some thing like this
SELECT * FROM table WHERE Id=? and param to put in after like we can have PRO*C (C with preprocessor Oracle)

We constuct a string like this
SELECT * FROM table WHERE Id=1
and this query is not the same at
SELECT * FROM table WHERE Id=2
then the SQL engine don't say it the same query then he calculate another time the optimize parse etc...

If you have this
SELECT * FROM table WHERE Id=? with param to passe the query is the same then we gain time of optimize parse.

It's work like that in Oracle.

Criteria just construct a String, it just put String from name of column and add string of the value you have, and add couple of column name, and value. Value is a print of $value
Like this :
colomnname = 'value' AND colomname2 = 'value2'

Criteria don't put
colomname = ?param1 AND colomname2 = ?param2

6
Garrath
Re: Support for SQL Parameterized queries ?
  • 2007/10/27 11:59

  • Garrath

  • Just popping in

  • Posts: 23

  • Since: 2007/6/11


For MySQL you can look this
http://www.php.net/manual/en/function.mysqli-prepare.php

you can understand more than my explication ( ) the difference between the actual way and this way.

7
phppp
Re: Support for SQL Parameterized queries ?
  • 2007/10/27 15:39

  • phppp

  • XOOPS Contributor

  • Posts: 2857

  • Since: 2004/1/25


short answer: Yes

8
fjardon
Re: Support for SQL Parameterized queries ?
  • 2007/10/28 8:34

  • fjardon

  • Just popping in

  • Posts: 2

  • Since: 2007/10/27


Quote:

phppp wrote:
short answer: Yes


Great !

9
Garrath
Re: Support for SQL Parameterized queries ?
  • 2007/11/5 0:10

  • Garrath

  • Just popping in

  • Posts: 23

  • Since: 2007/6/11



Login

Username:
Password:

Lost Password? Register now!

Who's Online

80 user(s) are online (52 user(s) are browsing Support Forums)


Members: 0


Guests: 80


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Jan 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits