PsiWray

[QUESTION] How to handle MySQL database NULL cells?

Recommended Posts

Posted (edited)

I have a database table that contains certain columns with  NULL s or values, depending on if the value has already been set or not for the current record. When I query the columns of a specific record that contains those NULL cells, the value contained in the Lua table returned inside the result has a false value instead of (what I expected to be) a nil value. The type of the MySQL table column is TIMESTAMP.

So my question is, am I doing something wrong and is there instead a way to correctly handle NULL cells inside a MySQL database or is it just like described above and there's nothing I can do about it?

Thanks for support.

Edited by PsiWray

Share this post


Link to post
Posted (edited)

Not sure,

 

Doesn't blob help you out?

 

But it shouldn't be an issue. At the end they both represents NO in most lua conditions.

 

This method is in most scenarios  just asking for trouble:

if a == nil then

 

This one works just 99% of the time:

if not a then

 

Edited by IIYAMA

Share this post


Link to post
3 hours ago, IIYAMA said:

Not sure,

 

Doesn't blob help you out?

 

But it shouldn't be an issue. At the end they both represents NO in most lua conditions.

 

This method is in most scenarios  just asking for trouble:


if a == nil then

 

This one works just 99% of the time:


if not a then

 

I was thinking about a situation where you have a stored BOOL inside the MySQL table and you either have its value (which can be true/false) or you don't, in which case you execute different code. I'm not in that situation yet but I was curious about this topic. I also understand that it's a very rare situation.

Thanks for the answer.

  • Like 1

Share this post


Link to post

Actually I've found the solution. When working with booleans, the query result is of type "number". That allows you to discriminate between existing values and NULL values. For example, if you query for a boolean column and you get a result of type boolean and of value false, that means the column doesn't have a value. If on the other hand, you receive a result of type number and value 1/0 then you can cast to boolean and the thing is done.

Share this post


Link to post
Posted (edited)

about the Boolean check, I think it would be good to store it as it is "true/false" but in a string value, then when you acquire it again transform in through this simple check

dbResult = dbResult == "true" and true or false

--that's a shortcut for
dbResult = someRetrievedValue
if dbResult == "true" then
  dbResult = true
else
  dbResult = false
end

 

Edited by savour

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.