469,338 Members | 8,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

strict_trans_tables and null defaults

I 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_D IGITS,NUM_PREC_RADIX,NULLABLE,REMARKS,COLUMN_DEF,S QL_DATA_TYPE,SQL_DATETIME_SUB,CHAR_OCTET_LENGTH,OR DINAL_POSITION,IS_NULLABLE
"null","null","my_table","id","4","int","11","6553 5","0","10","0","","null","0","0","11","1","NO"
"null","null","my_table","some_text","12","varchar ","20","65535","0","10","0","","","0","0","20","2" ,"NO"
"null","null","my_table","other_text","12","varcha r","20","65535","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

Jul 17 '06 #1
0 3260

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Kris M | last post: by
1 post views Thread by Jamie Burns | last post: by
3 posts views Thread by iStrain | last post: by
19 posts views Thread by Baldur Norddahl | last post: by
2 posts views Thread by zeljko.prince | last post: by
2 posts views Thread by clickon | last post: by
6 posts views Thread by axelsino | last post: by
26 posts views Thread by ryampolsky | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.