I am using 5.0.21 community-nt and have the following in the
configuration file:
transaction-isolation=READ-COMMITTED
sql-mode=ansi,stric t_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").newIns tance();
conn =
DriverManager.g etConnection(
"jdbc:mysql ://localhost:3306/mydb",
"myuser",
"mypassword ");
DatabaseMetaDat a metadata = conn.getMetaDat a();
stmt = conn.createStat ement();
rs = metadata.getCol umns(null,null, "my_table","%") ;
ResultSetMetaDa ta 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.prin t(columnNames[i - 1]);
if (i < columnCount)
{
System.out.prin t(",");
}
}
System.out.prin tln();
while (rs.next())
{
for (int i = 1; i <= columnCount; i++)
{
System.out.prin t("\"" + rs.getString(co lumnNames[i - 1]) + "\"");
if (i < columnCount)
{
System.out.prin t(",");
}
}
System.out.prin tln();
}
rs.close();
stmt.close();
conn.close();
}
}
Here's what I get...
TABLE_CAT,TABLE _SCHEM,TABLE_NA ME,COLUMN_NAME, DATA_TYPE,TYPE_ NAME,COLUMN_SIZ E,BUFFER_LENGTH ,DECIMAL_DIGITS ,NUM_PREC_RADIX ,NULLABLE,REMAR KS,COLUMN_DEF,S QL_DATA_TYPE,SQ L_DATETIME_SUB, CHAR_OCTET_LENG TH,ORDINAL_POSI TION,IS_NULLABL E
"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","va rchar","20","65 535","0","10"," 0","","","0","0 ","20","2", "NO"
"null","null"," my_table","othe r_text","12","v archar","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_tab les (perhaps
strict_all_tabl es as well) so that it really returns a NULL default?
Axel
configuration file:
transaction-isolation=READ-COMMITTED
sql-mode=ansi,stric t_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").newIns tance();
conn =
DriverManager.g etConnection(
"jdbc:mysql ://localhost:3306/mydb",
"myuser",
"mypassword ");
DatabaseMetaDat a metadata = conn.getMetaDat a();
stmt = conn.createStat ement();
rs = metadata.getCol umns(null,null, "my_table","%") ;
ResultSetMetaDa ta 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.prin t(columnNames[i - 1]);
if (i < columnCount)
{
System.out.prin t(",");
}
}
System.out.prin tln();
while (rs.next())
{
for (int i = 1; i <= columnCount; i++)
{
System.out.prin t("\"" + rs.getString(co lumnNames[i - 1]) + "\"");
if (i < columnCount)
{
System.out.prin t(",");
}
}
System.out.prin tln();
}
rs.close();
stmt.close();
conn.close();
}
}
Here's what I get...
TABLE_CAT,TABLE _SCHEM,TABLE_NA ME,COLUMN_NAME, DATA_TYPE,TYPE_ NAME,COLUMN_SIZ E,BUFFER_LENGTH ,DECIMAL_DIGITS ,NUM_PREC_RADIX ,NULLABLE,REMAR KS,COLUMN_DEF,S QL_DATA_TYPE,SQ L_DATETIME_SUB, CHAR_OCTET_LENG TH,ORDINAL_POSI TION,IS_NULLABL E
"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","va rchar","20","65 535","0","10"," 0","","","0","0 ","20","2", "NO"
"null","null"," my_table","othe r_text","12","v archar","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_tab les (perhaps
strict_all_tabl es as well) so that it really returns a NULL default?
Axel