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