PHP, SQL Server and fields using geography functions not working in 1.7.4

csdatum

Since moving from 1.6.x to 1.7.4 we have been unable to fix an issue with using a geography data type in SQL Server. The field is defined as the following:

$field = Field::inst('geography.STAsText()', 'geography');
$ed->fields($field);

In 1.6.5 the portion of the SQL query for selecting this field was editorTestTable.geography.STAsText() as 'editorTestTable.geography.STAsText()' but after updating to 1.7.4 we are unable to find the right combination of $name and $dbField in the field constructor to create this same query and display the results of the function. In 1.7.4 this portion of the query is simply showing as editorTestTable.geography.STAsText() and does not include a column name.

Are there any recommendations for the correct configuration to return our geography column data?

---

P.S. I was able to get this to work in a simplified example on 1.7.3 but not any other version of 1.7.x.

allan

Hi,

You'll need to forgive me I'm afraid, but I'm not familiar with that SQL function, so I'm not clear on what it should look like. You say it doesn't include a column name - should a parameter be getting passed into it? Is geography the schema and STAsText() a function in it? Or is that some built in function that is chained onto a column?

Thanks,
Allan

csdatum

It is the latter case. We are using the geography spaital data type in a column named geography and have created a field type plugin for handling the data client side. When selected, the geography data type is returned as a binary value. We make use of the data type's STAsText method to return the WKT representation as varchar to work with in our field type plugin. The method does not have any parameters and just acts on the geography column. I apologize for the lack of background information in the original post.

allan

Hi,

Sorry I wasn't able to get back to you about this yesterday. In the Database/Query.php file you'll find a _build_field private method. Could you replace it with:

    protected function _build_field( $addAlias=false )
    {
        $a = array();
        $asAlias = $this->_supportsAsAlias ?
            ' as ' :
            ' ';

        for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
            $field = $this->_field[$i];

            // Keep the name when referring to a table
            if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {
                $split = preg_split( '/ as (?![^\(]*\))/i', $field );

                if ( count($split) > 1 ) {
                    $a[] = $this->_protect_identifiers( $split[0] ).$asAlias.
                        $this->_field_quote. $split[1] .$this->_field_quote;
                }
                else {
                    $a[] = $this->_protect_identifiers( $field ).$asAlias.
                        $this->_field_quote. $field .$this->_field_quote;
                }
            }
            else if ( $addAlias && strpos($field, '(') !== false ) {
                $a[] = $this->_protect_identifiers( $field ).$asAlias.
                    $this->_field_quote. $field .$this->_field_quote;
            }
            else {
                $a[] = $this->_protect_identifiers( $field );
            }
        }

        return ' '.implode(', ', $a).' ';
    }

That will resolve the issue.

Thanks,
Allan

csdatum

I have tried to replace the method as you recommended, but this has caused an error with the initial count query from the _ssp_query method. Here is the response I'm receiving now.

Please advise if I've applied this incorrectly. Thanks!

allan

I didn't check this with server-side processing actually. Let me check into this and get back to you!

Allan

allan

Small change - give this one a whirl:

    protected function _build_field( $addAlias=false )
    {
        $a = array();
        $asAlias = $this->_supportsAsAlias ?
            ' as ' :
            ' ';

        for ( $i=0 ; $i<count($this->_field) ; $i++ ) {
            $field = $this->_field[$i];

            // Keep the name when referring to a table
            if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {
                $split = preg_split( '/ as (?![^\(]*\))/i', $field );

                if ( count($split) > 1 ) {
                    $a[] = $this->_protect_identifiers( $split[0] ).$asAlias.
                        $this->_field_quote. $split[1] .$this->_field_quote;
                }
                else {
                    $a[] = $this->_protect_identifiers( $field ).$asAlias.
                        $this->_field_quote. $field .$this->_field_quote;
                }
            }
            else if ( $addAlias && strpos($field, '(') !== false && ! strpos($field, ' as ') ) {
                $a[] = $this->_protect_identifiers( $field ).$asAlias.
                    $this->_field_quote. $field .$this->_field_quote;
            }
            else {
                $a[] = $this->_protect_identifiers( $field );
            }
        }

        return ' '.implode(', ', $a).' ';
    }

Allan

csdatum

Thanks, that did the trick! Will this fix be incorporated into an upcoming release as well?

allan

Yes indeed. We'll give it a good testing and it should be included. v1.8 is likely to be the next version of Editor at this time.

Allan