Cant connect to SQL Server database in my plugin

Plug-in & Open API
1

Hi guys,
I try to connect SQL server 2014 database in my plugin using jdbc but when i try to create connection to db the below exception occurred :
invalid database address: jdbc:sqlserver://MYPC:1433;databaseName=Java_Test;integratedSecurity=true

and here is my code :
String JDBC_DRIVER = “com.microsoft.sqlserver.jdbc.SQLServerDriver”;
String databaseURL = null;

final String SERVER_NAME = “MYPC”;
final String DATABASE_NAME = “Java_Test”;
final String SERVER_PORT = “1433”;

Connection connection = null;
databaseURL = “jdbc:sqlserver://”+SERVER_NAME+":"+SERVER_PORT+";databaseName="+DATABASE_NAME+";integratedSecurity=true";
try {
Class.forName(JDBC_DRIVER);

    try {
    	connection=DriverManager.getConnection(databaseURL);
    }

    catch (SQLException e) {
    	dialog("Exception: "+"\n"+e.getMessage());
    }

}

catch(Exception e){}

finally{
closeConnection();
}

Please consider that i test my code outside of VP in a dedicated project and every thing were good (connection to db and executing queries done without any exception)but when i try my code inside body of a plugin in VP it always failed.

I add sqljdbc41.jar driver to both project build path and lib folder of plugin and also add “<runtime …” tag to plugin.xml file

2

Invalid address seems to indicate that the syntax of your JDBC URI isn’t correct. My guess: MYPC doesn’t work.

See, this stuff relies on TCP/IP based services and not the Windows network services to connect. So while your Windows PC may be called MYPC that name doesn’t have to exist within the DNS thus it cannot connect.

Try replacing the name with the IP address, see if that works.

3

I mean by MYPC the real name of my pc (computer name in windows)
please notice i’m using windows authentication mode for connecting to sql server database and both VP and database are in my local pc
I don’t think this problem relates to server name because i connect to database with the same configurations outside of VP. If it relates to the server name it should not works at all.

4

What does outside of VP mean? If you’re using Window based clients to connect then obviously they will honor a netbios name. But Java does not; it sets up a TCP/IP based connection and therefor expects an IP address, not a netbios identification.

5

Hi Mamad,

It is because plugin load the JDBC jar at runtime, the driver cannot be registered.
Please add “DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());” before “DriverManager.getConnection()”

6

I mean in a dedicated java project that i compile and run it by myself out of Visual Paradigm application !!!

7

I think i do it already
if you look at my code in the first post of topic i used :
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”) which acts exactly like :
DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

However i add your code right before DriverManager.getConnection() but problem is still remain

8

Hi Mamad,

They’re not the same in plugin. I have tested with MySQL and reproduced your problem exactly with Class.forName but solved by DriverManager.registerDriver.
I saw you’re using SQL Server with windows authentication, it could be another problem.
Did you get the same exception after change to DriverManager.registerDriver?

If problem still exists, I’ll with SQL server and provide a working example.

1 Like
9

yeah , i get exactly same exception

very good idea .thank you

10

Hi Mamad,

I tested with SQL server driver, unlike MySQL, it really not required to call registerDriver.
I tried you code works (with mssql-jdbc-7.2.1.jre8.jar in lib folder):

Screenshot
Screenshot1111×599 19.3 KB 

package sample.jdbc.actions;

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

import com.vp.plugin.ApplicationManager;
import com.vp.plugin.ViewManager;
import com.vp.plugin.action.VPAction;
import com.vp.plugin.action.VPActionController;

public class JdbcSampleActionController implements VPActionController {

	@Override
	public void performAction(VPAction action) {
		ViewManager viewManager = ApplicationManager.instance().getViewManager();

		String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String databaseURL = null;

		final String SERVER_NAME = "localhost";
		final String DATABASE_NAME = "master";
		final String SERVER_PORT = "1433";

		Connection connection = null;
		databaseURL = "jdbc:sqlserver://" + SERVER_NAME + ":" + SERVER_PORT + ";databaseName=" + DATABASE_NAME;
		try {
			Class.forName(JDBC_DRIVER);
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			connection = DriverManager.getConnection(databaseURL, "sa", "Password");
			Statement statement = connection.createStatement();
			ResultSet query = statement.executeQuery("SELECT name FROM sys.types");
			try {
				if (query.next()) {
					String name = query.getString(1);
					viewManager.showMessageDialog(viewManager.getRootFrame(), name);
				}
			} finally {
				query.close();
				statement.close();
				connection.close();
			}
		}

		catch (SQLException e) {
			viewManager.showMessageDialog(viewManager.getRootFrame(), "Exception: " + "\n" + e.getMessage());
		}
	}

	@Override
	public void update(VPAction action) {

	}

}

<plugin
	id="sample.jdbc"
	name="Test JDBC"
	provider="Kit"
	class="sample.jdbc.JdbcSample">
        
	<actionSets>
		<actionSet id="sample.jdbc.actions">
			<action 
				id="sample.jdbc.actions.JdbcSampleActionController"
				actionType="generalAction"
				label="Test JDBC"
				style="normal"
				menuPath="Tools/Report">
				<actionController class="sample.jdbc.actions.JdbcSampleActionController"/>
			</action>
		</actionSet>
	</actionSets>
	
	<runtime>
		<library path="lib/mssql-jdbc-7.2.1.jre8.jar" relativePath="true"/>
	</runtime>
</plugin>
11

Hi guys
After a few months i checked the capability of connecting to an external database inside a VP plugin again.
I don’t know why but connecting to databases using windows authentications mode always failed inside my plugins ! but using sql server authentication mode instead, will establish the connection and every thing will be ok .
Anyway, using sql server authentication will satisfy my requirements but i would like to share my experiences to others.