strict_trans_tables and null defaults
am 17.07.2006 23:05:39 von axelsinoI am using 5.0.21 community-nt and have the following in the
configuration file:
transaction-isolation=READ-COMMITTED
sql-mode=ansi,strict_trans_tables
I have defined this little table:
create table my_table
(id int not null primary key auto_increment,
some_text varchar(20) not null,
other_text varchar(20) default 'aaa') type=innodb
Then I try this insert:
insert into my_table (other_text) values ('abc');
I get an error 1364(HY000) Field 'some_text' doesn't have a default
value.
Great, this is what I expected.... Life is good, so far.
Now, if I try to get the table metadata using JConnector 3.1.13 I get
some unexplicable results.
Here's the program
public class SomeTest
{
private static final String tableTypes[] =
{ "TABLE", "VIEW", "SYSTEM TABLE" };
public static void main(String[] args) throws Exception
{
Connection conn;
Statement stmt;
ResultSet rs;
String[] columnNames;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydb",
"myuser",
"mypassword");
DatabaseMetaData metadata = conn.getMetaData();
stmt = conn.createStatement();
rs = metadata.getColumns(null,null,"my_table","%");
ResultSetMetaData resultMetadata = rs.getMetaData();
int columnCount = resultMetadata.getColumnCount();
columnNames = new String[columnCount];
for (int i = 1; i <= columnCount; i++)
{
columnNames[i - 1] = resultMetadata.getColumnName(i);
System.out.print(columnNames[i - 1]);
if (i < columnCount)
{
System.out.print(",");
}
}
System.out.println();
while (rs.next())
{
for (int i = 1; i <= columnCount; i++)
{
System.out.print("\"" + rs.getString(columnNames[i - 1]) + "\"");
if (i < columnCount)
{
System.out.print(",");
}
}
System.out.println();
}
rs.close();
stmt.close();
conn.close();
}
}
Here's what I get...
TABLE_CAT,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,DATA_TYPE,TYPE_ NAME,COLUMN_SIZE,BUFFER_LENGTH,DECIMAL_DIGITS,NUM_PREC_RADIX ,NULLABLE,REMARKS,COLUMN_DEF,SQL_DATA_TYPE,SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE
"null","null","my_table","id","4","int","11","65535","0","10 ","0","","null","0","0","11","1","NO"
"null","null","my_table","some_text","12","varchar","20","65 535","0","10","0","","","0","0","20","2","NO"
"null","null","my_table","other_text","12","varchar","20","6 5535","0","10","1","","aaa","0","0","20","3","YES"
Looking at the field 'some_text', JConnector tells me that it has a
default of empty string instead of null which would be what the insert
statement would indicate.
I understand the reasoning behind assigning a 'default' value to every
field when none was specified in order not to break MyISAM
'transactions'...
My question is
Can this functionality be changed for strct_trans_tables (perhaps
strict_all_tables as well) so that it really returns a NULL default?
Axel