Accessing SQL Server on NetBeans using JDBC, Part 2: Perform SQL Operations

July 14, 2007 Java 51 Comments

This entry is part 2 of 3 in the series Accessing SQL Server on NetBeans using JDBC

Accessing SQL Server on NetBeans using JDBC

Accessing SQL Server on NetBeans using JDBC, Part 1: Create a connection
Accessing SQL Server on NetBeans using JDBC, Part 2: Perform SQL Operations
Accessing SQL Server on NetBeans using JDBC, Part 3: Troubleshooting

Perform SQL Operations

From Part 1, I have only established a connection with local SQL Server. Next Iâ€™ll show how to retrieve and modify data on remote SQL Server.

There are 3 parts:

Part I : Create a connection
This part which you’re reading shows about how to establish a connection between NetBeans and SQL Server. In this example, I use SQL Server 2000 SP4 and NetBeans IDE 5.5
Part II : Perform SQL Operations
This part show how to perform some basic operations from NetBeans with SQL Server. For instance, send querys as SELECT, INSERT, UPDATE to the database.
Part III: Troubleshooting
The last part is about problems and how to fix them.

SQL Server Connection using in this part

Assume that I have SQL Server running remotely on BKKPDC01 computer and I want to connect to Northwind database with username is â€˜saâ€™ and password is â€˜passwordâ€™. The connection string will be

String connectionUrl = "jdbc:sqlserver://bkkpdc01:1433;databaseName=Northwind;user=sa;password=password";

Retrieve data from database

To get some data, I need to execute query on the SQL Server and get the result back to me. First, I create stmt (Statement object) and execute query in SQL language. Then I store the result on ResultSet object and iterative show the result on the output window.

            Statement stmt = null;
            ResultSet rs = null;
            // SQL query command
            String SQL = "SELECT * FROM Products";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                System.out.println(rs.getString("ProductName") + " : " + rs.getString("UnitPrice"));
            }

Code Explanation:
– Statement object is used for sending SQL Statement to the database.
– ResultSet object is used to keep data from the executed query.
– In while-loop, iterative in the ResultSet object to show result (All ProductName and UnitPrice in Products table) on output window.

The example result will be similar to below.

Update data on database

To insert, update and delete records on SQL Server, you can use the code from retrieve data from database and simply change SQL command and also modify some code a little bit. On update, I must use executeUpdate(“SQL”) method on statement object instead executeQuery(“SQL”) and the return value will be rows affected instead of a record set.

Example
INSERT command

            // SQL query command
            String SQL = "INSERT INTO Products (ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReOrderLevel,Discontinued) VALUES ('MyProduct','10 Kg.',1234.0000,100,50,30,0)";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

UPDATE command

            // SQL query command
            String SQL = "UPDATE Products SET UnitPrice = 900, UnitsInStock = 55, UnitsOnOrder = 5 WHERE ProductName = 'MyProduct'";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

DELETE command

            // SQL query command
            String SQL = "DELETE FROM Products WHERE ProductName = 'MyProduct'");
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

Summary

You can download the source code example here (Right-click on the link and select Save target As…).
But you have to change connection string to match your environment. The example code will connect to Northwind database and try to retrieve records, insert a new record, update the record and delete the record from Products table. The result is below.

Series Navigation<< Accessing SQL Server on NetBeans using JDBC, Part 1: Create a connectionAccessing SQL Server on NetBeans using JDBC, Part 3: Troubleshooting >>

51 Comments

Cesar August 18, 2007

Another great one.

Thank you very much
Maricelys September 5, 2007

Hi again

Perfect…

Grettings!
Gunnar ÃžÃ³r Gunnarsson September 21, 2007

When I try example I it works but the variable “con” is shown with a line under it.
when I try example II I get an error:

………NetBeansProjectsTestSQLsrctestsqlMain.java:39: cannot find symbol
symbol : variable con
location: class testsql.Main
stmt = con.createStatement();

What am I doing wrong?
I use NetBeans 6 and SQL 2000
linglom September 22, 2007

I think you haven’t declare variable con yet. I should be a Connection class. Try to review from my source code, I declared as a global variable.
JosÃ© September 29, 2007

very helpfull, it works but I donÂ´t get the results on the output window (profiler shows the select is happening in the database)
src:
package testsql;

import java.sql.*;

/**
* @author Linglom
*/

