We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

MSSQL Dialect And ORM

Hi,

i use a mssql adapter to connect to mssql with my Phalcon project.

i have got a problem with mssql and the database's field type "varbinary(MAX)" . The "varbinary(MAX)" in mssql are used to store images.

When i save my model using mssql i get this error.

PDOException : SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

According with Microsoft i have to convert the type in the query SQL, like you can see in the examples below:

//INSERT
$query = "INSERT INTO images (ext, size, image) VALUES ('$ext', $filesize, CONVERT(varbinary(MAX),'$data'))";
//SELECT
$sql = "SELECT CONVERT(varchar(MAX), 'image') as image  FROM  images WHERE image_id = '$id'";

My question is: How can i do this using my model class in Phlacon ?

Hi,

i solved my problem using the data type text instead of varbinary(MAX) in MS SQL Server.



2.2k

Can I ask where did you get the MsSQL adapter? I'm currently looking for one, and to my knowledge there is no such thing shipped natively with Phalcon. Did you write your own?

Hi, i do not, i used this implementation: https://github.com/fishjerky/phalcon-mssql



22.8k

@mapirelli how did di you get the MSSQL driver implementation to work? Can you provide an example please? and where do I put the files.

Thanks

edited Mar '14

Hi, i used the files contained inside the test folder of the repository: From git: phalcon-mssql / test / app / library / db / There are two class inside here, and each class has a namespace. So than, you can register the namespaces with Phalcon.

copy the folder DB where you like example -> app / vendor / db /

//Register some namespaces
$loader->registerNamespaces(
    array(
       " Twm\Db\Adapter\Pdo"    => "vendor/db/mssql/adapter/",
       "Twm\Db\Dialect" => "vendor/db/mssql/dialect/",
    )
);

Then you have to modify the DI instance, add the MSSQL Adapter in the service.php of Phalcon.

$db = array( 'host' => 'PC\SQLEXPRESS', 'username' => 'root', 'password' => 'root', 'dbname' => 'mango', 'dialectClass' => '\Twm\Db\Dialect\Mssql'
);

Here you will see an example. https://github.com/fishjerky/phalcon-mssql/blob/master/test/app/library/db/test.php