Problem with commits to Database

Noah_Chapman · July 25, 2022, 6:26pm

Hey everyone, I am currently having a problem with adding to one of my tables in the database. Attached below is a diagram of a snippet of the database in which there is a parent class “PRJ” and linked to “PROJECT-APP” by a foreign key “PRJID”. The code snippet attached below shows what I am trying to do in which I generate a PRJ number for the PRJ table, insert that data into the PRJ table, and then use that exact same number to insert it into the PROJECT-APP table. The SQL query works for the PRJ table but not for the PROJECT-APP table. Let me know if I need to explain it more, I am still new to most of this database stuff so any feedback would be appreciated. Thanks!

Diagram-Capture

Ian_Kennedy · July 25, 2022, 6:49pm

Instead of putting a picture of your code copy and past the text and put it between [ Code ] [ /Code ] tags. (without the spaces next to the [ ])

Ian_Kennedy · July 25, 2022, 6:52pm

Instead of using text to build your sql you should use PreparedStatements. It is far safer and will also deal with quoting for you. Your problem could easily be due to unexpected characters in your data. For example ’ would cause a problem.

Noah_Chapman · July 25, 2022, 9:07pm

Sorry for the delayed response but here is the code.

[Var sql, sql2, Pnum As String
Var Pid As Integer
Var rs As RowSet
Var doc As New PDFDocument
Dim d As New Date

’ Create SQL Command Statement

sql = “SELECT PRJID FROM PRJ ORDER BY PRJID DESC LIMIT 1”

Try
rs = App.MyDatabase.SelectSQL(sql)
Pid = 20081
Catch error As DatabaseException
MessageBox(error.Message)
End Try

If TextFieldProjectNumber.Text <> “” Then
Pnum = TextFieldProjectNumber.Text.Trim(“PRJ-”)
Else
Pid = Pid + 1
Pnum = Pid.ToString
End If

sql = “INSERT INTO PRJ (PRJID, PRJCLASS) VALUES (”
sql = sql + “'” + SQLify(Pnum) + “‘, "
sql = sql + "’” + SQLify(“APP”) + “'”
sql = sql + “)”

Try
App.MyDatabase.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox(error.Message)
End Try

sql = “INSERT INTO PROJECT-APP (Comments, Manufacturer, NewAPPNo, PRJ_ID, PRJDetails, Quantity, ReferenceInfo, SalesOrderNo, ShipDate, Type, User) VALUES (”
sql = sql + “'” + SQLify(TextAreaComments.Text) + “‘, "
sql = sql + "’” + SQLify(RadioGroupManufacturer.SelectedItem.Caption) + “‘, "
sql = sql + "’” + SQLify(RadioGroupNewAPPNo.SelectedItem.Caption) + “‘, "
sql = sql + "’” + SQLify(Pnum) + “‘, "
sql = sql + "’” + SQLify(TextAreaProjectDetail.Text) + “‘, "
sql = sql + "’” + SQLify(TextAreaQuantity.Text) + “‘, "
sql = sql + "’” + SQLify(TextAreaReferenceDoc.Text) + “‘, "
sql = sql + "’” + SQLify(TextFieldSalesOrderNo.Text) + “‘, "
sql = sql + "’” + SQLify(TextFieldOrderDate.Text) + “‘, "
sql = sql + "’” + SQLify(ComboBoxProjectType.SelectedRowValue) + “‘, "
sql = sql + "’” + SQLify(PopupMenuUser.SelectedRowValue) + “'”
sql = sql + “)”

Try
App.MyDatabase.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox(error.Message)
End Try][/Code]

Noah_Chapman · July 25, 2022, 9:09pm

Also here is an updated look at the tables. I changed some variable names around thinking that could be the confusion which it wasn’t.

UpdatedTables

Ian_Kennedy · July 25, 2022, 9:57pm

Sorry, if you wrap it in [ followed by Code followed by ] at the start and [ /Code followed by ]

[ code ] ← miss the spaces out
your code here
[ /code ] ← miss the spaces out

Like that it is far more readable, formatted nicely.

Anyhow, as I said in the other post. Rather than doing code like:

sql = “INSERT INTO PROJECT-APP (Comments, Manufacturer, NewAPPNo, PRJ_ID, PRJDetails, Quantity, ReferenceInfo, SalesOrderNo, ShipDate, Type, User) VALUES (”
sql = sql + “'” + SQLify(TextAreaComments.Text) + “‘, "
sql = sql + "’” + SQLify(RadioGroupManufacturer.SelectedItem.Caption) + “‘, "
sql = sql + "’” + SQLify(RadioGroupNewAPPNo.SelectedItem.Caption) + “‘, "
sql = sql + "’” + SQLify(Pnum) + “‘, "
sql = sql + "’” + SQLify(TextAreaProjectDetail.Text) + “‘, "
sql = sql + "’” + SQLify(TextAreaQuantity.Text) + “‘, "
sql = sql + "’” + SQLify(TextAreaReferenceDoc.Text) + “‘, "
sql = sql + "’” + SQLify(TextFieldSalesOrderNo.Text) + “‘, "
sql = sql + "’” + SQLify(TextFieldOrderDate.Text) + “‘, "
sql = sql + "’” + SQLify(ComboBoxProjectType.SelectedRowValue) + “‘, "
sql = sql + "’” + SQLify(PopupMenuUser.SelectedRowValue) + “'”
sql = sql + “)”

you should use code like:

sql = "insert into INTO PROJECT-APP (Comments, Manufacturer, NewAPPNo, PRJ_ID, PRJDetails, Quantity, ReferenceInfo, SalesOrderNo, ShipDate, Type, User) "
sql = sql + " VALUES (?,?,?,?,?,?,?,?,?,?)"

Var oPrep As SQLitePreparedStatement = YourDB.prepare( sql )

oPrep.BindType( 0, SQLitePreparedStatement.SQLITE_TEXT ) ' The types will depend on the database engine
oPrep.BindType( 1, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 2, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 3, SQLitePreparedStatement.SQLITE_INTEGER )
oPrep.BindType( 4, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 5, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 6, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 7, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 8, SQLitePreparedStatement.SQLITE_TEXT )
oPrep.BindType( 9, SQLitePreparedStatement.SQLITE_TEXT )

oPrep.ExecuteSQL( 
   TextAreaComments.Text, _
   RadioGroupManufacturer.SelectedItem.Caption, _
   RadioGroupNewAPPNo.SelectedItem.Caption, _
   Pnum, _
   TextAreaProjectDetail.Text, _
   TextAreaQuantity.Text, _
   TextAreaReferenceDoc.Text, _
   TextFieldSalesOrderNo.Text, _
   TextFieldOrderDate.Text, _
   ComboBoxProjectType.SelectedRowValue, _
   PopupMenuUser.SelectedRowValue
)

This does a number of things for you:

It protects you from someone putting crazy data into your input sources. Look up SQL injection
It takes care of all of the odd characters such as " and ’ which will screw up your string implementation.

OK, It is a little bit more work. You have to specify the types but there are advantages, beyond the ones listed above.

Once you have defined a prepared statement you can reuse the object multiple times.
If you use it more than once it is very much faster (like 10 times or more, faster).

// Prepare your statement
// Call the bindtypes

Then any time you need to execute it you can simply call the same oPrep.ExecuteSQL call over and over. Super fast.

TimStreater · July 25, 2022, 10:00pm

Noah Chapman:

Var sql, sql2, Pnum As String
Var Pid As Integer
Var rs As RowSet
Var doc As New PDFDocument
Dim d As New Date

’ Create SQL Command Statement

sql = “SELECT PRJID FROM PRJ ORDER BY PRJID DESC LIMIT 1”

Try
rs = App.MyDatabase.SelectSQL(sql)
Pid = 20081
Catch error As DatabaseException
MessageBox(error.Message)
End Try

If TextFieldProjectNumber.Text <> “” Then
Pnum = TextFieldProjectNumber.Text.Trim(“PRJ-”)
Else
Pid = Pid + 1
Pnum = Pid.ToString
End If

sql = “INSERT INTO PRJ (PRJID, PRJCLASS) VALUES (”
sql = sql + “'” + SQLify(Pnum) + “‘, "
sql = sql + "’” + SQLify(“APP”) + “'”
sql = sql + “)”

Try
App.MyDatabase.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox(error.Message)
End Try

