Android Question MySQL query statement for assistance

[bskotu555]

I have a MySQL query statement that can currently retrieve the target row. I want to get the column name of the next non empty column after the last column of the row in MySQL. How do I write the code?

 

[Sandman]

I want to get the column name of the next non empty column after the last column of the row in MySQL.

I am unable to parse this sentence. Perhaps you could rephrase and give an example of what you want?

 

[aeric]

To retrieve the values of the resultset, you need to specify the column names or use GetString2(index)

Example:

Dim strQL As String = "SELECT Col1, Col2, Col3 FROM Table1 WHERE id = 6"
Dim RS As ResultSet = SQL1.ExecQuery(strSQL)
Do While RS.NextRow
  'Log(RS.GetString("Col1") & "|" & RS.GetString("Col2") & "|" & RS.GetString("Col3"))
  Log(RS.GetString2(0) & "|" & RS.GetString2(1) & "|" & RS.GetString2(2))
Loop
RS.Close

It is better to learn more from the SQLite guide. Many concepts apply to MySQL too.

https://www.b4x.com/guides/B4XSQLiteDatabase.html

 

[zed]

Let's imagine a table my_table with columns col1, col2, col3, col4, etc.
We could do something like this:

Dim rs As ResultSet
rs = SQL.ExecQuery("SELECT * FROM ma_table WHERE id = 1") ' or your target condition

If rs.NextRow Then
    Dim columnNames As List
    columnNames.Initialize
    columnNames.AddAll(Array As String("col1", "col2", "col3", "col4")) ' adapts to your table

    Dim lastNonEmptyIndex As Int = -1

    For i = 0 To columnNames.Size - 1
        Dim value As String = rs.GetString(columnNames.Get(i))
        If value.Trim <> "" Then
            lastNonEmptyIndex = i
        End If
    Next

    ' Finds the next non-empty column after the last filled one
    For i = lastNonEmptyIndex + 1 To columnNames.Size - 1
        Dim nextValue As String = rs.GetString(columnNames.Get(i))
        If nextValue.Trim <> "" Then
            Log("Next non-empty column : " & columnNames.Get(i))
            Exit
        End If
    Next
End If

rs.Close

 

[aeric]

I am unable to parse this sentence. Perhaps you could rephrase and give an example of what you want?

You are right. The OP question is very confusing.
But I get his message on WeChat. But I am still not 100% clear or understand correctly the question. I think he wants to exclude the columns with Null values.
As I wrote above, just specify the column names excluding the columns having Null values.

Assume we have a table like below.

Col1	Col2	Col3	Col4	Col5	Col6
AAA	100	A1	Null	Null	5
BBB	200	A1	Null	Null	6
CCC	300	A1	Null	Null	7

Using SQL query: SELECT Col1, Col2, Col3, Col6 FROM Table1 WHERE Col3 = 'A1'
and Col4 and Col5 will be skipped.

 

[Sandman]

You are right. The OP question is very confusing.
But I get his message on WeChat. But I am still not 100% clear or understand correctly the question.

Yeah, I've read the answers in this thread, and I'm not sure that's what he's asking for. Or perhaps he is. I don't know. I need for him to explain better before trying to guess an answer for him.

 

[emexes]

If your columns that might be null have a regular name structure, like the col1, col2, col3, col4 example courtesy of @zed then perhaps something like this

Dim rs As ResultSet = sql1.ExecQuery("select * from People")
Do While rs.NextRow
    Dim L As List
    L.Initialize
    For ColumnIndex = 0 To rs.ColumnCount - 1
        If rs.GetColumnName(ColumnIndex).StartsWith("col") Then    'add .ToLowerCase if necessary to make case-insensitive
            L.Add(rs.GetString2(ColumnIndex))
        End If
    Next
    Log(L)
Loop

(ArrayList) [FirstName, LastName, BirthYear, Suburb, Postcode, ResultI, ResultII, ResultIII, ResultIV, ResultV]
(ArrayList) [17.8, null, null, null, null]
(ArrayList) [null, 40.0, 29.5, null, null]
(ArrayList) [null, 73.0, null, null, null]
(ArrayList) [47.3, null, 45.2, null, null]
(ArrayList) [69.8, 43.2, 8.5, null, null]
(ArrayList) [null, null, 37.1, 84.9, null]
(ArrayList) [null, null, null, 47.1, null]
(ArrayList) [27.0, 62.4, null, 95.7, 55.0]
(ArrayList) [null, null, 88.8, 61.2, 18.2]
(ArrayList) [null, null, null, null, null]
(ArrayList) [97.5, 40.0, null, 99.6, null]
(ArrayList) [null, null, null, null, null]
(ArrayList) [null, null, 34.6, 55.2, null]
(ArrayList) [null, 77.4, 39.2, 65.6, null]
(ArrayList) [65.7, 93.1, null, null, null]
(ArrayList) [60.3, null, 42.7, null, 52.3]

 

