Editor Table Names with spaces

kffoods

I have successfully installed the samples. Very nice documentation. Excellent job on making it easy to get up and running quickly.

As I move to start hooking up to our database I was halted. I started with the online generator and got and error message about the table name. Hoping it was just a generator limitation I attempted to alter the samples. My project requires me to connect to an ERP table. I have control over the table layout but not the table name. You see the table name in our ERP system also has a company name prefixed. So a sample SQL table name would be [Kitchen Fresh Foods$Web Users]. It appears in the generator that no special characters or spaces are allowed. I did determine that the special character "$" is acceptable my adjusting the samples. I changed the sample table name 'datatables_demo' to 'datatables$demo' with success. but when I change to 'data tables$demo' I get the error message:

DataTables warning: table id=example - SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'data'.

Is my research valid? If so what are my options?

allan

Could you enable the debug option in your Editor server-side libraries (->debug( true ) in PHP, .Debug(true) in .NET, just before the process method call)?

That will show the SQL statement that the libraries are generating in the JSON returned from the server. What is that statement?

Allan

kffoods

The table Editor::inst( $db, "data tables$demo" )

Error and Debug info:

Notice: Undefined variable: demo in C:\inetpub\wwwroot\Editor\examples\php\staff.php on line 22
{"fieldErrors":[],"error":"SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'data'.","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT [id] as 'id', [first_name] as 'first_name', [last_name] as 'last_name', [position] as 'position', [email] as 'email', [office] as 'office', [extn] as 'extn', [age] as 'age', [salary] as 'salary', [start_date] as 'start_date' FROM data tables ","bindings":[]}]}

kffoods

oops. forgot to rollback a different alteration.

Editor::inst( $db, 'data tables$demo' )

Here is the sql statement
SELECT [id] as 'id', [first_name] as 'first_name', [last_name] as 'last_name', [position] as 'position', [email] as 'email', [office] as 'office', [extn] as 'extn', [age] as 'age', [salary] as 'salary', [start_date] as 'start_date' FROM data tables$demo "

Looks like just need to add the brackets around the table name

I tried with sucess.

Editor::inst( $db, '[data tables$demo]' )

Isn't this something that could be aromatically added just like for the fields names?

allan

I'll take a look into that and let you know!

Allan

allan

In the file php/Database/Query.php you'll find a function called _build_table. Could you replace it with:

    protected function _build_table()
    {
        $tables = array();

        for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) {
            $tables[] = $this->_protect_identifiers( $this->_table[$i] );
        }

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

That should protect the table name correctly.

Allan

kffoods

That didn't do it...

allan

Sorry - undo my previous suggestion. I've just been working on it locally and that actually ends up doing a double quote.

The issue is in fact on the line (same file):

if (strpos($identifier, '(') !== FALSE || strpos($identifier, '*') !== FALSE || strpos($identifier, ' ') !== FALSE)

remove that last condition - i.e. so it reads as:

if (strpos($identifier, '(') !== FALSE || strpos($identifier, '*') !== FALSE)

That will allow it to work for your use case (field names as well).

The trouble with this is that Oracle uses the space to indicate an alias (it doesn't allow "as" aliases). So simply removing that condition in the main line Editor libraries isn't an option at the moment. I need to have a think about how to resolve these two conflicting options! For the moment, that should get you moving I think.

Allan

kffoods

ok. i'll Try this.

BTW, If this idea helps, I don't think it's unreasonable to allows spaces in field names at the DB level but the returned result set would have them replaced with underscores. I have seen this done before.

allan

Spaces in the returned data isn't the problem. The issue is in how to parse it. For example First Name could be the same as First as 'Name' or it could be the column by that name. At the moment there is no way in the Editor libraries to distinguish between which was intended, since it attempts to do the escaping for you.

One option would be to allow you to provide the escape parameters, but I think that might get messy for more complex expressions and potentially confuse some who aren't as well versed in SQL.

Another option would be a parameter which tells Editor "yes I know what I'm doing - don't escape this", or "do escape this as is".

Allan

dslob

Hey Allan, thanks for the incredible libraries and support!

Have you found a long term solution to this problem? I'm using the .NET libraries and encountering the same issue for table names and field names with spaces.

allan

Hi,

I'm sorry no, we've not made any progress on this as we've found the majority of db columns don't have spaces in them. I fear that this is an area where our provided libraries aren't going to be able to be used.

Allan

dslob

Hey Allan, thanks for the quick response! I totally understand.

I was looking at the source for DataTables-Editor-Server, found here:
https://github.com/DataTables/Editor-NET

Would this be the line I need to modify to try your suggestion above?
https://github.com/DataTables/Editor-NET/blob/master/DataTables-Editor-Server/Query.cs#L1032

I should also mention that my databases are in MySQL and contain both table names and column names with spaces.

allan

Yes - remove the final condition on that line and it should work from there. As long as you aren't using spaces for anything else (arguments, as etc), which is why its there.

It should automatically quote the full field, including the spaces once it gets past that line.

Allan