Questions about ExecQuery2

parijs · Jan 15, 2012

I have:

B4X:

Dim jop As String
Dim jop1 As String
.
.
jop= "1"
jop1="2"

oRst = SQL.ExecQuery2("SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE arronnr = ?", Array As String (jop OR jop1))

And the error is Cannot parse: 1 as boolean
The type of arronnr = TEXT in the db

And how many OR can I use

Last question
Is it be possible to use for ("SELECT MapNaam Mapnaam as a Variable
and
Is it be possible to use for WHERE arronnr = arronnr as a Variable

:sign0013:

Erel · Jan 15, 2012

"Array As String" is a general command to create arrays.
You should instead use:

B4X:

oRst = SQL.ExecQuery2("SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE arronnr = ? OR ?", Array As String (jop, jop1))

You can use as many ORs as you need.

Not sure that I understand you last two questions.

parijs · Jan 15, 2012

Hi Erel,

I use your code and there is a view but everything is in it not only the 1 and 2 but also the 5, 6, 7, 8 .....

The other questions:

Is this possible?

B4X:

Dim B As String
B = "arronnr"

oRst = SQL.ExecQuery2("SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE B = ? OR ?", Array As String (jop, jop1))

or this

B4X:

Dim B As String
B = "MapNaam"

oRst = SQL.ExecQuery2("SELECT B, arronnr, wijk, keuze FROM visit WHERE arronnr = ? OR ?", Array As String (jop, jop1))

Thanks for your reply

Erel · Jan 15, 2012

SQL.ExecQuery2("SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE B = ? OR ?", Array As String (jop, jop1))

This will not work as the B inside the string is not related in any way to the variable named B.

This code will work:

B4X:

oRst = SQL.ExecQuery2("SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE " & B &"  = ? OR ?", Array As String (jop, jop1))

oRst = SQL.ExecQuery2("SELECT B, arronnr, wijk, keuze FROM visit WHERE arronnr = ? OR ?", Array As String (jop, jop1))

This code should work.

parijs · Jan 15, 2012

Thanks Erel,

Have you any idea why your code

B4X:

oRst = SQL.ExecQuery2("SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE arronnr = ? OR ?", Array As String (jop, jop1))

Gif me all of the db

Erel · Jan 15, 2012

Yes. It should be:

B4X:

"SELECT MapNaam, arronnr, wijk, keuze FROM visit WHERE arronnr = ? OR arronnr = ?", Array As String (jop, jop1)

BTW, it is recommended to use [ code ] tags when posting code. It makes it easier to read the code.

parijs · Jan 15, 2012

My excuse I did not know where I could find that.
But now I found the [ code ]

Tanks for all

parijs · Jan 16, 2012

Back again :sign0013:

I have tried your code

B4X:

myvar="keuze"
cur = SQL.ExecQuery("SELECT myvar FROM maandag")

But it give me "no such column"

Also I have a problem with

B4X:

Main.dagkeuze ="maandag"

cur = SQL.ExecQuery("SELECT keuze FROM Main.dagkeuze")      
intCount = SQL.ExecQuerySingleResult("SELECT count(*) FROM  Main.dagkeuze")

looked everywhere but can not find meaningful solutions.
So my hope lies with the Master.

Erel · Jan 16, 2012

I have tried your code

I'm afraid that this is not my code.

As I tried to explain before you cannot just put a variable name in a string.
You should write:

B4X:

cur = SQL.ExecQuery("SELECT " & myvar & " FROM maandag")

parijs · Jan 16, 2012

You are fantastico :sign0148:

jcmartini · Apr 9, 2013

ExecQuery2 escaping characters

I tried to use ExecQuery2 with a query using a virtual SQLite table. It seems to me that asterisk raises an error:

SQL1.ExecQuery2("SELECT fielda, fieldb FROM table WHERE fieldb MATCH '?'", Array As String(string & "*")

I didn't find any definitions for character escaping, how it works and for which character.

Erel · Apr 10, 2013

Which error do you get?

The nice thing about parameterized queries is that you do not need to escape anything:

B4X:

SQL1.ExecQuery2("SELECT fielda, fieldb FROM table WHERE fieldb LIKE ?", Array As String(string & "%")

jcmartini · Apr 16, 2013

I got:

Parsing code. Error
Error parsing program.
Error description: Syntax error.

NB: it's not the same SQL you show.

Erel · Apr 16, 2013

Can you post the relevant code?

arjian · Jan 27, 2014

HI
i got error in code bellow,help me to know why

B4X:

QueryNearPost="SELECT PostID,LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID = ?"
 
    Dim PostIDArray(NearPostsIDList.Size) As String
    For i=0 To NearPostsIDList.Size-1
        PostIDArray(i)=    NearPostsIDList.Get(i)
    Next
    CurNearPost=Main.SQL1.ExecQuery2(QueryNearPost,PostIDArray)

Mahares · Jan 27, 2014

Your array has 3 elements. You need 3 arguments. See code below:

B4X:

QueryNearPost="SELECT PostID,LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID = ? OR ? OR ?"

jsanchezc · Jan 28, 2014

Mahares said:
Your array has 3 elements. You need 3 arguments. See code below:

B4X:

QueryNearPost="SELECT PostID,LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID = ? OR ? OR ?"

You can also use IN instear contatenate several OR:
QueryNearPost="SELECT PostID,LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID IN (?,?,?)"
IN allow you to compare field value with a list of values (one or more values).

SELECT * FROM COUNTRIES WHERE NAME IN('FRANCE','BELGIUM','ITALY')
SELECT * FROM COUNTRIES WHERE ID IN (1,4,5,8,9,10)

If you have a variable number of arguments you can use ExecQuery and
create the sql string dinamicaly:

SQLSTR="SELECT * FROM COUNTRIES WHERE ID IN("
FOR I=1 TO 10
SQLSTR=SQLSTR & I
IF I < 10 THEN
SQLSTR=SQLSTR & ","
END IF
NEXT I
SQLSTR=SQLSTR & ") "
then call QueryExec

arjian · Jan 29, 2014

jsanchezc said:
You can also use IN instear contatenate several OR:
QueryNearPost="SELECT PostID,LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID IN (?,?,?)"
IN allow you to compare field value with a list of values (one or more values).

SELECT * FROM COUNTRIES WHERE NAME IN('FRANCE','BELGIUM','ITALY')
SELECT * FROM COUNTRIES WHERE ID IN (1,4,5,8,9,10)

If you have a variable number of arguments you can use ExecQuery and
create the sql string dinamicaly:

SQLSTR="SELECT * FROM COUNTRIES WHERE ID IN("
FOR I=1 TO 10
SQLSTR=SQLSTR & I
IF I < 10 THEN
SQLSTR=SQLSTR & ","
END IF
NEXT I
SQLSTR=SQLSTR & ") "
then call QueryExec

in fact number of arguments is variable so i used this code

B4X:

For i=0 To NearPostsIDList.Size-1
        Dim    CurNearPost As Cursor
        QueryNearPost="SELECT LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID = '" & NearPostsIDList.Get(i) & "'"
        CurNearPost=Main.SQL1.ExecQuery(QueryNearPost)
.
.

now the question is which code is better?,i think your code will be executed in shorter time (PostID has variable arguments between 0 to 4000)

jsanchezc · Feb 3, 2014

arjian said:
in fact number of arguments is variable so i used this code

B4X:

For i=0 To NearPostsIDList.Size-1 Dim CurNearPost As Cursor QueryNearPost="SELECT LocalName,LocalAddress,OwnerType,Capacity,Latitude,Longitude FROM " & PostTableName & " WHERE PostID = '" & NearPostsIDList.Get(i) & "'" CurNearPost=Main.SQL1.ExecQuery(QueryNearPost) . .

now the question is which code is better?,i think your code will be executed in shorter time (PostID has variable arguments between 0 to 4000)

B4X:

Dim QueryNearPost as String=""
Dim    CurNearPost As Cursor
Dim NumberRows as int=0
'---------------------------------------------------------------------------
'If you avoid long code lines you'll see all code, all fields.
'If put comma field separator before each field (instad at the end of each field)
' you can add fields or rem a line without worry about comma
'----------------------------------------------------------------------------
QueryNearPost="SELECT            LocalName"
QueryNearPost= QueryNearPost & " ,LocalAddress"
QueryNearPost= QueryNearPost & " ,OwnerType "
QueryNearPost= QueryNearPost & " ,Capacity "
QueryNearPost= QueryNearPost & " ,Latitude "
QueryNearPost= QueryNearPost & " ,Longitude "
QueryNearPost= QueryNearPost & "  FROM " & PostTableName
QueryNearPost= QueryNearPost & " WHERE PostID IN ("
'--------------------------------------------------------------
NumberRows=NearPostsIDList.Size-1
For i=0 To NumerRows
       QueryNearPost= QueryNearPost & "''" & NearPostsIDList.Get(i) & "'"
       if i < NumberRows then
         'add , separator between IN list, except last item
         QueryNearPost= QueryNearPost & ","
      end if
Next
'Add close )
QueryNearPost= QueryNearPost & ")"
'If needed add order by and so on...
'QueryNearPost= QueryNearPost & " Order by LocalName"
Log(QueryNearPost)  'You can copy/paste from log list to external ExecQuery Tool to test
'Execute Query
CurNearPost=Main.SQL1.ExecQuery(QueryNearPost)

To improve speed, PostTableName should have index: PostId

Questions about ExecQuery2

parijs

Active Member

Erel

B4X founder

parijs

Active Member

Erel

B4X founder

parijs

Active Member

Erel

B4X founder

parijs

Active Member

parijs

Active Member

Erel

B4X founder

parijs

Active Member

jcmartini

Member

Erel

B4X founder

jcmartini

Member

Erel

B4X founder

arjian

Member

Attachments

Mahares

Expert

jsanchezc

Member

arjian

Member

jsanchezc

Member