Hi,
Having a bit of trouble with using prepared statements.
The following works fine (from within a database class I’ve created):
Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM suppliers")
Return sql.SQLSelect
But the following does not:
Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM ?")
sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
sql.Bind(0, "suppliers")
Return sql.SQLSelect
Can someone kindly explain to me what’s going wrong?
Much appreciated!
Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM suppliers WHERE Name = ?")
sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
sql.Bind(0, "Amazon")
Return sql.SQLSelect
But again, this does not:
Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM ?")
sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
sql.Bind(0, "suppliers")
Return sql.SQLSelect
Are there only certain parts of an SQL statements into which substitutions can be made?
e.g. you can make substitutions after a “WHERE” statement, but not after a “FROM” statement?
Is this accurate?
If so, can someone kindly link me to documentation describing what can and cannot be substituted in a prepared statement?
Thanks in advance…
Ok, so if I DO want to substitute a table name (I’m trying to write generic get/set methods) I can do the following:
Dim stmt As String = "SELECT * FROM %table%"
stmt = stmt.Replace("%table%", "suppliers")
Dim sql As MySQLPreparedStatement = Self.Prepare(stmt)
Return sql.SQLSelect
…but then the question is, why use the prepared-statement bindings?
Isn’t:
Dim stmt As String = "SELECT * FROM suppliers WHERE Name = %name%"
stmt = stmt.Replace("%name%", "'Safeway'")
Dim sql As MySQLPreparedStatement = Self.Prepare(stmt)
Return sql.SQLSelect
Pretty much the same as:
Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM suppliers WHERE Name = ?")
sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
sql.Bind(0, "Safeway")
Return sql.SQLSelect
Whoa!! Thanks for the link to BKeeney’s ActiveRecord.
It’s…overwhelming.
I had no idea implementing a generic database handler would be so complicated! It’s certainly robust, I’ll say that for sure!
I’ll have to go through it and see if I can simplify it to my needs…
I have started using ActiveRecord, and using the ORM (Object Relationship Manager?) it has completely changed my life when it comes to databases. I just create an object (that maps back to the table I want to add a row into) and just manipulate the object like I would any other object, then when I tell it to save, it saves it as a record in the database table. For me, it doing all the heavy lifting when it comes to the database work (SQL, CRUD, etc), it makes my life much easier. Plus I dont have to become a Database Godess (like he has on staff).
When @Bob Keeney first released ActiveRecord, I was very impressed with it. And over the years he (as his team) has greatly improved it. Making me even more impressed.