Java Solutions: Database

Showing posts with label Database. Show all posts

Thursday, January 17, 2019

Solved - Change the data type of a column which has already had data; ORA-01439: column to be modified must be empty to change datatype

Lets say you have a column, Level, in your Product table. The original data type of the column is Varchar. Now, you want to change the data type to Number.

If you simply use "ALTER TABLE Product MODIFY Level NUMBER", you will have the following error.

ORA-01439: column to be modified must be empty to change datatype

To work around this problem, following the following steps.

1. Add a temporary column to the Product table with the required new data type.

ALTER TABLE Product ADD Level_temp NUMBER;

2. Use your data conversion logic, convert the data in column Level to NUMBER and add that value to the corresponding Level_temp column.

UPDATE Product SET Level_temp = <the converted value> where Level = <original value>;

If you don't need to convert the data, you can do the following.
UPDATE Product SET Level_temp = Level;

3. Drop the Level column.

ALTER TABLE Product DROP COLUMN Level;

4. Rename the Level_temp to Level.

ALTER TABLE Product RENAME COLUMN Level_temp TO Level;

-----------------------------------------------------------------------------------------------------------------
Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Thursday, October 6, 2016

postgresql alter table hangs

When the SQL, "Alter table Fruit add Column Location VarChar(30))", is executed at the psql terminal, it hangs.

It turns out that there is a lock on a table. The SQL below helps find all the locks in your database.

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Then use the SQL below to kill the blocking process.

select pg_terminate_backend(<blocking_pid>);

Reference:

Lock Monitoring

---------------------------------------------------------------------------------------------------

Monday, November 3, 2014

Oracle: Save query result to a local spread sheet or text file with fields separated by comma, pipeline, or other characters

At the command prompt, type sqlplus <username>/<password>, then hit enter.

Do the following to save to a file.

SQL> SPOOL <file path/file name><.txt or .csv> CREATE/APPEND/REPLACE

(By default, the option is REPLACE if you don't specify it.)

SQL> SET colsep <delimiter such as , or |>

//Print the column header to the output file. Default is ON
SQL> SET head ON/OFF

//Print underline beneath the column headers. Default is ON
SQL> SET UNDERLINE ON/OFF

//Print the total number of rows selected
SQL> SET FEEDBACK ON/OFF

SQL> <your SELECT sql statement>

SQL> SPOOL OFF

-------------------------------------------------------------------------------------------------------------

Friday, July 11, 2014

SQL: select another column if the column value is null/0/certain value

How to construct a sql to select from a column only when the value of the column is not a certain value (e.g. null, 0, etc), otherwise select from another column?

Lets assume that you have two tables, Fruit and Apple, in your database.

table Fruit
name price
Apple 1.69
Orange 0.98
Pear 1.29

table Apple
name price
Golden Delicious 1.49
Fuji 1.89
Gala 0

The following sql gets the price for each type of apple. If the price in the Apple table for that type of apple is 0, then use the Apple price in the Fruit table.

SELECT a.name name
CASE a.price //the column
WHEN 0 THEN f.price //if a.price=0, gets f.price
ELSE a.price //else gets a.price
END AS price //the resulting field name is price
FROM Apple a, Fruit f
WHERE f.name='Apple';

The general form of the sql for getting value from another column if value is a certain known value is as below.

SELECT
CASE <columnA>
WHEN <value1> THEN <columnB> //e.g. WHEN NULL or WHEN LIKE '%ard%'
WHEN <value2> THEN <columnC>
......
ELSE <columnA>
END AS <field name in the result set>
FROM <list of tables>

-----------------------------------------------------------------------------------------------------------------

Thursday, June 5, 2014

java.sql.SQLException: Fail to convert to internal representation

Stack Trace:
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:239)
at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:558)

The above exception occurs when a field data is retrieved from the ResultSet using a wrong data type that does not match the data type in the database, for example retrieving a varchar field in the database table from the ResultSet using getInt() or retrieving a integer field with getLong(), etc.

If you are using a counter such as

int counter = 1;
while (rs.next()) {
rs.getString(counter++);
rs.getInt(counter++);

...........
}

Check your code to make sure that the order of the fields in the select clause of your sql statement is in the same order as you retrieving the data from the result set (though this is not required for processing a result set). The data type of the get method has to match the data type of the field in the database.

-----------------------------------------------------------------------------------------------------------

Tuesday, April 15, 2014

CLOBs, NCLOBs and BLOBs

CLOB, NCLOB and BLOB are database data types with data size up to 128 terabytes. The data size of regular VARCHAR and VARBINARY is 32 kilobytes.

CLOB stands for character large object, which is a Unicode character string. It is appropriate for storing text-orientated information.

NCLOB stands for national character large object, which is a Unicode character string. This data type like the CLOB data type, is appropriate for storing text-orientated information.

BLOB stands for binary large object, which is a binary string. This data type is appropriate for storing images, sound, and videos.

Following is a sample code of using these data types.

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.PreparedStatement;

public class Test {
public static void main(String[] args) {
Connection conn = null;
try {
conn = <your method of getting the database connection>;

//Save data to database

//Create the data type objects
Clob theClob = conn.createClob();
NClob theNclob = conn.createNClob();
Blob theBlob = conn.createBlob();

//Add data to the data type objects
BufferedReader charReader = new BufferedReader(
new FileReader("descritpion.txt"));
StringBuilder file = new StringBuilder();
String line = "";
while ((line = charReader.readLine()) != null){
file.append(line);
}

theClob.setString(1, file.toString());
theNclob.setString(1, file.toString());
charReader.close();

BufferedInputStream input = new BufferedInputStream(
new FileInputStream("happyBird.gif"));

OutputStream byteWriter = theBlob.setBinaryStream(1);
int theChar = -1;
while ((theChar = input.read()) != -1) {
byteWriter.write(theChar);
}
byteWriter.flush();
input.close();
byteWriter.close();

//Insert the data into database
String sql = "INSERT into picture values(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setClob(1, theClob);
pstmt.setNClob(2, theNclob);
pstmt.setBlob(3, theBlob);
pstmt.executeUpdate();
pstmt.close();

//Retrieve data from database

String sql2 = "SELECT title, description, image from picture";
pstmt = conn.prepareStatement(sql2);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Clob myClob = rs.getClob(1);
String title = myClob.getSubstring(1, myClob.length());

NClob myNclob = rs.getNClob(2);
String description = myNclob.getSubstring(1, myNclob.length());

Blob myBlob = rs.getBlob(3);
byte[] imageArray = myBlob.getBytes(1, myBlob.length());
}
rs.close();
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
}

References:

1.Using Large Objects
2. 26 Oracle Data Types

---------------------------------------------------------------------------------------------------------------

Friday, April 11, 2014

ERROR 42Y07: java.sql.SQLException: Schema 'abc' does not exist

A. This exception throws when the schema does not exist in the database. A schema is a structure to group a set of tables, views and other data and operations.

To fix it, create the schema by executing this SQL statement:
Create SCHEMA <schema name>;

In Oracle, the above SQL does not actually create a schema. A schema is created when a user is created.
Create USER <username> IDENTIFIED BY <password>;

To create table and view within the schema in Oracle:
Create SCHEMA AUTHORIZATION <username>
<create table statement>
<create view statement>
<grant statement>;

B. This type of exception may also throw when you are referring a table that is not in your default/current schema. In this case, you need to add the schema to the entity you are referring such as <schema name>.<table name>.<column name>

References:
1. CREATE SCHEMA statement
2. ERROR 42Y07: Schema 'SchemaName' does not exist.

--------------------------------------------------------------------------------------------------------------

Working with Java DB in NetBeans

Java DB is part of the java JDK from version later than 5. To use Java DB in the NetBeans IDE, follow the steps below.

A. Create a Java DB Database in NetBeans

Click Window on the top menu and choose Services.
In Services window, expand the Database node,
Right click on Java DB and choose Properties to view the installation and location of the database
Right click on Java DB and choose Start Server
Right click on Java DB again and choose Create Database. Enter the Database Name, User Name and Password, and click OK.

Now the database is ready to use.
Driver: org.apache.derby.jdbc.EmbeddedDriver
URL: jdbc:derby:<database name>

B. Make connection to database

Expand the Java DB node
Right click on you database name and choose connect
A connection node with the text "jdbc:derby://localhost:1527/<database name> [<username> on <username in upper case>]" appears under the Drivers

C. Create table, view, and execute query

Expand the connection node created in B
Expand the node whose text corresponds to your schema

3. Use the Create Dialog
1). To create a table, right click on the Tables node and choose Create Table. In the pop up window, enter your table name, and use the Add column button to add columns, then click OK.
2). To create a view, right click on the Views node and choose Create View. In the pop up window, enter your view name and the SQL to created the view, then click OK.

4. Use the SQL Editor
1). Right click on the connection node, or the Tables node, or the Views node, or a table node, choose Execute Command.
2). In the SQL editor appeared in the main pane of the NetBeans, enter your SQL statement
3). Highlight the SQL you want to execute and click the Run SQL button (Ctrl+Shift+E) on top of the SQL editor to execute

If you would like to know how to communicate with your Java DB database through your java code, please read my post Java DB, the Oracle open source database for sample code.

References:

1. Java DB, the Oracle open source database
2. Working with the Java DB (Derby) Database

