Odd query results

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Odd query results

Bob Sneidar-2
Hi all.

I had an odd result from a query. First, I created a customer and site whose unique id's were 0 for a primary key autoincrementing column. I did this so that I can decommission devices by assigning 0 for their customer and site id's, and then find them later by selecting the customer ID 0. With me so far?

Well when I queried for empty or null site id's I got the records whose siteid's are 0! 0 is not empty OR null. Is this what SQL is supposed to do? In other words, is 0 the same as NULL the same as Empty String? It's so obscure because the unique autoincrementing integers usually start with 1, NEVER 0.

Sincerely,
The BugMeister
(Bob S)
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Odd query results

Mike Kerner
I'd have to see your SQL, but how are you checking for null and empty?
Null is a funky value in SQL that can mean a variety of things, and an
empty value in an integer column will give you heartache.  WIth many
DBMS's, you have to check IS NULL or IS NOT NULL depending on the column
values and types.  See the syntax reference for the DBMS you are using.  If
I was in your position, 0 would be unassigned and -1 would be
decommissioned, the column would be set to NOT NULL, and a trigger would
set it to 0 if it IS NULL() for insert and for update.

On Wed, Dec 14, 2016 at 7:31 PM, Bob Sneidar <[hidden email]>
wrote:

> Hi all.
>
> I had an odd result from a query. First, I created a customer and site
> whose unique id's were 0 for a primary key autoincrementing column. I did
> this so that I can decommission devices by assigning 0 for their customer
> and site id's, and then find them later by selecting the customer ID 0.
> With me so far?
>
> Well when I queried for empty or null site id's I got the records whose
> siteid's are 0! 0 is not empty OR null. Is this what SQL is supposed to do?
> In other words, is 0 the same as NULL the same as Empty String? It's so
> obscure because the unique autoincrementing integers usually start with 1,
> NEVER 0.
>
> Sincerely,
> The BugMeister
> (Bob S)
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Odd query results

Bob Sneidar-2
Good idea, except I think I set the primary key columns to unsigned. Also, they have to be unique, so I cannot have more than one unassigned device, which would often be the case.

I may have to alter my schema but of course that cuts the total number of integers I have available in half. Not a big deal though.

Bob S


On Dec 14, 2016, at 18:58 , Mike Kerner <[hidden email]<mailto:[hidden email]>> wrote:

I'd have to see your SQL, but how are you checking for null and empty?
Null is a funky value in SQL that can mean a variety of things, and an
empty value in an integer column will give you heartache.  WIth many
DBMS's, you have to check IS NULL or IS NOT NULL depending on the column
values and types.  See the syntax reference for the DBMS you are using.  If
I was in your position, 0 would be unassigned and -1 would be
decommissioned, the column would be set to NOT NULL, and a trigger would
set it to 0 if it IS NULL() for insert and for update.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Odd query results

Mike Kerner
They should be unique in the one table, not the many table.  So if I
understand what you're doing, you should have a table for locations, with
the id (unique), and the name.  Then in the devices table they will also
have an id, which is unique, but then it will also have a locationid, which
is not unique as it references the id in the locations table.  If you are
using unsigned for the location, then you will have to check IS NOT NULL()
or IS NULL() and use null for the cases where you haven't assigned a
location, yet (e.g. new records), and you could use 0 for out-of-service.

On Thu, Dec 15, 2016 at 5:38 PM, Bob Sneidar <[hidden email]>
wrote:

> Good idea, except I think I set the primary key columns to unsigned. Also,
> they have to be unique, so I cannot have more than one unassigned device,
> which would often be the case.
>
> I may have to alter my schema but of course that cuts the total number of
> integers I have available in half. Not a big deal though.
>
> Bob S
>
>
> On Dec 14, 2016, at 18:58 , Mike Kerner <[hidden email]<mailto:
> [hidden email]>> wrote:
>
> I'd have to see your SQL, but how are you checking for null and empty?
> Null is a funky value in SQL that can mean a variety of things, and an
> empty value in an integer column will give you heartache.  WIth many
> DBMS's, you have to check IS NULL or IS NOT NULL depending on the column
> values and types.  See the syntax reference for the DBMS you are using.  If
> I was in your position, 0 would be unassigned and -1 would be
> decommissioned, the column would be set to NOT NULL, and a trigger would
> set it to 0 if it IS NULL() for insert and for update.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Odd query results

Bob Sneidar-2
Thanks Mike. You are correct in your assessment. Many sites can belong to a customer, many devices to a site. But of course each primary key is unique. The user is required to find a customer, after which they are presented with a list of sites for that customer, and selecting one in a datagrid presents them with the devices at that site along with contacts, service records, notes etc. It kind of cascades.

The problem I was having is that when doing a query for an empty custom, it would find the one with a custid of 0, and yesterday when I set the custid to -1, it found that one too! I don't have to deal with NULL so much because I have a function called denullify which replaces all NULL values in an array with empty strings.

What I will have to do is conditionally check for empty values and set the dgdata of the customer data grid to empty instead of populating it. Or else I can simply not have a "warehouse inventory" customer record or site record and have an alternate means of finding decommissioned devices.

Thanks for your help.

Bob S


On Dec 15, 2016, at 18:04 , Mike Kerner <[hidden email]<mailto:[hidden email]>> wrote:

They should be unique in the one table, not the many table.  So if I
understand what you're doing, you should have a table for locations, with
the id (unique), and the name.  Then in the devices table they will also
have an id, which is unique, but then it will also have a locationid, which
is not unique as it references the id in the locations table.  If you are
using unsigned for the location, then you will have to check IS NOT NULL()
or IS NULL() and use null for the cases where you haven't assigned a
location, yet (e.g. new records), and you could use 0 for out-of-service.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode