Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Statement/Type-Related Parameters

The following are parameters related to SQL statements and data types supported by CUBRID. The type and value range for each parameter are as follows:

Parameter Name

Type

Default Value

add_column_update_hard_default

bool

no

alter_table_change_type_strict

bool

no

ansi_quotes

bool

yes

block_ddl_statement

bool

no

block_nowhere_statement

bool

no

compat_numeric_division_scale

bool

no

default_week_format

int

0

group_concat_max_len

int

1024

intl_mbs_support

bool

no

no_backslash_escapes

bool

yes

only_full_group_by

bool

no

oracle_style_empty_string

bool

no

pipes_as_concat

bool

yes

plus_as_concat

bool

yes

require_like_escape_character

bool

no

return_null_on_function_errors

bool

no

add_column_update_hard_default

The add_column_update_hard_default parameter is used to determine whether or not to provide the hard_default value as the input value for a column when you add a new column to the ALTER TABLE … ADD COLUMN clause.

If a value for this parameter is set to yes, enter a new input value of a column as a hard default value when you have NOT NULL constraints but no DEFAULT constraints. If the parameter value is set to no, enter NULL, even if NOT NULL constraints exist. If a value for this parameter is set to yes and there is no hard default value for the column type to add, an error message will be displayed and a roll-back occurs. For the hard default for each type, see the CHANGE Clause of the ALTER TABLE statement.

-- add_column_update_hard_default=no

 

CREATE TABLE tbl (i INT);

INSERT INTO tbl VALUES (1),(2);

ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

 

SELECT * FROM TBL;

 

            i          j

========================

            2       NULL

            1       NULL

 

-- add_column_update_hard_default=yes

 

CREATE TABLE tbl (i int);

INSERT INTO tbl VALUES (1),(2);

ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

 

SELECT * FROM tbl;

 

            i          j

=========================

            2          0

            1          0

alter_table_change_type_strict

The alter_table_change_type_strict parameter is used to determine whether or not to allow the conversion of column values according to the type change, and the default value is no. If a value for this parameter is set to no, the value may be changed when you change the column types or when you add NOT NULL constraints; if it is set to yes, the value is not changed. For details, see CHANGE Clause in the CHANGE/MODIFY Clause.

ansi_quotes

ansi_quotes is a parameter that enclose symbols and character string to handle identifiers. The default value is yes. If this parameter value is set to yes, double quotations are handled as identifier symbols and single quotations are handled as character string symbols. If it is set to no, both double and single quotations are handled as character string symbols.

block_ddl_statement

block_ddl_statement is a parameter that restricts the execution of DDL (Data Definition Language) statements by the client. If the parameter is configured to no, the given client is allowed to execute DDL statements. If it is configured to yes, the client is not permitted to execute DDL statements. The default value is no.

block_nowhere_statement

block_nowhere_statement is a parameter that restricts the execution of UPDATE/DELETE statements without a condition clause (WHERE) by the client. If the parameter is configured to no, the given client is allowed to execute UPDATE/DELETE statements without a condition clause. If it is configured to yes, the client is not permitted to execute UPDATE/DELETE statements without a condition clause. The default value is no.

compact_numeric_division_scale

compat_numeric_division_scale is a parameter that configures the scale to be displayed in the result (quotient) of a division operation. If the parameter is configured to no, the scale of the quotient is 9 if it is configured to yes, the scale is determined by that of the operand. The default value is no.

default_week_format

The default_week_format parameter is used to set the default value for the mode attribute of the WEEK function. The default value is 0. For details, see WEEK Function.

group_concat_max_len

The group_concat_max_len parameter is used to limit the return value size of the GROUP_CONCAT function. The default value is 1024 bytes, the minimum value is 4 bytes, and the maximum value is 33,554,432 bytes. If the return value of the GROUP_CONCAT function exceeds the limitation, NULL will be returned.

intl_mbs_support

