problem with MS SQL PreparedStatement with Where Clause

Databases
1

Hi everybody,

I have a problem with SQL Prepared Statement, the query not return value for Manager “Loiseau Stphane”. It’s ok for “Dupond Franck”
I think, it’s character “” which is problematic.

Have you ever encountered this problem

Thanks for your help.

Jrme

My Query code :

dim db as New MSSQLServerDatabase
Dim data As RecordSet
Dim ps As MSSQLServerPreparedStatement
Dim SQL as string
db.Host = "SRV001\\SQLEXPRESS"  // or just the IP if using the default instance
db.DatabaseName = "DATA_DB"
db.UserName = "DATA_USER"  // or "Domain\\UserID for trusted domain accounts
db.Password = "passwd"

dim Mois_CAL as String ="CAL_201901"

If db.Connect Then
  SQL="SELECT * FROM [DATA_DB].[dbo].["+Mois_CAL+"] WHERE Manager in ( ? , ? )"
  
  ps = db.Prepare(SQL)
  ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
  ps.BindType(1, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
  ps.Bind(0,"Dupond Franck")
  ps.Bind(1,"Loiseau Stphane")
  data = ps.SQLSelect()
  
  If data <> Nil Then
    GridList.ColumnCount=data.FieldCount
    While Not data.EOF
      GridList.AddRow("")
      For w As Integer = 0 To data.FieldCount-1
        GridList.cell(GridList.LastIndex,w)=data.IdxField(w+1).StringValue
      Next
      data.MoveNext
    Wend
    data.Close
  End If
Else
  MsgBox("Connection error:" +db.ErrorMessage)
End If
2

Hi,

perhaps it is an problem with the encoding. Of course it is possible to do that.
But as we have no information about your encodings and db-contents it is impossible to go further.
It also could be a blank or other special chars.
I think, your query comes with a different encoding then the db uses…
You can try to select the Loiseau Stphane dataset by id and have a look what you get.
You could try to run your query without xojo.

4

The DB collation is French_CI_AS.

If I use no PreparedStatement, the query result is ok.

My Code without Prepared Statement :

dim db as New MSSQLServerDatabase
Dim data As RecordSet
Dim SQL as string
db.Host = "SRV001\\SQLEXPRESS"  // or just the IP if using the default instance
db.DatabaseName = "DATA_DB"
db.UserName = "DATA_USER"  // or "Domain\\UserID for trusted domain accounts
db.Password = "passwd"

dim Mois_CAL as String ="CAL_201901"

If db.Connect Then
  SQL="SELECT * FROM [DATA_DB].[dbo].["+Mois_CAL+"] WHERE Manager in ('Dupond Franck','Loiseau Stphane' )"
  
data = db.SQLSelect(SQL)
  
  If data <> Nil Then
    GridList.ColumnCount=data.FieldCount
    While Not data.EOF
      GridList.AddRow("")
      For w As Integer = 0 To data.FieldCount-1
        GridList.cell(GridList.LastIndex,w)=data.IdxField(w+1).StringValue
      Next
      data.MoveNext
    Wend
    data.Close
  End If
Else
  MsgBox("Connection error:" +db.ErrorMessage)
End If
5

more important is the encoding the connection is using. Xojo works with utf8. Perhaps you have to convert that.

6

Seems like MSSQL French_CI_AS is Windows-1252. So you can try to convert your string to ISO Latin-1 bevor sending it to the db.

7

Same result if I use :

  ps = db.Prepare(SQL)
  ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
  ps.Bind(0,ConvertEncoding("Loiseau Stéphane", Encodings.WindowsLatin1))
  data = ps.SQLSelect()
8

add some special chars with xojo to the db and have al look in the ssms what you get there

9

The MSSQLServer JDBC driver has a dedicated “sendStringParametersAsUnicode” setting , see https://docs.microsoft.com/de-de/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017. Maybe Xojo needs to expose something similar?

10

[quote=429400:@JrmeLeray] ps = db.Prepare(SQL)
ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(0,ConvertEncoding(“Loiseau Stphane”, Encodings.WindowsLatin1))
data = ps.SQLSelect()[/quote]

Don’t know what its doing internally. You send a bit of utf-8 and a bit of latin1. I thing it will be converted back to utf-8.
So it is really important to know which encoding is used for the db-connection.

11

[quote=429403:@Marius Dieter Noetzel]Don’t know what its doing internally. You send a bit of utf-8 and a bit of latin1. I thing it will be converted back to utf-8.
So it is really important to know which encoding is used for the db-connection.[/quote]

If I insert record with Xojo and execute Select Query. My new record is in the result.

But Data present in db imported by ssms.

12

So you inserted or something like that with prepared statement in xojo and than you selected that new record with another tool (that ist working with the original data above e.g. ssms.)
Do you get what you expect there?
If you insert it with xojo (prepared statement) and select it with xojo, and use a wrong encoding both time, you could get good results but have wrong data saved to the db.
So the most important thing is to find out, which part of the game is doing the wrong encoding.

13

If I use :

dim Mois_CAL as String ="CAL_201901"
If db.Connect Then
  SQL="SELECT * FROM [DATA_DB].[dbo].["+Mois_CAL+"] WHERE Manager in ('Dupond Franck','Loiseau Stphane' )"
data = db.SQLSelect(SQL)

There is no problem with collation or encoding. The Query return good result.

Is there a bug with PreparedStatement ?

14

AS I said. We don’t know which Problem we have, so how should we know if there is a bug?
Different code is doing different things. Of course prepared statement if doing things with the binding part. That is what it should do. It is only a bug if you put the correct encoding in and it gives you the wrong back, but at the moment we don’t know that it is doing that.
Encoding problems are not easy to debug…