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

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

csdatumcsdatum Posts: 30Questions: 10Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0
    edited August 2018

    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!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

This discussion has been closed.