public class testsql {

private static Statement stmt = null;
private static ResultSet rs = null;
private static Connection con = null;

public static void main(String[] args) {
try {
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);

/* Connection string to connect to a remote server.
* Change the information to match your environment(hostname:port, username and password).
*/
String connectionUrl = “jdbc:sqlserver://localhost:1433;” +
“databaseName=visual_ftlhn;user=jboni0;password=exegis19740427”;

con = DriverManager.getConnection(connectionUrl);
stmt = con.createStatement();

retriveData(“SELECT * FROM pla_inn_ingresos where inn_codcia = 634 and inn_codpla = 20073009”);

int rowsEffected = 0;

// // Example INSERT new record
// rowsEffected = updateData(“INSERT INTO Products (ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,” +
// “ReOrderLevel,Discontinued) VALUES (‘MyProduct’,’10 Kg.’,1234.0000,100,50,30,0)”);
// System.out.println(rowsEffected + ” rows effected”);
// System.out.print(“Insert new record : “);
// retriveData(“SELECT * FROM Products WHERE ProductName = ‘MyProduct'”);
//
// // Example UPDATE the record
// rowsEffected = updateData(“UPDATE Products SET UnitPrice = 900, UnitsInStock = 55, UnitsOnOrder = 5” +
// “WHERE ProductName = ‘MyProduct'”);
// System.out.println(rowsEffected + ” rows effected”);
// System.out.print(“Update the record : “);
// retriveData(“SELECT * FROM Products WHERE ProductName = ‘MyProduct'”);
//
// // Example DELETE the record
// rowsEffected = updateData(“DELETE FROM Products WHERE ProductName = ‘MyProduct'”);
// System.out.println(rowsEffected + ” rows effected”);
// System.out.print(“Delete the record”);
// retriveData(“SELECT * FROM Products WHERE ProductName = ‘MyProduct'”);

} catch (SQLException sqlEx) {
System.out.println(“SQL Exception: “+ sqlEx.toString());
} catch (ClassNotFoundException classEx) {
System.out.println(“Class Not Found Exception: “+ classEx.toString());
} catch (Exception Ex) {
System.out.println(“Exception: “+ Ex.toString());
}
}

/* Update data on the database
*@param SQL an update commandstring (INSERT, DELETE, UPDATE)
*/
public static int updateData(String SQL) throws Exception {
return stmt.executeUpdate(SQL);
}

/* Show result on output window
@param SQL a retrive data command string (SELECT)
*/
public static void retriveData(String SQL) throws Exception {
rs = stmt.executeQuery(SQL);

while (rs.next()) {
System.out.println(rs.getString(“inn_codcia”) + rs.getString(“inn_codtpl”) +
rs.getString(“inn_codpla”) + rs.getString(“inn_codemp”) +
rs.getString(“inn_codtig”) + rs.getString(“inn_valor”) +
rs.getString(“inn_dias”) + rs.getString(“inn_hora_dia”));
}
}
}
JosÃ© September 30, 2007

ha ha ha… IÂ´m Sorry my select statement was no returning rows :).

Very good example, thank you
Rana Guha October 27, 2007

It works perfectly fine;
the Article desciption was quite lucid n explanatory.
thnkz a lot frnd;
Jason November 15, 2007

Thank you so much for this..! But I am having a problem with an SQL exception and i dont know whats wrong.. Please help me out.. Here is the code:

public class testsql {

private static Statement stmt = null;
private static ResultSet rs = null;
private static Connection con = null;

public static void main(String[] args) {
try {
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);

/* Connection string to connect to a remote server.
* Change the information to match your environment(hostname:port, username and password).
*/
String connectionUrl = “jdbc:sqlserver://lucero:1433;” +
“databaseName=dbBookone”;

con = DriverManager.getConnection(connectionUrl);
stmt = con.createStatement();

retriveData(“SELECT * FROM tblLogin”);

} catch (SQLException sqlEx) {
System.out.println(“SQL Exception: 1″+ sqlEx.toString()+”1”);
} catch (ClassNotFoundException classEx) {
System.out.println(“Class Not Found Exception: 2″+ classEx.toString()+”2”);
} catch (Exception Ex) {
System.out.println(“Exception: 3″+ Ex.toString()+”3”);
}
}

public static void retriveData(String SQL) throws Exception {
rs = stmt.executeQuery(SQL);

while (rs.next()) {
System.out.println(rs.getString(“UserName”));
}
}
}

Here is the exception:
SQL Exception: 1com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net
.ConnectException: Connection refused: connect1

the “connect” option is enabled in the SQL server

Really dont know what to do..
linglom November 16, 2007