sql = “INSERT INTO PROJECT-APP (Comments, Manufacturer, NewAPPNo, PRJ_ID, PRJDetails, Quantity, ReferenceInfo, SalesOrderNo, ShipDate, Type, User) VALUES (”
sql = sql + “'” + SQLify(TextAreaComments.Text) + “‘, "
sql = sql + "’” + SQLify(RadioGroupManufacturer.SelectedItem.Caption) + “‘, "
sql = sql + "’” + SQLify(RadioGroupNewAPPNo.SelectedItem.Caption) + “‘, "
sql = sql + "’” + SQLify(Pnum) + “‘, "
sql = sql + "’” + SQLify(TextAreaProjectDetail.Text) + “‘, "
sql = sql + "’” + SQLify(TextAreaQuantity.Text) + “‘, "
sql = sql + "’” + SQLify(TextAreaReferenceDoc.Text) + “‘, "
sql = sql + "’” + SQLify(TextFieldSalesOrderNo.Text) + “‘, "
sql = sql + "’” + SQLify(TextFieldOrderDate.Text) + “‘, "
sql = sql + "’” + SQLify(ComboBoxProjectType.SelectedRowValue) + “‘, "
sql = sql + "’” + SQLify(PopupMenuUser.SelectedRowValue) + “'”
sql = sql + “)”

Try
App.MyDatabase.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox(error.Message)
End Try

(1. Paste your code. 2. Select it. 3. Apply the </> button.)

Which database type?

What is SQLify?

You seem to have a mixture of proper double-quotes and curly ones, but that may just be a copy/paste issue.

What do you mean by “it works for one table but not the other”?

Ian_Kennedy · July 25, 2022, 10:06pm

Looking at your DB structure I’m assuming APPID is an auto increment field of some sort. Or it has a default. If not then you have a problem with your primary key on the PROJECT-APP table. Your insert statement doesn’t provide a value for that column. Unless your DB structure provides a value for it you will get an error.

I can see it is Unique and Not-null but I’m not sure of what the I indicates?

Ian_Kennedy · July 25, 2022, 10:26pm

This isn’t a particularly reliable way of getting the next available id number for a DB.

If someone adds a record between you calling the select statement and you running your insert statement then you are both working on the same number. A better solution is to perform the first insert and then ask the database which id it used. You then use that for any subsequent inserted related records.

Mysql as a last_insert_id function to get the value, I’m sure other databases have similar options.

// perform the insert, then call:
SELECT LAST_INSERT_ID();
// perform any secondary inserts using that ID.

Kem_Tekinay · July 26, 2022, 12:50am

If memory serves, BindType is not necessary with the SelectSQL and ExecuteSQL calls. Am I wrong?

Mark_Mitchell1 · July 26, 2022, 7:11am

Yep you are correct. No need to do the prepare statements including binds etc in recent versions of Xojo. This is done for you behind the scenes.

Ian_Kennedy · July 26, 2022, 8:02am

I didn’t think so but the docs seemed to say otherwise

Kem_Tekinay · July 26, 2022, 11:16am

Yes, I’ll confirm and add a note.

Noah_Chapman · July 26, 2022, 1:13pm

I am able to perform the INSERT INTO step for the PRJ table but when I try to make an entry into the PROJECT-APP table using the PRJID I added to the PRJ table, that’s where the code is breaking.

Noah_Chapman · July 26, 2022, 1:18pm

You are correct, the APPID field is autoincremented so I wouldn’t think that would be the issue. Could it perhaps be with the linking field of PRJID from the two tables and how I am trying to manually populate both tables with an incremented value?

Noah_Chapman · July 26, 2022, 1:26pm

Could it be something to do with the table name? When I step through the debugger, it returns an error stating, “syntax error at ‘-’” which I’d assume meant the problem is occurring when reading the sql statement. I didn’t think that would matter but it’s just another guess.

Noah_Chapman · July 26, 2022, 1:31pm

UPDATE: When I went into the database and code and removed the dash from the table name, the code executed perfectly and inserted into both tables in the database.

Ian_Kennedy · July 26, 2022, 1:42pm

“-” is a reserved item in SQL for subtraction. If it is in a field name you should use ’ around your name.

In terms of database I wouldn’t use an auto increment field in two places if they are supposed to be the link between the two tables. Have a master table with the auto increment, add a record too it. retrieve the number of the record added and then use it on the other table.

If they’re not forming a link then yes, an auto increment field is ideal for providing a unique key to the table.