SQL View only shows one row

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

SQL View only shows one row

Postby Adrian Jones » Tue Jun 04, 2013 4:02 pm

I am getting very confused with how Ff treats blanks vs nulls.

I have a mysql database (let's call it Mike), which in turn looks at another mysql database (let's call it Ron).

Mike has a view that looks up a table in Ron.

Mike has several other views based on this initial view that contain a subset of columns.

With all views, I can see all rows, and update rows.

With the same views turned into Ff remote forms, I can see ALL rows from the 'base' view.

However, if a field included in one of the restricted views contains an empty string (not null, but ''), Ff 1.62 returns only the rows up to by not including that row with this value in this field.

If I reset the value in all rows to null, all the rows now show in Ff.

I can't use the base view because it contains data types that Ff does not handle properly, which stops me from updating a row.

I can update any visible 'column-restricted' row, though.

Why?

I've had issues with nulls vs empty strings before as well, but this seems to be a different variation on a theme.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: SQL View only shows one row

Postby Adrian Jones » Wed Jun 05, 2013 7:32 am

I think I can summarise that original post to read: if a row returned by this view has a column that contains an empty string (as opposed to null), that row and all the later ones are not displayed in Ff.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: SQL View only shows one row

Postby Pete Tabord » Wed Jun 05, 2013 2:50 pm

We don't understand empty strings on a SQL server. They are supposed to be null.

OK, I understand that some SQL servers now support both, but FF, as yet, doesn't. It's not going to be easy to do, because we record 'NULL' as an -er- empty string. (in a text field at least). After some 30 years of trying to get 'BLANK' to behave consistently (it still doesn't, 100%) its likely that introducing code to cope with server end empty string will be, to say the least, destabilising.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: SQL View only shows one row

Postby Adrian Jones » Wed Jun 05, 2013 3:37 pm

So is it not possible for Ff to treat an empty string as though it were null?

Or do you think what I describe is a good situation?
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: SQL View only shows one row

Postby Pete Tabord » Wed Jun 05, 2013 4:27 pm

At the moment it doesn't, if we are talking an empty string on the server.

If it was to do so, then it would not know, for a derivation for example, how to distinguish between the traditional DQL BLANK = SQL NULL and the new DQL BLANK = SQL empty string. They would both be treated the same, and as far as I can understand from discussion with the MySQL chaps, that is not the case. They are different kinds of 'no data here'.

This is why Chris Date told the world not to have NULL's!
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: SQL View only shows one row

Postby Pete Tabord » Thu Jun 06, 2013 9:43 am

Adrian, you can maybe help me with some info.

I think I've come up with a possible implementation to get round the difficulty, but the implementation would depend on certain things being true. This ability to have a null as well as an empty string - I assume

a) If the field is flagged as NULL on the server, its contents are assumed to be lost/non-existent (by which I mean, a NULL field cannot have its contents examined, even if there was a value there before it was set to NULL). Or, to put it another way, no field can validly be NULL and contain an empty string.

b) Only string type fields (including numeric strings) can have an 'empty string', server numeric data types must contain a valid number - zero being a valid number - or be null.

If either of these is untrue my solution won't work.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: SQL View only shows one row

Postby Adrian Jones » Thu Jun 06, 2013 2:20 pm

Hi Pete,

I'll try to answer those, but I can only speak for myself and the situation I find myself in, and not on behalf of the 'mySQL' (other other SQL) community.

a) yes, no field can be both null and contain an empty string.

b) this app is mainly about text values, and other than some issues with dates (not related to nulls) I think that all other data types used have not caused a problem.

But I can't see how other types -- at least those that FF can attempt to use -- could contain a string value. So what you say must be true.

While we're discussing this, there is still an issue with writing back a blank value where there was previously something. Ff GPFs before any SQL seems to get issued (my check on that is whether there is anything in the ODBC log file).

And there is the issue of updating where:

i) it is not possible to do anything other than a full where clause for all the previous values of the record (rather than just the PKs);
ii) as a result, FF is asking for (I think) that the value is null, rather than empty string.

So a row comes in with ColA with the empty string value. The 'where' statement generated asks for 'where ColA is null', which does not get found, so the row does not get updated.

I mention these just in case they in some way impact on your suggested solution.

Regards,
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: SQL View only shows one row

Postby Pete Tabord » Fri Jun 07, 2013 9:00 am

Thanks for that info, Adrian. I hope the solution will fix those issues as well, it certainly should fix the second one, in that we will know we originally received an empty string, so can put a "" in the select rather than a NULL.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: SQL View only shows one row

Postby Graham Smith » Mon Jun 10, 2013 3:27 pm

Is it possible to use something like my suggestion for numbers (0 + Field) here? Something like concat("",Field) should produce the equal of a blank field as opposed to null.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: SQL View only shows one row

Postby Adrian Jones » Mon Jun 10, 2013 3:52 pm

Hi Graham,

Perhaps, if I didn't need to write back to this column.

Although the function is nullif(ThisField, [valueifnull]).

Otherwise concatenating something to a null value still returns null...
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: SQL View only shows one row

Postby Graham Smith » Tue Jun 11, 2013 12:27 pm

Adrian Jones wrote:Otherwise concatenating something to a null value still returns null...

I know that's the case on the SQL side but I was thinking of the Ff side and rather thought it might work. Haven't tried it of course...
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: SQL View only shows one row

Postby Adrian Jones » Tue Jun 11, 2013 2:20 pm

Not sure that will help here.

A previous workaround might have to be applied here. Having issues with the col containing empty string (could not update as the SQl generated by Ff looked for a null value), I contrived to update the row in SQL before passing it over to Ff so that any empty string values were changed to null.

This is assuming that the empty string is probably being entered by a web interface somewhere, and is, to all useable purposes, the same as null (e.g. no email address was entered by this interface).

But I doubt there are many systems where this tweaking of data would be permitted by those in charge of the DB!
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest

cron