Android Question dbutils.executemap and null value

mike1967 said:

Where i wrong ?

Click to expand...

Albert Kallal said:

Ah, yes, my love and hate relationship with relational databases and nulls! And no that often comment about how nulls are some speical cool thing about no meaning in a database? Nope! - not a debate for me!

Anyway, first lets address your issue:

B4X:

select ID, FirstName, LastName, HotelName, City, ifnull(LastName,'')  from tblHotels where LastName is NULL

OUTPUT:
View attachment 111636
Note our little nasty problem! - if you use expressions against columns in SQL, then it spits out funny column names!

So, when you try to "get" the column name - it simply does not exist - and hence you get nulls.

So, you can take your "already" kind of messy SQL and change it like say this:

B4X:

SELECT IfNull(id,'') as id, ,IfNull(codice,'') as codice, IfNull(tipologia,'') as tipologia ..........

On the other hand, that going to be one really long sql statement. Too long for my taste.

Did I ever admit that I really do try to be as lazy as possible when I code?

So, I suggest the following:
You adopt a standard - all nulls and all empty strings "" are ALWAYS saved as nulls in the database.

Or you NEVER allow nulls and always save empty strings into that data. I will say I done both in the past.

And the winner for me?

Save nulls back back to the DB.

Now, which ever road you like? (always blank (empty string) or always nulls? Then stick to that. The choice is yours.

I went with nulls. And I did so for which I think more reasons exist then not stickling with them.

And feel free to ask why - but I trying really hard to keep this post short! (good luck Albert!).

Anyway I did not not want to mess with messy SQL -

I thus use a routine called NullToBlank(), and of course the reverse. BlankToNull().

I found this less painful then having to mess up the sql queries.

But it DOES mean your code to transfer the map to the controls on the form/view does have the above functions.

B4X:

txtHotelName.Text = NullToBlank(rMap.Get("HotelName"))

' The code for these two routines are:

Sub NullToBlank(s As Object) As String
    If s = Null Then
        Return ""
    Else if s = "null" Then
        Return ""
    Else
        Return s
    End If
End Sub

Sub BlanktoNull(s As Object) As Object

    If Not(s Is String) Then
        Return s
    End If

    If s = "" Then
        Return Null
    Else
        Return s
    End If

End Sub

So I hope the above helps here.

Part II - how lazy can I get? (well, VERY!!!)

Now, being lazy? And tired of writing the same code over and

I wrote a utility to :

Get a map (from row of data).
Send map to the given view/form.

User edits. Then I have a routine Get map from form.
And then I have a routine called write map. It auto generates the update statement - sends the data back to db.

As a result, I now can shuffle data to/from any form. And I don't have to re-write the code for each new form. And I don't even have to generate each of the controls as members into the code module either!

So, I have this typical code - we are loading a row of data based on PK id (and I can WELL see that's what you doing in your code snip).

B4X:

    strSQL =  "SELECT * FROM InvoiceDetail WHERE IDA = " & gID
  
    oldRecord = DBUtils.ExecuteMap(Main.sqlite,strSQL,Null)   ' load to map
  
    MyCode.Floadermap(Activity,oldRecord)     ' send map to form

So Floadermap? It simply sends the map to the controls and fill out the view/form.

I adopted a standard in which the tag of each control represents the data column from the table.

So, I do this:

Shuffle data from map to form - nulls become blank text.
Shuffle form data back to map? - blank strings become null again.

So, that Floader routine? It looks like this:

B4X:

Sub FloaderMAP(v As Activity, rMap As Map)

    ' load controls with values based on passed map
    For Each MyV As View In v.GetAllViewsRecursive
        If MyV.Tag <> Null Then
            If MyV.Tag <> "" Then
                    SetControlMAP(MyV,rMap)
            End If
        End If
    Next

End Sub

Sub SetControlMAP(v As View, rMAP As Map)
  
    If v Is CheckBox Then
        If v.Tag <> "" Then
            Dim check As CheckBox =  v
            check.Checked = (NullToBlank(rMAP.Get(v.Tag)) = "1")
        End If
    else If v Is EditText Then
        If v.Tag <> "" Then
            Dim edit As EditText = v
            edit.Text = NullToBlank(rMAP.Get(v.Tag))
        End If
    else if v Is Label Then
        If v.Tag <> "" Then
            Dim label As Label = v
            label.Text = NullToBlank(rMAP.Get(v.Tag))
        End If

    If v Is CheckBox Then
        If v.Tag <> "" Then
            Dim check As CheckBox =  v
            check.Checked = (NullToBlank(rMAP.Get(v.Tag)) = "1")
        End If
    else If v Is EditText Then
        If v.Tag <> "" Then
            Dim edit As EditText = v
            edit.Text = NullToBlank(rMAP.Get(v.Tag))
        End If
    else if v Is Label Then
        If v.Tag <> "" Then
            Dim label As Label = v
            label.Text = NullToBlank(rMAP.Get(v.Tag))
        End If
    Else If v Is Spinner Then
     bla bla bla

Note my NullToBlank function. So, I keep the map with nulls. I keep the sql cursors and resultsets? All respect and keep nulls intact.

I thus ONLY convert WHEN sending the data to the controls on the form.

As noted, I don't write code to send data to the controls anymore. I use above to SEND the map to the form that loops the controls for me. So, now, if I create 2 or 10 forms/views in a project? I don't have to write new code to fill out those silly forms. And I also leverage those routines to deal with nulls.

I am MOST happy to post all of the routines I have for this map to form/views, and form/views back to map, and the routine that takes oldMap, newMap, and generates the database update statement for me

That's right - you don't think I would write the same code over and over to update the changes back to the database, do you?
Not!!!

Now EVEN if you don't adopt the above ideas?
You can as noted re-write your sql query - and add all those column "aliases" back.

Or adopt a NullToBlank function for use WHEN you transfer/fill out the controls on a form/view.

However, I have found just STUNNING performance with sqlLite. Oh man, lets all have a group hug for B4A? (group hugs!!!).


So, with such great performance? Gee, SELECT * from tblName - and I am happy, and done!!!
(why bother even typing out field names? - that's even too much work now!)

So, if you wish, I will post the routines I use for this shuffle - it only sends CHANGES back to the db row, and it generates the sql update statement for you, and as a result? I don't have to write update statements over and over for each form/view that I created to save the data back. (and those routines handle the null dance back and forth for me).

Even if you don't adopt the above ideas? You should consider that NullToBlank(), and BlankToNull() ideas.

Your get() column name? Gee, I thought that should / would error out with a invalid column name - but it looks like your gets are returning nulls since the column names are changing as a result of your ifNull() expressions.

So, you have to deal with the nulls issue one way (as you are doing), or another way.

And, again - my apologies if this post is a wee bit too long - I am working to keep my posts shorter!

And as noted, feel free to ask for the full set of routines I use for this. They are IMHO the most useful B4A code routines I have written!

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

Click to expand...