Android Question JdbcResultSet MovePrevious, recordcount, and other methos.

netsistemas

Active Member
Licensed User
Longtime User
how can move to previous JdbcResultSet.
Now, for count (recordcount), i count one by one the records:

B4X:
    Do While Cursor.NextRow
        Cuenta = Cuenta+ 1
        loop

but, then i need show the first record to user, and next, and next, an previous ( Move into recordset).

how?

If this is not posible, Can i cast the JdbcResultSet to ResultSet o similar.?
 

netsistemas

Active Member
Licensed User
Longtime User
I don't understand the purpose of the second line of code .

But, the firs line is ok in debug mode (Debug line by linie F8), but in debug mode (NO STOP), i view the error:
... cannot be cast to android.database.cursor... (logic).

i discart this thecnic.
I go to try to CLONE the recordset between jdbc and native cursor.
how do this?

The last target is not read the database for move into records: Copy data to some array o simil
Count record is not problem (select count(*)... previous to get all data ).
 
Upvote 0

netsistemas

Active Member
Licensed User
Longtime User
This demo connect to a SQL SERVER in my house (Windows xp, open firewall, and redirect access in router).

You can view the problem in button event.

'java.lang.ClassCastException: net.sourceforget.jtds.jdbc.JtdsResultSet cannot be cast to android.database.Cursor'

I reference the SQL (1.5) and JDBCSQL (1.5) librarys .

B4X:
Sub Button1_Click
    Dim Rt As ResultSet
    Dim Cursor As JdbcResultSet
    Try
       
   
        Cursor = modSQLServer.SQLGetRecordSet("Select matricula from vehiculos")
    'no exist this methor:
    '    Msgbox (Cursor.RowCount,"")
    Catch
        Log(LastException)
    End Try


Try
           

        Rt =  modSQLServer.SQLGetRecordSet2_deprecated("Select matricula from vehiculos")
   
        Msgbox (Rt.RowCount,"")
    Catch
        Log(LastException)
    End Try

   
End Sub


'PART OF code in module modSQLServer:
Sub  SQLGetRecordSet2_deprecated(SQL As String) As ResultSet
    Dim Rs As ResultSet
    
    Try
    


        SQLOpenConection

        Rs = SqlServer.ExecQuery(SQL  )
        
        Return Rs
    Catch
        
        Log(LastException)
        Msgbox (LastException.Message,"Error SQLGetRecordset")
        Return Null
    End Try
 
End Sub

Sub SQLOpenConection()
    
    SqlServer.InitializeAsync("SqlServer", "net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver:" & V_SqlData.ConnStr & ";databaseName=" & V_SqlData.DBName & ";user=" & V_SqlData.UserName & ";password=" & V_SqlData.PasswordTb & ";appname=FricaltecAPP;wsid=TEST;loginTimeout=10;socketTimeout=10", "", "")  'sql 2012
End Sub
 

Attachments

  • SqlServerDemo.zip
    10.7 KB · Views: 200
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Dim rs As ResultSet = JdbcSQL.ExecQuery2(...)
This is just a typo, should be
B4X:
Dim rs As JdbcResultSet = JdbcSQL.ExecQuery2(...)
 
Upvote 0

netsistemas

Active Member
Licensed User
Longtime User
The final objective is the storage data from sqlserver into some type of data in memory, which can advance, backward, count the number of records, but above all, be able to position myself in one.
This (position in x position) could be solved by positioning in the first, and then move as many times as necessary.
The

JdbcResultSet

object doesn't have these methods, and this is where my doubt begins.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
JdbcResultSet is a Wrapper. Try using "getObject" to get the actual underlying Java ResultSet object. You could then try to run the setFetchDirection method against it.

B4X:
Dim rs As JdbcResultSet = JdbcSQL.ExecQuery2(...)
Dim b4xResultSet As JavaObject = rs
Dim jResultSet As JavaObject = rs.RunMethod("getObject", Null)
'Set reverse
Dim fetchValues As JavaObject
fetchValues.InitializeStatic("java.sql.ResultSet")
jResultSet.RunMethod("setFetchDirection", fetchValues.getField("FETCH_REVERSE"))
Note: I would not consider this as running code (I did not run it). Just a reference/hint on how to proceed further
Note2: Values for setFetchDirection found here: https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#FETCH_REVERSE
 
Upvote 0

netsistemas

