Category Archives: RDBMS

DB, Java, JDBC, RDBMS, SQLServer

[Solved] SQLServerException: String or binary data would be truncated

Leave a comment

This SQLServerException is common with the applications using the MSSQL database. Once it occurs it generate the below stackTrace.

Exception Stack Trace

com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

Reason of Exception

This exception occurred when you are trying to insert text in a column of type varchar which is more than the size of defined column size then SQL server through this exception “SQLServerException: String or binary data would be truncated“.

Solutions

You can follow any of these processes to resolve this issue:

  • Apply validation for text length on the source frontend/client where you in insert the values. It should be less than or equal to size of column.
  • Apply truncation on text before inserting to the database and it should be less than the column size.
  • Increase the sufficient size of the column based on you requirement to resolve this issue.

Hope these processes resolved this issue. Please share your response in comments.

Happy Learning !!!

Architect, RDBMS, Vs

DBMS VS RDBMS

Leave a comment

RDBMS is extension of DBMS.

DBMS (Database Management System)

Software that provides a systematic way of creating, retrieving and managing data in a database.

DBMS Example : File System, XML, Dbase, Microsoft Access, LibreOffice Base, Fox Pro etc.

RDBMS(Relational Database Management Information System)

A DBMS based on relational model and store data in tables that related to each other.

RDBMS Example: MySQL, PostGre, SQL Server, Oracle, MariaDB, SqlLite etc.

Here area some main points that make differences between DBMS and RDBMS.

DBMS RDBMS
DBMS application stores data in files that have no relationship. RDBMS application stores data in tabular form which will have a relation between tables.
DBMS store data either in hierarchical form or navigational form. RDBMS table has one identifier called as primary key and data stored in tabular form.
DBMS does not support Normalization. RDBMS supports normalization.
DBMS does not provide any security with regards to data manipulation. RDBMS defines the integrity constraints for the purpose of ACID property.
DBMS does not support distributed database. RDBMS support distributed database.
DBMS is for small organization to deal with small data. It supports for single user. RDBMS is designed to handle large amount of data. It supports multiple users.
DBMS does not support client server architecture. RDBMS  support client server architecture.
DBMS have high data redundancy. RDBMS have low data redundancy.
DBMS takes more time to access data. RDBMS takes less time to access data.
Architect, RDBMS, Vs

ACID Vs BASE for Database Transactions

Leave a comment

In the previous post, you have learned about the ACID and BASE properties of database transactions in detail.

ACID vs BASE

Here you will know about high-level differences between ACID and BASE.

 

ACID BASE
Provides Vertical Scaling Provides Horizontal Scaling
Strong Consistency Weak Consistency – Stale Data OK
Isolation Last Write Wins, availability first
Transaction Programmer Managed
Available/Consistent Available/Partition Tolerant
Robust Database/Simple Code Simpler Database, Harder Code
Focus on “Commit” Best Effort
Nested Transactions Approximated Answers
Less Availability Aggressive (optimistic)
Conservative (pessimistic) Simpler
Difficult Evaluation(i.e Schema) Faster, Easier evolution
High Maintenance Cost Low Maintenance Cost
Expensive Joins and Relationship Free from joins and Relationship
Examples: Oracle, MySQL, SQL Server, etc. Example : DynamoDB, Cassandra, CouchDB, SimpleDB etc.

See Also:

Architect, RDBMS, Vs

SQL vs NoSQL

Leave a comment

In this post you will know about the main high level difference between SQL and NoSQL type databases.

SQL vs NoSQL

SQL NoSQL
Relational Type Non-Relational Type
Structured Data Stored in Tables The un-structured data store in JSON format in file but graph of database show relationship.
Strict Schema Dynamic Schema
Vertical Scalable Horizontal Scalable
Structured Query Language Un-structured Query Language
ACID Transactions CAP Theorem
Requires downtime In most cases automatic, No outage required
Rigid schema bound to the relationship Non-rigid schema and flexible.
Helpful to design complex queries. No joins relationship, no any powerful tool to prepare complex queries.
Recommend and best suited for OLTP (Online Transactional Processing) Systems. Less likely to be considered for the OLTP System.
Storage : Table (Row->Entity, Column->Attribute)
RDBMS: Oracle, MYSQL, SQL Server, IBM DB2 etc.		 Storage:
Key-Value: Redis, Dynamo
Document: MongoDB
Graph: Neo4j, InfiniteGraph
Wide-column- Cassandra, HBASE
SQL is not fit for Hierarchical work. NoSQL is the best fit for hierarchical work as it follows the key-value pair’s way to store values.
Architect, RDBMS

BASE Properties for Distributed Database Transactions

1 Comment

