So have an issues with pagination. All other queries work with query manager. Offset is the only one that does not. Comes back with 500Internal Server Error [object Object]
I have everything setup properly, I think since everything else works. Sorting, direction, etc. Thoughts?
Hello, what’s the exact error message you see as explained here:
{“code”:“42000”,“file”:“D:\home\site\wwwroot\dmxConnectLib\lib\db\Connection.php”,“line”:88,“message”:“SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘AS’.”,“trace”:"#0 D:\home\site\wwwroot\dmxConnectLib\lib\db\Connection.php(88): PDOStatement->execute()\n#1 D:\home\site\wwwroot\dmxConnectLib\modules\dbconnector.php(156): lib\db\Connection->execute(‘SELECT * FROM (…’, Array)\n#2 D:\home\site\wwwroot\dmxConnectLib\lib\App.php(159): modules\dbconnector->paged(Object(stdClass), ‘query1’)\n#3 D:\home\site\wwwroot\dmxConnectLib\lib\App.php(128): lib\App->execSteps(Object(stdClass))\n#4 D:\home\site\wwwroot\dmxConnectLib\lib\App.php(98): lib\App->execSteps(Array)\n#5 D:\home\site\wwwroot\dmxConnectLib\lib\App.php(71): lib\App->exec(Object(stdClass))\n#6 D:\home\site\wwwroot\dmxConnect\api\inspection\qry_ins_admin.php(8): lib\App->define(Object(stdClass))\n#7 {main}"}
What database type are you using?
MS SQL Native
Thank you.
There is an error with the query in the qry_ins_admin query1 action. Can you post the content of dmxConnect\api\inspection\qry_ins_admin.php. Does the query work for the first page? You mention that it only occurs when offset is set.
Correct. Only when offset is used does it have a problem. Other than that, works great
<?php
require('../../../dmxConnectLib/dmxConnect.php');
$app = new \lib\App();
$app->define(<<<'JSON'
{
"meta": {
"options": {},
"$_GET": [
{
"type": "text",
"name": "offset"
},
{
"type": "text",
"name": "limit"
},
{
"type": "text",
"name": "sort"
},
{
"type": "text",
"name": "dir"
}
]
},
"exec": {
"steps": [
"Connections/CWPS_AZURE_SQL_002",
"SecurityProviders/CWPS_EMPLOYEES",
{
"name": "",
"module": "auth",
"action": "restrict",
"options": {
"provider": "CWPS_EMPLOYEES",
"permission": "Service Manager"
}
},
{
"name": "query1",
"module": "dbconnector",
"action": "paged",
"options": {
"connection": "CWPS_AZURE_SQL_002",
"sql": {
"type": "SELECT",
"columns": [
{
"table": "EMP",
"column": "emp_full_name"
},
{
"table": "svc_insp_reports",
"column": "idinspection"
},
{
"table": "svc_insp_reports",
"column": "ins_company"
},
{
"table": "svc_insp_reports",
"column": "ins_site"
},
{
"table": "svc_insp_reports",
"column": "ins_rating"
},
{
"table": "svc_insp_reports",
"column": "ins_created"
},
{
"table": "svc_insp_reports",
"column": "ins_createby"
}
],
"table": {
"name": "svc_insp_reports"
},
"joins": [
{
"table": "employee",
"column": "*",
"alias": "EMP",
"type": "INNER",
"clauses": {
"condition": "AND",
"rules": [
{
"table": "EMP",
"column": "id_emp",
"operator": "equal",
"value": {
"table": "svc_insp_reports",
"column": "ins_createby"
},
"operation": "="
}
]
}
}
],
"orders": [],
"query": "SELECT EMP.emp_full_name, svc_insp_reports.idinspection, svc_insp_reports.ins_company, svc_insp_reports.ins_site, svc_insp_reports.ins_rating, svc_insp_reports.ins_created, svc_insp_reports.ins_createby FROM svc_insp_reports INNER JOIN employee AS EMP ON (EMP.id_emp = svc_insp_reports.ins_createby)",
"params": [],
"limit": 500
}
},
"output": true,
"meta": [
{
"name": "offset",
"type": "number"
},
{
"name": "limit",
"type": "number"
},
{
"name": "total",
"type": "number"
},
{
"name": "page",
"type": "object",
"sub": [
{
"name": "offset",
"type": "object",
"sub": [
{
"name": "first",
"type": "number"
},
{
"name": "prev",
"type": "number"
},
{
"name": "next",
"type": "number"
},
{
"name": "last",
"type": "number"
}
]
},
{
"name": "current",
"type": "number"
},
{
"name": "total",
"type": "number"
}
]
},
{
"name": "data",
"type": "array",
"sub": [
{
"name": "emp_full_name",
"type": "text"
},
{
"name": "idinspection",
"type": "number"
},
{
"name": "ins_company",
"type": "text"
},
{
"name": "ins_site",
"type": "text"
},
{
"name": "ins_rating",
"type": "number"
},
{
"name": "ins_created",
"type": "datetime"
},
{
"name": "ins_createby",
"type": "number"
}
]
}
],
"outputType": "object"
}
]
}
}
JSON
);
?>
It seems that the mssql version of the paged query doesn’t handle joins correctly. I need to investigate it further, as a workaround you should prevent joins in paged queries, you can put the join in a view and use that in the paged query.
Got it, thank you!