preparedstatement MBS

  1. 4 months 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 :-)

  12. 3 months ago

    Mike S

    Feb 13 S.E. Michigan

    Is this method of executing stored procedures via SQLPreparedStatementMBS expected to work on Macintosh platforms?

  13. Christian S

    Feb 13 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    yes, of course.

    Why?

  14. Mike S

    Feb 13 S.E. Michigan

    I have a stored procedure on an M$SQL server called "sp_AssignFields" that accepts 6 parameters, the last two of which are outputs. For example:

    ALTER PROCEDURE [dbo].[sp_AssignFields] 
    	-- Parameters
    	@Ble_id VARCHAR(30),
    	@Password VARCHAR(16),
    	@SerialNumber VARCHAR(16),
    	@Trace VARCHAR(MAX),
    	@IsNew BIT OUT,
    	@ErrorMessage VARCHAR(MAX) = '' OUT 
    AS
    	-- Setup
    ... etc...

    I have tried multiple ways to create an SQLPreparedStatementMBS, but so far have only been able to "brute-force" it with a complete sql statement and db.Select( sql )
    The example using "Param1" isn't clear to me...
    Any help is much appreciated.

  15. Mike S

    Feb 13 S.E. Michigan
    Edited 3 months ago

    // Using the example code, this is how I started:

        Dim strBLE_ID As String = "MyBleId"
        Dim strPassword As String = "MyPassword"
        Dim strSerial As String = "0123456789ABCDEF"   
        Dim strTraceInfo As String = "My App created this."
        Dim boIsNewReturned As Boolean = False
        Dim strErrorReturned As String
    
        Dim sql as string = "dbo.sp_AssignFields"
        Dim ps As SQLPreparedStatementMBS = db.Prepare(sql)
    
        // @Ble_id VARCHAR(30),
        // @Password VARCHAR(16),
        // @SerialNumber VARCHAR(16),
        // @Trace VARCHAR(255),
        // @IsNew BIT OUT,
        // @ErrorMessage VARCHAR(MAX) = '' OUT 
        
        ps.Bind( "Ble_id", strBLE_ID )
        ps.BindType( "Ble_id", ps.kTypeString )
         
        ps.Bind( "Password", strPassword )
        ps.BindType( "Password", ps.kTypeString )
         
        ps.Bind( "SerialNumber", strSerial )
        ps.BindType( "Password", ps.kTypeString )
         
        ps.Bind( "Trace", strTraceInfo )
        ps.BindType( "Trace", ps.kTypeString )
         
        ps.Bind( "IsNew", boIsNewReturned )
        ps.BindType( "IsNew", ps.kTypeBool )
         
        ps.Bind( "ErrorMessage", strErrorReturned )
        ps.BindType( "ErrorMessage", ps.kTypeString )
        
        dim r As RecordSet =  ps.SQLSelect
  16. Christian S

    Feb 13 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    For output functions, the recordset will not help.
    Please go with SQLCommandMBS class for that. Sorry

  17. Mike S

    Feb 13 S.E. Michigan

    Okay, thank you

or Sign Up to reply!