Issue Deleting records from SQL Server 2008 table using Data Tables Editor/php

AmericanTextile

Hello.

My company recently purchased DTE and set up a fairly complex example (in MySQL) using joins and other complex features. This worked without error. We then tried making a much simpler DTE connected to a MSSQL table with 8 columns plus an ID, I was able to get everything working properly with reading, inserting, and updating, but for some reason deleting is not working. The record gets removed from the front end, but not from the actual MSSQL table. I thought it could be configuraton-related as I have about 20 validators and a custom insert/edit form template, as well as ajax returning form options for dropdowns, so I created an even simpler example that's essentially your first example (Basic initialisation) from the site. Again, reading, inserting, and updating work fine, and again, deleting doesn't work.

I tried tracing the execution of a delete with my debugger, and it appears to run through validation checks (which seems odd for a delete) but never hits the protected function _exec() inside php/Database/Sqlserver/Query.php It also never hits the public function exec() in that same file. I'll keep trying to debug and would be happy to share whatever information might be useful, but I don't get how everything else works properly (ruling out a lot of things with configuration) but DELETE does not.

Any help would be appreciated.

allan

Hi,

Thanks for your post! Could you send me the code that you are using to configure Editor on the server-side?

Also if you could add .Debug(true) immediately before the .Process(...) method, that will enable its debugging, which will show the SQL it is trying to execute in the returned JSON (i.e. for the delete request).

Could you show me what that JSON is?

Thanks,
Allan

AmericanTextile

query.php:

// DataTables PHP library
include( "data-tables-editor/php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'atchq_system.445_calendar', 'id' )
    ->debug(true)
    ->fields(
        Field::inst( 'atchq_system.445_calendar.id' ),
        Field::inst( 'atchq_system.445_calendar.445_month' )
    )
    ->process( $_POST )
    ->json();

config.php:

<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Database user / pass
 */
$sql_details = array(
    "type" => "Sqlserver",   // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "username",        // Database user name
    "pass" => "password",        // Database password
    "host" => "Servername",        // Database host
    "port" => "",        // Database connection port (can be left empty for default)
    "db"   => "DBNAME",        // Database name
    "dsn"  => "",        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
    "pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options
);

Turned on debug, it returns a query for any other operation (select, update, insert) but when I perform a delete, it returns this:
{"data":[],"debug":[]}

allan

I'm reasonably certain that this is going to be related to the use of the schema name. Could you try dropping the schema name from the fields:

Editor::inst( $db, 'atchq_system.445_calendar', 'id' )
    ->debug(true)
    ->fields(
        Field::inst( 'id' ),
        Field::inst( '445_month' )
    )
    ->process( $_POST )
    ->json();

Thanks,
Allan

edit The table name isn't required either since there are no joins.

AmericanTextile

Excellent! Thank you, that seemed to be causing the issue. Had to remove the schema/table name from a few different places (custom form, JS Editor function, and PHP query) and everything started working as intended.

Thanks for your quick reply!

allan

Great to hear that did the job!

Support for schema's other than dbo in Editor is a little ropy at the moment. That is something that we know needs to be improved!

Allan