Active Member
Licensed User
Longtime User
I try it this code (some litle change): rs.runmethto -> Error in parse code -> rs.RunMetrogh is not a method correct.

This is my BUD CODE. I cant find the solution.
B4X:
Sub Button1_Click
    Dim Rs As JdbcResultSet
    Rs = modSQLServer.SQLGetRecordSet("Select matricula from vehiculos")
    '    Dim rs As JdbcResultSet = JdbcSQL.ExecQuery2(...)
        Dim b4xResultSet As JavaObject
    b4xResultSet = Rs
    Dim jResultSet As JavaObject
    Try
        'error next line:  (Exception) java.lang.Exception:  java.lang.RuntimeException: Method: getObject not matched.
        jResultSet = b4xResultSet.RunMethod("first", Null)
    Catch
        Log(LastException)
    End Try




        'Set reverse
    Dim fetchValues As JavaObject
    Try
        fetchValues.InitializeStatic("java.sql.ResultSet")
        'error in next line: (Exception) java.lang.Exception:  java.lang.IllegalArgumentException: method anywheresoftware.b4j.object.JavaObject.RunMethod argument 2 has type java.lang.Object[], got java.lang.Integer
        b4xResultSet.RunMethod("setFetchDirection", fetchValues.getField("FETCH_REVERSE"))
        jResultSet.RunMethod("setFetchDirection", fetchValues.getField("FETCH_REVERSE"))
   
    Catch
        Log(LastException)
    End Try

End Sub

FINALLY:
i think this is a limitation of this driver:
I go to create a new thread for clone jdbcresulset to resulset (may by mi application can live with out this utility)
 
Last edited:
Upvote 0

OliverA

Expert
Licensed User
Longtime User
I try it this code (some litle change): rs.runmethto -> Error in parse code -> rs.RunMetrogh is not a method correct.

This should have been
B4X:
Dim jResultSet As JavaObject = b4xResultSet.RunMethod("getObject", Null)
But I guess that made no difference...
 
Upvote 0

netsistemas

Active Member
Licensed User
Longtime User
Solved. (easy, to much easy).


1. Create a public var (like in a process_global in activity)
B4X:
    Dim Lista As List
    Dim Registro As Long

and create a datatype:
B4X:
Type TD_SqlData(ConnStr As String, DBName As String, UserName As String, PasswordTb As String)

2º Add a function to clone a JdbcResultet to list

B4X:
private Sub CloneRsToList(Rs As JdbcResultSet)
    Lista.Initialize
    Do While Rs.NextRow
        Dim SubLista As List
        SubLista.Initialize
        For f  = 0 To Rs.ColumnCount-1
            Dim Elemento As TD_RowData
            Elemento.Name = Rs.GetColumnName(f)
            Elemento.Value = Rs.GetString2(f)
            Elemento.Type= "Unknow"
              
            SubLista.add(Elemento)
        Next
        Lista.Add(SubLista)
      
    Loop

End Sub

3º Read data from sql server only one time, and use the LIST always in code

Registro is a var to return the number of record( THIS VAR mus be defined too in
B4X:
private Sub LoadObraAconsultar(SoloCuenta As Boolean ) As In GlobalCode
    Dim Sql As String

    Try
  

    If Lista.IsInitialized = False Then
        Sql = "Select * From DetalleObra "
  
        Sql = Sql & " order by 1"
      
        Cursor = modGeneral.SQLGetRecordSet(Sql) 'cursor = SqlServer.ExecQuery(SQL  )
        CloneRsToList(Cursor)
    End If   
  
    If SoloCuenta Then
        Return Lista.Size
    Else
  
        Dim Elemento As TD_RowData
        Dim ListaCampos As List
        ListaCampos = Lista.Get(Registro-1)
        ListView1.Clear
  
      
        Dim N As Long
        For n = 0 To ListaCampos.Size-1
            Elemento = ListaCampos.Get(n)
                  
            Dim Linea1 As String
            Linea1 = Elemento.Name.ToUpperCase
          

          
            ListView1.AddTwoLines( Linea1,Elemento.Value)
        Next
        Return 0
    End If       
    Catch
        Log(LastException)
    Return 0
    End Try
  
      
  
End Sub


may be, this code ARE NOT CORRECT 100% (copy paste), but the idea is easy.

The field TYPE are ignored from sql server, but include it in design for future...
 
Last edited:
Upvote 0
Top