PHP, mySQL, and NULL values

I'd like a little direction on the best way to handle the following:

I have a mySQL database with column of data stored as FLOAT. (the following also holds if the column is DOUBLE) There are a handful of values in the column that are NULL.

When I draw the values out using straight PHP, the NULL remains intact.

When I draw the values via the Flash Builder Data/Services, the NULL values are converted to ZERO (0).

The reasoning (I think) is something like:

When I create the Return Type and Flash Builder introspects my table, it translates the mySQL FLOAT to the actionscript NUMBER - which seems reasonable.?However, NULL is not a valid value for an actionscript NUMBER variable - so (it seems) they are converted to zero.?Understandable, but not cool...

After tinkering for a while, I found the only way (though there are a few variations on this theme) that I can get a NULL value correctly brought into my application is to:

1. Redefine the mySQL column as VARCHAR

2. When I create the Return Type, Flash Builder considers the column an OBJECT, and it will retain the NULL values.

So, this works.?But it means I'm storing numbers as VARCHAR in the database which doesn't sit right with me...

Anybody encountered this??What's the best way to manage this translation?

PHP, mySQL, and NULL values

To the best of my knowledge everything you've written is correct. Since Number does not accept null, it defaults to (0), and since String handles that, you can pass the null.

Changing your DB to use a varchar field instead of a float is a no-no. The application's processing and use of data should not define the data type of that column in the DB. The DB column and it's associated type are there to serve a purpose. If you have a float column, perhaps it's because you want to sort on that column. Not to mention the performance of matching selects against strings vs. floats.

If I was in your dilemma, I'd look for an engineering solution to the problem. I'd do one of the following:

1. Use a sentinel value - If NULL and (0) serve different purposes, I'd modify whatever input logic to insert a -1 when it would've been NULL. Then, my application can search for (-1), signalling actions based on the NULL 'state'.

OR

2. If NULL/0 doesn't serve a particular purpose, I'd write a client-side adapter/wrapper object. Then, when deserializing on the client, I'd watch for (0) and initialize that wrapper instance appropriately.

Of these two, I'd almost always go with (1).

-M

Engineering Motto - ''The shortest distance between two points is under construction''

PHP, mySQL, and NULL values

Thanks for you reply - good to know I've at least got the ''what I think is happening'' right.?And I very much appreciate your taking the time to lay out two detailed options.

Unfortunately your two options won't work in my instance.?I'm working on a financial/statistical application which means very large positive and negative numbers are valid, as is zero.?Null means something entirely different (as it often does).?So treating null as a ''different'' number won't work since, in theory, any number is entirely valid.?In my case Null means 'no value' and generally either needs to be ignored, or requires user action in the application.

Maybe I'm in the minority in terms of usage, but this seems like it wouldn't be an uncommon scenario(?).

I've been using Flex for this kind of application for quite a while, and when I manage my data myself, I can capture nulls before they are typed (since I generally import via XML), which gets around this problem.?But I was excited about Flash Builder's data/services functionality - hoping it would save me all the effort of writing the data management layer... but maybe I'm out of luck??(Changing the DB to varchar obviously isn't an option, I just included it to be complete in passing along what I thought was happening).

