[SOLVED] Conversion problem
lazarus / 16-Databases

----------------------------------------------------------------------
Slyde · 2023-04-27 06:23

I have some NULL text fields in my SQLite3 db.  And when I read a row
I populate some TMemos with the fields.  But when I get to a field
that's NULL, I get the following error:

Could not convert variant of type (Null) into type (String).

from a line like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Memo5.Text
:= frmMain.DS2.DataSet.Fields[5].Value;
if frmMain.DS2.DataSet.Fields[5].Value is NULL, then I need to be able
to convert it for TMemo.  I just don't know how to do it.

----------------------------------------------------------------------
Slyde · 2023-04-27 06:39

I fixed the problem and tried to remove this post.  My apology.

----------------------------------------------------------------------
Zvoni · 2023-04-27 06:41

Yes solved, i know, but why don't you "convert" it inside the SQL-
Statement/Resultset?
There is no reason to do this crap in Frontend-code

----------------------------------------------------------------------
Slyde · 2023-04-27 06:53

I don't know how to do that.  I do it on the frontend because I don't
how to convert on the backend a NULL value in SQL to a value TMemo
will accept.

----------------------------------------------------------------------
Zvoni · 2023-04-27 07:54

Quote from: Slyde on April 27, 2023, 08:53:56 amI don't know how to do
that.  I do it on the frontend because I don't how to convert on the
backend a NULL value in SQL to a value TMemo will accept.
Show your SQL-Statement (SELECT blablabla)
And what database? SQLite, MySQL?

----------------------------------------------------------------------
Slyde · 2023-04-27 07:57

SQLite3

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ZQ_Grid.SQL.Text := 'SELECT qid, q, a1, a2, a3, a4, ta FROM
MC_Questions';

----------------------------------------------------------------------
Zvoni · 2023-04-27 08:00

Quote from: Slyde on April 27, 2023, 09:57:50 amSQLite3

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ZQ_Grid.SQL.Text := 'SELECT qid, q, a1, a2, a3, a4, ta FROM
MC_Questions';Which column returns the NULL? You have above
Dataset.Field[5]
Is it a3 or a4?
I don't remember if Fields-List is zero-based

EDIT:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT qid,
q, COALESCE(a1, 'Default for a1') AS a1,COALESCE(a2, 'Default for a2')
AS a2,COALESCE(a3, 'Default for a3') AS a3,COALESCE(a4, 'Default for
a4') AS a4,COALESCE(ta, 'Default for ta') AS taFROM MC_Questions 
COALESCE --> Returns the first NON-NULL-Value
Instead of "Default for xx" you can enter whatever text you want to be
shown.
Even empty string
COALESCE(a3, '') As a3

----------------------------------------------------------------------
Slyde · 2023-04-27 08:04

SQLite3 is zero-based.  Column 6, the 'ta' column has some NULLs in
it.

This is how I fixed it:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
frmMain.DS2.DataSet.Fields[6].Value = Null then    Edit1.Text := '' 
else    Edit1.Text := frmMain.DS2.DataSet.Fields[6].Value;
It may be crap, but at least I got it to work.  I'm learning as I go,
Zvoni.

----------------------------------------------------------------------
Zvoni · 2023-04-27 08:08

Quote from: Slyde on April 27, 2023, 10:04:31 amSQLite3 is zero-
based.  Column 6, the 'ta' column has some NULLs in it.

This is how I fixed it:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
frmMain.DS2.DataSet.Fields[6].Value = Null then    Edit1.Text := '' 
else    Edit1.Text := frmMain.DS2.DataSet.Fields[6].Value;As i said:
No need for it.
If you have fields which are already setup as "NON-NULL" in your
Database you can remove the COALESCE (e.g. if a1 to a4 can never be
NULL in the database you can remove the COALESCE)
e.g.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT qid,
q, a1, a2, a3, a4, COALESCE(ta, 'Default for ta') AS taFROM
MC_Questions
EDIT: Looks like multiple-choice questionnaire
qid = Primary Key
q = Text-Field "Question"
a1-a4 = Text-Fields "Answer X"
what is ta? (just out of curiosity)

----------------------------------------------------------------------
Slyde · 2023-04-27 08:12

I've never used COALESCE before.  But I was thinking of doing the same
thing with an UPDATE statement.  Just looked at it on SO.

Thanks for helping, Zvoni.  I appreciate you.

----------------------------------------------------------------------
Slyde · 2023-04-27 08:12

'ta' := The Answer

----------------------------------------------------------------------
Zvoni · 2023-04-27 08:14

Quote from: Slyde on April 27, 2023, 10:12:06 amI've never used
COALESCE before.  But I was thinking of doing the same thing with an
UPDATE statement.  Just looked at it on SO.

Thanks for helping, Zvoni.  I appreciate you.
COALESCE is IMO one of the more important functions in SQL.
I use it extensively in my queries, when i have to grab data from
different tables at the same time, especially in LEFT JOINS

EDIT:
What COALESCE(ta, '') is doing on the backend would be equivalent to
frontend (-aircode)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     if
frmMain.DS2.DataSet.Fields[6].Value <> Null then               
Edit1.Text := frmMain.DS2.DataSet.Fields[6].Value    Else       
Edit1.Text := '';

----------------------------------------------------------------------
Slyde · 2023-04-27 08:26

I incorporated COALESCE into my query and it worked like a charm. 
Thanks again for your help, Zvoni.

----------------------------------------------------------------------
jcmontherock · 2023-04-27 08:34

In MySQL the field returned is 'NULL'. I use:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     //
Change text color if null field    if sText = 'NULL' then
StringGrid1.Canvas.Font.Color := clSilver    else                    
     StringGrid1.Canvas.Font.Color := clBlack;...

----------------------------------------------------------------------
wp · 2023-04-27 08:48

Quote from: Slyde on April 27, 2023, 08:23:48 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Memo5.Text
:= frmMain.DS2.DataSet.Fields[5].Value;Maybe like this?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Memo5.Text
:= frmMain.DS2.DataSet.Fields[5].AsString;Or like this?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if not
IsNull(frmMain.DS2.Dataset.Fields[5]) then Memo5.Text :=
frmMain.DS2.DataSet.Fields[5].AsString else Memo5.Text := '';

----------------------------------------------------------------------
Zvoni · 2023-04-27 09:06

Quote from: jcmontherock on April 27, 2023, 10:34:49 amIn MySQL the
field returned is 'NULL'. I use:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     //
Change text color if null field    if sText = 'NULL' then
StringGrid1.Canvas.Font.Color := clSilver    else                    
     StringGrid1.Canvas.Font.Color := clBlack;... That's not a NULL-
Value.
It's a text 'NULL'

btw: COALESCE works in MySQL, too.

EDIT: This looks like a "Boolean" scenario.
First, i'd change the Query to return 1 or 0 (Integer-values)
Something like
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Vers1
(ANSI-SQL): SELECT SomeID, CASE WHEN SomeField IS NULL THEN 0 ELSE 1
END AS SomeColorIndex FROM SomeTableVers2 (MySQL-Specific): SELECT
SomeID, IF(ISNULL(SomeField),0,1) AS SomeColorIndex FROM SomeTable
and then in code along these lines (untested)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var 
MyColor:Array[0..1] Of TColor=(clSilver, clBlack);..//Execute Query to
retrieve the valuesStringGrid1.Canvas.Font.Color :=
MyColor[SomeDataSet.FieldByName['SomeColorIndex'].AsInteger];

----------------------------------------------------------------------
jcmontherock · 2023-04-27 09:40

Yes, sorry, you are right...
I forgot that in result of my query I tested if the field returned is
nil before I gave it the value 'NULL'.

----------------------------------------------------------------------
Zvoni · 2023-04-27 09:41

Quote from: jcmontherock on April 27, 2023, 11:40:49 amYes, sorry, you
are right...
I forgot that in result of my query I tested if the field returned is
nil before I gave it the value 'NULL'.
And as written above: completely unneccessary

----------------------------------------------------------------------
Slyde · 2023-04-27 11:57

Hey wp.  This is the way I'd normally do it, like you showed here in
your reply:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Memo5.Text
:= frmMain.DS2.DataSet.Fields[5].AsString;
But everything's not normal right now     Twisted my knee last week
and doc gave me some pain killers that really really work.  The trade-
off is a LOT of cloudy thinking though. 

I appreciate everyone's input on this.  Excellent forum!

----------------------------------------------------------------------
Zvoni · 2023-04-27 12:46

Quote from: Slyde on April 27, 2023, 01:57:36 pmThe trade-off is a LOT
of cloudy thinking though. 
Then lean back and enjoy...

btw:... do you share?

----------------------------------------------------------------------
Slyde · 2023-04-27 12:54