Android Question [Solved] How to use MySQL LIMIT clause in jRDC2?

Sergio Castellari

Sergio Castellari

Active Member
Licensed User
Situation:
a) I need to make the following query:

B4X: 
sql.select_articulos_precios=Select a.CODIGO, a.CODIFICADO, a.NOMBRE, a.PVENTA, a.PVENTA_2, a.PVENTA_3, a.PVENTA_4, a.PVENTA_5, a.DOLARIZADO From ARTICULO a \
                              Donde a.FECHAEGRE = "0000-00-00" y Substr (a.NOMBRE, 1,?) =? ordenar por un.NOMBRE Limite 0,?

But when using it, it throws the following error:
<pre> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &apos;&apos;300&apos;&apos; at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>

I use 300 as a limit.
If I do the same query from MySQL Query Browse, it works fine.

What can I be doing wrong in the query inside "config.properties"
Greetings[/CODE]
 
Last edited by a moderator:
Sort by date Sort by votes
TILogistic

TILogistic

Expert
Licensed User
Longtime User
Sergio Castellari said:
Situation:
a) I need to make the following query:

B4X: 
sql.select_articulos_precios=Select a.CODIGO, a.CODIFICADO, a.NOMBRE, a.PVENTA, a.PVENTA_2, a.PVENTA_3, a.PVENTA_4, a.PVENTA_5, a.DOLARIZADO From ARTICULO a \
                              Donde a.FECHAEGRE = "0000-00-00" y Substr (a.NOMBRE, 1,?) =? ordenar por un.NOMBRE Limite 0,?

But when using it, it throws the following error:
<pre> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &apos;&apos;300&apos;&apos; at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>

I use 300 as a limit.
If I do the same query from MySQL Query Browse, it works fine.

What can I be doing wrong in the query inside "config.properties"
Greetings[/CODE]
Click to expand...
??

Donde a.FECHAEGRE = "0000-00-00" y Substr (a.NOMBRE, 1,?) =? ordenar por un.NOMBRE Limite 0,?
Click to expand...

Select instruction is in Spanish.

???
 
Upvote 0
Sergio Castellari

Sergio Castellari

Active Member
Licensed User
Erel said:
It should be LIMIT, not limite.
Click to expand...
Chrome translated it automatically. But it really is "LIMIT" as I intended to use it.
Anyway:
a) LIMIT ?
b) LIMIT 0,?
c) LIMIT = ?

None of them work.
It only works if you put a FIXED value, for example:
LIMIT 250 o LIMIT 0,250
 
Upvote 0
Sergio Castellari

Sergio Castellari

Active Member
Licensed User
Hi @OliverA,
Thank you for your concern.
In the file "config.Properties" I have the following SQL statement:

B4X: 
#####################
# ARTICULOS y STOCK #
#####################
#Consulta ARTICULOS (lista de Precios)
sql.select_articulos_precios_asterix=Select a.CODIGO, a.CODIFICADO, a.NOMBRE, a.PVENTA, a.PVENTA_2, a.PVENTA_3, a.PVENTA_4, a.PVENTA_5, a.DOLARIZADO From ARTICULO a \
                              Where a.FECHAEGRE="0000-00-00" and (a.CODPROV>=? and a.CODPROV<=?) and (a.LINEA>=? and a.LINEA<=?) and \
                              (a.RUBRO>=? and a.RUBRO<=?) order by a.NOMBRE Limit 5000
sql.select_articulos_precios_filtrada=Select a.CODIGO, a.CODIFICADO, a.NOMBRE, a.PVENTA, a.PVENTA_2, a.PVENTA_3, a.PVENTA_4, a.PVENTA_5, a.DOLARIZADO From ARTICULO a \
                              Where a.FECHAEGRE="0000-00-00" and Substr(a.NOMBRE,1,?)=? and (a.CODPROV>=? and a.CODPROV<=?) and (a.LINEA>=? and a.LINEA<=?) and \
                              (a.RUBRO>=? and a.RUBRO<=?) order by a.NOMBRE Limit 250
sql.select_articulos_precios_vacia=Select a.CODIGO, a.CODIFICADO, a.NOMBRE, a.PVENTA, a.PVENTA_2, a.PVENTA_3, a.PVENTA_4, a.PVENTA_5, a.DOLARIZADO From ARTICULO a \
                              Where a.FECHAEGRE="0000-00-00" and (a.CODPROV>=? and a.CODPROV<=?) and (a.LINEA>=? and a.LINEA<=?) and \
                              (a.RUBRO>=? and a.RUBRO<=?) order by a.NOMBRE Limit 250

From the APP B4A, I call it as follows:

B4X: 
'+++ BUSCAR Articulos ++++
Private Sub btnArticulo_Click
    IME1.HideKeyboard
    Dim aRegistros, aCampos                                        As List
    Dim aCom()                                                                As String
    Dim MinPro = "00000", MaxPro = "99999"        As String
    Dim MinLin = "000", MaxLin = "999"                As String
    Dim MinRub = "000", MaxRub = "999"                As String
    Dim prove = lblProv.Text.SubString2(0,5)     As String
    Dim linea = lblLinea.Text.SubString2(0,3) As String
    Dim rubro = lblRubro.Text.SubString2(0,3) As String
    Dim nCar = 0                                                             As Int
    Dim cFiltro = edtArticulo.Text                         As String
    nCar = cFiltro.Length
'    If nCar = 0 Then
'        Main.beepError.Beep : edtArticulo.RequestFocus : Return
'    End If
    If prove <> "00000" Then                    'Verifico si filtro PROVEDOR
        MinPro=prove : MaxPro=prove
    End If
    If linea <> "000" Then                        'Verifico si filtro LINEA
        MinLin=linea : MaxLin=linea
    End If
    If rubro <> "000" Then                        'Verifico si filtro RUBRO
        MinRub=rubro : MaxRub=rubro
    End If
    ProgressDialogShow("Buscando Articulos...ESPERE...")
    aRegistros.Initialize
    aCampos.Initialize
    If cFiltro <> "*" And cFiltro <> "" Then
        'Trae los primeros 250 registros o menos (segun filtro elegido)
        aCom = Array As String(nCar,cFiltro,MinPro,MaxPro,MinLin,MaxLin,MinRub,MaxRub)
        Wait For (Main.jConex.GetRecord("select_articulos_precios_filtrada", aCom)) Complete (Respuesta As Map)
    Else If cFiltro = "*" Then
        'Trae los primeros 5000 registros o menos (segun filtro elegido)
        aCom = Array As String(MinPro,MaxPro,MinLin,MaxLin,MinRub,MaxRub)
        Wait For (Main.jConex.GetRecord("select_articulos_precios_asterix", aCom)) Complete (Respuesta As Map)
    Else
        'Trae los primeros 250 registros o menos (segun filtro elegido)  Boton Re-Inicio
        aCom = Array As String(MinPro,MaxPro,MinLin,MaxLin,MinRub,MaxRub)
        Wait For (Main.jConex.GetRecord("select_articulos_precios_vacia", aCom)) Complete (Respuesta As Map)
    End If
    If Respuesta.Get("Correcto") Then
        Dim rs     As DBResult
        Dim cCod,cDes,cPre1,cPre2,cPre3,cPre4,cPre5,cDol As String
        rs = Respuesta.Get("Datos")
        Dim Lista As List = rs.Rows
        Dim nLineas = Lista.Size, nVez=0 As Int
        Log("Registros totales Articulos: " & nLineas)
    For Each row() As Object In rs.Rows
            cCod = row(0) & Chr(13) & row(1)
            cDes = row(2)
            cPre1 = row(3)
            cPre2 = row(4)
            cPre3 = row(5)
            cPre4 = row(6)
            cPre5 = row(7)
            cDol = row(8)
            If cDol = "T" Then cDes = "[u$s] " & cDes
            aCampos.Initialize
            aCampos.Add(cCod)
            aCampos.Add(cDes)
            aCampos.Add(cPre1)
            aCampos.Add(cPre2)
            aCampos.Add(cPre3)
            aCampos.Add(cPre4)
            aCampos.Add(cPre5)
            aRegistros.Add(aCampos)
        Next
        B4XTable1.SetData(aRegistros)                                     
        SeteoColumna("Cod", "CENTER", "LEFT",0,-1,-1)
        SeteoColumna("Descripción", "CENTER", "LEFT",0,-1,-1)
        SeteoColumna("Vta1", "CENTER", "RIGHT",2,2,-1)
        SeteoColumna("Vta2", "CENTER", "RIGHT",2,2,-1)
        SeteoColumna("Vta3", "CENTER", "RIGHT",2,2,-1)
        SeteoColumna("Vta4", "CENTER", "RIGHT",2,2,-1)
        SeteoColumna("Vta5", "CENTER", "RIGHT",2,2,-1)
        B4XTable1.Refresh
        ProgressDialogHide
    Else
        ProgressDialogHide
         MsgboxAsync(Respuesta.Get("Mensaje"),"")
        Return
    End If
End Sub

When I use the "LIMIT" clause with a FIXED value, IT WORKS. But if I try to use it with values sent from the APP, it gives ERROR.
I have tried the three ways described above, but none of them work.
Maybe there is a different syntax to do it from jRDC2.
From the windows application "MySQL Query Brows" it works correctly.

However, I decided to use FIXED values (as shown in the code) and that way it works CORRECTLY.

A big virtual hug!

NOTE: By the way, every day I am more in love with B4X. As I learn more, I am impressed by the power, flexibility and speed of operation.
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
You're still not showing all the code on how you call the jRDC2 statement with the limit as one of your properties that you sent to the server. I need to see how you use the LIMIT statement with the ? in the statement. How you call it (the statement with LIMIT ? or LIMIT ?, ?) from your program.
 
Upvote 0
Sergio Castellari

Sergio Castellari

Active Member
Licensed User
OliverA said:
You're still not showing all the code on how you call the jRDC2 statement with the limit as one of your properties that you sent to the server. I need to see how you use the LIMIT statement with the ? in the statement. How you call it (the statement with LIMIT ? or LIMIT ?, ?) from your program.
Click to expand...
Try these three ways:
a) LIMIT ?
b) LIMIT 0,?
c) LIMIT = ?

None worked. They give the error of the initial post.
That is why I show in the previous post, the call and the SQL statements, with "LIMIT" and a fixed value.
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Sergio Castellari said:
None worked. They give the error of the initial post.
Click to expand...
How do you pass the parameters? String array? Object Array? Is the Limit you sent a String? A Int? I need to see how you call the statement from your client program. You have not shown me that yet.
 
Upvote 0
Sergio Castellari

Sergio Castellari

Active Member
Licensed User
I was sending it as "INT"

