I'm having a problem getting back appropriate metadata for stored
procedures from MS SQL Server 2000 and 2005.
I've created a simple stored procedure that has an output param that
is a cursor.
When I ask for the metadata for that stored procedure using a JDBC
driver I get back a datatype value for my parameter specifying an int
not a result set.
Here is my stored procedure:
CREATE PROCEDURE xasp_INx_OUTcur_RETint
@OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT LASTNAME, FIRSTNAME
FROM CONTACTS2
OPEN @OutCrsr
RETURN 7
Here is the java code:
Connection conn = driver.connect(url, props);
DatabaseMetaData dbMeta = conn.getMetaData();
ResultSet columnRes = dbMeta.getProcedureColumns(cat, schem, name,
"%");
while (columnRes.next())
{
String procCat = columnRes.getString("PROCEDURE_CAT");
String procSchem = columnRes.getString("PROCEDURE_SCHEM");
String procName = columnRes.getString("PROCEDURE_NAME");
String colName = columnRes.getString("COLUMN_NAME");
short colType = columnRes.getShort("COLUMN_TYPE");
short dataType = columnRes.getShort("DATA_TYPE");
String typeName = columnRes.getString("TYPE_NAME");
int precision = columnRes.getInt("PRECISION");
// pass this info on to another method
}
The dataType is set to the type returned from the procedure not to the
type for the param.
Am I doing something stupid here? Did I create my stored procedure
wrong?
In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.
Is this the intended behavior going forward?
In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.
[junit] The database name component of the object qualifier must be the name of the current database. [junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of the current database. [junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)
Hi, guys. It's the first time I connect a database.
My OS is windows XP.
I downloaded the sqljdbc on microsoft website and unzipped it. Copy the sqljdbc_1.1 to the directory C:Program Files.
Modified the system variables: classpath=.;%JAVA_HOME%libdt.jar;%JAVA_HOME%lib ools.jar; %ProgramFiles%sqljdbc_1.1chssqljdbc.jar JAVA_HOME=D:Program FilesJavajdk1.5.0_10 ProgramFiles=C:Program Files
I installed the sqlserver 2005and created a sqlserver db engine with windows authentification by default.
Using the management studio I created a database named BudgetAuthorization. After that I created a user: CREATE LOGIN aaron792 WITH PASSWORD='12345' USE BudgetAuthorization CREATE USER aaron792
Now I want to write a java application to access the database. The application and the database are on the same computer.
I started the TCP/IP service, using the configuration manager.
Then use the eclipse to write the java application:
import java.sql.*; public class TestJDBC { public static void main(String[] srg) { String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // Start JDBC String dbURL = "jdbcqlserver://localhost:1433; DatabaseName=BudgetAuthorization"; // Connect the server and the database //String userName = "Administrator"; // windows user //String userPwd = "password"; // windows login password String userName="aaron792"; String userPwd="12345"; Connection dbConn; try { Class.forName(driverName); dbConn = DriverManager.getConnection(dbURL,userName,userPwd); System.out.println("Connection Successful!"); // if successful print Connection Successful! } catch (Exception e) { e.printStackTrace(); } } }
But it cann't be done! The error information is: Login failed for user 'aaron792'. The user is not associated with a trusted SQL Server connection.
I also tried the windows user, still failed. What's wrong? Can anyone help? Many thanks!
Help me please with one problem which I'm facing with while deploying java server application on my computer (this java app is jBilling). I have configured it as it was said in docs, I have added environment variable CLASPATH as Microsoft documentation said. I'm using SQL Server JDBC driver 1.2 by Microsoft. The result is that I find this message in log file:
2007-05-27 17:21:41,906 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.jdbc.sqlserver.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: No ClassLoaders found for: com.microsoft.jdbc.sqlserver.SQLServerDriver)) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:168) ... and so on.
I have copied sqljdbc.jar to lib folder of the server. That's how I specified connection string:
I am having a problem connecting to a database I have with a java app I am creating for my senior project. I am using the latest MS JDBC for connecting to the SQL Server.
con =ds.getConnection(); } catch (Exception ex) { ex.printStackTrace(); }
The ds and con variables are declared and initialized correctly. The error I am getting now is this: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "*databasename*" requested by the login. The login failed.
Based on jax-ws 2.0 technology, I built a java client application to access a report via the web service interface of the SQL Server Reporting Services 2005.
I used the jax-ws "wsimport" tool to generate the java classes needed to call the web services (The class generation is based on the wsdl and I used this one http://serverName/reportserver/reportexecution2005.asmx?wsdl) and wrote the following sample code :
ReportExecutionService service = new ReportExecutionService(); ReportExecutionServiceSoap rs = service.getReportExecutionServiceSoap();
String reportPath = "HelloWorld";
String format = "MHTML"; String devInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"; Holder<byte[]> result = new Holder<byte[]>(); Holder<String> extension = new Holder<String>(); Holder<String> mimeType = new Holder<String>(); Holder<String> encoding = new Holder<String>(); Holder<ArrayOfWarning> warnings = new Holder<ArrayOfWarning>(); Holder<ArrayOfString> streamIDs = new Holder<ArrayOfString>();
The trouble is that an exception is caught on the call to the render method
javax.xml.ws.soap.SOAPFaultException: The session identifier is missing. A session identifier is required for this operation. ---> The session identifier is missing. A session identifier is required for this operation. I found on the web (in C# examples) that this SessionId is supposed to be contained in the ExecutionId attribute of an
ExecutionHeader object. The ReportExecutionServiceSoap class (my rs object in the example) does not offer any way to directly set this SessionId or to associate an ExceptionHeader before calling the render method.
I'm having a problem connecting with a Java application but I CAN connect using my .Net application - the user name and password are the same for both (using the same database on SQL Server Express 2005).
The error I get is: "com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "CORNERS" requested by the login. The login failed." An interesing note - I get the same message if the database is not running.
SQL Server Express 2005 is installed in mixed mode.
Here is my connection string in the .Net appplication: <add key="connectString" value="Server=(local);UID=sa;PWD=myPasswd;Database=CORNERS" />.
My preferred programming language is Java (sorry Microsoft). I've searched for examples of running DMX queries into an Analysis Services database from Java but failed to locate any. I've seen suggestions that XMLA could be used but again, I can't locate any examples (in any language). For my current project I ran up the white flag and used C# instead but this wouldn't be an option in other cases. It would be possible to make the DMX calls from C# objects and call those from Java but that's pretty labourious to code.
Anyone have any tips for using Java against SQL Server? Our ITdirector is thinking of making us a Java shop, but I really would liketo stay with SQL Server as our RDBMS. We have Oracle Financials butour in-house development (currently VB6 client-server, soon to bereplaced by a service-oriented architecture based around Sonic andincluding SalesForce, both of which are Java) has been against SQLServer. He says five years ago at his old company Java/SQL Server hadlousy performance. I vaguely remember that since then Microsoft cameout with their own JDBC driver for SQL Server... Anyone have experiencewith this? Any performance tips will be hugely appreciated.TIA
hi all and sorry for my englishI write into a SqlServer2000 database via jdbc using a PreparedStatement(pstmt) and one of fields is a TimeStamp, my code is:qwdum=System.currentTimeMillis();pstmt.setTimestamp(8 , new Timestamp(qwdum));sometimes the field on table is correct (the milliseconds are the same oflast 3 digits of variable qwdum), other times it has a difference of 1 o 2millisecondsthe same code with jdbc Oracle or iSeries works fineany idea?thanksRoberto Nenni
I am in the process of evalulating using SSRS with our Java app and I have a couple questions up front:
I see you can call SSRS thru web services and display an HTML report. Can you have the report go straight to the printer, rather then preview. So we can offer both Print and Print Preview options.
I see the page streaming option. So on page preview we could ask for page 1 to get a snappy display, then ask for the rest of the report to generate in background?
i want to use the sql server with java, but i don't find any documentation. I already loaded the sqljdbc.jar from the microsoft webpage, but i don't know how to connect.
This is my Connection function, but it doesn't connect. I get an exception like "cannot connect to ..."
Hello. I have just got windows xp profesional x64.When i am on the internett my jave does not open the programs and games.Can some one help me please.Thanks.
Hello. I am having trouble finding a descent API for executing TSQLvia JDBC. I have tried the Microsoft driver AND the JDTS driver's butget the same syntax errors. I am hoping there is a good solution alsofor executing scripts using the same. Any help is MUCHappreciated.Thx.
My configuration is as follows.I have a Linux MySQL server database on one box andI want to connect to it from a local Windows XP laptop.My application was developed using SunOneStudio.I have registered the DSN and tested the database connection via ODBCin Windows XP.It apparently connects from XP to the server.I have compiled two base classes and have a tester class whichcompiles except for the following compiler error:PathTesterOne.java [19:1] cannot resolve symbolsymbol : variable pathologlocation: class PathTesterOneConnection c = patholog.initialize();"patholog" is the Database Name on the Linux MYSQL server.the initialize method looks like this....// establish the database connectionpublic static void initialize(){aMenu = new MainMenu();try{ // load the jdbc - odbc bridge driver for WindowsClass.forName("com.mysql.jdbc.Driver");// create connection instanceaConnection = DriverManager.getConnection(url, "username","password");// create statement object instance for this connectionaStatement = aConnection.createStatement();}catch (ClassNotFoundException e){System.out.println(e);}catch (SQLException e){ System.out.println(e); }}// The Data Source name is "patholog"static String url = ("jdbc:mysql://192.168.1.101/test?user=jerald&password=wein3150");static Connection aConnection;static Statement aStatement;Why am I not able to connect to the database?I am not understanding what the source of the error may be.Thanks for any help
have a Java Class that I want to be able to access from within a SQL Server Stored procedure. I know we can access C# and other .NET Framework code, but how can you access a Java Class?
I want to call a stored procedure (SQL SErver 2000) from JAVA. The SP contains a temporary Table. At the end of the SP there is a Select like "Select * from #tmp".
Now I get no Resultset in my JAVA - Application. When i do a normal Select on an Table I get a Resultset.
I need the temp. Table in this SP. How can I get the Result in JAVA. It is urgent for me.
Hi.. We have a java application which connect to our MS-SQL DB during midnight using JDBC connector and extract information. But very often ,the java applicatian halted and because of the deadlock as shown below.
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Transaction (Process ID 54) was deadlocked o n lock resources with another process and has been chosen as the deadlock victim.
now the java programmer is blaming me that the MS SQL DB is having deadlock and cause their Java application to hang.
Is there any way to clear the deadlock before the the Java applcation run or how to prove the Java application script itself is having problem and causing the deadlock ?
I am working on a hospital application using java programming language, i need to know how to connect two applications to one table over a network where the data read by application one can not be read by application 2 and vise versa...
I have a java compiled jar file, which accept the connection string as follows: -Uusername -Ppassword -Sservername:port
This string works fine with the default instance of SQL Server, I mean as long as you supply the name of the SQL Server as 'SQLServer', but if I want to run it against the named instance of SQL Server like 'SQLSERVERNamedInstance' then the connection is failing, giving me the unknown host error. Any idea why?
Hello,Does anyone know if SQL Server supports invoking Java methods from aStored Procedure or even a Trigger. For example, it looks like oracleprovides this capability based on the article athttp://www.cs.umbc.edu/help/oracle8...86/04_call2.htm. Itlooks like for a Trigger it uses a SP as an in-between. Any insightinto this would be greatly appreciated.Thanks,--Willard
I am facing a lot of problems integrating the reporting services into the java application using url access.If any body has sucessfully implemented this please provide me with the code or point me to the resources.
Hi, Is there a special jdbc driver for SQL Server Compact Edition (2005) available? I'm getting error messages when trying to run java application on Pocket PC (Mobile 5.0):
java.lang.UnsatisfiedLinkError: java/sql/DriverManager.getCallerClassLoader()Ljava/lang/ClassLoader; at java.sql.DriverManager.getConnection(Unknown Source)...
I have a Java Class that I want to be able to access from within a SQL Server Stored procedure. I know you can access C# and other .Net code, but can you also access a regular Java class this way?
i just downloaded the latest version of the drver v 1.2 am using Eclipse latest version along with Microsoft SQL Server 2005 with Express Mangment Tools i installed the driver (copied the .dll to C:windowssystem32 ad included the .jar file in my project) am using mixed mode authentication
when i use the following url: "jdbcqlserver://localhost:1433;user=sa;password=;integratedSecurity=true;"; it connects to the master DB... good
whe i use the following: "jdbcqlserver://localhost:1433;user=sa;password=;database=MyHits;integratedSecurity=true;"; it gives me the follwoing:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "MyHits" requested by the login. The login failed. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at db_worker.<init>(db_worker.java:25) at runner.main(runner.java:10)
although am sure i didnt miss anything (enbaled tcp/ip, no special permission thing on the DB MyHits) etc..
note: the databaseName ield in the url only works when i set it = to master,else it wont work (northwind or whatever) wont work!!!