In the previous post, you have learned about ACID properties of the database transactions for traditional databases. As transactions are growing over the internet, systems are scalable and distributed. In some of the systems where availability is more important than the consistency.

For Example, Amazon, eBay, etc.

For such types of systems in 2000, Eric Brewer’s introduce a theorem that’s called CAP Theorem. It states that

“In a distribution system can only have two out of following three Consitentency, Availability, and Partition Tolerance- One of them must be a sacrifice. You can’t promise all three at a time across reading/write requests.”

Based on CAP theorem, where scalability and availability is most important. It introduces alternatives to ACID is BASE for distributed database transactions.

BASE full form:

BASE

Basically Available

The system guarantees availability.

It majorly focuses on availability, potentially with outdated data and it does not provide guarantee on global data consistency across the entire system.

Soft-state

The state of the system may change over time.

Even without explicit state updates, data may change due to the asynchronous propagation of updates and nodes that become available.

Eventual consistent

The system will eventually become consistent.

Updates eventually propagated, the system would reach in a consistent state if no further updates and network partitions fixed.

See Also:

Architect, RDBMS

CAP Theorem

Leave a comment

Now a days, most of the enterprise based applications are distributed (a collection of interconnected nodes that shared data) over the internet/cloud so that increases the availability of systems. As the application grows and in terms of users and transactions counts and required persistence than big concern is database scalability.

After considering such facts In the year 2000, Eric Brewer developed one theorem that is called as CAP Theorem or Brewer’s conjecture.

CAP Theorem, states that:

“In a distribution system can only have two out of following three Consitentency, Availability, and Partition Tolerance- One of them must be a sacrifice. You can’t promise all three at a time across reading/write requests.”

  • Consistency: Every read request receives the most recent write or an error.
  • Availability: Every request should receive a (non-error) response, without the guarantee that it contains the most recent write.
  • Partition Tolerance: The system continues to work despite an arbitrary number of messages being dropped/delayed by the network between nodes/partitions.

Cap Theorem

In the CAP theorem, consistency is quite different from the ACID database transactions. In distributed systems, partition tolerance means the system will work continue unless there is a complete network failure. If a few nodes fail then the system should keep going.

CAP Theorem Example

You can decide your system technologies based on your primary importance for Consistency, Availability and Partitioning Tolerance. Here we are just taking one example base on database selection:

CA (Consistency + Availability) Type

In this system consistency and availability is primary constraints but such type of system not provide a guarantee of one of the system is offline then the whole system is offline. Otherwise, some of the nodes will not consistent and also not have the latest information.

For Example, Oracle and MySQL are good with Consistency and Availability but not partition tolerant.

CP (Consistency + Partition Tolerant) Type

In this system, consistency and partition tolerance is primary constrains but such a system not provide a guarantee for availability and throws an error as long as the partitioned state not resolved.

For Example, Hadoop and MongoDB stored redundant data in multiple slave nodes and it tolerates an outage of a large number of nodes in the cluster.

AP (Availability + Partition Tolerant) Type

Such a system can not guarantee consistency because if updates can be made to either of a node if some nodes or network issues. This system can have different values on different nodes.

For Example, CouchDB, Dynamo DB, and Cassandra PA type database.

Note CouchDB and Dynamo DB store values in key-value pairs while Cassandra store values in the form of a column family.

See Also:

References

Architect, RDBMS

ACID Properties for Database Transactions

1 Comment

ACID is one of the main concepts or compliance to handle transactions in relational database systems (RDBMS). Full form of the ACID acronym is :

ACID

Here you will know about these concepts in detail:

Atomicity

Each Database transaction must completely succeed or failure/rolled back.

This concept state that partially success not allowed. Each transaction should completely successful or failed. Suppose your transactions have multiple steps (either read or write ) to perform operations in the database then if one operation gets failed then your database will return in the same state it was before starting the transactions.

Consistency

A transaction can not leave the database in an inconsistent state.

This concept state that if your data is replicated across multiple nodes(copies of the database) then these nodes should have the same information.

Isolation

One transaction can not interfere with Others.

This concept state that one client database transactions should not interfere with other client transactions. If one transaction is taking place for the same records then other transactions should wait.

Durability

Completed transactions should persist, even when servers restart etc.

This concept states that once your transaction got completed then this information should not be lost even system is powered off or restart. It should store in nonvolatile storage like the hard drive.

ACID Example

The best example of an ACID is transferring money from one account to another account. There may be two databases with different banks, so this transaction will perform withdrawal from one account while depositing on another account. If one of the steps got failed then return both database state to initial from where get started. The balance should be consistent on all nodes and if someone also performing some transaction at the same time that should be in the waiting state so that it keeps it isolated. If the transaction got completed it should durable by maintaining state in data.

