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:

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

Jul 17 '06 #1
0 3403

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

Similar topics

6
35375
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
1
14937
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?
3
2141
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,...
19
3465
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.
2
5315
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);
2
8640
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...
6
14406
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". ...
26
4432
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?
1
2214
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...
0
9956
marktang
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...
0
9799
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,...
0
11172
Oralloy
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...
0
9592
agi2029
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...
0
7139
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();...
0
5808
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...
1
4627
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
2
4235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
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.