Android Question Question about cursors

MrKim · Jan 29, 2020

Edit: I should have been more clear. I am using SQL Server: mssql-jdbc-6.2.2.jre8.jar

I have several cursors returning small datasets with lots of fields. Since I need to store the data (because there are, evidently, only forward only cursors) I plan to put it in a map. It would be nice if I could do that with a loop using crsr.GetXXXX(Index). The problem is I also need the field name for the Key.

So my question is is there any way to get the field Names as well as the values using the index. Or, perhaps, some better, more efficient way to move the data from a cursor into a reusable format without a whole lot of assignment statement?

i.e.

Something like this:

B4X:

 Dim  X as Int
Do While Crsr.NextRow = True
    Dim Lbl As TextArea, P As B4XView, LV As ListView, MachineData As Map
    MachineData.Initialize
For X = 0 To Crsr.NumberOfFields
    MachineData.Put(Crsr.GetFieldName(X), Crsr.GetXXX(X))
Next
    .....

Instead of this:

B4X:

Do While Crsr.NextRow = True
    Dim Lbl As TextArea, P As B4XView, LV As ListView, MachineData As Map
    MachineData.Initialize
    MachineData.Put("SchM_ID", Crsr.GetInt("SchM_ID"))
    MachineData.Put("SchM_WCCode", Crsr.GetString("SchM_WCCode"))
    MachineData.Put("SchM_Machine", Crsr.GetString("SchM_Machine"))
    MachineData.Put("SchM_Notes", Crsr.GetString("SchM_Notes"))
    MachineData.Put("SchM_HrsPerWk", Crsr.GetInt("SchM_HrsPerWk"))
    MachineData.Put("SchM_Cell", Crsr.GetInt("SchM_Cell"))
    MachineData.Put("SchM_EmpAssignDayShift", Crsr.GetString("SchM_EmpAssignDayShift"))
    MachineData.Put("SchM_EmpAssignShift2", Crsr.GetString("SchM_EmpAssignShift2"))
    MachineData.Put("SchM_EmpAssignShift3", Crsr.GetString("SchM_EmpAssignShift2"))
    MachineData.Put("SchM_Group", Crsr.GetInt("SchM_Group"))
    MachineData.Put("SchM_FacilityNum", Crsr.GetInt("SchM_FacilityNum"))
    MachineData.Put("SchM_Location", Crsr.GetString("SchM_Location"))
    .....

Thanks in advance for any help, ideas.

aeric · Jan 29, 2020

I think it is not possible unless all your columns are same type.
As you need use GetString and GetInt interchangeably.

If let say you have first 20 columns are String and following 20 columns are Int then it may be possible.

aeric · Jan 29, 2020

Otherwise treat all columns value convert to String.

B4X:

    For X = 0 To Crsr.ColumnCount - 1
        MachineData.Put(Crsr.GetColumnName(X), Crsr.GetString2(X))
    Next

aeric · Jan 29, 2020

Or you can rename your fields with suffix "_int" and "_dbl"

For example:
SchM_FacilityNum_int
SchM_Amount_dbl

B4X:

    For X = 0 To Crsr.ColumnCount - 1
        If Crsr.GetColumnName(X).EndsWith("_int") Then
            MachineData.Put(Crsr.GetColumnName(X), Crsr.GetInt2(X))
        Else If Crsr.GetColumnName(X).EndsWith("_dbl") Then
            MachineData.Put(Crsr.GetColumnName(X), Crsr.GetDouble2(X))
        Else
            MachineData.Put(Crsr.GetColumnName(X), Crsr.GetString2(X))
        End If
    Next

Albert Kallal · Jan 29, 2020

Ok, first a great question. We all know that stuffing a string type into a column that you want as null is a "got ya". The so called JavaScript Schrödinger's cat box?
Well, who's not been bitten by that nasty!

So in that inside that box, JS thinks it a null, but using a string as means to "look inside" that object box, you get a string!!!! - and that will return the TEXT "null" as as string!!! How nasty! - you push that back, and you get a string null now saved!!!

There are a good number of posts here as to why using object type in place of string is VERY warranted, and will save you days of time in a room with padded walls. You thus no doubt use some type of parms array() of object types for your updates etc. - good for you! You can't use parms() array of strings - you get update errors, or worse start shoving strings of wrong values.

