B4J Question SQL ... Am I missing something ?

[quique]

Sorry for the probably naive question but, coming from .net framework, I've always used several objects / constructs to interact with the db, which I am struggling to reproduce on B4J.

I am missing mainly the DataTable object concept, which in .net allows me to get a "table" subset, matching an sql query:

    Public Shared Function getdatatable(ByVal query As String) As DataTable
        Dim adapter As mysqlDataAdapter = db.adaptador(query)
        Dim tabla As New DataTable
        adapter.Fill(tabla)
        getdatatable = tabla
        adapter.Dispose()
    End Function

As far as I can see, on B4J I can only get a reader cursor as a resultset as the only way to interact with data. Is this correct ?

Also, if I create a sub which returns a ResultSet, the ResultSet is held on memory ? (Is it ok to close the sql connection I fetched earlier from the pool, in order to grab the ResultSet ?

I.E. is this correct ?

Sub Class_Globals
    Public pool As ConnectionPool, mysql As SQL
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    Try
        pool.Initialize("com.mysql.cj.jdbc.Driver", "jdbc:mysql://localhost/test?characterEncoding=utf8", _
        "xxxx", "yyyy")
        Log("MySQL pool started")
        pool.GetConnection.Close
    Catch
        Log("MySQL pool error")
        ExitApplication
    End Try
End Sub

Sub read(query As String) As ResultSet
    mysql = pool.GetConnection
    Dim RS As ResultSet
    Try
        RS = mysql.ExecQuery(query)
    Catch
        Log(LastException)
    End Try
    mysql.close
    Return RS
End Sub

 

[EnriqueGonzalez]

hi tocallo!

i use vb net a lot and one of the things i miss the most is datatable.

you will not find that here, the code you use is okey but let me share with you the following class: DataTable, it is just a list with a map.

Use it like this:

    Private cmbAsignado As ComboBox
    Dim listOfEmpleados As dataTable
    listOfEmpleados.Initialize(sql,queryEmpleados,Null)

    For i = 0 To listOfEmpleados.RowsCount -1
        cmbAsignado.Items.Add(listOfEmpleados.getCellValue(i,"Nombre"))
    Next
    cmbAsignado.Tag = listOfEmpleados

 

[keirS]

As far as I can see, on B4J I can only get a reader cursor as a resultset as the only way to interact with data. Is this correct ?

Also, if I create a sub which returns a ResultSet, the ResultSet is held on memory ? (Is it ok to close the sql connection I fetched earlier from the pool, in order to grab the ResultSet ?

I.E. is this correct ?

The standard B4J SQL library only supports a read forward resultset. JDBC drivers may support scrollable resultsets and updatable result sets (most of the common RDBMS drivers do)

It is not OK to close the connection whilst you are using a resultset. A read forward only resulset may or may not be held in memory. This is dependent on:

1. The JDBC driver being used.
2. The nature of the query.

This a very complex area. For example the MySQL driver by default fetches all rows from the server (potentially uses lots of memory). The Oracle JDBC driver by default caches the results on the DB server and fetches them in chunks as the Resultset.Next method is called and does not keep them all in memory.

 

[Harris]

For example the MySQL driver by default fetches all rows from the server (potentially uses lots of memory).

Is this true even when LIMIT is used?

ie:

res = SQL.ExecQuery("Select * FROM shiftmast WHERE printed = 0 AND edate > 0 LIMIT 1")


Thanks

 

[EnriqueGonzalez]

Is this true even when LIMIT is used?

ie:

res = SQL.ExecQuery("Select * FROM shiftmast WHERE printed = 0 AND edate > 0 LIMIT 1")


Thanks

he is refering to all the rows returned by the query, not all the table.

 

[Harris]

he is refering to all the rows returned by the query, not all the table.

Ok,
I will back out here since I don't know where you are coming from and my experience...
I thought the above query would return 1 row - not the entire table... RowCount - in this case- states 1 record - since LIMIT was to return FIRST record found.

Thanks

 

[EnriqueGonzalez]

I thought the above query would return 1 row

he is refering to all the rows returned by the query,

Both statements are the same

 

[keirS]

Is this true even when LIMIT is used?

ie:

res = SQL.ExecQuery("Select * FROM shiftmast WHERE printed = 0 AND edate > 0 LIMIT 1")


Thanks

Nothing to do with LIMIT. Query results are cached on the server for a lot of DB's. JDBC has a FetchSize property that tells the driver how many results to download locally in a round trip to the server. B4J SQL uses a read forward cursor so in theory the JDBC driver can discard rows already read and fetch more cached rows from the server each time ResultSet.next is called. This allows for the processing of massive result sets without running out of memory.