Search found 3 matches: AlterColumnSQL

Searched query: +AlterColumnSQL

Return to “SYSTEM NOTICE, Error Description”

by AlexeiK
18 Jan 2016, 10:00
Forum: Customizations
Topic: SYSTEM NOTICE, Error Description
Replies: 31
Views: 79880

Re: SYSTEM NOTICE, Error Description

so for clear.
after patching adodb from mantis 1.3
i got only one error on user_table
because adodb cant delete index before alter column.
i did it manualy and got mantis 1.3 installed.


and +

$g_upgrade[196] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), 'username C(255) '. $t_notnull .' DEFAULT " \'\' "' ) );
$g_upgrade[197] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), 'realname C(255) '. $t_notnull .' DEFAULT " \'\' "' ) );
$g_upgrade[198] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), 'password C(64) '. $t_notnull .' DEFAULT " \'\' "' ) );
$g_upgrade[199] = array( 'AlterColumnSQL', array( db_get_table( 'user' ), 'email C(255) '. $t_notnull .' DEFAULT " \'\' "' ) );
by AlexeiK
18 Jan 2016, 06:58
Forum: Customizations
Topic: SYSTEM NOTICE, Error Description
Replies: 31
Views: 79880

Re: SYSTEM NOTICE, Error Description

trying to install mantis 1.3
today i install php 5.3 and sqlsrv version 2.0 is compatible with PHP 5.2.4 to 5.3.x and SQL Native client 2008 R2 to connect to Microsoft SQL Server 2000, 2005, or 2008.
and MS SQL 2000.

but!
running mantis 1.3 i get

Code: Select all

BAD
SQL Server 2005 (9.0.0) or later is required for installation.
so! mantis 1.3 wants MS SQL 2005 or later , but scripts on altering column doesnt supports MS SQL 2005.


and then i lowering MIN_VERSION to 8.0.0.0

Code: Select all

BAD
CREATE TABLE mantis_bug_file_table ( id INT IDENTITY(1,1) NOT NULL, bug_id INT DEFAULT 0 NOT NULL, title VARCHAR(250) DEFAULT '' NOT NULL, description VARCHAR(250) DEFAULT '' NOT NULL, diskfile VARCHAR(250) DEFAULT '' NOT NULL, filename VARCHAR(250) DEFAULT '' NOT NULL, folder VARCHAR(250) DEFAULT '' NOT NULL, filesize INT DEFAULT 0 NOT NULL, file_type VARCHAR(250) DEFAULT '' NOT NULL, date_added TIME DEFAULT '1970-01-01T00:00:00' NOT NULL, content IMAGE NULL, PRIMARY KEY (id) )
SQLState: 42000 Error Code: 2715 Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Column or parameter #10: Cannot find data type TIME.
i see you use TIME data type that MS SQL 2000 doesnt supports.

so we need to change scripting ALTER COLUMN for schema.php and thats all.


as i see in datadict-mssqlnative.inc.php
it supports ALTER COLUMN for 2005 but it doesnt works well.

there is IF.
one direction is what we want
one for default
schema.php always use default

Code: Select all

if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
[b]RIGHT[/b]
	            list(,$colname,$default) = $matches;
				$existing = $this->MetaColumns($tabname);
				$constraintname = false;
				$rs = $this->connection->Execute( "select name from sys.default_constraints WHERE object_name(parent_object_id) = '" . $tabname ."' AND col_name(parent_object_id, parent_column_id) = '" . $colname . "'");
				if ( is_object($rs) ) {
					$row = $rs->FetchRow();
					$constraintname = $row[0];
				}
	            $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
	            $t = trim(str_replace('DEFAULT '.$default,'',$v));
				if ( $constraintname != false ) {
					$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
				}
				$sql[] = $alter . $colname . ' ' . $t ;
				if ( $constraintname != false ) {
					$sql[] = 'ALTER TABLE '.$tabname.' ADD CONSTRAINT '.$constraintname.' DEFAULT ' . $default . ' FOR ' . $colname;
				} else {
					$sql[] = 'ALTER TABLE '.$tabname.' ADD CONSTRAINT DF__'. $tabname . '__'.  $colname.  '__' . dechex(rand()) .' DEFAULT ' . $default . ' FOR ' . $colname;				
				}
				if ($not_null) {
					$sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
				}
			} else {
				if ($not_null) {
[b]                        WRONG[/b]
					$sql[] = $alter . $v  . ' NOT NULL';
		         } else {
					$sql[] = $alter . $v;
				}
			}

