Sql prepare and like

Databases
1

Hi! I’m new to xojo, I’m getting stuck with this query on a mssql server db and a xojo webapp…

ps = db.Prepare(“SELECT DTA_STUDE.CODSTUDE, ANA_STUD.COGSTUDE, ANA_STUD.NOMSTUDE, DTA_STUDE.CODFSRED, DTA_STUDE.SALBUONI, DTA_STUDE.VALBUONI FROM DTA_STUDE INNER JOIN ANA_STUD ON DTA_STUDE.CODSTUDE = ANA_STUD.CODSTUDE WHERE DTA_STUDE.CODSCUOL = ? AND DTA_STUDE.CLASTUDE = ? AND DTA_STUDE.SEZSTUDE = ? AND DTA_STUDE.CODANNSC = ? AND ANA_STUD.TIPSTUDE= ‘S’ AND ANA_STUD.COGSTUDE LIKE ? ORDER BY ANA_STUD.COGSTUDE”)
ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(1, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(2, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(3, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(0, SpScuola.Text)
ps.Bind(1, SpClasse.Text)
ps.Bind(2, SpSezione.Text)
ps.Bind(3, Login_Page.SpAnnoSco.Text)
ps.Bind(4, RicercaCognome)

the problem is referring to a WebSearchField (is called RicercaCognome). If I put the entire value of the field (for example ‘ROSSI’) it works, but If I put only the beginning of the field value (for example ‘ROS’) no record is returned.
Maybe is wrong something in the query?

please, give me an hint…

regards,

cirollo

2

Try with

[code]ps.Bind(4, RicercaCognome + “%”)

[/code]

3

If you use LIKE in a PreparedStatement, you need to add % and _ to the String you Bind.

ROS% will find all that starts with ROS
%ROS will fin all that ends with ROS
%ROS% will find all that contains ROS
RO__I will find all that starts with RO followed by 2 chars and ends with I
and so on :slight_smile:

In short terms, use @Wayne Golding advice :slight_smile:

4

Wayne,

tried your suggestion but I get this error…

undefined operator. type websearchfield does not define “operator_add” with type textliteral

5

Not at my Desk, but try the following:

ps.Bind(4, RicercaCognome.Text + "%")
6

so, ps.Bind(4, RicercaCognome.Text + “%”) is correct, no error on compile but it doesn’t work!

tried also to put in the searchbox ROS% from keyboard but no records returner…the strange is that the entire name works

7

What happens if you use the Database Encoding?

Something like

Dim s As String = RicercaCognome.Text + "%" ps.Bind(4, s.ConvertEncoding(Encodings.WindowANSI))

WindowANSI is just an example. Check the Database (Table) Encoding before.

8

BTW: You can combine the Bind and Type in 1 Line.
Instead of:

ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING) ps.Bind(4, RicercaCognome)

you can use

Dim s As String = RicercaCognome.Text + "%" ps.Bind(4, s.ConvertEncoding(Encodings.WindowANSI), MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)

9

mmmhh…

if I check the collation of the db I get

Latin1_Generale_CI_AS

and, by the way, your example returns me: item does not exists (referring to Ecoding.Windows.ANSI)

If I use another app (made in Visual Fox Pro) on the same db and search for ROS% it works, strange! so I thins the encoding is ok for % char…

10

Wrote it from the top of my head. There should be no Dot between Windows and ANSI :wink:

11

And it should be Encoding, not Ecodiing. :stuck_out_tongue:

12

Sorry, your code is correct and I get the error…is when I reported the error that I made mistakes…

13

To be 100% correct, it should be Encodings :stuck_out_tongue:

14

To debug a query like this I would use some database access tool or a terminal session to verify that your query does in fact return the intended result (maybe you need ilike instead of like or something). Only once you know that you should tackle the task of turning that into a prepared query.

15

@Maximilian Tyrtania

tried the same query with SQL2008 R2 enterprise manager and it works…

I noticed using the profiler that the parameter becomes

… like 'ROS '%

tried also with

Dim s As String = RicercaCognome.Text.trim

but no luck!