Basic question on connecting to a SQL server

#1

I’m sure this is very simple, but I’m a pretty simple user right now. I have an application that I’ve been developing that connects just fine to a MySQL server running on my local machine. I set up the connection with:

$db = new \atk4\data\Persistence_SQL(‘mysql://user:password@localhost/dbfile’);

I thought it would be straightforward to simply change the connection to:

$db = new \atk4\data\Persistence_SQL(‘mysql://user:password@host/dbfile’);

However, when I do this I get an error:

PDOException: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it.

What am I doing wrong? I can connect to the database via Excel, for example, and get any data I want.

Thanks for any help you can give me.

#2

Well, I figured out how to connect with the MS SQL database – I didn’t have the SQLSRV drivers installed in my PHP installation. So now I can connect to the MS SQL server with the line:

$db = new \atk4\data\Persistence_SQL(‘sqlsrv:Server=server;Database=database’, ‘user’, ‘pw’);

Unfortunately, that didn’t get me very far. I now get an error

  • error : “SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ‘OUTPUT’.”
  • query : “select “e”.“ValueID”,“e”.“DateFrom”,“e”.“DateTo”,“e”.“DepartmentID”,(select “DepartmentName” from “tblDepartment” “e_D” where “DepartmentID” = “e”.“DepartmentID”) “DepartmentName”,“e”.“EmployeeID”,(select “EmployeeName” from “tblEmployee” “e_E” where “EmployeeID” = “e”.“EmployeeID”) “EmployeeName” from “tblEmployee_Values” “e” where “e”.“DepartmentID” in (select “DepartmentID” from “tblDepartment” where “DepartmentID” in (1)) and “e”.“DateFrom” <= ‘2019-03-03’ and “e”.“DateTo” >= ‘2019-02-04’”

If I change the one line of code back to the DSN that references my development local database everything works great.

It looks to me like there’s some incompatibility between the formatting that something’s (ATK data?) doing to the query and what the driver is expecting. Am I off base? Does this mean I’d need to write a custom Persistence class for MS SQL (I hope not – that’s way too advanced for me right now)?

I’d appreciate it if anyone who uses Agile toolkit data/ui with a Microsoft SQL server would let me know what they did to get it working.

Thanks,
Sam

1 Like
#3

Hi, I tested it using your suggested way to connect and got it working for showing a basic grid with pagination by adding to the dqsl-File “Connection.php” in the switch-statement for ($dsn[‘driver’]):

    case 'sqlsrv':
        $c = new static(array_merge([
        'connection'       => new \PDO($dsn['dsn'], $dsn['user'], $dsn['pass']),
        'expression_class'      => 'atk4\dsql\Expression_SQLSRV',
        'query_class'      => 'atk4\dsql\Query_SQLSRV',
        ], $args));
        break;

I also created the files Query_SQLSRV.php and Expression_SQLSRV.php next to the other ones for MySQL and so on.

The latter is only an empty “class Expression_SQLSRV extends Expression”. The former is a copy from MySQL, but I removed the $escape_char and added a override function to make “limit” (which does not exist in sqlsrv) work for the pagination:

public function _render_limit()
{
    if (isset($this->args['limit'])) {
        if (isset($this->args['order'])) {
            return ' OFFSET '.
                (int) $this->args['limit']['shift'].
                ' ROWS FETCH NEXT '.
                (int) $this->args['limit']['cnt'] . 
                ' ROWS ONLY';
        } else {
            return ' ORDER BY(SELECT NULL) OFFSET '.
                (int) $this->args['limit']['shift'].
                ' ROWS FETCH NEXT '.
                (int) $this->args['limit']['cnt'] .
                ' ROWS ONLY';
        }
    }
}

This only touches the surface of the problems encountered with SQLSRV, but might help getting a woking integration for sqlsrv started. If problems with the queries arise, the patterns and functions from the generic Query.php and Expression.php have to be overwritten in the according *_SQLSRV.php files.

I encountered the OUPUT-Error too but did not find a solution yet despite using TSQL the old way:

$conn = new PDO( "sqlsrv:server=...; Database=...", "...", "...");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$tsql = "select ...";
$stmnt = $conn->prepare($tsql);
$stmnt->execute();
$results = $stmnt->fetchAll(PDO::FETCH_ASSOC);
#4

The error “[SQL Server]Incorrect syntax near ‘OUTPUT’” seems to be triggered by setting the length of the parameter passed to bindParam which irritates the SQLSRV-driver.

In “Expression.php” you can find the line in function execute. Change

bind = $statement->bindParam($key, $this->boundValues[$key], $type, strlen($val));

to

$bind = $statement->bindParam($key, $this->boundValues[$key], $type);

I have not tested if this introduces new errors for other atk4 features. For more information see THIS THREAD in the microsoft MSDN forum.