Is using DatabaseRecord with Database.InsertRecord the same as using a prepared statement for doing an insert security wise?
I always assumed that it was, but I just realized I do not really know if that is the case, even though it is reasonable to expect it to be.
If it matters I’m using Postgres
Thanks,
Karen
Dave you are forgetting your Xojo! 
API 1 has another mechanism for doing inserts where you don’t write the SQL. You use the DatabaseRecord class. That COULD be made i to a prepaid statement because Xojo knows the DataTypes and Table Column types.
From the old language Reference:
Dim row As New DatabaseRecord
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"
myDB.InsertRecord("Team", row)
GOD I HOPE SO!
for the record… I never use that syntax, and don’t recomend other do either, but…
IF you’re not sure why not just create a prepared stmt & be 100% sure that it IS ?
EDIT : a quick test suggests it DOES use a prepared
However, it might be slow since it seems to create a new prepared for EVERY insert rather than reuse one like you could do manually
That may impact which direction you want to go
If you have a lot of data to load creating one prepared & reusing the heck out of it to insert data should perform better than lots of calls to insertrecord
It’s messier.
I can give you code in APL that is tight, concise, an impossible to decipher
Or I could give you code in another language that does the same thing but is longer, and clearer to read
IMHO clear code thats obvious its doing exactly what you want & intend is, long term, more useful than code that requires deciphering or that you’re not sure of
And, FWIW, its not that complex to write “InsertRecord” that does use a prepared that YOU control
All this said reading the source from the open sourced my sql connector leads me to believe that a prepared statement IS use for insert record - at least in that plugin
Since the others are closed source I cant speak to whether they do/do not use prepared statements internally
I’d suspect they would
I did not say it was complicated, just that it was messier than using DatabaseRecord - which is equally as clear but less messy.
At different times , depending on what was I doing, I have used both.
The thing that really annoys me about PostgresSQL Prepared statements in Xojo is that the parameters are numbered from 1 but the bind index is numbered from 0.
Btw does RecordSet.Edit/ Recordset.Update also use prepared statements? Again I always assumed it did, but don’t know for sure.
When I’m creating php tables, I usea var for the row number kinda like
$rowIndex = -1;
$table.addCell( ++$rowIndex, 0, 'Foo' );
$table.addCell( ++$rowIndex, 0, 'Foo' );
$table.addCell( ++$rowIndex, 0, 'Foo' );
Then I never have to deal with setting the index. That might work for you where you start your index with 0…
Sure it might be more code if you wrote each prepared stmt usage out long form
I wouldn’t
I’d write ONE “CustomInsertRecord” extension and use that to be 100% sure prepared are being used for inserts
DatabaseRecord itself isnt much beyond a wrapper for an array of pairs
And then you can do whatever you need / want as far as bind vars, conversion for custom situations, etc and even look at the source type and destination column type from the DB (not just Xojo’s mapping of DB type into Xojo type)
For instance if the DB table is set up with a varchar type for an image then you could custom convert the image to a Base64 encoded image and undo that when you retrieve it
Xojo wont necessarily do that using InsertRecord
And doing this you could still end up with one call to a method that encapsulates all the “messiness”
Sometimes “less messy” results in “less control” too
FWIW the mysql sources do NOT indicate that an edit/update uses a prepared statement that I can see
Thanks.
I can be a bit lazy sometimes
-Karen
trust me you’re not alone there
but sometimes doing it yourself is a good alternative since you need that extra control