To Jason,
The problem can occurs by 2 causes.
1. You have mistyped hostname or port number for the SQL server in the conectionUrl string so check the name again.

2. You try to connect to remote SQL server which hasn’t enable the SQL server to accept remote connection yet. To enable, you can see my post at Enable remote connection to SQL Server 2005 Express.
Yaseen Rao December 4, 2007

great stuff, specially about enabling remote connections in sql server 2005, i was struggling with that.
kumar January 10, 2008

String connectionUrl = â€œjdbc:sqlserver://localhost:1433;â€? +
â€œdatabaseName=visual_ftlhn;user=jboni0;password=exegis19740427â€³;
how would i conert this to mysql database :”jdbc:mysql://localhost:1527;” data base name ia =”vist” username is =”kumar” password=”kumar”
plz get me the syntax for it 2 represnt
Adam January 12, 2008

The program compile fine but when I run it I get this message:
SQL Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The TDS prelogin response is incomplete. The target server must be SQL Server 2000 or later.
BUILD SUCCESSFUL (total time: 1 second)

Any idea
Thanks
Adam January 12, 2008

I forgot to mention that I have sql server 7.0 installed on OS Windows 2000.
linglom January 14, 2008

To Adam,
The error message tells that you have used earlier version of SQL Server which is not support. The target server must be SQL Server 2000 or later.

You can visit this thread for more information.
http://forum.java.sun.com/thread.jspa?threadID=725228&tstart=0

To kumar,
For given attributes in your comment, the connection string to MySQL would be like
String connectionUrl = “jdbc:mysql://localhost:1527/vist?” +
“user=kumar&password=kumar”;

Or you can visit my post about MySQL Connection at
http://www.linglom.com/2007/12/05/accessing-mysql-on-netbeans-using-jdbc-part-i-create-a-connection/
nida April 10, 2008

was a very good one and easy to learn .Helped me a lot. thanks a lot
hazky June 19, 2008

