Cannot get data in my GRID

[Dennis]

Dear all,

I have been trying for a cuple of days to get data from the database into the KendoUI GRID. For some reason the data doesn't show, so I ran a test and as far as I can see it is working as it is supposed to.

My test.php file looks like this:

<?php
    $sRootDir = "../..";

    require_once ($sRootDir.'/lib/Kendo/DataSourceResult.php');
    require_once ($sRootDir.'/lib/Kendo/Autoload.php');
    require_once ($sRootDir."/include/includeDomain.php");
    require_once ($sRootDir."/include/includeLib.php");


    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        header('Content-Type: application/json');
        $sType = $_GET["type"];

        $sRequest = json_decode(file_get_contents('php://input'));

//Password data removed
        $pResult = new DataSourceResult(**dns**, **user**, **password**);

        $aColumns = array('CLIENTEMEA', 'CLIENTNAME', 'CLIENTKVK');

        switch($sType) {
            case 'create':
                $sResult = $pResult->create('RULES_CLIENTS', $aColumns, $sRequest->models, 'CLIENTEMEA');
                break;
            case 'read':
                $sResult = $pResult->read('RULES_CLIENTS', $aColumns, $sRequest);
                break;
            case 'update':
                $sResult = $pResult->update('RULES_CLIENTS', $aColumns, $sRequest->models, 'CLIENTEMEA');
                break;
            case 'destroy':
                $sResult = $pResult->destroy('RULES_CLIENTS', $sRequest->models, 'CLIENTEMEA');
                break;
        }

        echo json_encode($sResult, JSON_NUMERIC_CHECK);

        $FileName = "testFile.txt";
        $FileHandle = fopen($FileName, 'w') or die("can't open file");
        fwrite($FileHandle, json_encode($sResult, JSON_NUMERIC_CHECK));
        fclose($FileHandle);

        exit;
    }

    require_once($sRootDir."/html/tpbilling/client/index2.html");
?>

And my index2 file looks like this:

<div id="grid"></div>

<script>jQuery(function(){
jQuery("#grid").kendoGrid(
{"columns":[
{"field":"CLIENTEMEA","width":100,"title":"Client EMEA"},
{"field":"CLIENTNAME","width":400,"title":"Client Name"},
{"field":"CLIENTKVK","width":100,"title":"KVK Number"}],
"dataSource":
{"transport":{
"create":{"url":"../../tpbilling/client/test.php?type=create","contentType":"application\/json","type":"POST"},
"read":{"url":"../../tpbilling/client/test.php?type=read","contentType":"application\/json","type":"POST"},
"update":{"url":"../../tpbilling/client/test.php?type=update","contentType":"application\/json","type":"POST"},
"destroy":{"url":"../../tpbilling/client/test.php?type=destroy","contentType":"application\/json","type":"POST"},
"parameterMap":function(data) {
return kendo.stringify(data);
}},"batch":true,"pageSize":20,
"schema":{
"data":"data","errors":"errors","model":{
"id":"CLIENTEMEA","fields":[
{"field":"CLIENTEMEA","type":"number","validation":{"required":true}},
{"field":"CLIENTNAME","type":"string","validation":{"required":true}},
{"field":"CLIENTKVK","type":"number","validation":{"required":true}}
]},
"total":"total"}},
"toolbar":[{"name":"create"}],"height":430,"editable":"popup","pageable":true});});
                </script>

Now you can see in the php-file I created a text file to check which data is echo-ed. That data looks like this:

{"total":2,"data":[{"CLIENTEMEA":127,"CLIENTNAME":"Telenet","CLIENTKVK":123456789},{"CLIENTEMEA":129,"CLIENTNAME":"Tele2","CLIENTKVK":987654321}]}

Which is correct. I made 2 test entries to see what's what.

Now when I start the page, the GRID always shows up empty. It's also not possible to add new records. When I do the submit button doesn't submit the data, so all I can do is cancel.

Can anyone see anything wrong with the code? Are there other tests I could do to find out the problem?

Any help would be appreciated.

Thanks in advance,

Dennis Biemans.

[Alexander Valchev]

Hello Dennis,

Your Kendo configuration and server response looks OK.
If I understood correctly, the Grid is not loaded with data initially on page load. Do you see any JavaScript errors in the browser's console? Does the DataSource fires Ajax request to the server? Are you able to retrieve the data via standard jQuery.ajax request?

$.ajax({
    url:"../../tpbilling/client/test.php?type=read",
    contentType:"application\/json",
    type:"POST",
    success: function(response) {
       console.log(response);
    }
});

Looking forward to your reply.

Regards,
Alexander Valchev
Telerik

[Dennis]

Hi Alexander,

Thanks for your reply.

It made me aware of the console functionality to read data from Ajax requests and that pointed out the issue for me.

When I found out the PDO_OCI drivers where unsupported I used a wrapper which acts as a PDO, but uses the OCI driver. While testing that wrapper, I had some debug code. I had left debug code of which a response was added to the Ajax call. After removing that debug code I do get my data in de GRID. So that part of the problem is solved. Thank you for that.

Now I can also provide more information on the other part of my problem: Not being able to add, edit or delete records. When I try to do either operation I get a 500 internal server error. I added a screenshot with the console information if that helps.

Kind regards,

Dennis Biemans.


edit: I have been doing some more testing, and I found out that the error occurs on "$statement->execute($input_parameters);" in DataSourceResult.

The Query that it wants to execute reads: "INSERT INTO RULES_CLIENTS (CLIENTEMEA, CLIENTNAME, CLIENTKVK) VALUES (?, ?, ?)" which seems perfectly fine.

The statement variable:
{"statement":{"pending":[],"params":[],"ocistatement":"INSERT INTO RULES_CLIENTS (CLIENTEMEA, CLIENTNAME, CLIENTKVK) VALUES ( :bypos1 , :bypos2 , :bypos3 )","ociparams":[],"host":{"conn":null,"transaction":false,"dsn":{"dbname":"nlmaaoradb02.emea.tpg.ads\/personal"},"attributes":{"16":"oci"}},"statement":"INSERT INTO RULES_CLIENTS (CLIENTEMEA, CLIENTNAME, CLIENTKVK) VALUES (?, ?, ?)","cursor":null,"queryString":"","attributes":[]},"queryString":null}

[Alexander Valchev]

Hi Dennis,

We are not sure where the problem comes from. Can you check what is the error that the server returns? Probably the insert statement causes a constraint conflict.

Regards,
Alexander Valchev
Telerik