MSSQLPreparedStatement not working

Databases
21

He mentioned in his first post that providing the values in the SQLSelect brought up an error, but perhaps providing them through variables instead of as literals? It doesn’t make sense to me that it would make a difference, but hey, stranger things have happened.

22

The first post has no bind types in the first example and binds to literals in the second

23

Oh, I see. You are still recommending BindType, just avoiding Bind and using SQLSelect with parameters. Got it.

If I get a chance, and it’s not too complicated, I’ll see if I can install MS SQL Server in Parallels so I can play with this on the plane tomorrow.

24

Not “recommending”
Just saying give it a try
I dont have an MS SQL Server to try to see whats up and what I would recommend

25

I quickly tried all the variations mentioned above with the same results… nilObjectException for the RecordSet.

26

Installed and tested. My results are exactly the same.

  dim db as new MSSQLServerDatabase
  db.Host = "127.0.0.1"
  db.UserName = "ktekinay"
  db.Password = "********"
  db.DatabaseName = "tester"
  
  if not db.Connect then
    AddToResult "Could not connect"
    AddToResult db.ErrorMessage
    return
  end if
  
  dim sql as string
  sql = "SELECT * FROM users"
  
  dim ps as PreparedSQLStatement = db.Prepare( sql )
  if db.Error then
    AddToResult db.ErrorMessage
    return
  end if
  
  dim rs as RecordSet = ps.SQLSelect
  if db.Error then
    AddToResult db.ErrorMessage
    return
  end if
  
  if rs is nil then
    AddToResult "Nil!"
    return
  end if
  
  while not rs.EOF
    AddToResult rs.Field( "name" ).StringValue + " " + rs.Field( "pass" ).StringValue
    
    rs.MoveNext
  wend

No error reported, but the RecordSet is nil. If I run this simple query directly, it returns a result.

27

[quote=182510:@Wayne Golding]I have used prepared statements with mssql without problem in the past, but I do use the sqlpreparedstatement type to make the statement db agnostic. Can’t see why there’d be a difference, but give it a try.

Also be aware that a bug in the MSSQL Native client causes issues with column types VarChar(Max) & Text along with others.[/quote]

Thanks for the suggestion on PreparedSQLStatement. I gave it a try with the same results (nil record set & no DB.Error) but I see your point on making it DB agnostic.

Looks like I’m going to have to use ODBC or give Christians SQL product a try until this is resolved.

28

I always use ODBC with MSSQL due to the bug in the Native client that causes crashes when data type sizes are undefined - Text, varchar(max) etc.

29

Like Wayne’s response above, I cut my losses trying to fool around with Xojo’s standard MSSQL plugin and switched to Christian’s MBS plug early on … I use prepared statements profusely within my code and large TEXT types as well … never had a problem ever since I switched.

30

Thanks, I see that the varchar(max) bug was reported in the MS native client to Microsoft back in 2009, and it’s still not fixed (Problem with varchar(max)). I’m really surprised this hasn’t received more attention from Microsoft.

31

Hi Don,

Thanks for the info. I think I’m going to give ODBC a try first since I have the Xojo Pro license and there is no additional cost involved (other than my time). If I run into issues, i’ll go the MBS route.

32

MBS Plugins allows variable binding by name, which I think is much safer than by index…
see
http://www.mbsplugins.net/archive/2014-03-12/SQLPreparedStatementMBS_improv/monkeybreadsoftware_blog_archive

just another reason for the plugin…