intl_mbs_support is a parameter that specifies whether or not to support multi-byte character set. If the parameter is configured to no, a multi-byte character set is not allowed if it is configured to yes, a multi-byte character set is allowed. To improve performance, it is recommended to configure the intl_mbs_support parameter to no and use alphabets for table and column names because operation cost for supporting multi-byte character set is high.

no_backslash_escapes

The no_backslash_escapes is used to determine whether or not to use backslash () as an escape character, and the default value is yes. If a value for this parameter is set to no, backslash () will be used as an escape character; if it is set to yes, backslash () will be used as a normal character. For details, see Escape Special Characters.

only_full_group_by

only_full_group_by is a parameter that specifies whether extended syntax about using GROUP BY statement is used or not.

If this parameter value is set to no, an extended syntax is applied thus, a column that is not specified in the GROUP BY statement can be specified in the SELECT column list. If it is set to yes, a column that is only specified in the GROUP BY statement can be the SELECT column list.

The default value is no. Therefore, specify the only_full_group_by parameter value to yes to execute queries by SQL standards. Because the extended syntax is not applied in this case, an error below is displayed.

ERROR: Attributes exposed in aggregate queries must also appear in the group by clause.

oracle_style_empty_string

oracle_style_empty_string is a parameter that improves compatibility with other DBMS (Database Management Systems) and specifies whether or not to process empty strings as NULL as in Oracle DBMS. If the oracle_style_empty_string parameter is configured to no, the character string is processed as a valid string if it is configured to yes, the empty string is processed as NULL.

pipes_as_concat

pipes_as_concat is a parameter about using a double pipe symbol. The default value is yes. If this parameter value is set to yes, a double pipe symbol is handled as a concatenation operator if no, it is handled as the OR operator.

plus_as_concat

The plus_as_concat parameter is a parameter for the use of the + operator, and the default value is yes. If a value for this parameter is set to yes, the + operator will be interpreted as a concatenation operator; if it is set to no, the operator will be interpreted as a numeric operator.

-- plus_as_concat = yes

SELECT '1'+'1';

         '1'+'1'

======================

         '11'  SELECT '1'+'a';

 

         '1'+'a'

======================

         '1a'

 

-- plus_as_concat = no

SELECT '1'+'1';

                '1'+'1'

==========================

 2.000000000000000e+000

 

SELECT '1'+'a';

 

ERROR: Cannot coerce 'a' to type double.

require_like_escape_characterintl_mbs_support

The require_like_escape_character parameter is used to determine whether or not to use an ESCAPE character in the LIKE clause, and the default value is no. If a value for this parameter is set to yes and a value for no_backslash_escapes is set to no, backslash () will be used as an ESCAPE character in the strings of the LIKE clause, otherwise you should specify an ESCAPE character by using the LIKE… ESCAPE clause. For details, see LIKE Predicate.

return_null_on_function_errors

The return_null_on_function_errors parameter is used to define actions when errors occur in some SQL functions, and the default value is no. If a value for this parameter is set to yes, NULL is returned; if it is set to no, an error is returned when the error occurs in functions, and the related message is displayed.

The following SQL functions are affected by this system parameter.

  • ADDTIME
  • DATEDIFF
  • DAY
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • FROM_DAYS
  • FROM_UNIXTIME
  • HOUR
  • LAST_DAY
  • MAKEDATE
  • MAKETIME
  • MINUTE
  • MONTH
  • QUARTER
  • SEC_TO_TIME
  • SECOND
  • TIME
  • TIME_TO_SEC
  • TIMEDIFF
  • TO_DAYS
  • WEEK
  • WEEKDAY
  • YEAR

-- return_null_on_function_errors=no

 

SELECT HOUR('2010-01-01');

 

ERROR: Conversion error in time format.

 

-- return_null_on_function_errors=yes

 

SELECT HOUR('2010-01-01');

 

   hour('2010-01-01')

======================

  NULL