preparedstatement MBS

  1. 2 weeks ago

    Russ L

    Jan 7 Pre-Release Testers, Xojo Pro

    hi,

    does anyone have an example of using prepared statement to insert data into MSSQL?
    i looked on the site, but it seems to be a stored procedure example using param1

    i know some databases use ? chars but I'm unsure about sql server

    Russ

    YAY! Fixed.

    I was creating a preparedstatment on the source db not the destination.

    I do enjoy making myself look stupid in public :-)

  2. Christian S

    Jan 7 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    With MBS Plugin you can always use ? Or : followed by name or ID.

  3. Russ L

    Jan 7 Pre-Release Testers, Xojo Pro

    yeah, its strange. I'm trying to copy data from a mysql recordset into a mssql table with the same structure
    the code below produces no errors, but no data gets written either.
    in this case the table contains two fields an Id int and a varchar(50) but i have dozens of tables i want to do his with, so i want it to build itself

    I'm sure i must be mssing something obvious, but i cant see what. Can you?

    Russ

    dim rsData as recordset = GetData("select * from " + tname)
    if rsdata <> nil  then
      //Build up the SQL
      dim ins as string ="SET IDENTITY_INSERT [" + Tname + "] ON; "  + EndOfLine + " INSERT into [" + Tname + "] ("
      for c as Integer = 1 to rsdata.FieldCount -1
        ins = ins + "[" + rsdata.IdxField(c).Name + "], "
      next
      ins = ins + "[" + rsdata.IdxField(rsdata.FieldCount).Name + "]) VALUES (" 
      
      for c as Integer = 1 to rsdata.FieldCount 
        ins = ins + ":f" +str(c) + ","
      next
      ins = ins.Left(ins.len-1) + ")"
      
      
      // Now insert the data
      dim p as SQLPreparedStatementMBS = db.Prepare(ins)
      
      for c as Integer = 1 to rsdata.FieldCount 
        dim t as integer = rsdata.ColumnType(c-1)
        select case t
        case 3,7
          p.BindType("f"+ str(c), SQLPreparedStatementMBS.kTypeLong)
        case 5,10
          p.BindType( "f" + str(c), SQLPreparedStatementMBS.kTypeString)
        case else
          msgbox (str(t))
        end select
      next
      
      
      while not rsdata.eof
        for c as Integer = 1 to rsdata.FieldCount 
          p.bind("f"+str(c), rsdata.IdxField(c).Value )
        next
        
        p.SQLExecute 
        if db2.Error then msgbox("Error : " + db2.ErrorMessage)
        
        rsdata.movenext
      wend
    end if
  4. Christian S

    Jan 7 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    did you log something?

    Like what is final SQL statement?
    do you get errors?

    you can turn on exceptions to not miss an error.

  5. Russ L

    Jan 7 Pre-Release Testers, Xojo Pro

    well, no errors. I step through in the debugger. the sql just shows the :f1 :f2, not the actual sql.

    i will try with exceptions rather than checking for errors.

  6. Russ L

    Jan 7 Pre-Release Testers, Xojo Pro

    Ah!

    i get this exception on the execute

    Commands out of sync; you can't run this command now

  7. Russ L

    Jan 7 Pre-Release Testers, Xojo Pro

    is it because I'm trying to set identity_insert as a first statement in the SQL do you think?

  8. Christian S

    Jan 7 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    We have three ways to talk to Microsoft SQL server and not all of them allow multiple record sets.

    Did you turn scrollable on? That helps for some.

  9. Tanner L

    is not verified Jan 7 Pre-Release Testers Toronto, Canada

    @Russ L ins = ins + "[" + rsdata.IdxField(rsdata.FieldCount).Name + "]) VALUES ("

    What are you expecting rsdata.FieldCount to represent here?

  10. Russ L

    Jan 8 Pre-Release Testers, Xojo Pro

    .

    @Tanner L What are you expecting rsdata.FieldCount to represent here?

    @Tanner L the index of the last field in the recordset

  11. Russ L

    Jan 8 Pre-Release Testers, Xojo Pro Answer

    YAY! Fixed.

    I was creating a preparedstatment on the source db not the destination.

    I do enjoy making myself look stupid in public :-)

or Sign Up to reply!