I’ve a web app with some filters on a window, like these:
from initial code… to inital code… they’re webpopupmenu.
I’ve added to them also the item " " (1 space), because I need, when these filters are empty (or maybe one of the two) to select all the records.
In SQL I do: SELECT field1,field2 FROM mytable WHERE field1 >=’ ’ and field1 <=’ ’
if I want all the records, but this in xojo doesn’t work, because no records are displayed (in my listbox).
If I put some values in the filters, everything work.
Here is a suggestion. It entails a slight change of strategy. Put your select statement in a stored procedure. The limits are defined as parameters in the stored procedure. In Xojo, you call the stored procedure and simply pass the parameters. The results are in the result recordset.
Your procedure could have a condition where parameters are a value such as % - the * in SQL server, for example. When % is encountered, you branch to a select without the parameters, then the result will be all records. Here is a link on the IF…ELSE operator in SQL Server.
This approach has two advantages: the work is done by the database server, and your code is safer.
Using a db’s stored procedure language as the “business logic server” is a common pattern
Personally I’d avoid it and build an actual “business logic server” that apps talk to
It’s seductive “Oh do it all on the server , it will be …”
faster
more secure
and NOT portable at all
Doing this ties you so tightly into a vendors db that IF you should EVER have to change out one for another you’ve made you life really hard - like maybe swapping out Sybase for Oracle (been there done that) or move from mySQL to Postgresql (been there done that too)
Check out multi-tier architectures
That said - the fact that the query doesn’t work “as is” seems to be the issue
I truly suspect the query gets constructed in a way that it returns the correct data - its just an empty record set or maybe an unchecked error
Dim ps As MSSQLServerPreparedStatement
Dim stringasql as string
stringasql="SELECT DTA_STUDE.CODSTUDE, ANA_STUD.COGSTUDE, ANA_STUD.NOMSTUDE, DTA_STUDE.CODSCUOL,"_
+" DTA_STUDE.CLASTUDE,DTA_STUDE.SEZSTUDE FROM DTA_STUDE INNER JOIN"_
+" ANA_STUD ON DTA_STUDE.CODSTUDE = ANA_STUD.CODSTUDE INNER JOIN SCU_UWEB ON DTA_STUDE.CODSCUOL ="_
+" SCU_UWEB.CODSCUOL WHERE SCU_UWEB.CODUWEB = ? AND DTA_STUDE.CODANNSC = ? "_
+" AND LEN(ANA_STUD.COGSTUDE)>0 AND ANA_STUD.TIPSTUDE=?"
// these can be empty, one or all
stringasql=stringasql+" AND DTA_STUDE.CODSCUOL >=? AND DTA_STUDE.CODSCUOL <=? "
stringasql=stringasql+" AND DTA_STUDE.CLASTUDE >=? AND DTA_STUDE.CLASTUDE <=? "
stringasql=stringasql+" AND DTA_STUDE.SEZSTUDE >=? AND DTA_STUDE.SEZSTUDE <=? "
stringasql=stringasql+" ORDER BY ANA_STUD.COGSTUDE"
//
ps = db.Prepare(stringasql)
ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(1, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(2, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
// these can be empty, one or all
ps.BindType(3, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(5, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(6, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(7, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(8, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(0, Login_Page.TxtUweb.Text)
ps.Bind(1, Login_Page.SpAnnoSco.Text)
ps.Bind(2, Statistiche.SpTipo.Text)
// these can be empty, one or all
ps.Bind(3, Statistiche.SpScuIni.Text)
ps.Bind(4, Statistiche.SpScuFin.Text)
ps.Bind(5, Statistiche.SpClaIni.Text)
ps.Bind(6, Statistiche.SpClaFin.Text)
ps.Bind(7, Statistiche.SpSezIni.Text)
ps.Bind(8, Statistiche.SpSezFin.Text)
the problem is that, if for example the 3rd filter is empy, I cannot remove it, passing from bind2 to bind4 (I think???) but bind4 should become bind3 and so on…
this is much complicated with many filters (for now I have 6 filters that can be empty, each one…)