B4X: 
    Dim aRegistros, aCampos    As List
    Dim aCom() As String
    Dim nLimit = 250 As Int
    Dim MinPro = "00000", MaxPro = "99999" As String
    Dim MinLin = "000", MaxLin = "999" As String
    Dim MinRub = "000", MaxRub = "999" As String
    Dim prove = lblProv.Text.SubString2(0,5) As String
    Dim linea = lblLinea.Text.SubString2(0,3) As String
    Dim rubro = lblRubro.Text.SubString2(0,3) As String
    Dim nCar = 0 As Int
    Dim cFiltro = edtArticulo.Text As String
    nCar = cFiltro.Length
    If prove <> "00000" Then                    'Verifico si filtro PROVEDOR
        MinPro=prove : MaxPro=prove
    End If
    If linea <> "000" Then                        'Verifico si filtro LINEA
        MinLin=linea : MaxLin=linea
    End If
    If rubro <> "000" Then                        'Verifico si filtro RUBRO
        MinRub=rubro : MaxRub=rubro
    End If
    ProgressDialogShow("Buscando Articulos...ESPERE...")
    aRegistros.Initialize
    aCampos.Initialize
    If cFiltro <> "*" And cFiltro <> "" Then
        'Trae los primeros 250 registros o menos (segun filtro elegido)
        aCom = Array As String(nCar,cFiltro,MinPro,MaxPro,MinLin,MaxLin,MinRub,MaxRub, nLimit)
        Wait For (Main.jConex.GetRecord("select_articulos_precios_filtrada", aCom)) Complete (Respuesta As Map)
        ...
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
No, you are not. Since you are creating an Array As String, your nLimit is converted to a string. Try
B4X: 
...
Dim aCom() As Object
...
aCom = Array (nCar,cFiltro,MinPro,MaxPro,MinLin,MaxLin,MinRub,MaxRub, nLimit)
...
Note: You need to change your GetRecord to accept an object array instead of a string array
 
Upvote 0
Sergio Castellari

Sergio Castellari