Maybe another approach: Where in the flow does the item get typed??I see the %26lt;myServiceClass%26gt;.as (as well as it's _Super_ class) but it seems that the typing occurs before those getters are run??Somewhere there must be some code that actually picks up the passed DB value and stores it in the typed actionscript variable.?Can that be safely modified (if I can find it?)

Thanks again for your reply - I'm excited about Flash Builder for a number of reasons - so hoping I can get this issue sorted.

Just more notes in case anybody tries to do something similar (I changed the topic title since this is not PHP/mySQL specific).

The Return Type Definition has some interesting quirks.?Probably best explained by example:

1. Say I have two tables:

- the first table (A) has a single column of type VARCHAR, and the values are strings

- the second table (B) has a single column of type FLOAT, and the values are NULL

2. BOTH of those tables share the same column name

3. Now, create a return type definition based on table A.

- Flash Builder will do its introspection, and create a datatype of STRING for the result - so far so good.

4. Change your service to point to table B but KEEP THE SAME RETURN TYPE

- When the app runs, the data will by typed as a String, but will have translated my NULLs to ZEROS (as a string)

This is interesting... and entirely unexpected (by me anyway).?If my return type is KNOWN to be of type String (Flash Builder defined it that way, and I agreed), but I mess with it a bit, and point it to a column of type Float - the return type does some translation (in the case of NULL) - it's not just a strict typing.

Now, I know I'm kind of screwing with this thing here - but I wouldn't expect the return type to change like this.?In this scenario I'm trying to force a DB column of Float into a actionscript of type String, without any translation through Number.?Again, I know I'm hacking a bit here - But I would expect this to work.

The upshot is that the defined Return Type doesn't seem strict enough.?It does some translation on the fly (though I have yet to figure out where or why) based on the DB type.

Message was edited by: pajr

And one final (probably) update - since I'm giving up.

There is indeed a translation of typing being done - in my case it's being done in the Zend Framework.

The Flash Builder DataType gets it's cues from Zend's translation (not strictly from the DB).?(See the following for the Zend typing:?http://framework.zend.com/wiki/display/ZFPROP/Zend_Amf+-+Wade+Arnold#Zend_Amf-Wa deArnold-TypeMappingTable)

So, to pass a Null value you need to start in Zend since the value becomes zero before a single line of Actionscript is run (though, it would be an issue once you got to Actionscript as well).?I spent some time digging around in Zend (my first time working with it) and basically gave up.?Zend does a bunch of serialization for server communication which, while it probably helps perfrormance, basically makes it impossible to safely modify (without spending way more time than I care to on what looks to be a pretty bloated bit of infrastructure...).

I'm going to continue to follow this in the hopes that somebody smarter than me figures this out - but at the moment, a (perfectly valid!!!!!!) Null value is extremely difficult to get from a database to a Flex front-end.?Frustrating...

I'm going back to XML and writing my own data layers - it's less efficient, but at least it provides consistently expected results!

I presume you are using the ''click here to generate a sample'' link to generate your service.

When you say in point 4, ''Change your service to point to table B''

How are you doing this ?

Thanks

-Sunil

Sunil,

Yes - to create my service i'm choosing the ''Generate Sample PHP class'' radio button, and generally using defaults across the board (for file locations etc).

To ''change the service to point to table B'' I'm simply going into the PHP service class and changing the query in the getAllItems() function to select from a different table (with the same column name).?(Again, recognizing this is a bit of a hack).

So basically, I'm using one query in the getAllItems() function to generate the DataType/ReturnType (that returns a string), then changing the query (without telling Flash Builder) to return a Float.?In general I get string representations of numbers, which is what I'd expect.?But for NULL, I get ''0''.

Though, as I mentioned in my previous message, I don't think this is a Flex/Fb issue.?I see Zend looking to the data type in the DB and translating the results to an expected Actionscript type.?(in other words, Flash Builder's ReturnType wizard doesn't look at the DB directly, it looks at Zend's translation of the DB).?So in this particular case (I think...) Zend is typing my Float (regardless of what FlashBuilder is expecting) and swapping a Null to a zero.?Flash Builder in turn takes that zero and gives me the String representation: ''0''.

Of course it's entirely possible I'm missing something here?- between all the actionscript and Zend classes, there's a lot of code to make this communcation happen.?I'd love to hear I just overcomplicated this thing....!

Thanks for your reply.

This is not a zend issue, FB is converting it to 0. This is a bug http://bugs.adobe.com/jira/browse/FB-23353 you can track. It can be made as NaN.

However there are some workarounds, You can configure the return type of the operation and instead of number for that column change that to a String or Object, you should get the desired result.

Thanks

-Sunil

Sunil,

Thanks for creating the bug report.

I'm a little surprised this isn't coming from Zend, but as I mentioned, I'm MORE than happy to hear that's the case.?(For what it's worth, I think I see a zero value, instead of null or NaN, in the writeObject() function in the zend file:?%26lt;zendInstall%26gt;/library/Zend/AMF/Parse/Amf3/Serializer.php .?But I'm not very familiar with Zend so...)

Can you help me out with the workaround you mention??When I try to change the return type in the wizard I get a message like ''The information for properties of primitive type returned from an AMF service cannot be edtied''.?Is there another place I can change this? (assuming I should not edit the Super class since it can be regenerated).

I've attached a (big-ish) image of my entire workflow (with a fresh project) demonstrating both the result, and the point above (can't change return type).?I've attached this image to the bug ticket too.

If I can get the return type changed, and can manage to get just about anything besides zero, I'd be very happy...

Thanks again for the help.

If you use test operation, you will see that zend is indeed returning null.

One of the things we are considering is editing the types in future builds. I would suggest you to follow this workaround

1. configure return type invokes your php function to get a sample result, so make sure your query returns only 1 record (that is the record with the null value)

2. You will get that type as Object and then you can change that to any type you want

This is a terse workaround, but this bug should get fixed

Thanks

-Sunil

Thanks again for this.

Unfortunately this workaround doesn't work.?(Looking back at the workflow I attached before) I removed all but one recored from my table.?That record has one field (defined as FLOAT), and it contains NULL.

The query in my PHP service returns that one record.

When I create my Return Type, that field is still typed as a Number (not Object).

The ONLY way I've been able to get that typed as an Object is to change my column definition to VARCHAR.

(A little aside -

But, as I described in an earlier post:

1. type DB column as varchar

2. Fb return type will be typed as object

3. app will show ''null'' string (so far so good)

4. change DB column defintion to float

5. making NO other changes, app will convert null to zero. (unexpected)

Which says to me, that there's an additional translation going on....)

So your suggestion makes sense, and is what I'd expect, but it's not what I'm experiencing.

(I'm using the latest stable build SDK: 4.0.0.6898)

Agreed though, the right solution is to fix the bug..

Thanks again.

Also, another workaround is that

The weak type returned from the php is getting converted to a strong type by a utility function called convertResultHandler as defined in the constructor of the super_service file, in the following piece of code.

_serviceControl.convertResultHandler = TypeUtility.convertResultHandler;

What you could do is that you can give your own function as convertResultHandler in the service file's constructor (not the super) and then in that function, call TypeUtility.convertResultHandler and if the original value is null the convertResultHandler would have changed that to 0, you change it back to NaN.

Hope this helps.

-Sunil

philosophy