FreeTDS Development Group

[屋宜宗穂 <ymym4649 AT gmail.com>]

Hi All:

< Details of occurring event >

I use FreeTDS for the connection from PHP on Linux to MSSQL.
When SQL transaction that became an error of the column type mismatch was
executed, ROLLBACK was compulsorily issued by FreeTDS.
(Compulsion ROLLBACK is not generated at other SQL errors.)
I completed the correction of the source, and had SQL of the column type
mismatch not requested now.
Is it a specification of FreeTDS that ROLLBACK is compulsorily issued when
the error of SQL of the column type mismatch is issued?
Or, is there a problem in my environment or source?
Please confirm my verification outcome, and tell if you understand the
correspondence schedule causing and from now on.


< Verification Outcome >

ROLLBACK occurs only at this time.

1. Set the value with a different type to the column. (Set the value of the
string type is inserted in column "colbit" of the bit type.)
*SQL:  UPDATE [testtable] SET [colbit] = '1' WHERE testtableid = '1';
*ErrorMessage: SQLSTATE[HY000]: General error: 20018 The ROLLBACK
TRANSACTION request has no corresponding BEGIN TRANSACTION. [20018]
(severity 5) []

ROLLBACK does not occur, below...

2. Set the value to the column that doesn't exist. (Set the value to column
"colnotexist" that doesn't exist.)
*SQL:  UPDATE [testtable] SET [colbit] = '1', colnotexist='0' WHERE
testtableid = '1';
*ErrorMessage: SQLSTATE[HY000]: General error: 20018 Invalid column name
'colnotexist'. [20018] (severity 5) []

3. Set the value to the table that doesn't exist. (Set the value to table
"testtable" that doesn't exist.)
*SQL:  UPDATE [testtable] SET [colbit] = '1' WHERE testtableid = '1';
*ErrorMessage: SQLSTATE[HY000]: General error: 20018 Invalid object name
'testtable'. [20018] (severity 5) []

4. Set NULL value to the column which is NOT NULL restricted. (Set NULL
value to column "testtableno" which attribute is NOTNULL.)
*SQL:  UPDATE [testtable] foobarSET [colbit] = 1 WHERE testtableid =
'1';*ErrorMessage: SQLSTATE[HY000]: General error: 20018 Cannot insert the
value NULL into column 'testtableNo', table 'testdb.dbo.testtable'; column
does not allow nulls. UPDATE fails. [20018] (severity 5) []

5. When a SQL grammatical error occurs.(Illegal syntax "foobarSET" is set.)
*SQL:  UPDATE [testtable] foobarSET [colbit] = 1 WHERE testtableid = '1';
*ErrorMessage: SQLSTATE[HY000]: General error: 20018 Incorrect syntax near
'foobarSET'. [20018] (severity 5) []


< Execution Environment >
・WEB server: CentOS, Apache, PHP5, Symfony1.4(Doctrine), and FreeTDS0.82
・DB server: WindowsServer2008 and MSSQLServer2005


< Consideration >
Hereafter, a similar event was reported on the site as for Python.
It seems not to be neither PHP, to be nor Symfony, and to be no
Doctrine(ORM) endemic problem.
Moreover, this event doesn't reproduce it in the management tool of the
MSSQL manager etc.
Therefore, I think that the possibility of a potential trouble on unixODBC
or the FreeTDS side is high.
(The event that occurs only when Unix system uses MSSQL.)