Sqlsrv PDO driver from Microsoft

[funkjedi]

Uses the Sqlsrv PDO driver from Microsoft tested it using SQL Server 2005 Express. Related issue Issue #165.

[koenpunt]

This is great, thanks! But how does it know which server to connect to in the tests? And does mssql server runs at all on Travis?

[tuupola]

According to docs mssql is not supported by Travis-CI. For local tests should add an entry to test/helpers/config.php.

[funkjedi]

I pieced this PR together from a version of php-activerecord I'd modified a couple years ago and I guess I missed the config.

[ghost]

hi mr.funkjedi i am trying your adapter for a project done for mysql and now to be done on sqlsrv

do i have to take the only adapter or the complete phpactiverecord you have posted

i have done a test only adding the adapter with a partial success

The SQL is a sql server express 2012

Some idea or suggestio?

Notice: Undefined offset: 0 in\php-activerecord\lib\Model.php on line 1671

Notice: Uninitialized string offset: 0 in \php-activerecord\lib\adapters\SqlsrvAdapter.php on line 32

Notice: Uninitialized string offset: -1 in \php-activerecord\lib\adapters\SqlsrvAdapter.php on line 32

Fatal error: Uncaught exception 'ActiveRecord\DatabaseException' with message 'exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Nome di oggetto o colonna vuoto o mancante. Per le istruzioni SELECT INTO, verificare che tutte le colonne abbiano un nome. Per le altre istruzioni, cercare eventuali nomi alias vuoti. Gli alias definiti come "" o [] non sono consentiti. Modificare l'alias in un nome valido.' in \php-activerecord\lib\Connection.php:323 Stack trace: #0 \lib\Connection.php(323): PDOStatement->execute(Array) #1 \php-activerecord\lib\Table.php(218): ActiveRecord\Connection->query('SELECT *, prope...', Array) #2 \php-activerecord\lib\Table.php(209): ActiveRecord\Table->find_by_sql('SELECT *, prope...', Array, false, NULL) #3 in \php-activerecord\lib\Connection.php on line 327

Thanks, a lot

[funkjedi]

Notice: Undefined offset: 0 in\php-activerecord\lib\Model.php on line 1671

This would seem to indicator an issue with your primary key. Do you have primary keys defined for your table?

[ghost]

I confirm what you are saying infact Class:find('n') won't work but Class:dind_by_id('n') is ok

is it possible that DB migration has created primary key in a manner non conventional

for example this is a CREATE SCRIPT for one of my table

CREATE TABLE [dbo].[users] ( [id] [int] IDENTITY(23,1) NOT NULL, [parent_id] [int] NULL, [agency_id] [int] NOT NULL, [nome_accesso] nvarchar NULL, [pass_md5] nvarchar NULL, [email] nvarchar NULL, [telefono] nvarchar NULL, [permesso_admin_agenzia] [smallint] NOT NULL, CONSTRAINT [PK_users_id] PRIMARY KEY CLUSTERED ( [id] ASC, [agency_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

is there any mistake?

[ghost]

so thanks solution/workaround found

on every Model/class declaration I am adding the static $primary_key = 'id';

in that way no error!

I think that there's somtihng in the way the adapter try to find the primary key con ms sqlsrv 2012 ... or somthing in the way I declare primary key

thanks for valuable indication Francesco

[funkjedi]

The adapter attempts to identify the column used for the primary key by looking up the primary key constraint using

$sql =
    "SELECT c.COLUMN_NAME as field, c.DATA_TYPE as data_type, c.CHARACTER_MAXIMUM_LENGTH AS length, c.NUMERIC_PRECISION_RADIX AS radix, c.COLUMN_DEFAULT AS data_default, c.IS_NULLABLE AS nullable, " .
        "COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS extra, " .
        "(SELECT a.CONSTRAINT_TYPE " .
        "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b " .
        "WHERE a.CONSTRAINT_TYPE='PRIMARY KEY' " .
        "AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME " .
        "AND a.TABLE_NAME = b.TABLE_NAME AND b.COLUMN_NAME = c.COLUMN_NAME) AS PK " .
    "FROM INFORMATION_SCHEMA.COLUMNS c " .
    "WHERE c.TABLE_NAME=?";

in your case it's failing because you've defined a clustered primary key.

[ghost]

ok I think this is the reason, but create procedure add something on this version of sql a primary can be CLUSTERED or NONCLUSTERED

I have dropped the table and recreate by this : DROP TABLE [dbo].[propertysections] go

CREATE TABLE [dbo].[propertysections]( [id] [int] IDENTITY(2,1) NOT NULL, [descrizione] nvarchar NULL, CONSTRAINT [PK_propertysections_id] PRIMARY KEY
( [id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

for now i continue to define static primary_key in model

and also,

SELECT c.COLUMN_NAME as field, c.DATA_TYPE as data_type, c.CHARACTER_MAXIMUM_LENGTH AS length, c.NUMERIC_PRECISION_RADIX AS radix, c.COLUMN_DEFAULT AS data_default, c.IS_NULLABLE AS nullable, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS extra, (SELECT a.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b WHERE a.CONSTRAINT_TYPE ='PRIMARY KEY' AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND a.TABLE_NAME = b.TABLE_NAME AND b.COLUMN_NAME = c.COLUMN_NAME) AS PK FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME='propertysections'

on my sql express 2012 return an error message 512, level 16, telling subquery return more than one value... is it possible that information_schema are now described in a new way?

[ghost]

by the way have I tell you ... thanks?

[ghost]

Hi TIM I have found a better solution also the one I said before is not a solution. because of the fact that class where not correctly readed in effect there was a problem with the query the parameter ('?') was not interpreted by PDO query and so never have a result

brutally sobstituting the parameter with the string, without [ ] all is gone UP

by the way tomorrow I hope to understand the couse of non correctly interpreting parameter

good night

2014-05-26 18:33 GMT+02:00 Tim Robertson [email protected]:

The adapter attempts to identify the column used for the primary key by looking up the primary key constraint using

$sql = "SELECT c.COLUMN_NAME as field, c.DATA_TYPE as data_type, c.CHARACTER_MAXIMUM_LENGTH AS length, c.NUMERIC_PRECISION_RADIX AS radix, c.COLUMN_DEFAULT AS data_default, c.IS_NULLABLE AS nullable, " . "COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') AS extra, " . "(SELECT a.CONSTRAINT_TYPE " . "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b " . "WHERE a.CONSTRAINT_TYPE='PRIMARY KEY' " . "AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME " . "AND a.TABLE_NAME = b.TABLE_NAME AND b.COLUMN_NAME = c.COLUMN_NAME) AS PK " . "FROM INFORMATION_SCHEMA.COLUMNS c " . "WHERE c.TABLE_NAME=?";

in your case it's failing because you've defined a clustered primary key.

— Reply to this email directly or view it on GitHub https://github.com/jpfuentes2/php-activerecord/pull/409#issuecomment-44202918 .

Francesco Venturini MSN: [email protected] Skype: fventurinitr

[AndroxxTraxxon]

Has anyone found a solution to this, or was it just abandoned because it was getting messy? If it's the latter, I'd like to take a swing at getting this working.

[mohamadmulhem]

Has anyone found a solution to this, or was it just abandoned because it was getting messy? If it's the latter, I'd like to take a swing at getting this working.

I had time to fix it, you can see it modified in my repository https://github.com/mohamadmulhem/php-activerecord

It is working well with MS SQL Server 2012 & 2014