While you can't get the data types and columns from a actual query or cursor, you can execute a command 100% separate against the table, and get the column types. I thus suggest you grab the types into say a map, and thus you have a handy structure that will return the data type for a given column.

It turns out sqlite supports a command called PRAGMA schema.table_info(table-name);

When you execute the above, (and note the lack of quotes around the table name, sqlite will spit out this as a cursor/table result. To be fair single quotes around the table name is allowed for the above. The PRAGMA command can get you darn near anything in regards to a database (index information - you name it)

Ref here:

https://sqlite.org/pragma.html#pragma_table_info

So you get this:

Thus "before" you start to use a table, and say query against it? Well, you can grab the table structure like this:

B4X:

    Dim rs As ResultSet = Main.sql.ExecQuery("PRAGMA table_info(customers);")
    Dim MyFields As Map
    MyFields.Initialize

    Do While rs.NextRow

           MyFields.Put(rs.GetString("name"), rs.GetString("type"))

    Loop
    rs.Close

    ' get data type of any column
    Log (MyFields.Get("CustomerId") )
    Log ( MyFields.Get("Company") )

Output:

B4X:

INTEGER
NVARCHAR(80)

So, I would likely wrap the above in some "general" Sub/function, call it and it would return for you a "nice" setup map/collection.
At that point you now have a nice tableref "design" map. You thus now have a handy dandy means to get the type of a column. I kind of wish one could get the info from a cursor, but at least a "reasonable" approach, and nice means to get this info exists. So, say at application startup, you could get a few maps that you require.

Regards,
Albert D. Kallal
Edmonton, Alberta, Canada

Mahares · Jan 29, 2020

MrKim said:
way to move the data from a cursor into a reusable format without a whole lot of assignment statement?

Here is a totally different approach to @Albert Kallal (Access MVP) without having to deal with maps. You stay within SQLite but use VIEW to build the information you need and work with it. You can easily create a view from any table or query. When you are finished with the database , you can drop the views or leave them as part of the database. If you no longer need the views, when you close the database you can add TEMP to the query between CREATE and VIEW so the views get deleted when the database connection is closed:. Here is an example:

B4X:

strQuery="CREATE VIEW vPopulation AS SELECT COUNTRY, POPULATION FROM tblPopulation ORDER BY COUNTRY"
SQL1.ExecNonQuery(strQuery)

Cursor1 = SQL1.ExecQuery("SELECT * FROM vPopulation") ' use the view as if it was a table

Albert Kallal · Jan 29, 2020

the problem is I also need the field name for the Key.

Ok, I may have missed the goal here. I had assumed the main goal is to get the data types.

Since it seems (more clear to me) that the goal here is to not have to type in all those field names by hand?

You can use this approach:
eg:

B4X:

     Dim MachineData  As Map
     MachineData.Initialize
     Dim strF As String
     Dim Crsr As ResultSet = Main.sql.ExecQuery("select * from tblHotels")
    Dim i As Int

       Do While Crsr.NextRow

          For i = 0 To Crsr.ColumnCount - 1
              strF =  Crsr.GetColumnName(i)
              MachineData.Put(strF,Crsr.GetString(strF))
          Next

     Loop
     Crsr.Close

So, the above is certainly a possible. You thus do NOT have to “hand code” the column names with above.
Quite sure the above was your "actual" question here. So, yes, you can get/grab/use the field name as per above.

The ONLY downside to above is that we pulling number types as string, and also pulling null values as a string. As I STRONG pointed out, you can risk this.

So, it not clear if the goal is just above, or we STILL want the strong data typing? (this is where I may have assumed wrong - my bad).

However, your posted code DOES have strong types - not just getstring.

So, if you REALLY need the data types?

That’s where my previous field type map code would be used. And VERY nice is that you could add a PK Boolean flag also (but lets take this one step at a time!!!).

So, one could make two geneal routines. (one to get the field data types), and another to return any sql as a map.

So, right now, the above loop may well suffice, but if you want the Map to be strong data typed to the column(s) data type?

However, if you want data typed maps?

Then the above loop wold become this:

B4X:

    Dim tblHotels As Map = GetTAbleDef("tblHotels")
    Do While Crsr.NextRow

          For i = 0 To Crsr.ColumnCount - 1

              strF =  Crsr.GetColumnName(i)
              strType = tblHotels.Get(strF)

              If strType.Contains("NVARCHAR") Then
                   MachineData.Put(strF,Crsr.GetString(strF))
              Else if strType.Contains("INTEGER") Then
                   MachineData.Put(strF,Crsr.GetInt(strF))
              Else if strType.Contains("NUMERIC") Then
                 MachineData.Put(strF,Crsr.GetDouble(strF))       
              End If

          Next

     Loop
     Crsr.Close

Not too messay!

So you can use a “clean” loop to pull the resultset into a map. However, if you need/want to preserve the type (as opposed to just puling as string), then you have to adopt the above bit of code. It certainly a “bit more” messy, but this again could be a generalized bit of code that you pass the sql and it returns a map.

The “GetTableDef” routine is previous posted above, but as a separate
routine it could/would be:

B4X:

Sub GetTableDef (strTAble As String) As Map

     Dim rs As ResultSet = Main.sql.ExecQuery("PRAGMA table_info(" & strTAble & "customers);")

     Dim MyFields As Map

     MyFields.Initialize

     Do While rs.NextRow

          MyFields.Put(rs.GetString("name"), rs.GetString("type"))

     Loop

     rs.Close

    Return MyFields

So, if you can live with “strings”, then you can loop to a map rather easy. If you “need” the strong data typing, then you have to add the above if/else set of statements to determine the data type. Either way, you can eliminate the need to “hand code” each row, and having to hand code the field names.

I actually miss-read the post. I thought the main goal was to get the data types. In reading again, the goal was not having to type by hand all the columns (hard code, and hand code,and presurabley save world poverty here).

However, since your posted code DID in fact have strong typed data, I assumed this was still part of the goal.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

Albert Kallal · Jan 29, 2020

Mahares said:
You stay within SQLite but use VIEW to build the information you need and work with it

The problem as I read this is not sql, and getting a cursor (a view, or some sql does not change the problem. The problem is the poster asking this bit:

So my question is is there any way to get the field Names as well as the values using the index.

So, creating a view does not change the problem here. I can use some sql, or create a view (which is just sql against another query). After all is said and done, you THEN still have to deal with a result set, and STILL want to via code get the coluumn names as opposed to typing by hand all the column names.

So, to answer the poster? You can get the field names as a column collection as I outlined in the this post - first simple loop. However, you can't get the column data as a index, you HAVE to use the field name. However, we can index by column number to get the column name, and then use that. So we do acheive your goal as you ask.

As noted, the simple loop is quite clean - no having to type in the column names. The "hard" part is if that easy solution ALSO requires the strong data typing.

So, I might have missed something here, but a result set of sql, or a result set from a view puts us at the EXACT same spot in terms of the NEXT question of obtaining the columns, and thus not having to hard code the column names in code.

So, the above two routines should solve this nice. And if we REALLY had our ducks in a row? Well, we should create a recordSet class, both with update, insert and "save". (and movenext, and move previous). This would allow use of a recordSet object much like we do with VBA, or even vb.net and a "datatable".

I "am" considering to create a recordset type of object - as it would reduce a lot of in-line sql code, and also allow edit of data in way that so many developers here have experienced in the past.

And mapping data to controls on a screen? I would set the tag value to the name of the column, and thus a "general" routine could/would then take the data from the cursor (edit: our map) and set the values on the form.

I might be miss-reading this, but the issue is not a cursor based on sql, or one based on a view. But what happens AFTER you get the cursor, and is one going to hand/hard code the field names, or use some loop to get the column names as per the original posters question.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

Jorge M A · Jan 29, 2020

Here's my two cents:

It's very true that there's a question:

MrKim said:
is there any way to get the field Names as well as the values using the index.

But it is also true that there is a Requirement:

MrKim said:
I need to store the data

And a Plan:

MrKim said:
I plan to put it in a map.

I'm assuming that this definition is due to the fact that later you have to look for some particular information for some kind of process, so you must have a way to locate the record you are looking for, assuming also that there are several records for a given table, by the proposed codes, your plan to place them in Maps is unfeasible, because you would only be saving the values of the last record.

From the Documentation:

Map
A collection that holds pairs of keys and values. The keys are unique. Which means that if you add a key/value pair (entry) and the collection already holds an entry with the same key, the previous entry will be removed from the map.

I have the impression that the solution must lie in something else...

MrKim · Jan 30, 2020

Jorge M A said:
Here's my two cents:

It's very true that there's a question:

But it is also true that there is a Requirement:

And a Plan:

I'm assuming that this definition is due to the fact that later you have to look for some particular information for some kind of process, so you must have a way to locate the record you are looking for, assuming also that there are several records for a given table, by the proposed codes, your plan to place them in Maps is unfeasible, because you would only be saving the values of the last record.

From the Documentation:

I have the impression that the solution must lie in something else...

First, thanks everyone for some interesting and thoughtful ideas. I completely missed Crsr.GetColumnName so that is a big start. Yes, unfortunately what is missing is Crsr.GetDataType. But, it appears after some very minor testing, that virtually everything I need can be had with Getstring! Numbers, Dates, and strings are all returned as strings by getstring so something like this should work.

B4X:

                Dim XXXX As Int, M As Map
                M.Initialize("")
                For XXXX = 0 To Crsr.ColumnCount - 1
                    M.Put(Crsr.GetColumnName(XXXX), Crsr.GetString2(XXXX))
                Next

r.e.

Jorge M A said:
... your plan to place them in Maps is unfeasible, because you would only be saving the values of the last record.

I display a portion of the data for each record in a list. When the user clicks on it I display a LOT more information in another window. My plan is to create a map for each record and attach that map to a tag on each item in the list.

Albert Kallal · Jan 30, 2020

Excellent!
To be fair, I missed the fact that you using jDBC to sql server. However, you are in luck! The object model for resultSet vs jdbResultSet is 100% identical!

While the Microsoft web site on JDBC shows move next, move first etc.? (well, give them a try, but I am quite sure you don’t’ get re-use of the cursor from Android.

So, then and thus your original point, gripe, and search for a solution remains 100% correct here.

However, as my above code shows, you can get the column names by index, and THEN use that to grab the column data.

The jdbcResult set works 100% the same as the sqlite result set – including this ability to grab the column name by index. And the syntax is 100% identical also!

And, once again, if you need the data types for each row, then again, you can use the tableMap idea floated above. In place of this:

B4X:

Dim rs As ResultSet = Main.sql.ExecQuery("PRAGMA table_info(" & strTAble & "customers);")

You can use this:

B4X:

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblHotels'

And it outputs this:

B4X:

COLUMN_NAME    DATA_TYPE
ID    int
LastName    nvarchar
FirstName    nvarchar
HotelName    nvarchar
Address    nvarchar
AX    nvarchar
.etc .etc.

And once again, you can create that “handy dandy” table map if you do need the data types and strong data typing.

At the end of the day:

100% agree that some type of “cursor” or dataset that allows ease of working with a data set is a GREAT goal and idea on your part. And being able to work, use, play, re-loop that dataset is a “long time” approach in just about EVERY development platform I have used. (so ZERO surprise on your part as a goal here).

As I have outlined, you can get the columlist, and use that for getstring as per above.

And, if you really do need the data types for strong data tping in your code? Well then again you can adopt the above table map idea.

Edit: do keep in mind that you can run into some REAL nasty issues with null columns - they return the text null as a string, and when you go back the other way, you find a VERY hard time updating columns with null data. (you have to use type of object, not string, since a string data type really has trouble with a null data type).

The ONE thing you don’t get compared to the sqlite tablemap idea is the PK column. However, you CAN get this information (the PK) if you need be – but this post is a bit long already. If you need both the data types and which column is the PK, then do ask – I’ll post a working solution.

So, quite a few people get by JUST using GetString for everything - it will depend on if that data is to be updated and set back to sql server. If you pulling to fill out things, then you should be ok. (or place the results of the Getstring into a object as opposed to string types - the issue will be nulls if you don't address this issue.

Still think we could use some kind of datatable object that takes the query, lets us play, re-loop, and modify each row. Then when done, we can do a Object.Update command with a connection and send the whole mess back to sql server.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

Android Question Question about cursors

MrKim

Well-Known Member

aeric

Expert

aeric

Expert

aeric

Expert

Albert Kallal

Active Member

Mahares

Expert

Albert Kallal

Active Member

Albert Kallal

Active Member

Jorge M A

Well-Known Member

MrKim

Well-Known Member

Albert Kallal

Active Member

Similar Threads

Android Question Question about cursors

Well-Known Member

Expert

Expert

Expert

Active Member

Expert

Active Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency