Meherchilakalapudi.. writes for u….

About my Guru

Posted by meherchilakalapudi on January 9, 2009

HAPPY X-MAS AND NEW YEAR FOR ALL THE READERS

“ Follow Your Dream…
Trouble arrives in measures,
and we stack it up real high,
until we’re convinced,
we have no reason to try.
If you feel defeated,
you’re absolutely wrong,
for if you follow your dream,
you could never lose for long.
Ignore the minor set-backs
that pile up and trouble you,
or you will build a mountain,
out of the stones hurled at you.
The future holds great promise,
your destiny unknown,
but God is always helping,
and you’re never alone.
Soar bravely toward your goal.
Let nothing darken the way.
You can change your tomorrow,
if you seek your dream today.”

“I have been to the end of the waters.
I have been to the end of the sky.
I have been to the end of the mountains.
I have found none that are not my friends.

Though it’s true
that I’ve found many
friends along life’s road,
and many have brightened my life
and helped shape who I am,
no one has
touched my life like you.
You are my best friend,
and you always will ..”

…..Meher

(meher_04@rediffmail.com)

Posted in Uncategorized | Leave a Comment »

Jdbc with SQL SERVER 2000 OR 2005

Posted by meherchilakalapudi on February 8, 2010

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
"USERID", "PASSWORD");

CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");

proc_stmt.setString(1, "employee");
ResultSet rs = proc_stmt.executeQuery();

    if (rs.next()) {
      int employeeId = rs.getInt(1);
      System.out.println("Generated employeeId: " + employeeId);
    } else {
      System.out.println("Stored procedure couldn't generate new Id");
    }
  }
}

Connect to a database and read from table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class Main {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

Connection m_Connection = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=MyDatabase", "userid", "password");

Statement m_Statement = m_Connection.createStatement();
String query = "SELECT * FROM MyTable";

ResultSet m_ResultSet = m_Statement.executeQuery(query);

    while (m_ResultSet.next()) {
      System.out.println(m_ResultSet.getString(1) + ", " + m_ResultSet.getString(2) + ", "
          + m_ResultSet.getString(3));

}
}
}

Create a sensitive scrollable result set

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);

    Statement stmt = connection.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
  }
}

Creating a SQLServer Table to Store Java Types

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);

    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    //     Column Name          SQLServer Type           Java Type
    String sql = "CREATE TABLE sqlserver_all_table("
        + "col_boolean          BIT, "                // boolean
        + "col_byte             TINYINT, "            // byte
        + "col_short            SMALLINT, "           // short
        + "col_int              INTEGER, "            // int
        + "col_float            REAL, "               // float
        + "col_double           DOUBLE PRECISION, "   // double
        + "col_bigdecimal       DECIMAL(13,0), "      // BigDecimal; can also be NUMERIC(p,s)
        + "col_string           VARCHAR(254), "       // String
        + "col_date             DATETIME, "           // Date
        + "col_time             DATETIME, "           // Time
        + "col_timestamp        TIMESTAMP, "          // Timestamp
        + "col_characterstream  TEXT, "               // CharacterStream or AsciiStream (< 2 GBytes)
        + "col_binarystream     IMAGE)";              // BinaryStream (< 2 GBytes)

stmt.executeUpdate(sql);
}
}

Getting the Number of Rows in a Table Using a Scrollable Result Set

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);

Connection connection = DriverManager.getConnection(url, username, password);

    // Create a scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

// Move to the end of the result set
resultSet.last();

// Get the row number of the last row which is also the row count
int rowCount = resultSet.getRow();

}
}

Determining If a Database Supports Updatable Result Sets: An updatable result set allows modification to data in a table through the result set.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);

    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dmd = connection.getMetaData();
    if (dmd.supportsResultSetConcurrency(
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {
        System.out.println("Updatable result sets are supported");
    } else {
        System.out.println("Updatable result sets are not supported");
    }
  }
}

Updating a Row in a Database Table Using an Updatable Result Set

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);

    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

resultSet.first();

// Update the value of column col_string on that row
resultSet.updateString("col_string", "new data");

// Update the row; if auto-commit is enabled, update is committed
resultSet.updateRow();

}
}

Calling a Stored Procedure in a Database with no parameters

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);

    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{call myproc}");
    cs.execute();
  }
}

Get all table schemas

String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);

Connection connection = DriverManager.getConnection(url, username, password);

DatabaseMetaData dbmd = connection.getMetaData();

String[] types = { "TABLE" };
ResultSet resultSet = dbmd.getTables(null, null, "%", types);

while (resultSet.next()) {
String tableName = resultSet.getString(3);

      String tableCatalog = resultSet.getString(1);
      String tableSchema = resultSet.getString(2);
    }
  }
}

Connect to database and call stored procedure

Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
"USERID", "PASSWORD");

CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");

proc_stmt.setString(1, "employee");
ResultSet rs = proc_stmt.executeQuery();

Call a stored procedure with no parameters and return value.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    CallableStatement proc_stmt = null;

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
        "USERID", "PASSWORD");
    proc_stmt = con.prepareCall("{ call someStoredProc() }");

proc_stmt.executeQuery();

}
}

Posted in Jdbc with SQL SERVER 2000 OR 2005 | Leave a Comment »

« Previous Entries

Meherchilakalapudi.. writes for u….

Just another WordPress.com weblog

Categories

Archives

Blogroll

Meta

Subscribe

About my Guru

Jdbc with SQL SERVER 2000 OR 2005