Tag Archives: sql

SQL

Select value from DB table which presents more than one time

duplicates
If you want to see duplicated column values in DB table use next:

 select 
     doc_id,
     count(1)
 from doc_pictures
 group by doc_id
 having count(1) > 1

Thanx to http://stackoverflow.com/a/19635626

How to kill session in Oracle DB

oracle
To kill session in oracle we need:

Step 1: Find SID and SERIAL#:

SELECT SID, SERIAL# FROM v$session WHERE username = 'TEST';

Step 2: Try to disconnect session:

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

Or:

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

If doesn’t help, use next step.

Step 3: Run kill session command:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

If doesn’t help, use next step.

Step 4: Kill system process. Firstly find SPID:

SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = XXX;

And then for windows:

orakill ORACLE_SID spid

Or for linux:

kill spid

For more info, read here:
http://oracle-base.com/articles/misc/killing-oracle-sessions.php
http://www.ora00600.com/wordpress/articles/kill-oracle-session/

Using JDBC

jdbc
Next snippet shows using JDBC:

import java.sql.*;

public class DBConnect {

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmtSelect = null;
		PreparedStatement stmtInsert = null;
		PreparedStatement stmtUpdate = null;
		PreparedStatement stmtDelete = null;
		Statement stmtBatchInsert = null;
		Statement stmtBatchUpdate = null;
		ResultSet rs = null;

		try {
			Class.forName("com.pointbase.jdbc.jdbcUniversalDriver");
			String connectionString = "jdbc:pointbase:server://localhost:9092/nameOfDB";
			conn = DriverManager.getConnection(connectionString, "PBPUBLIC",
					"PBPUBLIC");

			// add
			stmtInsert = conn
					.prepareStatement("insert into users "
							+ "(NAME, ADDRESS, USERNAME, PASSWORD, ACCESSLEVEL)"
							+ " values (?, ?, ?, ?, ?)");
			stmtInsert.setString(1, "Superman");
			stmtInsert.setString(2, "Earth");
			stmtInsert.setString(3, "spet");
			stmtInsert.setString(4, "123456");
			stmtInsert.setString(5, "0");
			int resInsert = stmtInsert.executeUpdate();
			System.out.println("resInsert: " + resInsert);
			
			// upd
			stmtUpdate =conn.prepareStatement("update users set ADDRESS = ? where name='Superman'", Statement.RETURN_GENERATED_KEYS);
			stmtUpdate.setString(1, "Mars");
			int resUpdate = stmtUpdate.executeUpdate();
			System.out.println("resUpdate: " + resUpdate);

			// del
			conn.setAutoCommit(false);
			stmtDelete = conn
					.prepareStatement("delete from users where name=?");
			stmtDelete.setString(1, "Superman");
			int resDelete = stmtDelete.executeUpdate();
			System.out.println("resDelete: " + resDelete);
			
			// batch insert
			stmtBatchInsert = conn.createStatement();
			stmtBatchInsert.addBatch("insert into users values ('User1', 'Earth', 'p1', '123456', 0)");
			stmtBatchInsert.addBatch("insert into users values ('User2', 'Earth', 'p2', '123456', 0)");
			stmtBatchInsert.addBatch("insert into users values ('User3', 'Earth', 'p3', '123456', 0)");
			conn.setAutoCommit(false);
			int[] resBatchInsert = stmtBatchInsert.executeBatch();
			System.out.println("resBatchInsert: " + resBatchInsert);
			conn.commit();
			
			// batch update
			stmtBatchUpdate = conn.createStatement();
			stmtBatchUpdate.addBatch("update users set ADDRESS = 'no 111' where userid = 129");
			stmtBatchUpdate.addBatch("update users set ADDRESS = 'no 222' where userid = 130");
			stmtBatchUpdate.addBatch("update users set ADDRESS = 'no 333' where userid = 131");
			conn.setAutoCommit(false);
			int[] resBatchUpdate = stmtBatchUpdate.executeBatch();
			System.out.println("resBatchUpdate: " + resBatchUpdate);
			conn.commit();

			// get
			stmtSelect = conn.createStatement();
			stmtSelect.setFetchSize(1);
			rs = stmtSelect.executeQuery("select * from users");
			while (rs.next()) {
				String s = rs.getString("NAME");
				System.out.println(s);
			}
			
			// stored proc
			CallableStatement callableStatement = conn.prepareCall("{call CountAllOrdersSum(?, ?)}");
			callableStatement.setString(1, "In Progress"); 
			callableStatement.registerOutParameter(2, java.sql.Types.DOUBLE);
			callableStatement.execute();
		    double resCall = callableStatement.getDouble(2);
		    System.out.println("resCall: " + resCall);
		    callableStatement.close();
			

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(e.getSQLState());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmtSelect.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmtInsert.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmtUpdate.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmtDelete.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.rollback();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Don’t forget to add to classpass JDBC-driver for your database.
Also, see JDBC Home

Sql queries under transaction in Groovy

trans
When you wish to perform some sql queries in transaction, use withTransaction method:

this.class.classLoader.rootLoader.addURL(
  new URL("file:///$jbossPath/lib/ojdbc6.jar"))
Sql sql = Sql.newInstance("ds.url", "ds.user.name", 
  "ds.user.password", "oracle.jdbc.driver.OracleDriver")

sql.withTransaction {
  sql.eachRow("SELECT * FROM USERS") {
    sql.executeUpdate("update USERS set PASSWORD = ? where USER_ID = ?", 
      ["123456", it.USER_ID])
  }
}

MySQL backup/restore from command line

backup:
# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore:
# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

SQL

Difference between SQL query restriction and join-restriction

For ex. you have simple join (Query 1):

SELECT * FROM table1
LEFT JOIN table2 ON table1.column1=table2.column2

and the other query is (Query 2):

SELECT * FROM table1,table2
WHERE table1.column1=table2.column2

What is the difference?

Query 1 gives you more records than Query 2. Case, in result will present ALL rows from table1 and columns of table2 = NULL
The second query results coinciding rows only.

SQL

Select records in specific order in SQL


In order to obtain result records in spec. order you can apply next query:

string strRowIDs = "NULL";
string strSelectOrder = string.Empty;
if (aRowIDs.Count > 0)
{
	int nCnt = 0;
	strRowIDs = string.Empty;
	strSelectOrder = "select_order = case _id";
	foreach (int i in aRowIDs)
	{
		strSelectOrder += " when " + i.ToString() + " then " + (nCnt++).ToString();
		strRowIDs += "," + i.ToString();
	}
	strSelectOrder += " end";
}
string strWhere = "_id in (" + strRowIDs.Trim(new char[] { ',' }) + ")";

// Get data
/*
select *,strSelectOrder
from XXX
where strWhere 
order by select_order
*/

where aRowIDs- set of IDs
select_order – new order