Active Member
Licensed User
And at this point, wouldn't it be easier to pass "nLimit" to a string?
NOTE: I have no idea how to take a numeric variable to a string variable ...
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
Sergio Castellari said:
And at this point, wouldn't it be easier to pass "nLimit" to a string?
Click to expand...
No. The whole issue is that nLimit is sent to jRDC2 as a string. Internally (in the Java code), the parameterized query code in B4J uses getObject to determine what a parameter is. If it is a string, it keeps it as a string. But Limit does not need a string, it needs a number. If you look at the sample code provided in the jRDC2 post (https://www.b4x.com/android/forum/t...-rdc-remote-database-connector.61801/#content), parameters are passed as an object array, not a string array.
Sergio Castellari said:
NOTE: I have no idea how to take a numeric variable to a string variable ...
Click to expand...
The whole goal is to not convert the numeric variable to a string. By making it part of a String Array (in your code aCom is a string array), the B4X language converts your numeric value to a String.
 
Upvote 0
Sergio Castellari

Sergio Castellari

Active Member
Licensed User
OliverA said:
No. The whole issue is that nLimit is sent to jRDC2 as a string. Internally (in the Java code), the parameterized query code in B4J uses getObject to determine what a parameter is. If it is a string, it keeps it as a string. But Limit does not need a string, it needs a number. If you look at the sample code provided in the jRDC2 post (https://www.b4x.com/android/forum/t...-rdc-remote-database-connector.61801/#content), parameters are passed as an object array, not a string array.
Click to expand...
AHA. Now I am getting it.
But the thing is, I have a jRDC2 Multi (created by Erel)
https://www.b4x.com/android/forum/threads/mysql-connector-for-b4i.116617/#post-729095
which is edited for multiple databases:
APP side:

B4X: 
Sub CreateRequest As DBRequestManager
    'Por cada solicitud, se crea un nuevo DBRequest...
  Dim Req As DBRequestManager
  Req.Initialize(Me, rdcLink & "?DBName=" & Main.pDBName )   'Aqui va la DBName a utilizar!!!
  Return Req
End Sub

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
   Dim cmd As DBCommand
   cmd.Initialize
   cmd.Name = Name
   If Parameters <> Null Then cmd.Parameters = Parameters
   Return cmd
End Sub

Public Sub GetRecord (Command As String, parameters() As String) As ResumableSub
    Dim Respuesta As Map
    Respuesta.Initialize
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand(Command, parameters)
    Log("conectando al servidor...espere...")
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    Respuesta.Put("Correcto",j.Success)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(Res As DBResult)
      Respuesta.Put("Mensaje","Datos obtenidos")
        'req.PrintTable(Res)
    Else
      Respuesta.Put("Mensaje","ERROR !!!. Servidor de datos NO disponible. Vuelva a intentarlo nuevamente...")
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
    Respuesta.Put("Datos",Res)
    Return Respuesta
End Sub

JRDC2 Server Side:

B4X: 
Sub AppStart (Args() As String)
    srvr.Initialize("")
    Connectors = srvr.CreateThreadSafeMap
    Dim dbs As List = Array("gest","pepe","guapa") 'read from a file
    For Each db As String In dbs
        Dim con As RDCConnector
        con.Initialize(db)
        Connectors.Put(db, con)
    Next
    srvr.Port = 17101
    srvr.AddHandler("/test", "TestHandler", False)
    srvr.AddHandler("/rdc", "RDCHandler", False)
    srvr.Start
    Log($"jRDC is running (version = $1.2{VERSION})"$)
    StartMessageLoop
End Sub

The problem is that I have no idea how to adapt or create a new "GetRecord" for these cases.
 
Upvote 0
TILogistic

TILogistic

Expert
Licensed User
Longtime User
GetRecord (Command As String, parameters() As String) As ResumableSub

to

GetRecord (Command As String, parameters() As Object) As ResumableSub

see:
B4X: 
Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
   Dim cmd As DBCommand
   cmd.Initialize
   cmd.Name = Name
   If Parameters <> Null Then cmd.Parameters = Parameters
   Return cmd
End Sub
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
B4X: 
Public Sub GetRecord (Command As String, parameters() As String) As ResumableSub
Change to
B4X: 
Public Sub GetRecord (Command As String, parameters() As Object) As ResumableSub
Nija’d by a few seconds
 
Upvote 0
TILogistic

TILogistic

Expert
Licensed User
Longtime User
see:
Important

LIMIT N1
or
LIMIT N1, N2

N1 Offset
N2 Row

ref:
www.mysqltutorial.org

MySQL LIMIT

In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.
www.mysqltutorial.org www.mysqltutorial.org
 
Last edited:
Upvote 0
TILogistic

TILogistic

Expert
Licensed User
Longtime User
Write the SQL statement in this way, to understand it.

SQL: 
sql.select_articulos_precios_filtrada = SELECT a.CODIGO \
                                             , a.CODIFICADO \
                                             , a.NOMBRE \
                                             , a.PVENTA \
                                             , a.PVENTA_2 \
                                             , a.PVENTA_3 \
                                             , a.PVENTA_4 \
                                             , a.PVENTA_5 \
                                             , a.DOLARIZADO  \
                                          FROM ARTICULO a \
                                         WHERE a.FECHAEGRE = "0000-00-00"  \
                                           AND SUBSTR(a.NOMBRE, 1, ?) = ? \
                                           AND (a.CODPROV >= ? AND a.CODPROV <= ?)  \
                                           AND (a.LINEA >= ? AND a.LINEA <= ?)  \
                                           AND (a.RUBRO >= ? AND a.RUBRO <= ?)  \
                                         ORDER BY a.NOMBRE \
                                         LIMIT ?
???
 
Upvote 0
You must log in or register to reply here.

Similar Threads

aeric
  • Article
B4J Code Snippet [Project Template] [B4X] jRDC2 Client (B4J, B4A, B4i)
Replies
1
Views
3K
PABLO2013
PABLO2013
josejad
  • Article
Android Example [B4XPages] jRDC2 + MySql CRUD + Login
2 3
Replies
43
Views
12K
aeric
aeric
Bladimir Silva Toro
  • Article
Android Tutorial Tutorial of connecting Android to MS SQL Server using jRDC2
2
Replies
30
Views
16K
josejad
josejad
O
  • Question
Android Question jRDC2 query problem
Replies
17
Views
5K
oscarsarrio
O
Erel
  • Locked
  • Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
138
Views
144K
Erel
Erel
Top