Coding SQL commands can be a nightmare. I know there are internal RS ones but these suffer from some stability issues and memory leaks at the moment so I am sticking to sqlinsert.
Some statements can be a nightmare like quotes in quotes. I know RS uses ’ and " for quotes. One of the neat features of Fox pro is that you have a third way using [ and ] so you could do this
sqlinsert([ insert into data(name,age) values (’ “FRED” ',23)] )
This really makes it easier to read and write embedded quotes, much clearer. Is this something that can be easily added to XoJo, or can you already do something like this.
While that might help some it would still be error prone.
Do the stability issues and leaks in the DB API methods extend to prepared statements?
To avoid “quote hell” I have been using a lot of prepared statements wrapped in methods (the prepared statement is in a static variable so only prepared once). Not sure if they offer anything over using DatabaseRecords for inserts
I’ve been using the same strategy (prepared statements) with success. Not only does it make SQL in code more manageable, it also protects your database against SQL injections… so I guess my suggestion would be “To quote… with prepared statements”.
The only big problem I have with prepared statements is that SQLite (and REALSQLDatabase) do not give you a specific error message if the PreparedStatement can’t be made because of an SQL error. Instead is just gives you a generic, “Prepared statement can’t be made” or something like that. Kind of useless. But the prepared statement is a good thing for many reasons.
I don’t have the specific feedback case handy, but I reported it about a month ago.
I agree debugging can be tricky with prep statements.
The most useful one for me (besides for the SQL injection protection) is that prepared statements take care of escape coding funny characters, E.g. the ’ (single quote) character.
I cannot care to remember how many times I’ve been bitten by user input that contains the single quote character and trying to insert that data into a database. Since switching over to prepared statements I’ve never had any issues with funny user input.
I am writing without testing, just remembering; so some inaccuracy can be expected.
There is no guaranteed way (that I know) to avoid this. The usual way is testing your base SQL code with fixed values right from a query tool and after transpose to your code inserting the right parameters to be prepared.
Some usual problems the I remember where things like: imagine a DB table with just 2 columns: ID, V1
Now prepare this select passing :ID = 1 : “SELECT ID, V1 FROM VAL_TABLE WHERE IG = :ID”
Prepare should fail, no reference to the inexistent column IG
But in a test query “SELECT ID, V1 FROM VAL_TABLE WHERE IG = 0” you will find the error.
If Xojo guys could get extended info, it would be great. Just don’t know if SQLite layer gives you details.
load SQL Queries from String constants (if necessary replace placeholders for variables)
load long SQL Queries from a text file linked in as a resource
But yes, a syntax to allow long, multiline String literals in the code editor without wrapping everything in “”, concatenating using + and using multiple lines with _ would be great. <can’t find the Feature Request right now, but I’m sure I made such years ago, in the time before the BugZilla Tracker>
And I’d appreciate a simple text editor built in to the IDE to manage text file resources: <https://xojo.com/issue/14051>
This is my preferred method but just be aware the SQL is then accessible to the user - (Contents of the App bundle and resource files). A client may not want the SQL to be available for security/IP reasons. I use obfuscated strings in that scenario.
So do I, combined with conditional compiling to avoid inclusion of code for other plattforms / backends at all.
Supplemented with some helper functions to handle parameter replacements and stripping of whitespace / SQL comments etc, so I can reduce the footprint in the server’s log file to a minimum as well.
I’ve made myself a small tool to edit and store queries and prepared statements in a encrypted sqlite database which exists in the applications resource folder. When the application starts it takes all the strings into a dictionary which can be looked up verry fast.
I am building an application which gives the end-user the choice for using SQLite, MySQL or PostgreSQL. Depending on the configuration it’s decided which database plugin to be used and queries might be different. I believe maintenance is easier when keeping them outside the main-executable and I give every stored query or prepared statement a name, an index and a description.
I think there are more ways to go and would say if satisfied with the method you’re using just stick with it.