LOOK HERE!

in mantis 1.3 in datadict-mssqlnative.inc.php has no SUPPORT for ALTERCOLUMNSL function.
this function is COMMENTED!

Code: Select all

	/*
	function AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
	{
		$tabname = $this->TableName ($tabname);
		$sql = array();
		list($lines,$pkey) = $this->_GenFields($flds);
		foreach($lines as $v) {
			$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
		}

		return $sql;
	}
	*/
that is why mantis 1.3 use default from adodb-datadict.inc.php

in mantis 1.2 everything is ok about ALTERCOLUMNSSQL function in datadict-mssqlnative.inc.php

i looked in adodb.master repository and there is no support for ALTERCOLUMNSQL too.
by AlexeiK
15 Jan 2016, 09:37
Forum: Customizations
Topic: SYSTEM NOTICE, Error Description
Replies: 31
Views: 79880

Re: SYSTEM NOTICE, Error Description

cant pass this
$g_upgrade[188] = array( 'AlterColumnSQL', array( db_get_table( 'project' ), 'inherit_global L ' . $t_notnull . ' DEFAULT \'0\'' ) );
BAD
ALTER TABLE mantis_project_table ALTER COLUMN inherit_global BIT DEFAULT 0 NOT NULL
SQLState: 42000 Error Code: 156 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'DEFAULT'.
MSSQL 2008 R2

i run 1.3 nighlty on my 1.2 db.

Code: Select all

Msg 5074, Level 16, State 1, Line 2
The object 'DF__mantis_pr__inher__4EFDAD20' is dependent on column 'inherit_global'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN inherit_global failed because one or more objects access this column.
thats why this alter cant by applied.

the right way is:

Code: Select all

alter table mantis_project_table drop constraint DF__mantis_pr__inher__4EFDAD20
go
alter table mantis_project_table alter column inherit_global BIT
go
alter table mantis_project_table add constraint DF__mantis_pr__inher__4EFDAD20 default 0 for inherit_global
go
drop needed constrain and after that alter column.
so if column has constrain it must be dropped in own step.
after that you can change column type.
after that you can add constrain for DEFAULT value.

here you can see some info:
http://stackoverflow.com/questions/9299 ... ault-value


i have run 1.3 on new db on MSSQL 2008 R2.
but it cant be installed because of column alters on columns that already has constrain.
error is below.

Code: Select all

Schema step 65: AlterColumnSQL ( mantis_user_pref_table )	BAD
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT DEFAULT 0 NOT NULL
SQLState: 42000 Error Code: 156 Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'DEFAULT'.
here is why so:
http://www.select-sql.com/mssql/how-to- ... mssql.html
it is because versions 2005 and later use another T-SQL commands.

here some info:
http://stackoverflow.com/questions/1978 ... 5-3-and-up
about who is who.

Q: how combobox "Type of database" makes ?
because if i want to install mantis on MSSQL 2000 i need one version of "MS sql server native driver"
if i want MSSQL 2005 and later i need "MS sql server native driver" same or higher version of the drivers.
it will be good if i can enter connection string or choose version of driver.


on my windows i have two "MS sql server native driver" from 2008 R2 and 2012.
mantis 1.3 uses 2012(which is ODBC Driver 11 for SQL Server) and i can`t connect to sql server 2000 by this way.

here is some explanation about drivers for MS SQL servers:
http://stackoverflow.com/questions/5343 ... nt-vs-odbc

Return to “SYSTEM NOTICE, Error Description”