Hi,
I was just wondering when creating a new record do most prefer to set default values in the SQL create table code, or is it better to do it all in Xojo?
It seems cleaner to me to do it all in the SQL create table code and then execute:
INSERT INTO Table DEFAULT VALUESBut is there some benefit I’m missing by not doing it in Xojo?
INSERT INTO Table ( col1, col2, col3 ) VALUES ( val1, val2, val3 )Thank you
Hi Mark,
I think that this generally comes down to your preference. My preference is to have the data in SQL code such as:
INSERT INTO Table ( col1, col2, col3 ) VALUES ( val1, val2, val3 )Some of the reasons are purely selfish - When I am working on a database at midnight with some healthy caffeine intake, being absolutely clear in my code will minimize any mistakes. 
When using Xojo code, data input and retrieval is sometimes faster because the background code is likely optimized for the database.
Sincerely…
Thanks Eugene,
I understand what you’re saying. I just find that when you have a lot of columns to insert it can be hard to read.
I wish SQLite would support this syntax for better readability:
INSERT INTO table SET col1 = val1, col2 = val2, col3 = val3[quote=55759:@Mark S]Thanks Eugene,
I understand what you’re saying. I just find that when you have a lot of columns to insert it can be hard to read.
I wish SQLite would support this syntax for better readability:
INSERT INTO table SET col1 = val1, col2 = val2, col3 = val3I’d write it using a prepared statement not raw sql in either case
You know I was thinking that, but when I saw the docs on Prepared Statements using raw SQL for Inserts, I assumed using a prepared statement was not the way to go.
Is it safe to assume the example project included with 2013 3.3 on Prepared Statements is up to date and the way to go? I ask because I see two methods included in the project that are not used. The methods are Sqlize and Stringize
Thank you
With prepared statements you don’t have to try & guess what the right way to sqlize or stringize is at all
So those methods probably should have been removed from that example code
Thanks Norman.
Why can’t I bind an empty string? I have a TEXT column set up so that it cannot be NULL.
Using this returns the error, column cannot be NULL.
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, "")
Thank you
Are you inserting data ?
You don’t need to BIND for inserts - bind is for SELECT as the execution then places the values directly in the given variable - and it has to be a variable as you can’t place values into a literal
I use a stored procedure to insert/update/delete data. That way, I don’t have to write the logic more than once when building different applications (Desktop/Web/iOS/whatever) to manipulate the data. I find that much more convenient.
I fully agree.
Plus, you can make changes in SQL on the fly without having to re-compile your app every time you want to make a change.
I was taught to separate the business logic from the UI. The logic can change depending on new requirements, but to the user, everything stays the same. And the stored procedure is code as close as possible to the data as you can get, so it will execute a lot faster than if you have to go through several layers (network, etc.) every time you manipulate data. For example, if you move an invoice to history, there is no point of pulling all that invoice data across the network. It’s much easier just to send the invoice number to a stored procedure and let the server do the processing. Invoices have invoice line items, some have one, some have 100s. And I have no need of any of these lines to come to the client side just so that I can create history lines.
I have no idea how easy it is to use stored procedures in SQLite databases as I have always used Postgres as my DB engine and there they work just fine.
SQLite does not support stored procedures.
[quote=55831:@Norman Palardy]Are you inserting data ?
You don’t need to BIND for inserts - bind is for SELECT as the execution then places the values directly in the given variable - and it has to be a variable as you can’t place values into a literal[/quote]
Norman, I was following the code from the Xojo example database. Copy and pasted below…
[code] db = New SQLiteDatabase
If Not db.Connect Then
MsgBox(“The example could not create an in memory database and cannot be used without it. The demo app will now quit.”)
Quit
End If
// Create the tables for the database
DB.SQLExecute "create table Customers (FirstName varchar, LastName varchar, Address varchar, Email varchar, "+ _
"Company varchar, Phone varchar, Fax varchar, City varchar, State varchar, PostalCode varchar, "+ _
“ID integer NOT NULL PRIMARY KEY)”
// and insert some sample data
Dim sql As String = “insert into Customers (FirstName, LastName, Address, Email, Company, Phone, Fax, City, State, PostalCode, ID ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”
Dim prepInsert As SQLitePreparedStatement = db.Prepare(sql)
Dim lines(-1) As String = Split(ReplaceLineEndings(Customers,EndOfLine), EndOfLine)
For ID As Integer = 0 To UBound(lines)
If "" <> Trim(lines(id)) Then
Dim fields(-1) As String = Split(lines(ID), ChrB(9))
Const firstname = 0
Const lastname = 1
Const address = 2
Const email = 3
Const company = 4
Const phone = 5
Const fax = 6
Const city = 7
Const state = 8
Const postalcode = 9
Const idField = 10
Dim cid As Integer = Val(fields(idFIeld))
prepInsert.BindType(0, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(0, fields(firstname))
prepInsert.BindType(1,REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(1, fields(lastname))
prepInsert.BindType(2,REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(2, fields(address))
prepInsert.BindType(3, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(3, fields(email))
prepInsert.BindType(4, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(4, fields(company))
prepInsert.BindType(5, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(5, fields(phone))
prepInsert.BindType(6, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(6, fields(fax))
prepInsert.BindType(7, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(7, fields(city))
prepInsert.BindType(8, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(8, fields(state))
prepInsert.BindType(9, REALSQLPreparedStatement.SQLITE_TEXT)
prepInsert.Bind(9, fields(postalcode))
prepInsert.BindType(2,REALSQLPreparedStatement.SQLITE_INTEGER)
prepInsert.Bind(2, cid)
prepInsert.SQLExecute
db.Commit
End If
Next
[/code]
I used to do that about 20 years ago
But once you have a LOT of business logic in stored procedures & you then find your company decides to switch to a new vendor (gawd knows why) you spend a year and a half rewriting everything out of one proprietary stored procedures language into some other kind of middle ware (we use Java) and then everything can talk to that and the database is not touched by ANYTHING other than that.
SQL & various stored procedure dialects are great until you have to move to something else.
Or you need something that you can’t do nicely in whatever dialect you’re using.
[quote=55862:@Mark S]SQLite does not support stored procedures.
Norman, I was following the code from the Xojo example database. Copy and pasted below
[/quote]
AH
Yeah you can SEND the values in FROM a variable - but not a literal
In this example it READS the value from the variable but it can’t do that with a literal since - well its not a variable
I’m probably missing something really obvious here, but I just can’t find it. The error I’m getting is, col2 cannot be NULL.
[code] dim sql as string
dim somestring as String = “test”
dim somevalue as Integer = 9999
sql = "INSERT INTO Table ( "+ _
"col1, " + _
"col2, " + _
"col3 ) " + _
“VALUES ( ?,?,? )”
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0,SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, somevalue)
ps.BindType(1,SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(1, somevalue)
ps.BindType(2,SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(2, somestring)
ps.SqlExecute (sql)[/code]
Hi Mark,
Here are a couple of items to check…
[code] dim sql as string
dim somestring as String = “test”
dim somevalue as Integer = 9999
sql = “INSERT INTO Table1 (col1, col2,col3 ) VALUES ( ?, ?, ?)”
MsgBox sql
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0,SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(1,SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2,SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, somevalue)
ps.Bind(1, somevalue)
ps.Bind(2, somestring)
ps.SqlExecute[/code]
I modified the code and the above works on my Windows 8.1, Xojo 2013 r4.1 computer
I am not able to modify my previous post.
I added the code below to double-check the sql string
MsgBox sqlFeel free to delete it
Thank you Eugene! When converting my raw sql code to my new prepared statement, I left in the sql at the end of sqlexecute. All is well now.
Also thanks to Norman.
Happy New Year All!