--------------------------------------------------------------------------------------------------------------

Thursday, April 10, 2014

Java DB, the Oracle open source database

Java DB is a full-featured, easy-to-use, open source database which is the Apache Derby open source database included in the java JDK starting version 5.

DBMS or Vendor: derby
Driver: org.apache.derby.jdbc.EmbeddedDriver
URL: when it is used before the database is created, the URL is jdbc:derby:<database name>;create=true, afterwards, the URL is simply jdbc:derby:<database name>

You need to include the jars in the <java home>\db\lib directory in your project classpath for using the database.

Following is a sample code of using the Java DB.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JavaDBTest {
private Connection conn = null;
private boolean databaseCreated = false;

private void createDatabase()
throws ClassNotFoundException, SQLException {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
conn = DriverManager.getConnection(
"jdbc:derby:testDB;create=true");
databaseCreated = true;
}

public Connection getConnection()
throws ClassNotFoundException, SQLException{
if (conn != null) return conn;

if (databaseCreated) {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
conn = DriverManager.getConnection(
"jdbc:derby:testDB");
} else {
createDatabase();
}
return conn;
}
public static void main(String[] args){
JavaDBTest test = new JavaDBTest();
try {
Connection connection = test.getConnection();

DatabaseMetaData dmd = connection.getMetaData();
System.out.println(dmd.getDriverName());

Statement statement = connection.createStatement();
statement.execute("create table fruit (name varchar(30), value varchar(20))");
statement.executeUpdate("insert into fruit values ('Apple', '100')");

ResultSet result = statement.executeQuery("select * from fruit");
while(result.next()){
System.out.println("name="+result.getString(1));
System.out.println("value="+result.getString(2));
}
result.close();
statement.close();
connection.close();
}catch(Exception e){
e.printStackTrace();
}

}
}

References:
1.Java DB
2. Working with Java DB in NetBeans

-----------------------------------------------------------------------------------------------------------------

Monday, February 10, 2014

List primary keys/foreign keys by table names containing a particular string

Oracle:

SELECT cc.table_name, cc.column_name
FROM all_constraints ac, all_cons_columns cc
WHERE cc.table_name like '%<your string>%'
AND ac.constraint_type = 'P'
AND ac.constraint_name = cc.constraint_name
AND ac.owner = cc.owner;

For foreign keys, change the ac.constraint_type='P' to ac.constraint_type='R'

DB2:

SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME like '%<your string>%'
AND KEYSEQ > 0
ORDER BY KEYSEQ;

For foreign keys
SELECT a.TBNAME, b.COLNAME
FROM SYSIBM.SYSRELS a, SYSIBM.SYSFOREIGNKEYS b
WHERE A.TBNAME like '%<your string>%'
AND a.CREATOR = B.CREATOR
AND a.TBNAME = b.TBNAME;
ORDER BY a.RELNAME, b.COLSEQ;

MySQL:

SELECT Col.Table_Name, Col.Column_Name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Tab.Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name like '%<your string>%'
ORDER BY Col.TABLE_NAME, Col.ORDINAL_POSITION;

For foreign keys, change Tab.Constraint_Type = 'PRIMARY KEY ' to Tab.Constraint_Type = 'FOREIGN KEY '

-------------------------------------------------------------------------------------------------------------------

Wednesday, February 5, 2014

How to list all the table names containing a particular string in a database with sql?

In Oracle database

1. To get all the tables that you own:

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME like '%<your string>%';

2. To get all the tables that you can access:

SELECT unique TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME like '%<your string>%';

3. If you have access to the DBA_TABLES view:

SELECT unique TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME like '%<your string>%';

4. From the legacy CAT view:

SELECT TABLE_NAME FROM CAT WHERE TABLE_TYPE='TABLE' and TABLE_NAME like '%<your string>%';

DB2 Database

SELECT TABNAME FROM SYSCAT.TABLES WHERE TYPE = 'T' and TABNAME like '%<your string>%';

MySQL Database

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '%<your string>%';

----------------------------------------------------------------------------------------------------------------

Wednesday, January 29, 2014

How to list all the table names in the database with sql?

Oracle:

To get all the tables that you own: select * from user_tables;
To get all the tables that you have access: select * from all_tables;
If you have access to the DBA_TABLES data dictionary view: select * from dba_tables;
From legacy CAT view: select * from CAT where TABLE_TYPE = 'TABLE';
To get all the table columns: select * from all_tab_columns order by owner, table_name, column_id;

DB2:

SELECT * FROM SYSCAT.TABLES
To get all the table columns: SELECT COLNAME FROM SYSCAT.COLUMNS order by TABNAME;

MySQL

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ;
To list all the table columns: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME

-------------------------------------------------------------------------------------------------------------------