how to encode sql containing % value and how to get result from multple tables

[andrew.sky]

Dear all,

I have two situation

Situation 1:

function searchStuff(...) {

var term = "test";
var sql = "Select * from sampleTable where description like %"+term
+"%;
database.getNativeData(sql);
}

The above always fails and when i study the log output, somehow the
first % just before the term is converted to a funny character.
Anyone have any idea how to resolve this ?


Situation 2:
I have two BCF javascript function that have sql select statement to
select stuff from two different tables..
Can i use one VCF to contain results from two tables ? If yes, how
can this be done and how do i get the result out ?

regards
Andrew

[Adam Gedeborg]

Andrew,

Well for Situation #1, you set up the SQL Statement wrong.  Now I am on vacation and have no way to test this, but my gut feeling is that you need to have single quotes surrounding your LIKE to make it work...as follows:

function searchStuff(...) {

var term = "test";
var sql = "Select * from sampleTable where description like '%"+term
+"%'";
database.getNativeData(sql);
}

After the second % that is a single quote followed by a double quote.

Good luck with that!  Hopefully somebody can help with Situation #2...I would say adding an element to the query results object like queryResults.queryResults2 might work. See if you can get a second opinion...

-Adam

Happy Thanksgiving!

[Mike Adkins]

Try this. 

function searchStuff(...) {

var term = "test";
var sql = "Select * from sampleTable where description like '%"+term
+"%' ";
database.getNativeData(sql);
}

You may want to set the case on the column as well as the var.

Mike

Sent from my iPhone

[Mike Adkins]

For solution two can you union the two tables with an extra column as
a table identifier like:

Select 'A', colA, colB, colC from tableA
Union
Select 'B', colA, colB, colC from tableB

So one BCF and one VCF.

Then when you iterate through you data on the VCF you know which table
the data came from. I do this all the the time with oracle.

Hope that helps.

Mike

Sent from my iPhone

On Nov 26, 2009, at 9:56 AM, "andrew.sky" <andre...@gmail.com> wrote:

> --
>
> You received this message because you are subscribed to the Google
> Groups "QuickConnect" group.
> To post to this group, send email to quickconn...@googlegroups.com
> .
> To unsubscribe from this group, send email to quickconnectiPh...@googlegroups.com
> .
> For more options, visit this group at http://groups.google.com/group/quickconnectiPhone?hl=en
> .
>
>

[andrew.sky]

Hi Adam,

Tried putting quotes, it worked for the following statement without
the % in front of the search term as follows:

var term = 'test';
var sql = "Select * from sampleTable where description like '"+term
+"%'";

but when i put two %, one in front and one behind the term,
QuickConnect doesn't seems to URLEncode the first % properly..
I realised from the log file in QuickConnect framework that when the
sql statement is passed to backend objective C,
the passed in value is as follows:

cmd=getData --> this is correct
msg = .......description%20like%20'%test%25'

The above log file shows that the last %' is encoded properly to %25',
but the first one is just %, i am wondering if we need any escape
character when we pass in '% .

On Nov 26, 11:15 pm, Adam Gedeborg <adamthestu...@gmail.com> wrote:
> Andrew,
> Well for Situation #1, you set up the SQL Statement wrong.  Now I am  
> on vacation and have no way to test this, but my gut feeling is that  
> you need to have single quotes surrounding your LIKE to make it  
> work...as follows:
>
> function searchStuff(...) {
>
> var term = "test";
> var sql = "Select * from sampleTable where description like '%"+term
> +"%'";
> database.getNativeData(sql);
>
> }
>
> After the second % that is a single quote followed by a double quote.
>
> Good luck with that!  Hopefully somebody can help with Situation  
> #2...I would say adding an element to the query results object like  
> queryResults.queryResults2 might work. See if you can get a second  
> opinion...
>
> -Adam
> Happy Thanksgiving!
>

[Lee Barney]

Andrew,

What was the statement you used with the pre and post % and what was the log output?

Lee

[Lee Barney]

Andrew,

Try this.  Change the lines starting at line 350 of the com.js file.  I haven't tried this myself but it may solve your current problem.  If it works let me know so I can make the change in the framework.

if(URL){

  //they may have placed the parameter sequence in the URL so make any required changes

  URL = replaceAll(URL, "=","%3D");

  URL = URL.replace("?", "%3F");

   URL = URL.replace("%", "%25");

Lee

On Fri, Nov 27, 2009 at 11:54 PM, andrew.sky <andre...@gmail.com> wrote:

[andrew.sky]

Hi Lee,
I will be on oversea trip till 4 December. Will give it a try on 5 Dec
if you can wait till that time :) Before i receive this suggestion, I
use a a quick and dirty way out as follows:

var sql = "Select * from sampleTable where description like '"%25+term
+"%'";

and suprisingly, it works.

regards
Andrew

On Nov 29, 1:54 am, Lee Barney <barney....@gmail.com> wrote:
> Andrew,
>
> Try this.  Change the lines starting at line 350 of the com.js file.  I
> haven't tried this myself but it may solve your current problem.  If it
> works let me know so I can make the change in the framework.
>
> if(URL){
>
>    //they may have placed the parameter sequence in the URL so make any
> required changes
>
>    URL = replaceAll(URL, "=","%3D");
>
>    URL = URL.replace("?", "%3F");
>    URL = URL.replace("%", "%25");
>
> Lee
>

> > quickconnectiPh...@googlegroups.com<quickconnectiPhone%2Bunsu...@googlegroups.com>
> > .

[Lee Barney]

Very strange.  The version you are using relies on WebKit to do the modification of %.  They must have missed something.

Lee

To unsubscribe from this group, send email to quickconnectiPh...@googlegroups.com.

[Lee Barney]

Andrew,

Here is an idea.  Is the term numeric?  If it is then WebKit could be interpreting the % as having already been converted.

Lee

On Mon, Nov 30, 2009 at 12:05 AM, andrew.sky <andre...@gmail.com> wrote:

To unsubscribe from this group, send email to quickconnectiPh...@googlegroups.com.

[andrew.sky]

Hi Lee,

I am unable to find line 350 in com.js on the following:

if(URL)....

I am using ver 1.5 of the framework. I tried searching the keyword
"URL" in com.js in ver 1.6 beta 4 and it is not found there as well.

regards
Andrew

On Dec 1, 1:53 am, Lee Barney <barney....@gmail.com> wrote:
> Andrew,
>

> Here is an idea.  Is the term numeric?  If it is then WebKit could be
> interpreting the % as having already been converted.
>
> Lee
>

> > <quickconnectiPhone%2Bunsu...@googlegroups.com<quickconnectiPhone%252Buns...@googlegroups.com>

[Lee Barney]

Andrew,

I gave you a bad line number.

Look for the following code.

function setDeviceData(dbName, SQL, preparedStatementParameters, callBackParams){

if(dbName && SQL){

        SQL = replaceAll(SQL, "=","%3D");

        SQL = replaceAll(SQL, "?","%3F");

and then add the line in the other email.

Lee

To unsubscribe from this group, send email to quickconnectiPh...@googlegroups.com.