SQL Insert Into - Part Deux

Targets Windows
1

OK, Kem and Alberto helped me to get a working INSERT INTO statement as follows:

  "SELECT 2, ProposalID, LineupID, CasesListBoxRow, CasesListboxValue,CellEditControl, CellDataSource " + _
  "FROM CasesListboxContent " + _
  "WHERE CasesListboxColumn = 4"
  
  db.SQLExecute(sql)

What I need now, but can't figure out how to do it after reading everything I could about INSERT INTO sql statements:

1) Make both the "2" and the "4" in my existing statement variable. I looked at dynamic SQL but it was over my head.

2) Be able to take one source column (e.g., the "4") and insert it's data into multiple columns (e.g., not just the "2" shown in the statement.

How do I do these two things? ... Thanks in advance!

Windows 10
Xojo 2016 R1
3

Are you using ExecuteSQL? Use the ParamArray changing 2 and 4 with ? or $1, $2 depending on your database.
For 2, execute the SQL 2 or more times.
More info:
https://documentation.xojo.com/api/databases/database.html#database-executesql

Something like:
yourdb.executeSQL(sql, ValueFor2, ValueFor4)
or I misunderstood your question?

4

Thats a basic SELECT statement
It doesnt insert anything into anything.

What do you actually want to do here?

you probably want something along these lines: (this is the select, not the insert)

"SELECT ?, ProposalID, LineupID, CasesListBoxRow, CasesListboxValue,CellEditControl, CellDataSource 
FROM CasesListboxContent 
WHERE CasesListboxColumn = ?", valueforthefirstitem, valuefortheseconditem
5

You need to read his other thread. He only posted the select part of the query

1 Like
6

His problem is :point_up:t2:

He could implement his own “fast prepare” using string replacements in SQL templates.

2 Likes
7

Prepared statements exist in API1, though:

Dim ps As SQLitePreparedStatement
ps = theDb.Prepare("SELECT ?, ProposalID, LineupID, CasesListBoxRow, CasesListboxValue,CellEditControl, CellDataSource 
FROM CasesListboxContent 
WHERE CasesListboxColumn = ?")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) //could be a number
ps.Bind(0, theFirstValue)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)  //could be a number
ps.Bind(1, theSecondValue)

resultset =  ps.SQLSelect

so it should be possible to just wrap that up in an INSERT and call SQLExecute…?

2 Likes
8

Sorry I missed Xojo 2016.

9

If the parameters are just integers there’s no injection, you may use an one-liner replacement safely in a well designed template.

1 Like
10

Sorry for the delay in getting back to you … I had to step out for the last 6 hours. You guys are ALL gold! I can’t begin to tell you what your support means to me.

I thought I tried this with a prepared statement and it didn’t do what I needed … but then again, I’m going to have to compare my prepared statement to what Rick and Jeff are talking about in their responses. I’m going to try right now what you guys have proposed and will get back with you.

Thanks again!

11

Using what Jeff, Rick and Alberto shared in their different responses, I put together the following code which solved my need completely! Thanks again to you guys (and Kem Tekinay from an earlier post) … you’re the best!

  sql = "INSERT INTO CasesListboxContent (CasesListboxColumn, ProposalID, LineupID, CasesListBoxRow,CasesListboxValue, CellEditControl, CellDataSource) " + _
  "SELECT ?, ProposalID, LineupID, CasesListBoxRow, CasesListboxValue,CellEditControl, CellDataSource " + _
  "FROM CasesListboxContent " + _
  "WHERE CasesListboxColumn = ?"
  
  Dim colStart As Integer  // starting destination column number
  Dim colEnd As Integer  // ending destination column number
  Dim colSource As Integer  // source column number
  
  colStart = 2
  colEnd = oldColumnCount
  colSource = oldColumnCount + 1
  
  // Fill multiple columns with inserted data
  for caseCol As Integer = colStart to colEnd
    ps = db.Prepare(sql)
    rs = ps.SQLSelect(caseCol, colSource)
    
    if db.Error then
      
      // open Modal MsgBox window and inform user of error
      taMessage = "SELECT INSERT INTO FROM CasesListBoxContents DB Error: " + db.ErrorMessage
      wndModalMsgBox.ShowModal
      
    end if
    
  next
1 Like
12

I don’t remember how things worked those days but I’m surprised that this worked without bindings in 2016.

13

Actually Rick, it worked when I tried it with MBS bindings, as well as, without bindings as I posted. Surprised me too … I’ve been using bindings for the past 6 years at least … totally unaware that I could do it without them!

14

We do it today without even creating a PreparedStatement (using the current SelectSQL() and ExecuteSQL()), but I remember writing my own lazy and safe “shortcuts” with Xojo around those Xojo 201x days, because some problems with the Xojo binding/prepare/use and/or avoiding complexities that I don’t remember today.

Today it is easy as

Try
 db.ExecuteSQL( "UPDATE User SET name=? WHERE user_id=?", theName, theCode)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try
1 Like
15

Wow! That is a lot easier! I never had good luck with the XOJO SQL database plugin, so I went with everything MBS, especially the prepared statements and associated bindings. Never had a problem after that.

Thanks again for all the help! I retired last month (I’ll be 73 in July) but promised my biggest customer that I’d do some feature adds to one of my apps that they are using. The SQL issue you (and Alberto and Jeff) helped me through was the last hurdle I had. I should be able to finish the coding now this weekend and finally officially hang up the mouse (at least for business purposes) thanks to you guys.

2 Likes