Mysql – Out-of-Range and Overflow Handling for float values

cmysql-5.1

am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3)

Currently if the user enter the value above 999999.000 MYSQL will save its maximum value,ie
1000000.000 and it will not throw any error.

If I set the sqlmode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' my insert procedure will throw error, if the value for taxvalue is greater than 999999.000

Am using this column in a user entry page and that application is written in C#. I wanted to show a validation message to user if he tries to enter the value above 999999.000.

what is the best method I can use to make sure,user is getting a validation message before he clicks the save button.?I have tried with regular expression but its not giving me the desired result because user can enter any value with the range of (9,3)..

Best Answer

In my view, you're on the right track and validating it in C# before sending it to the database is the better way, so you're able to show the changes before a submit.

A regular expression should indeed be able to solve your validating issue, but depending on how you allow the number to be entered plays a part in how complex it should be. Say do you allow thousand-seperator, decimal points, negative numbers, the number zero and so on. And such cases always make Regular Expression a daunting task if not rather familiar with the syntax.

So I'd say that in your situation, the very easiest way would be to simply check whether the entered number is indeed a number and then cast it to a decimal and simply check whether it's value is between -1000000.000 and 1000000.000 (not inclusive). Decimal.TryParse and an if-statement or two would be all that's needed for this.

Related Solutions

Mysql – Yum update thesql from 5.1 to 5.5

The replication setup you have cannot be maintained when upgrding to MySQL 5.5

You will have to go with Master/Slave. Here is the reason:

Someone asked a question about gibberish appearing in a binary log and I answered this question

In my answer, I explained that binary logs start at different positions in different versions of MySQL

Here are those positions:

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

With replication, these numbers apply to the relay logs as well. The net effect is that a Slave can replicate from an older version of MySQL, but not the other way around.

You are better off doing the following:

Convert Master/Master to Master/Slave
Upgrade to MySQL 5.5 on the Slave
Point all apps at the Slave (now called NewMaster)
service mysql stop on the Master
Upgrade to MySQL 5.5 on the Master (now called NewSlave) but do not start mysql
service mysql stop on NewMaster
rsync NewMaster's /var/lib/mysql to NewSlaves's /var/lib/mysql
rm /var/log/mysql/master.info on NewMaster
rm /var/log/mysql/master.info on NewSlave
service mysql start --skip-networking on NewMaster
run RESET MASTER on NewMaster
service mysql restart on NewMaster
service mysql start on NewSlave
run RESET MASTER on NewSlave
setup Master/Master Replication using position 107 as start master_log_pos

With regard to using yum to upgrade, I run in panic and scream in horror at the thought of it.

Here is my more conservative approach:

I would mysqldump everything EXCEPT the mysql schema.
Run this to generate SQL for all mysql user grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql
Uninstall MySQL 5.1
Install MySQL 5.5 from RPMs by hand.
Load /root/MySQLGrants.sql into MySQL 5.5
Load the mysqldump'd data back into MySQL 5.5

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2, 
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.

Best Answer

Related Solutions

Mysql – Yum update thesql from 5.1 to 5.5

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Related Question