Table with multiple underscores fails to return integer values

eanderson

I'm noticing strange behavior with Editor 1.6.1 for PHP. When I set up my PHP script to process the Editor, I only see some values being returned in the table. They happen to be only the varchar fields from the table. All integer and char fields are returning as NULL in the debug console. I have another Datatable displaying integer values just fine. After troubleshooting everything including leftJoins, where clause, and commenting out individual fields one by one, I determined that the issue seemed to lie with the table name.

Here is a bit about my setup: I'm using SQL Server 2012 R2 as the database back-end, PHP 5.4.16 running on Apache 2.4.6. Also using Bootstrap 3.3.7 and jQuery 3.1.0 scripts, but I imagine that doesn't matter much in this case. I just want to provide a complete picture to my environment.

I'll provide my server-side code to give an idea of what works and what doesn't:

This works but integer and char fields return as null values (data is actually not returned from the DB, it's not just missing from the table):

Editor::inst( $db, 'dynamic_sku_config, 'id' )
    ->fields(
        Field::inst( 'dynamic_sku_config.product_family_id' )
            ->options( Options::inst()
                ->table( 'product_family' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'dynamic_sku_config.component_type_id' )
            ->options( Options::inst()
                ->table( 'component_type' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'dynamic_sku_config.ordinal_position' ),
        Field::inst( 'dynamic_sku_config.following_delimiter' ),
        Field::inst( 'dynamic_sku_config.min_components' ),
        Field::inst( 'dynamic_sku_config.max_components' ),
        Field::inst( 'dynamic_sku_config.action' ),
        Field::inst( 'product_family.name' ),
        Field::inst( 'component_type.name' )
    )
    ->leftJoin( 'product_family', 'product_family.id', '=', 'dynamic_sku_config.product_family_id' )
    ->leftJoin( 'component_type', 'component_type.id', '=', 'dynamic_sku_config.component_type_id' )
    ->where( 'dynamic_sku_config.product_family_id', $_POST['selectedProductFamily'], '=' )
    ->process( $_POST )
    ->json();

In the above example, here are the database field types (each table has a field named "id" as an integer primary key):

dynamic_sku_config
product_family_id, int
component_type_id, int
ordinal_position, int
following_delimiter, char(1)
min_components, int
max_components, int
action, varchar(16)

component_type
name, varchar(50)

product_family
name, varchar(50)

Interestingly, since I'm joining to other tables for product_family and component_type, the Datatable will display the name field from the joined tables (product_family_id and component_type_id are not displayed to the user). The action field is also displayed as it is varchar. Every other field is blank due to null values coming back from the server.

I copied the contents of dynamic_sku_config into another table called dskutest. Here is the modified code (identical to above except for the table name):

Editor::inst( $db, 'dskutest', 'id' )
    ->fields(
        Field::inst( 'dskutest.product_family_id' )
            ->options( Options::inst()
                ->table( 'product_family' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'dskutest.component_type_id' )
            ->options( Options::inst()
                ->table( 'component_type' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'dskutest.ordinal_position' ),
        Field::inst( 'dskutest.following_delimiter' ),
        Field::inst( 'dskutest.min_components' ),
        Field::inst( 'dskutest.max_components' ),
        Field::inst( 'dskutest.action' ),
        Field::inst( 'product_family.name' ),
        Field::inst( 'component_type.name' )
    )
    ->leftJoin( 'product_family', 'product_family.id', '=', 'dskutest.product_family_id' )
    ->leftJoin( 'component_type', 'component_type.id', '=', 'dskutest.component_type_id' )
    ->where( 'dskutest.product_family_id', $_POST['selectedProductFamily'], '=' )
    ->process( $_POST )
    ->json();

All fields are now populating in the Datatable with no changes to the front-end page. I should point out that tables with a single underscore in them (e.g., product_family, component_type, etc.) work without issue. I hadn't captured the actual query being sent to the SQL Server but I could probably get it if it would be of interest or provide any clues as to why this might be happening.

I didn't see anyone else with this same issue on the forums, so I thought I would post it myself, either as a workaround for anyone encountering the same issue (or maybe a bug fix for a future release!).

allan

Very interesting! Is it a Windows server you are running this on, or a *nix one?

To see the SQL that Editor is used you can enable the debug mode. That will hopefully lead us in the right direction!

Allan

eanderson

Hi Allan,

Sorry, it was pretty late and I forgot to mention it! This is a Red Hat Enterprise Linux server (release 7.2). The driver I am using to connect to SQL Server is FreeTDS, which uses the dblib PDO driver. I currently have "tds version = 7.4" set in my freetds.conf file, which is recommended for SQL Server 2012 as outlined here. I do recall having to make a small modification to the DSN string in the database-specific Query.php file for SQL Server, as it was originally using the sqlsrv PDO driver and I was having trouble with that driver on RHEL 7.2. The driver could certainly play a role here more than any bug with Datatables -- it hadn't occurred to me until you mentioned it.

I enabled debug mode and was able to get the two SQL statements being generated. Both versions have serverSide: true in the Datatable options, hence the offset. The queries are unmodified other than adding some line breaks for readability.

This one is for the dynamic_sku_config table:

SELECT
[dynamic_sku_config].[id] as 'dynamic_sku_config.id'
, [dynamic_sku_config].[product_family_id] as 'dynamic_sku_config.product_family_id'
, [dynamic_sku_config].[component_type_id] as 'dynamic_sku_config.component_type_id'
, [dynamic_sku_config].[ordinal_position] as 'dynamic_sku_config.ordinal_position'
, [dynamic_sku_config].[following_delimiter] as 'dynamic_sku_config.following_delimiter'
, [dynamic_sku_config].[min_components] as 'dynamic_sku_config.min_components'
, [dynamic_sku_config].[max_components] as 'dynamic_sku_config.max_components'
, [dynamic_sku_config].[action] as 'dynamic_sku_config.action'
, [product_family].[name] as 'product_family.name'
, [component_type].[name] as 'component_type.name'
FROM  [dynamic_sku_config]
LEFT JOIN [product_family] ON [product_family].[id] = [dynamic_sku_config].[product_family_id]  LEFT JOIN [component_type] ON [component_type].[id] = [dynamic_sku_config].[component_type_id]
WHERE [dynamic_sku_config].[product_family_id] = :where_0
ORDER BY [product_family].[name]  asc OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

This one is for the dskutest table:

SELECT
[dskutest].[id] as 'dskutest.id', [dskutest].[product_family_id] as 'dskutest.product_family_id'
, [dskutest].[component_type_id] as 'dskutest.component_type_id'
, [dskutest].[ordinal_position] as 'dskutest.ordinal_position'
, [dskutest].[following_delimiter] as 'dskutest.following_delimiter'
, [dskutest].[min_components] as 'dskutest.min_components'
, [dskutest].[max_components] as 'dskutest.max_components'
, [dskutest].[action] as 'dskutest.action'
, [product_family].[name] as 'product_family.name'
, [component_type].[name] as 'component_type.name'
FROM  [dskutest]
LEFT JOIN [product_family] ON [product_family].[id] = [dskutest].[product_family_id]
LEFT JOIN [component_type] ON [component_type].[id] = [dskutest].[component_type_id]
WHERE [dskutest].[product_family_id] = :where_0
ORDER BY [product_family].[name]  asc  OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

If I take these queries and run them directly against the database within SQL Server Management Studio, both queries return the same data (as they should -- dskutest is a literal copy of dynamic_sku_config).

I didn't test if multiple underscores in a field name only affects that field or gives some other error, but that might provide some clarity on strange behavior with the dblib PDO driver when objects have multiple underscores (i.e., it may not be limited to just table names). At any rate, I'm beginning to think it may not be a bug with Datatables after all, but I haven't done enough troubleshooting to be certain.

eanderson

Some additional findings through testing: It appears the underscore issue may have been a red herring. I noticed even when changing the table name to something with no underscores, some fields were still not being returned. I think it actually has nothing to do with the data type of the field (varchar vs int vs anything else), I think the issue might be the field length, which includes the table name. Here is a quick example of a working field name:

[dskuconfdemo].[delimiter12345678] (length: 34)

The value from field delimiter12345678 is returned in the data and displayed in the Datatable.

Here is a non-working field name:

[dskuconfdemo].[delimiter123456789] (length: 35)

Once I make the change to a field name one character longer, the data for that field is returned as NULL and nothing shows up for that field in the Datatable.

So it seems like maybe there is a limit to the field name length of 34 characters? This is why the table name 'dynamic_sku_config' returned no data for any fields except "action", which was only 29 characters in length combined with the table name. The "product family" and :component type" fields also returned data because they [product_family].[name] and [component_type].[name] are both only 23 characters.

allan

It appears the underscore issue may have been a red herring

I'm actually really rather glad to hear that! I was worried I wasn't going to have a clue at all about it!

I know Oracle has a 32 character limit for fields, I wonder if you are running into something like that here - although Oracle at least has the decency to throw an error stating that this is the case.

Do you have a raw PDO connection that you can use an execute queries against directly, bypassing all of Editor's abstraction stuff. That would at least rule that out.

Allan

eanderson

(1 / 2)

So I built a quick table with various data types and field name lengths and tested using PDO->query(). Here is some detail on the setup (lots of code coming):

SQL Server table structure via sp_help output is attached (Capture.PNG) to give you an idea of the field types.

Here is the first set of PHP code:

$config = parse_ini_file($_SERVER['DOCUMENT_ROOT'] . '/../configs/DynamicSKU.ini');
$dsn = 'dblib:host=' . $config['host'] . ';dbname=' . $config['db'];
$pdo = new PDO($dsn, $config['username'], $config['password']);

$sql = 'SELECT [id],[a],[bb],[ccc],[dddd],[eeeee],[ffffff],[ggggggg],[hhhhhhhh],[iiiiiiiii],[jjjjjjjjjj],[kkkkkkkkkkk],[llllllllllll],[mmmmmmmmmmmmm],[nnnnnnnnnnnnnn],[ooooooooooooooo],[pppppppppppppppp],[qqqqqqqqqqqqqqqqq],[rrrrrrrrrrrrrrrrrr],[sssssssssssssssssss],[tttttttttttttttttttt],[uuuuuuuuuuuuuuuuuuuuu],[vvvvvvvvvvvvvvvvvvvvvv],[wwwwwwwwwwwwwwwwwwwwwww],[xxxxxxxxxxxxxxxxxxxxxxxx],[yyyyyyyyyyyyyyyyyyyyyyyyy],[zzzzzzzzzzzzzzzzzzzzzzzzzz] FROM [DynamicSKU].[dbo].[tmpfieldlengths]';
$query = $pdo->query($sql);
while ($row = $query->fetch()) {
    var_dump($row);
}

Output (I used var_dump so you can see the datatypes returned by the PDO queries -- every field type seems to get converted to a string):

array(54) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["a"]=> string(25) "This is some varchar text" [1]=> string(25) "This is some varchar text" ["bb"]=> string(1) "2" [2]=> string(1) "2" ["ccc"]=> string(1) "1" [3]=> string(1) "1" ["dddd"]=> string(5) "abcde" [4]=> string(5) "abcde" ["eeeee"]=> string(10) "2017-02-19" [5]=> string(10) "2017-02-19" ["ffffff"]=> string(26) "Feb 19 2017 09:46:58:393AM" [6]=> string(26) "Feb 19 2017 09:46:58:393AM" ["ggggggg"]=> string(30) "This is some more varchar text" [7]=> string(30) "This is some more varchar text" ["hhhhhhhh"]=> string(1) "3" [8]=> string(1) "3" ["iiiiiiiii"]=> string(1) "0" [9]=> string(1) "0" ["jjjjjjjjjj"]=> string(10) "ten chars " [10]=> string(10) "ten chars " ["kkkkkkkkkkk"]=> string(10) "2017-02-09" [11]=> string(10) "2017-02-09" ["llllllllllll"]=> string(26) "Feb 18 2017 09:46:58:393AM" [12]=> string(26) "Feb 18 2017 09:46:58:393AM" ["mmmmmmmmmmmmm"]=> string(22) "Even more varchar text" [13]=> string(22) "Even more varchar text" ["nnnnnnnnnnnnnn"]=> string(10) "1000000000" [14]=> string(10) "1000000000" ["ooooooooooooooo"]=> string(2) "22" [15]=> string(2) "22" ["pppppppppppppppp"]=> string(18) "98.599999999999994" [16]=> string(18) "98.599999999999994" ["qqqqqqqqqqqqqqqqq"]=> string(18) "144.77000000000001" [17]=> string(18) "144.77000000000001" ["rrrrrrrrrrrrrrrrrr"]=> string(5) "Small" [18]=> string(5) "Small" ["sssssssssssssssssss"]=> string(2) "34" [19]=> string(2) "34" ["tttttttttttttttttttt"]=> string(10) "ten nchars" [20]=> string(10) "ten nchars" ["uuuuuuuuuuuuuuuuuuuuu"]=> string(18) "157932.25320000001" [21]=> string(18) "157932.25320000001" ["vvvvvvvvvvvvvvvvvvvvvv"]=> string(11) "22 varchars" [22]=> string(11) "22 varchars" ["wwwwwwwwwwwwwwwwwwwwwww"]=> string(1) "5" [23]=> string(1) "5" ["xxxxxxxxxxxxxxxxxxxxxxxx"]=> string(24) "twenty four characters " [24]=> string(24) "twenty four characters " ["yyyyyyyyyyyyyyyyyyyyyyyyy"]=> string(8) "29384632" [25]=> string(8) "29384632" ["zzzzzzzzzzzzzzzzzzzzzzzzzz"]=> string(20) "This is a text field" [26]=> string(20) "This is a text field" }

When I added the table name to each field in the SELECT statement, I get the same output (PDO object declaration was unchanged, so omitted for brevity):

$sql = 'SELECT [tmpfieldlengths].[id],[tmpfieldlengths].[a],[tmpfieldlengths].[bb],[tmpfieldlengths].[ccc],[tmpfieldlengths].[dddd],[tmpfieldlengths].[eeeee],[tmpfieldlengths].[ffffff],[tmpfieldlengths].[ggggggg],[tmpfieldlengths].[hhhhhhhh],[tmpfieldlengths].[iiiiiiiii],[tmpfieldlengths].[jjjjjjjjjj],[tmpfieldlengths].[kkkkkkkkkkk],[tmpfieldlengths].[llllllllllll],[tmpfieldlengths].[mmmmmmmmmmmmm],[tmpfieldlengths].[nnnnnnnnnnnnnn],[tmpfieldlengths].[ooooooooooooooo],[tmpfieldlengths].[pppppppppppppppp],[tmpfieldlengths].[qqqqqqqqqqqqqqqqq],[tmpfieldlengths].[rrrrrrrrrrrrrrrrrr],[tmpfieldlengths].[sssssssssssssssssss],[tmpfieldlengths].[tttttttttttttttttttt],[tmpfieldlengths].[uuuuuuuuuuuuuuuuuuuuu],[tmpfieldlengths].[vvvvvvvvvvvvvvvvvvvvvv],[tmpfieldlengths].[wwwwwwwwwwwwwwwwwwwwwww],[tmpfieldlengths].[xxxxxxxxxxxxxxxxxxxxxxxx],[tmpfieldlengths].[yyyyyyyyyyyyyyyyyyyyyyyyy],[tmpfieldlengths].[zzzzzzzzzzzzzzzzzzzzzzzzzz] FROM [DynamicSKU].[dbo].[tmpfieldlengths]';
$query = $pdo->query($sql);
while ($row = $query->fetch()) {
    var_dump($row);
}

Output:

array(54) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["a"]=> string(25) "This is some varchar text" [1]=> string(25) "This is some varchar text" ["bb"]=> string(1) "2" [2]=> string(1) "2" ["ccc"]=> string(1) "1" [3]=> string(1) "1" ["dddd"]=> string(5) "abcde" [4]=> string(5) "abcde" ["eeeee"]=> string(10) "2017-02-19" [5]=> string(10) "2017-02-19" ["ffffff"]=> string(26) "Feb 19 2017 09:46:58:393AM" [6]=> string(26) "Feb 19 2017 09:46:58:393AM" ["ggggggg"]=> string(30) "This is some more varchar text" [7]=> string(30) "This is some more varchar text" ["hhhhhhhh"]=> string(1) "3" [8]=> string(1) "3" ["iiiiiiiii"]=> string(1) "0" [9]=> string(1) "0" ["jjjjjjjjjj"]=> string(10) "ten chars " [10]=> string(10) "ten chars " ["kkkkkkkkkkk"]=> string(10) "2017-02-09" [11]=> string(10) "2017-02-09" ["llllllllllll"]=> string(26) "Feb 18 2017 09:46:58:393AM" [12]=> string(26) "Feb 18 2017 09:46:58:393AM" ["mmmmmmmmmmmmm"]=> string(22) "Even more varchar text" [13]=> string(22) "Even more varchar text" ["nnnnnnnnnnnnnn"]=> string(10) "1000000000" [14]=> string(10) "1000000000" ["ooooooooooooooo"]=> string(2) "22" [15]=> string(2) "22" ["pppppppppppppppp"]=> string(18) "98.599999999999994" [16]=> string(18) "98.599999999999994" ["qqqqqqqqqqqqqqqqq"]=> string(18) "144.77000000000001" [17]=> string(18) "144.77000000000001" ["rrrrrrrrrrrrrrrrrr"]=> string(5) "Small" [18]=> string(5) "Small" ["sssssssssssssssssss"]=> string(2) "34" [19]=> string(2) "34" ["tttttttttttttttttttt"]=> string(10) "ten nchars" [20]=> string(10) "ten nchars" ["uuuuuuuuuuuuuuuuuuuuu"]=> string(18) "157932.25320000001" [21]=> string(18) "157932.25320000001" ["vvvvvvvvvvvvvvvvvvvvvv"]=> string(11) "22 varchars" [22]=> string(11) "22 varchars" ["wwwwwwwwwwwwwwwwwwwwwww"]=> string(1) "5" [23]=> string(1) "5" ["xxxxxxxxxxxxxxxxxxxxxxxx"]=> string(24) "twenty four characters " [24]=> string(24) "twenty four characters " ["yyyyyyyyyyyyyyyyyyyyyyyyy"]=> string(8) "29384632" [25]=> string(8) "29384632" ["zzzzzzzzzzzzzzzzzzzzzzzzzz"]=> string(20) "This is a text field" [26]=> string(20) "This is a text field" }

The length of table name + field name varies from 21 for "[tmpfieldlengths].[a]" to 46 for "[tmpfieldlengths].[zzzzzzzzzzzzzzzzzzzzzzzzzz]". Each field returns data as shown in the output above.

Even if I switch to using PDO prepared statements, the result is the same:

$sql = 'SELECT [tmpfieldlengths].[id],[tmpfieldlengths].[a],[tmpfieldlengths].[bb],[tmpfieldlengths].[ccc],[tmpfieldlengths].[dddd],[tmpfieldlengths].[eeeee],[tmpfieldlengths].[ffffff],[tmpfieldlengths].[ggggggg],[tmpfieldlengths].[hhhhhhhh],[tmpfieldlengths].[iiiiiiiii],[tmpfieldlengths].[jjjjjjjjjj],[tmpfieldlengths].[kkkkkkkkkkk],[tmpfieldlengths].[llllllllllll],[tmpfieldlengths].[mmmmmmmmmmmmm],[tmpfieldlengths].[nnnnnnnnnnnnnn],[tmpfieldlengths].[ooooooooooooooo],[tmpfieldlengths].[pppppppppppppppp],[tmpfieldlengths].[qqqqqqqqqqqqqqqqq],[tmpfieldlengths].[rrrrrrrrrrrrrrrrrr],[tmpfieldlengths].[sssssssssssssssssss],[tmpfieldlengths].[tttttttttttttttttttt],[tmpfieldlengths].[uuuuuuuuuuuuuuuuuuuuu],[tmpfieldlengths].[vvvvvvvvvvvvvvvvvvvvvv],[tmpfieldlengths].[wwwwwwwwwwwwwwwwwwwwwww],[tmpfieldlengths].[xxxxxxxxxxxxxxxxxxxxxxxx],[tmpfieldlengths].[yyyyyyyyyyyyyyyyyyyyyyyyy],[tmpfieldlengths].[zzzzzzzzzzzzzzzzzzzzzzzzzz] FROM [DynamicSKU].[dbo].[tmpfieldlengths]';
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch()) {
    var_dump($row);
}

eanderson

(2 / 2)

Output:

array(54) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["a"]=> string(25) "This is some varchar text" [1]=> string(25) "This is some varchar text" ["bb"]=> string(1) "2" [2]=> string(1) "2" ["ccc"]=> string(1) "1" [3]=> string(1) "1" ["dddd"]=> string(5) "abcde" [4]=> string(5) "abcde" ["eeeee"]=> string(10) "2017-02-19" [5]=> string(10) "2017-02-19" ["ffffff"]=> string(26) "Feb 19 2017 09:46:58:393AM" [6]=> string(26) "Feb 19 2017 09:46:58:393AM" ["ggggggg"]=> string(30) "This is some more varchar text" [7]=> string(30) "This is some more varchar text" ["hhhhhhhh"]=> string(1) "3" [8]=> string(1) "3" ["iiiiiiiii"]=> string(1) "0" [9]=> string(1) "0" ["jjjjjjjjjj"]=> string(10) "ten chars " [10]=> string(10) "ten chars " ["kkkkkkkkkkk"]=> string(10) "2017-02-09" [11]=> string(10) "2017-02-09" ["llllllllllll"]=> string(26) "Feb 18 2017 09:46:58:393AM" [12]=> string(26) "Feb 18 2017 09:46:58:393AM" ["mmmmmmmmmmmmm"]=> string(22) "Even more varchar text" [13]=> string(22) "Even more varchar text" ["nnnnnnnnnnnnnn"]=> string(10) "1000000000" [14]=> string(10) "1000000000" ["ooooooooooooooo"]=> string(2) "22" [15]=> string(2) "22" ["pppppppppppppppp"]=> string(18) "98.599999999999994" [16]=> string(18) "98.599999999999994" ["qqqqqqqqqqqqqqqqq"]=> string(18) "144.77000000000001" [17]=> string(18) "144.77000000000001" ["rrrrrrrrrrrrrrrrrr"]=> string(5) "Small" [18]=> string(5) "Small" ["sssssssssssssssssss"]=> string(2) "34" [19]=> string(2) "34" ["tttttttttttttttttttt"]=> string(10) "ten nchars" [20]=> string(10) "ten nchars" ["uuuuuuuuuuuuuuuuuuuuu"]=> string(18) "157932.25320000001" [21]=> string(18) "157932.25320000001" ["vvvvvvvvvvvvvvvvvvvvvv"]=> string(11) "22 varchars" [22]=> string(11) "22 varchars" ["wwwwwwwwwwwwwwwwwwwwwww"]=> string(1) "5" [23]=> string(1) "5" ["xxxxxxxxxxxxxxxxxxxxxxxx"]=> string(24) "twenty four characters " [24]=> string(24) "twenty four characters " ["yyyyyyyyyyyyyyyyyyyyyyyyy"]=> string(8) "29384632" [25]=> string(8) "29384632" ["zzzzzzzzzzzzzzzzzzzzzzzzzz"]=> string(20) "This is a text field" [26]=> string(20) "This is a text field" }

The length of the data in the field was never an issue, since I couldn't even return integers or single character values when the table + field name length was over 34. One thing I haven't tried is seeing if Datatables runs into the same issue when connecting to another DB type (such as MySQL). I use Datatables for some other projects which leverage MySQL but I haven't been able to confirm yet if the same behavior occurs.

allan

So the one thing that Editor does differently from the above is that it will use as to make sure that the database returns column names exactly as Editor expects. For example it might use:

$sql = 'SELECT [tmpfieldlengths].[id] as "tmplfieldlengths.id" ...';

Might you be able to test that with one of your longer fields? I'm wondering if that, combined with a long field name might be pushing it over a 64 or 128 or something limit.

Thanks!
Allan

eanderson

Unfortunately, no difference, although I am noticing something strange:

$sql = 'SELECT 1, [tmpfieldlengths].[zzzzzzzzzzzzzzzzzzzzzzzzzz] AS "tmpfieldlengths.zzzzzzzzzzzzzzzzzzzzzzzzzz" FROM [DynamicSKU].[dbo].[tmpfieldlengths]';
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch()) {
    var_dump($row);
}

tmpfieldlengths.zzzzzzzzzzzzzzzzzzzzzzzzzz has a length of 42 characters, exceeding the length when I first observed this issue. The output remains unchanged though:

array(4) { [""]=> string(1) "1" [0]=> string(1) "1" ["tmpfieldlengths.zzzzzzzzzzzzzz"]=> string(20) "This is a text field" [1]=> string(20) "This is a text field" }

There is no difference whether I use square brackets or not in the alias either:

array(4) { [""]=> string(1) "1" [0]=> string(1) "1" ["[tmpfieldlengths].[zzzzzzzzzzz"]=> string(20) "This is a text field" [1]=> string(20) "This is a text field" }

Notice in both the outputs though, the field name is getting truncated to 30 characters.

Just for fun, I tested a bit more. This query fails:

$sql = 'SELECT 1, [tmpfieldlengths].[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz123456789] AS "tmpfieldlengths.zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz123456789" FROM [DynamicSKU].[dbo].[tmpfieldlengths]';

tmpfieldlengths.zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz123456789 length is 129 characters.

This works:

$sql = 'SELECT 1, [tmpfieldlengths].[zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz123456789] AS "tmpfieldlengths.zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz123456789" FROM [DynamicSKU].[dbo].[tmpfieldlengths]';

tmpfieldlengths.zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz123456789 length is 65 characters.

It also works with table + field name up to and including 128 characters in length. But I wonder if anything over 30 (+4 characters for the brackets that might get added back in DT code?) is failing because the field name would not be valid? For example, the field ["[tmpfieldlengths].[zzzzzzzzzzz"] is not even valid syntax because the field name is missing its closing bracket due to truncation.

Without digging into the DT source code too much (because you obviously have a better handle on it than I do!), I did notice this line in the Sqlserver\Query.php file:

protected $_identifier_limiter = array( '[', ']' );

I assume this comes into play in the Query class somehow, but it does seem to make sense that the dblib PDO driver is limiting to 30 characters, plus the 4 for opening and closing brackets on the table name and field name, which would explain why anything over 34 characters was returning NULL values (presumably because the truncated field name doesn't exist). I don't know if the pdo_sqlsrv driver would return different results, but I don't have a PHP for Windows environment (I could set one up to test, might take a day or two) and per this link, pdo_dblib is the way to go on Linux.

eanderson

Worthy of note:

https://bugs.php.net/bug.php?id=33060

Although this is an old bug related to SQL Server 2000, it looks like the FreeTDS driver may still be limiting the fetched column name length due to a limitation from older versions of SQL Server. Changing the tds version in my freetds.conf file did not make a difference (I use version 7.4 for SQL Server 2012, but changing to 8.0 made no difference).

allan

This is excellent debugging - thank you for spending the time doing this. I agree, it does very much look like an issue with the dblib PDO driver.

If the name of the column being returned is not identical to the as statement used by Editor it will not be able to read those values (since it simply does an associative array lookup). That would explain the null values as you say.

If you happen to have E_ALL as your warning level, it should really be showing information about array names that are being looked up and accessed, but are not available due to the truncation.

The _identifier_limiter parameter in the Editor code is possibly poorly named! It isn't anything to do with limiting the number of characters, but rather quoting the field names. For example in SQL Server you can have columns with a space in their name, but to be able to query them you need to use the brackets: [My Column]. In MySQL they use a back tick, which is why this is an overridable parameter in the libraries - to allow it to be configured for each database type supported.

Allan

eanderson

Ahh yes, I think I didn't explain myself well enough. What I meant to say is that I now understand why the fields were getting cut off after 34 characters -- 30 characters for the retrieved two part field name and then an additional 4 characters for the opening and closing brackets. I thought it initially odd that the limit was 34 characters, as it seemed arbitrary to me. After researching a bit, I now know that the 30 character limit was originally a SQL Server 2000 limitation. I think maybe I need to check with FreeTDS to see about updating their driver!

Thanks for following up with me on all this and helping get to the bottom of the issue. I guess the answer to all this would be to use concise table/field names when using connecting to SQL Server from Linux until FreeTDS updates their driver to allow longer field names (ideally, it would sync with the max table + field length where data is still returned, which seems to be 128 characters).

Thanks again and thanks also for making such a great product! Keep it up!

allan

Got it! I've just had a little look through the PHP source (prompted by the bug you linked to above) and there isn't anything obvious there that would cause the truncation (although its been a while since I've actually done any C coding!). So yes, I concur - the problem is upstream.

Concise is the way forward at the moment .

Allan