This is webapplication where user input member name or email etc and this code will
search in database and return the member list
If using PrepareStatement following code is not returning any data but without
preparestatement it is working fine.
using Xojo.Data
using Xojo.Core
dim SQL as Text
dim qry() as Text
dim i as integer = 0
qry = atext.Split(" ") -> atext = "jiten shah"
SQL = SQL + ""
SQL = SQL + "select "
SQL = SQL + " a.member_id, a.member_name, a.email, a.phone "
SQL = SQL + "from "
SQL = SQL + " member_master a "
SQL = SQL + " join member_card_detail b on a.member_id = b.member_id "
SQL = SQL + "where "
SQL = SQL + " a.cstat='x' and b.cstat='x' "
SQL = SQL + " and b.card_collection_date is null "
SQL = SQL + " and b.card_printing_date is not null "
for i = 0 to qry.Ubound
SQL = SQL + " and instr(lower(concat_ws('', a.member_id, a.member_name, a.email, a.phone)), ?) > 0 "
next
SQL = SQL + "order by "
SQL = SQL + " a.member_name "
dim pstmt as PreparedSQLStatement
pstmt = DB.Prepare(SQL)
dim pmt As New Dictionary
dim b_text as Text
'
i = 0
for each b_text in qry
pmt.Value(i) = b_text
i = i + 1
next
for each entry as DictionaryEntry in pmt
i = entry.Key
b_text = entry.Value
pstmt.BindType(i, MySQLPreparedStatement.MYSQL_TYPE_STRING)
pstmt.Bind(i, b_Text)
next
dim rs as RecordSet = pstmt.SQLSelect()
if DB.Error = True then
msgbox(DB.ErrorMessage)
return Nil
end if
return rs