[emexes]

or even better, filter out null values instead of adding them to the list:

Dim rs As ResultSet = sql1.ExecQuery("select * from People")
Do While rs.NextRow
    Dim L As List
    L.Initialize
    For ColumnIndex = 0 To rs.ColumnCount - 1
        If rs.GetColumnName(ColumnIndex).StartsWith("Result") Then    'add .ToLowerCase if necessary to make case-insensitive
            If rs.GetBlob2(ColumnIndex) <> Null Then
                L.Add(rs.GetString2(ColumnIndex))
            End If
        End If
    Next
    Log(L)
Loop

(ArrayList) [FirstName, LastName, BirthYear, Suburb, Postcode, ResultI, ResultII, ResultIII, ResultIV, ResultV]
(ArrayList) [26.6, 43.3, 62.3]
(ArrayList) [35.3]
(ArrayList) [51.0, 75.8, 75.6, 77.4]
(ArrayList) [74.3, 90.8, 72.4, 58.4]
(ArrayList) [87.4, 47.0, 60.9]
(ArrayList) [16.4, 45.4]
(ArrayList) [83.4, 9.8]
(ArrayList) [41.2, 54.9, 34.0]
(ArrayList) [37.6]
(ArrayList) [50.8]
(ArrayList) [3.5, 8.0, 63.5]
(ArrayList) [10.5, 89.1, 64.2]
(ArrayList) [13.9]
(ArrayList) [17.2, 48.5, 60.5]
(ArrayList) [83.4, 35.8]
(ArrayList) [31.4, 83.6, 96.4]
(ArrayList) [23.1, 49.4, 25.1, 0.6, 11.8]
(ArrayList) [31.7, 10.7]
(ArrayList) [8.8]
(ArrayList) [37.9, 83.1, 93.2]
(ArrayList) [83.9, 73.6]

 

[bskotu555]

If your columns that might be null have a regular name structure, like the col1, col2, col3, col4 example courtesy of @zed then perhaps something like this

Dim rs As ResultSet = sql1.ExecQuery("select * from People")
Do While rs.NextRow
    Dim L As List
    L.Initialize
    For ColumnIndex = 0 To rs.ColumnCount - 1
        If rs.GetColumnName(ColumnIndex).StartsWith("col") Then    'add .ToLowerCase if necessary to make case-insensitive
            L.Add(rs.GetString2(ColumnIndex))
        End If
    Next
    Log(L)
Loop

(ArrayList) [FirstName, LastName, BirthYear, Suburb, Postcode, ResultI, ResultII, ResultIII, ResultIV, ResultV]
(ArrayList) [17.8, null, null, null, null]
(ArrayList) [null, 40.0, 29.5, null, null]
(ArrayList) [null, 73.0, null, null, null]
(ArrayList) [47.3, null, 45.2, null, null]
(ArrayList) [69.8, 43.2, 8.5, null, null]
(ArrayList) [null, null, 37.1, 84.9, null]
(ArrayList) [null, null, null, 47.1, null]
(ArrayList) [27.0, 62.4, null, 95.7, 55.0]
(ArrayList) [null, null, 88.8, 61.2, 18.2]
(ArrayList) [null, null, null, null, null]
(ArrayList) [97.5, 40.0, null, 99.6, null]
(ArrayList) [null, null, null, null, null]
(ArrayList) [null, null, 34.6, 55.2, null]
(ArrayList) [null, 77.4, 39.2, 65.6, null]
(ArrayList) [65.7, 93.1, null, null, null]
(ArrayList) [60.3, null, 42.7, null, 52.3]

 

[emexes]

View attachment 166946

If the might-be-null column names are numbers, then maybe something like this (same as previous code, except for the column name selection logic) :

Dim rs As ResultSet = sql1.ExecQuery("select * from People")
Do While rs.NextRow
    Dim L As List
    L.Initialize
    For ColumnIndex = 0 To rs.ColumnCount - 1
        If "123456789".Contains(rs.GetColumnName(ColumnIndex).CharAt(0)) Then    'if column name begins with digit 1..9
            If rs.GetBlob2(ColumnIndex) <> Null Then
                L.Add(rs.GetString2(ColumnIndex))
            End If
        End If
    Next
    Log(L)
Loop