preparedstatement MBS


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


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

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?


[code]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©.Name + "], "
ins = ins + “[” + rsdata.IdxField(rsdata.FieldCount).Name + “]) VALUES (”

for c as Integer = 1 to rsdata.FieldCount
ins = ins + “:f” +str© + “,”
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©, SQLPreparedStatementMBS.kTypeLong)
case 5,10
p.BindType( “f” + str©, SQLPreparedStatementMBS.kTypeString)
case else
msgbox (str(t))
end select

while not rsdata.eof
for c as Integer = 1 to rsdata.FieldCount
p.bind(“f”+str©, rsdata.IdxField©.Value )

if db2.Error then msgbox("Error : " + db2.ErrorMessage)


end if

did you log something?

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

you can turn on exceptions to not miss an error.

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.


i get this exception on the execute

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

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

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.

What are you expecting rsdata.FieldCount to represent here?

.[quote=420483:@Tanner Lee]What are you expecting rsdata.FieldCount to represent here?[/quote]

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

YAY! Fixed.

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

I do enjoy making myself look stupid in public :slight_smile:

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

yes, of course.


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.

// 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[/code]

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

Okay, thank you