Problem when using sql server in codeigniter 4 adapter

[sandyh90]

Hello, I have a problem when using this library this is caused when I use SQL server DB driver, and here is the error that i got.

Problem 1

first, my error is caused by an incorrect "rowcount" because SQL Server has a different way to create an alias column [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'rowcount'

here source that problem cause https://github.com/n1crack/datatables/blob/f5b5c327df8525d5ff949c6c90a7752c6071df2b/src/DB/Codeigniter4Adapter.php#L53

and I change to this

public function count(Query $query)
    {
        $sql = $this->db->query("Select count(*) as [rowcount] from ($query)t", $query->escapes);

        return (int)$sql->getRow()->rowcount;
    }

Problem 2

when i fixed an error caused by problem 1 because create an alias column in the SQL server differently i faced another problem like this

"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '`'."

and for this error, I don't know exactly where that problem was caused, but i think this error from this query

(SELECT ` *` FROM (SELECT *\nFROM \"attend-ci\".\"dbo\".\"users\")t)

Near * there have ` both sides i think this error comes from, that was generated from this query ($query)t

because that produced from this code

public function count(Query $query)
    {
        $sql = $this->db->query("Select count(*) as [rowcount] from ($query)t", $query->escapes);

        return (int)$sql->getRow()->rowcount;
    }

here my debug error for this error maybe can help you

{
    "title": "CodeIgniter\\Database\\Exceptions\\DatabaseException",
    "type": "CodeIgniter\\Database\\Exceptions\\DatabaseException",
    "code": 500,
    "message": "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '`'.",
    "file": "D:\\Server\\ProjectApps\\attend\\vendor\\codeigniter4\\framework\\system\\Database\\BaseConnection.php",
    "line": 645,
    "trace": [
        {
            "file": "D:\\Server\\ProjectApps\\attend\\vendor\\ozdemir\\datatables\\src\\DB\\Codeigniter4Adapter.php",
            "line": 52,
            "function": "query",
            "class": "CodeIgniter\\Database\\BaseConnection",
            "type": "->",
            "args": [
                "Select count(*) as [rowcount] from (SELECT ` *` FROM (SELECT *\nFROM \"attend-ci\".\"dbo\".\"users\")t)t",
                []
            ]
        },
        {
            "file": "D:\\Server\\ProjectApps\\attend\\vendor\\ozdemir\\datatables\\src\\Datatables.php",
            "line": 243,
            "function": "count",
            "class": "Ozdemir\\Datatables\\DB\\Codeigniter4Adapter",
            "type": "->",
            "args": [
                {
                    "escapes": [],
                    "sql": "SELECT ` *` FROM (SELECT *\nFROM \"attend-ci\".\"dbo\".\"users\")t"
                }
            ]
        },
        {
            "file": "D:\\Server\\ProjectApps\\attend\\vendor\\ozdemir\\datatables\\src\\Datatables.php",
            "line": 195,
            "function": "setResponseData",
            "class": "Ozdemir\\Datatables\\Datatables",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\Server\\ProjectApps\\attend\\app\\Controllers\\Admin\\UsersManageController.php",
            "line": 41,
            "function": "generate",
            "class": "Ozdemir\\Datatables\\Datatables",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\Server\\ProjectApps\\attend\\vendor\\codeigniter4\\framework\\system\\CodeIgniter.php",
            "line": 934,
            "function": "getUser",
            "class": "App\\Controllers\\Admin\\UsersManageController",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\Server\\ProjectApps\\attend\\vendor\\codeigniter4\\framework\\system\\CodeIgniter.php",
            "line": 499,
            "function": "runController",
            "class": "CodeIgniter\\CodeIgniter",
            "type": "->",
            "args": [
                {}
            ]
        }
    ]
}

I hope you can fix this error and help me

Reference:

• 55

[n1crack]

First of all, the adapter is for mysql server. If you are using SQL server, you must create a new adapter for it.

I don't have mssql server. You can copy the adapter file and rewrite the sql codes as the syntax is different.

[sandyh90]

Ok i will try to create new adapter that can support for mssql server, thank for your advice i will close this issue and if there's have any problem again i will open it again