In the case of ACID, other transactions must wait to complete the current transaction. This waiting causes the performance issue. In this case for maintaining consistency and improve performance, the solution is vertical scaling so that getting more get more powerful systems to process each transaction in less time and become available to process other requests.

See Also:

RDBMS

Database Integrity Constraints

Leave a comment

Database integrity constraints are a set of rules, defined by database administrator or application developer to maintain the assurance of accuracy and consistency of data. It ensures that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not violated.

Types of Integrity Constraint

  1. Domain Constraints
  2. Primary Integrity Constraints
  3. Referential Integrity Constraints
  4. Key Constraints

Domain Constraints

Domain constraints can be determined as the valid set of values for an attribute. The data type of database includes integer, character, string, currency, date and time, etc. The value of the attribute must be available in the corresponding database..

Primary Integrity Constraints

The primary/entity integrity says that every table must have a primary key and column or columns chosen for the primary key should be unique or not null. The Primary key is used to identify individual row or record from the entity. If the primary key has a NULL value, then we can’t identify those rows or records.

Referential Integrity Constraints

A referential integrity constraint concerns the concept of a foreign key.  It states that any foreign-key value can only be in one of two states as the primary key of the relative table or null value. Null represents no relation between table.

Key Constraints

Keys constraints are the entity set rule that is used to identify a record within its entity uniquely. An entity can have multiple keys, but out of which one key will be the primary key as given above in Primary Integrity Constraints.

Types of Data Integrity

The following types of data integrity rules applied by developers or data administrators on  tables :

  • Nulls:  Define a rule for the column to allows or disallows Null value in inserts or updates of rows. When referenced type column data is updated or deleted, all associated columns data is set to NULL.
  • Unique Column Values: Define a rule on a column or set of columns that allow only insert or update of a row with a unique value for the column or set of columns.
  • Primary Key Values: Define a rule on a column or set of columns so that each row or record in the table can be uniquely identified by the values in the column or set of columns.
  • Referential Integrity: Define a rule on a column or set of columns in a table that allows the insert or update of a record only if the value for the column or set of columns (foreign key value) matches a value in a column of a related table (the referenced value/ primary key value).
  • Restrict: A referential integrity rule that restricts the update or deletion of referenced data.
  • Set to Default: When referenced column data is updated or deleted, all associated dependent column data is set to a default value.
  • Cascade: When referenced column data is updated, all associated dependent column data is correspondingly updated; when a referenced row/record is deleted, all associated dependent rows/records are deleted.
  • Database triggers: Define rules to invoke triggers when particular column or table perform operations insert, update or delete and called procedures. Triggers used in the following situations:
    • Update or delete dependent data when above referential integrity rules not able to handle. For Ex:  update and delete SET NULL, update and delete SET DEFAULT or update CASCADE.
    • Call the procedure from different nodes (distributed database) when child and parent tables are on different nodes.
    • Apply business rules apart from the above data integrity constraints.

     

References

https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch7.htm

Java, JDBC, RDBMS

JDBC : Stored Procedure Example

Leave a comment

A SQL stored procedure (SP) is a collection SQL statements , SQL command logic, which is compiled and stored on the database and execute on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

See Also :

Types of parameters of Stored Procedure:

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute procedures by JDBC instead of writing of procedure.

Table and Procedure :  Here is small procedure to getStudentName based on student id.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('1', 'Saurabh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('2', 'Rajesh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('3', 'Ramesh Gupta');

Create Stored procedure
USE `facingissuesonitdb`;
DROP procedure IF EXISTS `getStudentName`;

DELIMITER $
USE `facingissuesonitdb`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentName`(in student_id integer, out first_name varchar(25))
BEGIN
select name into  first_name from student where id=student_id;
END$

DELIMITER ;

Java code to execute stored procedure by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import connection.MyConnection;

public class StoredProcedureTest {

	private static final String PROCEDURE_QUERY = "{call getStudentName(?,?)}";

	public static void main(String[] args) {
	 try {
             //to get connection code check blog for Connection according to your need.
	     Connection conn = MyConnection.getConnectionMain("MySql");
	     CallableStatement csmt = conn.prepareCall(PROCEDURE_QUERY);
	     csmt.setInt(1, 2);
	     // Because second parameter is OUT so register it
	    csmt.registerOutParameter(2, java.sql.Types.VARCHAR);
	    csmt.execute();
	     String firstName = csmt.getString(2);// for retrieving out parameter
											     // after execution
	     System.out.println(firstName);
	    } catch (SQLException ex) {
			ex.printStackTrace();
	   }
	}
}




Output



MySql Connection created successfully !!
MySql Connection Creation end !!
Rajesh Gupta



More on JDBC


Follow below links to know more on JDBC and solving JDBC issues :