Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQLi Articles
Page 271 of 341
What are Lob Data Types? What are the restrictions on these datatypes in JDBC?
A BLOB is binary large object that can hold a variable amount of data with a maximum length of 65535 charactersThese are used to store large amounts of binary data, such as images or other types of files.A CLOB stands for Character Large Object in general, an SQL Clob is a built-in datatype is used to store large amount of textual data. Using this datatype, you can store data up to 2, 147, 483, 647 characters.Blob and Clob data types together are known as LOB (Large Object) datatypes. Following are the restrictions on these datatypes.Cannot compare: We cannot compare CLOB ...
Read MoreWrite an JDBC example to retrieve Clob value from a table using the getCharacterStream() method?
The ResultSet interface provides the method named getClob() to retrieve clob datatype from a table in a database. In addition to this it also provides a method named getCharacterStream()Like getClob() this method also accepts an integer representing the index of the column (or, a String value representing the name of the column) and retrieves the value at the specified column. The difference is unlike the getClob() method (which returns a Clob object) this method returns an object of the Reader class.ExampleAssume we have created a table named MyData in the database with the following description.+---------+--------------+------+-----+---------+-------+ | Field | Type ...
Read MoreHow do we insert/store a file into MySQL database using JDBC?
In general, the contents of a file are stored under Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) datatype in MySQL database.JDBC provides support for the Clob datatype, to store the contents of a file in to a table in a database.The setCharacterStream() method of the PreparedStatement interface accepts an integer representing the index of the parameter and, a Reader object as a parameter.And sets the contents of the given reader object (file) as value to the parameter (place holder) in the specified index.Whenever you need to send very large text value you can use this method.Storing text file using JDBC:If you need to ...
Read MoreResolve error 1045 (28000) access denied for user 'root'@'localhost' (using password: YES)?
To fix this error, you need to specify the -p option for password.The syntax is as followsmysql -uyourUserName -pLet us implement it.First, we need to open CMD using Windows+R shortcut keys. The snapshot is as followsType CMD and press OK button. You will get a command prompt.The snapshot is as followsNow reach the MySQL bin directory.The snapshot is as followsNow use the syntax discussed in the beginning.The command is as follows
Read MoreWhat is the return type of a “count” query against MySQL using Java JDBC?
The return type of count is long. The Java statement is as followsrs.next(); long result= rs.getLong("anyAliasName");First, create a table with some records in our sample database test3. The query to create a table is as followsmysql> create table CountDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CountDemo(Name) values('John'); Query OK, 1 row affected (0.21 sec) mysql> insert into CountDemo(Name) values('Carol'); Query OK, 1 row affected (0.16 sec) ...
Read MoreHow to avoid null result of “SELECT max(rank) FROM test” for an empty table?
You can use COALESCE() along with aggregate function MAX() for this.The syntax is as followsSELECT COALESCE(MAX(`yourColumnName`), 0) FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table avoidNullDemo -> ( -> `rank` int -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into avoidNullDemo values(10); Query OK, 1 row affected (0.20 sec) mysql> insert into avoidNullDemo values(NULL); Query OK, 1 row affected (0.18 sec) mysql> insert into avoidNullDemo values(20); Query OK, 1 ...
Read MoreUse MySQL concat() and lower() effectively
The contact() method is used to concatenate. However, lower() is used to change the case to lowercase. For our example, let us create a table.The query to create a table is as followsmysql> create table concatAndLowerDemo -> ( -> FirstValue varchar(10), -> SecondValue varchar(10), -> ThirdValue varchar(10), -> FourthValue varchar(10) -> ); Query OK, 0 rows affected (0.55 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into concatAndLowerDemo values('John', '12345', 'Java', 'MySQL'); Query OK, 1 row affected (0.21 sec) mysql> insert into concatAndLowerDemo values('Hi', '12345', ...
Read MoreMySQL query to calculate the average of values in a row?
To calculate the average of values in a row in MySQL, use the following syntaxSELECT (yourTableName.yourColumnName1+yourTableName.yourColumnName2+yourTableName.yourColumnName3+, ..........N)/numberOfColumns AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table calculateAverageDemo -> ( -> x int, -> y int, -> z int -> ); Query OK, 0 rows affected (1.41 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into calculateAverageDemo values(10, 20, 30); Query OK, 1 row affected (0.78 sec) mysql> insert into calculateAverageDemo values(40, 50, 70); Query ...
Read MoreHow to determine the current delimiter in MySQL?
To determine current delimiter in MySQL, use the following syntax\sThe above syntax will let you know about the current delimiter. Let us implement the above syntax.The query is as followsmysql> \sThe following is the output-------------- C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe Ver 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL) Connection id: 19 Current database: sample Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 Using delimiter: ; Server version: 8.0.12 MySQL Community Server - GPL Protocol version: 10 Connection: localhost via TCP/IP Insert id: 11 Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: ...
Read MoreWhy should we use MySQL CASE Statement?
Use MySQL CASE for a fixed number of arguments.The syntax is as followsSELECT *, CASE WHEN yourColumName1>yourColumName2 THEN 'yourMessage1' ELSE 'yourMessage2' END AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table CaseFunctionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CaseFunctionDemo(Value1, Value2) values(10, 20); Query OK, 1 row affected ...
Read More