Android Question JdbcResultSet MovePrevious, recordcount, and other methos.

[netsistemas]

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

    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.?

 

[Erel]

Dim rs As ResultSet = JdbcSQL.ExecQuery2(...)
Dim jResultSet As JavaObject = rs

Whether these methods will work or not depend on the type of result set returned by the jdbc driver. It will be safer to add count(*) to the query itself.

 

[netsistemas]

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 ).

 

[Erel]

Post the relevant code and the full error message.
I assume that you are using JdbcSQL library, correct?

 

[netsistemas]

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 .

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

 

[OliverA]

Dim rs As ResultSet = JdbcSQL.ExecQuery2(...)

This is just a typo, should be

Dim rs As JdbcResultSet = JdbcSQL.ExecQuery2(...)

 

[netsistemas]

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.

 

[OliverA]

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.

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

 

[netsistemas]

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.

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:

jTDS Support for JDBC result sets

I go to create a new thread for clone jdbcresulset to resulset (may by mi application can live with out this utility)

 

[OliverA]

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

Dim jResultSet As JavaObject = b4xResultSet.RunMethod("getObject", Null)

But I guess that made no difference...

 

[Erel]

No need to get getObject. The compiler does it for you as it is a wrapper.

However all this discussion is irrelevant. Unless you are reading millions of rows just add all the data to a List and do whatever you like with it.

 

[netsistemas]

Solved. (easy, to much easy).


1. Create a public var (like in a process_global in activity)

    Dim Lista As List
    Dim Registro As Long

and create a datatype:

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

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

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...