thaks 4 u`r tute….
linglom June 20, 2008

Thanks a lot for the comment.
Arjun June 25, 2008

This is probably the best explanation on this topic I found online. Very helpful. Thanx.
Harmeet August 23, 2008

Excellent tutorial to get an initial over view with jdbc and mysql.

Thanks
reno812 September 20, 2008

I was in a hurry to finish a little Java app for a friend that was struggling with a project in school, and this is just what I was looking for. Had a little trouble at first because of my SQL Server not being SP4, but after sorting that everything was on track.

Thanks for such a great helpful topic!
loga September 28, 2008

thanks a lot for this tut!
peter jones February 12, 2009

marvelous, very nice paedagogy. right to the target. I have blank user and blank password fields. I get this error:”SQLSyntaxErrorException: Syntax error: Encountered “[” at line 1 column 13.” netbeans 6.5 Glassfish JDBC.
T February 12, 2009

Is there any tutorials for displaying a result set in a JList or JTable?? thanx.
linglom February 14, 2009

Hi, Peter Jones
Can you show the source code?
mehrvarz May 17, 2009

tnx verry much
ragnor July 15, 2009

Hi! I’m developing an small application with netbeans and I’ve got 2 problems:
-When I do an INSERT in one ‘insert users’ form it doesn’t refresh in the form with the table of users. It’s necessary to exit and run again the application to see the effect of the INSERT.
-My DELETE query in a table with 2 vars doesn’t work. I’ve tried in 2 different ways:
sql = “DELETE FROM usuarios_grupos WHERE (login = ‘”+listbox1.getSelected()+”‘ AND grupo = ‘”+listbox2.getSelected()+”‘)”;
sql = “DELETE FROM usuarios_grupos (login, grupo) VALUES(‘”+listbox1.getSelected()+”‘,'”+listbox2.getSelected()+”‘)”;

However, if the destiny table has only 1 var it works perfectly:
sql = “DELETE FROM usuarios WHERE login = ‘”+listbox1.getSelected()+”‘”;

Anybody can help me? :S
linglom July 16, 2009

Hi, Ragnor

The first question, this article might help – How to Use Tables.

The second question, have you verified that your syntax of the failed query is correct? If query with a parameter is work, others should work, too.
Mon August 17, 2009

Hi Linglom,

You are really a big help!

Thanks.
deathprog August 19, 2009

hi
Thank you very much
i can insert ,delete,show.
but i have an error in update
please help me

public int modify(){
String SQL = “UPDATE stagiaire SET NOM = “+getNom()+” , PRENOM = “+getPrenom()+”, AGE = “+getAge()+”,TEL = “+getTel()+”,CODE_ECOLE =”+getCode_ecole()+” WHERE CODE = “+getCode()+””;
return this.Req_LMD(SQL);

}

//***************************

public int Req_LMD(String req)
{
int v=0;
System.out.println(req);
if(this.Connexion())
{
Statement Stat=null;
try {
Stat=(Statement) Con.createStatement();
v= Stat.executeUpdate(req);
}
catch (Exception e) { e.printStackTrace();}
this.Deconnexion();
System.out.println(“fini”);
}
System.out.println(“Nbre de lignes validÃƒÂ© : “+v);
return v;
}
//*********************

it show me this error

UPDATE stagiaire SET NOM = saad , PRENOM = saad, AGE = 23,TEL = 234,CODE_ECOLE =345 WHERE CODE = 0
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Champ ‘saad’ inconnu dans field list
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1634)
fini
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1553)
Nbre de lignes validÃƒÂ© : 0

thx you .
linglom August 20, 2009
Hi, deathprog

I think that there is an error in SQL syntax. You must quote (‘ ‘) the text value. The query should look like this – UPDATE stagiaire SET NOM = ‘saad’ , PRENOM = ‘saad’,….

The code should be like this:
String SQL = "UPDATE stagiaire SET NOM = '" + getNom() + "', PRENOM = '" + getPrenom() + "'....."
String SQL = "UPDATE stagiaire SET NOM = '" + getNom() + "', PRENOM = '" + getPrenom() + "'....."
deathprog August 21, 2009

ohh it works finally thx you so much !!!

:d
deathprog August 25, 2009

hi

how can I retrieve an array element of my database into a variable that I have the name or surname

thx you for helping me.
linglom August 25, 2009

Hi, Deathprog

Array element? Did you mean multiple values in a field? Can you show some example?
deathprog August 25, 2009

hi linglom
thx you for your response.

i have a table in my database (nom;prenom;age ;code…)
how can i retrieve for example “code” to a variable
like

String var = (“the value of the element code”)
linglom August 25, 2009

You can see a sample on Retrieve data from database section of this post. On the example, I execute SQL query and display ProductName and Unitprice columns on console window.
Praveen November 19, 2009

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package testsql;

import java.sql.*;

/**
*
* @author Imacro
*/
public class Main {

private static Statement stmt = null;
private static ResultSet rs = null;
private static Connection con = null;

public static void main(String[] args) {
try{
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
String connectionUrl = “jdbc:sqlserver://localhost:3306;” +
“databaseName=Employeedetails;user=sa;password=123456;”;
con = DriverManager.getConnection(connectionUrl);
Statement stmt = null;

retriveData(“SELECT * FROM empdetails”);

int rowsEffected = 0;

rowsEffected = updateData(“INSERT INTO empdetails (EmployeeName,DateOfBirth,DateOfJoining,Gender,Qualification,NativePlace) VALUES (‘Praveen’,’09/10/1988′,’08/10/2009′,’Male’,’B.E (E.C.E)’,’Viluppuram’)”);
System.out.println(rowsEffected + ” rows effected”);
System.out.print(“Insert new record : “);
retriveData(“SELECT * FROM empdetails WHERE EmployeeName = ‘Praveen'”);

} catch (SQLException sqlEx) {
System.out.println(“SQL Exception: “+ sqlEx.toString());
} catch (ClassNotFoundException classEx) {
System.out.println(“Class Not Found Exception: “+ classEx.toString());
} catch (Exception Ex) {
System.out.println(“Exception: “+ Ex.toString());
}
}
public static int updateData(String SQL) throws Exception {
return stmt.executeUpdate(SQL);
}
public static void retriveData(String SQL) throws Exception {
rs = stmt.executeQuery(SQL);

while (rs.next()) {
System.out.println(rs.getString(“EmployeeName”) + ” : ” + rs.getString(“DateOfBirth”) + ” : ” + rs.getString(“DateOfJoining”) + ” : ” + rs.getString(“Gender”) + ” : ” + rs.getString(“Qualification”) + ” : ” + rs.getString(“NativePlace”));
}

}

}

output:
run:
Nov 19, 2009 5:55:30 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:30 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:30 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:30 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:30 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:30 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:31 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:31 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:31 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:31 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:32 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:32 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:33 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:33 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:34 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:34 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:35 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:35 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:36 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:36 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:38 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:38 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:39 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:39 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:40 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:40 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:41 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:41 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:42 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:42 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:43 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:43 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:44 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Unexpected response type:65
Nov 19, 2009 5:55:44 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 3306 Error reading prelogin response: Connection reset
SQL Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
BUILD SUCCESSFUL (total time: 14 seconds)

But i didn’t get the output page( Retrieval of data from database).
Please reply, where i made the mistake???
linglom November 25, 2009

Hi, Praveen
This error seems to be connectivity problem. What is the version of your SQL Server?

You should verify the connection string. Ensure that the SQL Server is running and listening on the certain port.
Praveen November 25, 2009

My Version is Sql server 2000….
linglom November 25, 2009

Are you sure that the SQL Server is running on port 3306 as in your connection string?

To find port number on SQL Server, see How To Connect to a SQL Server 2000 Named Instance with JDBC.
Tom March 5, 2010

I spent long time solving the connection problem and then I realese that I have to specify instanceName ( according to Microsoft specification) now everything works just fine my connectionString:
“jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=AdventureWorks;user=tomek;password=tommy1986;”
Carlos Fernandez March 8, 2010

Very nice guide, it really helped me! Thank you very much!
Pete March 11, 2010

A genuine thank you from a VB.net programmer new to the sqljdbc4.jar.

Thank you Thank you Thank you!!!
Norman May 13, 2010

Thanks a lot for the sample & discussion, i learned a lot 🙂
Norman May 13, 2010

Thanks a lot for the sample & discussion 🙂
Francisco July 2, 2010

Hey, thanks a lot for your help,
Tt was very useful,
best regards friend.
Khalida-san December 1, 2011

that’s nice! thanks a lot!
miky February 1, 2012

I have got login failed for user sa ,problem and the answer is really helpful 10 q so much
Akshay March 13, 2016

declare @b int
set @b=(select count(*) from Patient)
print @b

I’ve to take the count value from server and print it on netbeans window
my netbeans code is

Statement st=con.createStatement();
ResultSet rs=st.executeQuery(“declare @b int set @b=(select count(*) from Patient)”);
while(rs.next()){
int ID=rs.getInt(what shoud i put here?);
System.out.println(ID);
}
but above code is not working
linglom March 14, 2016
Try with this code:
ResultSet rs=st.executeQuery("SELECT count(*) FROM Patient"); while(rs.next()){ int ID=rs.getInt(1); System.out.println(ID); }
ResultSet rs=st.executeQuery("SELECT count(*) FROM Patient"); while(rs.next()){ int ID=rs.getInt(1); System.out.println(ID); }
getInt accepts column index or column label as a parameter. You can check java document for more information.
https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html
Akshay March 14, 2016

Thax Linglom that worked but now i’ve new problem

DefaultPieDataset dataset=new DefaultPieDataset();
dataset.setValue(“ADHD”,new Integer(10));
dataset.setValue(“Arthritis”,new Integer(20));
dataset.setValue(“Asthma”,new Integer(30));
dataset.setValue(“ASD”,new Integer(40));
dataset.setValue(“Avian_Influenza”,new Integer(50));
dataset.setValue(“Cancer”,new Integer(60));
JFreeChart chart=ChartFactory.createPieChart(“Diseses Pie Chart”,dataset,true,true,true);
PiePlot P=(PiePlot)chart.getPlot();
ChartFrame frame=new ChartFrame(“Pie Chart”,chart);
frame.setVisible(true);
frame.setSize(450,500);

it has some errors that it not creating any pie chart

run:
Exception in thread “main” java.lang.NoClassDefFoundError: org/jfree/util/PublicCloneable
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:800)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:449)
at java.net.URLClassLoader.access$100(URLClassLoader.java:71)
at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
at dataminer.SQLConnection.main(SQLConnection.java:42)
Caused by: java.lang.ClassNotFoundException: org.jfree.util.PublicCloneable
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
… 13 more
Java Result: 1
BUILD SUCCESSFUL (total time: 0 seconds)
linglom March 15, 2016

Check if you add required libraries to your project’s classpath. For example, JFreeChart requires the JCommon class library as stated on this page, http://www.jfree.org/jfreechart/download.html

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Linglom.com

Accessing SQL Server on NetBeans using JDBC, Part 2: Perform SQL Operations

Perform SQL Operations

SQL Server Connection using in this part

Retrieve data from database

Update data on database

Summary

51 Comments

Leave a Reply

Perform SQL Operations

SQL Server Connection using in this part

Retrieve data from database

Update data on database

Summary

Related Posts

51 Comments

Leave a Reply