revDataFromQuery with variable list question

[Stephen MacLean]

Hi Terry & Pete,

Thanks for the replies. Pete's suggestion was what I was looking for and your use of merge reminded me about using it for this part of it. 

This works beautifully:

put merge("%[[tFld]]%") into tFld

put "SELECT mFromGUID from messages WHERE mTextContent LIKE :1"

put revDataFromQuery(,comma,gDatabaseID,gMsgListQUERY,"tFld") into tSearchResult

I use this variable replacement form of revDataFromQuery because it helps prevent SQL injection attacks and I've had issues with merge on some punctuation in an input field. It also helps keep the queries more readable for me;)

To answer Terry's question, mTextContent is a column in the DB. The above select statement get's translated by LC to look like (with my variable containing the text "Dad"):

"SELECT mFromGUID from messages WHERE mTextContent LIKE '%Dad%'"

LC seems to know the column type and binds it correctly in SQLite.

Thanks again!

Best,

Steve

On Mar 15, 2014, at 3:50 AM, Terence Heaford <t.heaford at btinternet.com> wrote:

> I normally do my Sqlite queries like this, I think examples speak a thousand words.:
> 
>   put merge("select date,type,description,amount,balance,category,recID from '[[the uAcctName of this stack]]' order by date asc") into tSQL
>   put revDataFromQuery(tab, return, the uDB of this stack, tSQL) into tData
> 
> or 
> 
>   put "select categoryName from 'categoryList' order by categoryName asc" into tSQL
>   put revDataFromQuery(tab, return, the uDB of stack  “myStack", tSQL) into tCatNames
> 
> or
> 
>   put merge("SELECT date,type,description,amount,balance,category,recID from '[[the uAcctName of this stack]]' where date >= [[tStartDate]] and date <= [[tEndDate]] [[tCatStatement]] order by date asc") into tSQL
>   put revDataFromQuery(tab, return, the uDB of this stack, tSQL) into tData
> 
> or
> 
> put merge("type like '%[[tType]]%'") into tTypeQuery
> 
> put merge("[[tQuery]] [[tCatQuery]] [[tAmountQuery]] [[tDescQuery]] [[tTypeQuery]] order by date asc") into tSQL
> 
> 
> Is mTextContent a variable?
> 
> 
> All the best
> 
> Terry
> 
> 
> 
> On 15 Mar 2014, at 05:44, Stephen MacLean <smaclean at madmansoft.com> wrote:
> 
>> Hi All,
>> 
>> I'm having a problem with this:
>> 
>> put "SELECT mFromGUID from messages WHERE (mTextContent LIKE '%:1%')"  into gMsgListQuery
>> 
>> put revDataFromQuery(,comma, gDatabaseID, gMsgListQUERY,"tFld") into tSearchResult
>> 
>> puts nothing into tSearchResult and the result is empty.
>> 
>> Do I have escape something differently? Or just missing something?
>> 
>> TIA,
>> 
>> Steve MacLean
>> 
>> 
>> 
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode