Posted: Wed May 06, 2009 3:07 pm Post subject: [asterisk-dev] NULL from MySQL database not NULL?
Hi,
I am not sure if it is a bug, but Asterisk SVN-branch-1.6.1-r190371 +
Addons 1.6.1 behaves in the following way:
(assuming the following query returns 'null' from DB: "SELECT
some_null_col FROM table WHERE where id = 1")
console:
-- Executing [601@test:1] MYSQL("SIP/OpenSER-082cf100", "Connect
connid1 mysql-ro asterisk pass1 test") in new stack
-- Executing [601@test:2] MYSQL("SIP/OpenSER-082cf100", "Query
resultid1 1 SELECT some_null_col FROM table WHERE where id = 1") in
new stack
-- Executing [601@test:3] MYSQL("SIP/OpenSER-082cf100", "Fetch
fetchid1 2 X-DB-null") in new stack
-- Executing [601@test:4] MYSQL("SIP/OpenSER-082cf100", "Clear 2")
in new stack
-- Executing [601@test:5] MYSQL("SIP/OpenSER-082cf100",
"Disconnect 1") in new stack
-- Executing [601@test:6] NoOp("SIP/OpenSER-082cf100", "X-DB-null:
NULL") in new stack
-- Executing [601@test:7] NoOp("SIP/OpenSER-082cf100", "ISNULL =
0") in new stack
So looks like 'NULL' value fetched from MySQL table is not considered
'NULL' by ISNULL function??!!!
Regards,
Chris
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
Posted: Wed May 06, 2009 3:47 pm Post subject: [asterisk-dev] NULL from MySQL database not NULL?
On Wednesday 06 May 2009 10:57:30 Chris Maciejewski wrote:
Quote:
I am not sure if it is a bug, but Asterisk SVN-branch-1.6.1-r190371 +
Addons 1.6.1 behaves in the following way:
It's not a bug.
Quote:
So looks like 'NULL' value fetched from MySQL table is not considered
'NULL' by ISNULL function??!!!
The ISNULL function is not related to the database function in any way. In
some ways, it's rather poorly named, because the intent of the function is
to measure whether or not the value of the variable is blank, which the string
"NULL" certainly is not.
--
Tilghman
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
console:
-- Executing [601@test:1] MYSQL("SIP/OpenSER-082cf100", "Connect
connid1 mysql-ro asterisk pass1 test") in new stack
-- Executing [601@test:2] MYSQL("SIP/OpenSER-082cf100", "Query
resultid1 1 SELECT some_null_col FROM table WHERE where id = 1") in
new stack
-- Executing [601@test:3] MYSQL("SIP/OpenSER-082cf100", "Fetch
fetchid1 2 X-DB-null") in new stack
-- Executing [601@test:4] MYSQL("SIP/OpenSER-082cf100", "Clear 2")
in new stack
-- Executing [601@test:5] MYSQL("SIP/OpenSER-082cf100",
"Disconnect 1") in new stack
-- Executing [601@test:6] NoOp("SIP/OpenSER-082cf100", "X-DB-null:
NULL") in new stack
-- Executing [601@test:7] NoOp("SIP/OpenSER-082cf100", "ISNULL =
0") in new stack
This begs the question. Is the value of X-DB-null literally the word "NULL"?
How about some tacking this onto the end of your dialplan ...
exten => 601,n,Set(VALONE='')
exten => 601,n,Set(VALTWO=0)
exten => 601,n,Set(VALTHREE='NULL')
exten => 601,n,Set(VALFOUR=1)
exten => 601,n,NoOp("ISNULL(ONE) "${ISNULL(${VALONE}))
exten => 601,n,NoOp("ISNULL(TWO) "${ISNULL(${VALTWO}))
exten => 601,n,NoOp("ISNULL(THREE) "${ISNULL(${VALTHREE}))
exten => 601,n,NoOp("ISNULL(FOUR) "${ISNULL(${VALFOUR}))
My hunch is that of those, only VALONE should return non-zero from ISNULL.
But you'll know for sure soon. Try it out and let us know what you find.
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
Posted: Wed May 06, 2009 7:31 pm Post subject: [asterisk-dev] NULL from MySQL database not NULL?
Hi Tilghman,
I would argue with this because my "null" value in the database is not
a literal "null" string, but the actual null value. It is a result of
the following query: "UPDATE table SET some_null_col = NULL WHERE id =
1"
So the actual value of some_null_col is NULL and not "NULL" and once
such a value is assigned to a channel variable, that variable is NULL,
therefore ISNULL function should return 1.
On Wednesday 06 May 2009 10:57:30 Chris Maciejewski wrote:
> I am not sure if it is a bug, but Asterisk SVN-branch-1.6.1-r190371 +
> Addons 1.6.1 behaves in the following way:
It's not a bug.
> So looks like 'NULL' value fetched from MySQL table is not considered
> 'NULL' by ISNULL function??!!!
The ISNULL function is not related to the database function in any way. In
some ways, it's rather poorly named, because the intent of the function is
to measure whether or not the value of the variable is blank, which the string
"NULL" certainly is not.
--
Tilghman
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
Posted: Wed May 06, 2009 8:24 pm Post subject: [asterisk-dev] NULL from MySQL database not NULL?
On Wednesday 06 May 2009 14:52:44 Chris Maciejewski wrote:
Quote:
I would argue with this because my "null" value in the database is not
a literal "null" string, but the actual null value. It is a result of
the following query: "UPDATE table SET some_null_col = NULL WHERE id =
1"
So the actual value of some_null_col is NULL and not "NULL" and once
such a value is assigned to a channel variable, that variable is NULL,
therefore ISNULL function should return 1.
Again, the ISNULL function has no relation whatsoever to your database.
--
Tilghman
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
On Wednesday 06 May 2009 14:52:44 Chris Maciejewski wrote:
> I would argue with this because my "null" value in the database is not
> a literal "null" string, but the actual null value. It is a result of
> the following query: "UPDATE table SET some_null_col = NULL WHERE id =
> 1"
>
> So the actual value of some_null_col is NULL and not "NULL" and once
> such a value is assigned to a channel variable, that variable is NULL,
> therefore ISNULL function should return 1.
Again, the ISNULL function has no relation whatsoever to your database.
--
Tilghman
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
Posted: Thu May 07, 2009 2:35 pm Post subject: [asterisk-dev] NULL from MySQL database not NULL?
On Thu, May 7, 2009 at 8:21 AM, Chris Maciejewski <chris@wima.co.uk> wrote:
Quote:
I understand ISNULL function has no relation to a database.
And ISNULL function is working perfectly fine.
There is a problem with MYSQL( fetch.... ) which converts NULL into "NULL".
I opened a bug 15045 and submitted a patch to fix this.
The problem is that there is no distinction within Asterisk variables
between empty and NULL. So, Your patch would just return empty string
which could be perfectly valid string response (not NULL). I believe
that it's more uncommon to have actual data in MySQL to contain string
"NULL" than empty string, so it seems a good choice. Changes in this
could break some dialplans, which would lead to unsatisfied users.
The total solution of course would be to have distinction in Asterisk
between string not set and empty string.
Regards,
Atis
--
Atis Lezdins,
VoIP Project Manager / Developer,
IQ Labs Inc,
atis@iq-labs.net
Skype: atis.lezdins
Cell Phone: +371 28806004
Cell Phone: +1 800 7300689
Work phone: +1 800 7502835
_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum