473,886 Members | 2,366 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

strict_trans_ta bles and null defaults

I am using 5.0.21 community-nt and have the following in the
configuration file:

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

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[] =

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",
"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();
Here's what I get...

"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?


Jul 17 '06 #1
0 3403

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Kris M | last post by:
How do i handle a null value for a date variable type. I am retrieving date data from an access database and storing the records in an array for processing. The array field has a date type and the db records are date type with null permissible. How should i handle the insertion of a null date record into the array? I just cant leave it empty right? K
by: Jamie Burns | last post by:
Hello, I am trying to perform a LEFT JOIN on a table which may or may not have matching rows. If there are no matching rows, it returns NULL's for all the missing fields. Is there anyway of returning the default values for that table instead of NULL's, in a portable way?
by: iStrain | last post by:
Hiya. I'm _sure_ this is an FAQ, but Googling hasn't produced the answer in a way I can make sense out of. I know I should get this, but so far no way... I'm creating tables and doing queries in Perl, and Nulls have started to bother me greatly. The first issue is, as far as I understand it, a column should be NOT NULL if it is necessary (required) data. Now, if a column doesn't have to be NOT NULL; that is, it's not _required_ data,...
by: Baldur Norddahl | last post by:
Hi, How come "X=null" is not the same as "X is null"? I got a few selects with queries like this: select * from foo where customer=#customer# or (#customer# is null and customer is null) Without the last part, it will not correctly match null customers.
by: zeljko.prince | last post by:
This is a copy from http://forums.mysql.com/read.php?10,73797,73797#msg-73797. Perhaps someone on this group will know the answer. Given the following table: CREATE TABLE foo(field VARCHAR(20) NOT NULL); The following query *can not* be executed: INSERT INTO foo VALUES(NULL);
by: clickon | last post by:
I am using ASP.net 2.0 and trying to take advantage of the updated data editing facilities provided through the SQLDataSource control and the DetailsView control. The data is a record from a customer complaints table and one of the fields on the DetailsView control is called ComplaintType. The field is a template field and in insert mode and edit mode i have used a DropDownList control bound to a set of keys and values in a ComplaintTypes...
by: axelsino | last post by:
I know this has been asked before and yes, I have read the section in the documentation about it. But, my question is: If I have setup mysql with strict_trans_tables, will MySQL allow "null" defaults in "not null" columns? If the answer is yes, will that information be reflected when I ask for metadata (column information, etc.)? Currently, when I ask for metadata information, not-null columns will return a default of "empty string". ...
by: ryampolsky | last post by:
I'm using strtok to break apart a colon-delimited string. It basically works, but it looks like strtok skips over empty sections. In other words, if the string has 2 colons in a row, it doesn't treat that as a null token, it just treats the 2 colons as a single delimiter. Is that the intended behavior?
by: janetopps | last post by:
I used a database transfer utility (Bullzip) to move data from Access to MySQL Asp pages, I couldn't transfer the data if i did not opt to leave the default values, therefore the default values didn't get transfered. I checked the access database and some of the fields have default values, ie, current time to go in automatically. Obviously, these being absent in MySQL are causing problems as they show NULL I use MYSQL query...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.