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