No Way To Do An Insert With JDBC?

Jan 30, 2008

I started with a simple insert on one of my tables. It throws an exception for trying to place NULL in one of two calculated fields in the table.
I then tried setting up a simple stored procedure and calling it using:

CallableStatement statement = con.prepareCall("? = call dbo.AddSignedForms");
statement.registerOutParameter(1, java.sql.Types.INTEGER);
statement.execute();


Which generates this exception:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

of course there is no such expression in any of the code.

I then set up a simple two column table called tblTestJDBC. now i'm getting this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbo.tblTestJDBC'.

Btw we DID manage to get a simple select statement to work from my applet. Nothing else has. Is this the MS JDBC driver? Should I just avoid using it or are we missing something here?

View 10 Replies


ADVERTISEMENT

JDBC Batch Insert + GetGeneratedKeys

Apr 13, 2007

I'm using MS JDBC driver to connect to SQLServer 2005 and trying to perform batch insert. Here is the code i'm using:




Code Snippet

try {
// Assume we have a valid con
con.setAutoCommit(false);

// this is the simplified SQL for the purposes of this example
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO temp_batchOpt (");
sql.append("temp)");
sql.append(" VALUES (?)");

PreparedStatement insertStatement =
con.prepareStatement(sql.toString(),
SQLServerStatement.RETURN_GENERATED_KEYS);

for (int i = 0; i < 10; i++) {

insertStatement.setInt(1,3);
insertStatement.addBatch();
}

int[] r = insertStatement.executeBatch();

// the correct number of insertions are made
if(r != null)
for(int x: r)
System.out.println("inserted "+x);

SQLServerResultSet keyRS = (SQLServerResultSet)insertStatement.getGeneratedKeys();
while (keyRS.next()) {
int id = keyRS.getInt(1);
System.out.println("**filling id ="+id);
}
con.commit();
} catch (BatchUpdateException bue) {
bue.printStackTrace();
}
catch(SQLException sqle){
sqle.printStackTrace();
}
finally {
// close the connection...
}



I'm getting the following exception:




Code Snippetinserted 1
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(Unknown Source)
at com.x.U.m.Transformer.prefetchData(Transformer.java:285)
at com.x.U.m.Transformer.init(Transformer.java:51)
at com.x.U.m.XMLParse.main(XMLParse.java:215)



I can get the getGeneratedKeys() to work fine with single update (not usign batch update). Is batch + getGenerated key not supported?

TIA

View 4 Replies View Related

JDBC: PreparedStatement#getParameterMetaData() Doesn't Work For INSERT SQL Query

Feb 26, 2008

Hi,

I'm using MSSQL 2005 JDBC driver version 1.2 with MSSQL Server 2005. Getting parameter's metadata doesn't seem to be working properly there. PreparedStatement#getParameterMetaData() works fine if PreparedStatement is created for some SELECT but fails for any INSERT statements. The same code works fine with other DBs (I checked with HSQLdb and PostgreSQL). See sample below

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbcqlserver://mssql2005\mssql2005;DatabaseName=alexdtms;user=xxx;password=xxx";

// I don't care about non-closed statements, it's just sample code
Connection con = DriverManager.getConnection(connectionUrl);
con.createStatement().executeUpdate("CREATE Table Table1 (RECORD_ID varchar, TgtQty float)");
PreparedStatement ps = con.prepareStatement("INSERT INTO Table1 VALUES ( ?,? )");

ParameterMetaData pmd = ps.getParameterMetaData();// <-- !!!! Exception is thrown here
for (int i = 1, count = pmd.getParameterCount(); i <= count; i++)
{
System.err.println(">>>>AD_TEMP " + pmd.getParameterType(i) + " " + pmd.getParameterTypeName(i) + " " + pmd.getParameterMode(i));
}

Thanks,
Alexander Dolgin

View 1 Replies View Related

SQL Server 2005 JDBC Driver - GetParameterMetaData() For 'INSERT ... INTO' Statement

Aug 15, 2006

I use the Microsoft SQL Server 2005 JDBC Driver (1.0.809.102 and 1.1.1320.0) to connect to a SQL Server 2005 database. I'm currently implementing a generic data access layer that executes an arbitrary SQL statement:

public void prepareQuery(String sql) throws SQLException, ClassNotFoundException {
PreparedStatement stm = getConnection().prepareStatement(sql);
ParameterMetaData pmd = stm.getParameterMetaData();
int numPar = pmd.getParameterCount();
System.out.println("Number of parameters: " + numPar);
// ... acquire and process 'numPar' parameters ...
}

Exemplarily, I created a table named 'TEST_TABLE' with three Integer columns ('C1', 'C2' and 'C3') and a Varchar column ('C4'). Calling

prepareQuery("INSERT INTO [TEST_TABLE] ( [C1], [C2], [C3], [C4] ) VALUES ( 1, 2, ?, ? )")

gives the following result:

Number of parameters: 4

This is definitely wrong because that statement has only two parameters, one of type Integer and one of type Varchar. How can I get the correct number and types of the parameters?

View 3 Replies View Related

Cannot Load JDBC Driver Class 'com.microsoft.jdbc.sqlserver.SQLServerDriver'

Apr 14, 2008

I have read similar posts to this, but I am still having problems.

I am trying to use connection pooling to connect to a local SQL Server 2005 database. I am running my application using
MyEclipse Enterprise Workbench. I have verified that sqljdbc.jar resides in "WebRoot/WEB-INF/lib/"

"WebRoot/WEB-INF/web.xml":
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsichemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<resource-ref>
<res-ref-name>jdbc/DefaultDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>


"WebRoot/META-INFcontext.xml":

<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/DefaultDS"
auth="Container"
type="javax.sql.DataSource"
username="tec"
password="tec"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive"
url="jdbcqlserver://localhost:1433/tec;databaseName=tec;user=tec;password=test;"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>

Classpath:
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
<classpathentry kind="src" path="src"/>
<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
<classpathentry kind="con" path="com.genuitec.eclipse.j2eedt.core.J2EE14_CONTAINER"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/dom.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxen-full.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxp-api.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jdbc2_0-stdext.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sqljdbc.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jstl.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/mail.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sax.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/saxpath.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/standard.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xalan.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xercesImpl.jar"/>
<classpathentry kind="output" path="WebRoot/WEB-INF/classes"/>
</classpath>

Code to connect:

import java.io.Serializable;
import java.sql.*;

import javax.sql.*;
import javax.naming.*;
public int testConnect(){
Connection conn = null;
InitialContext ctx = null;
java.sql.Statement stmt = null;

try {
ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/DefaultDS");/*This is generating the Cannot load JDBC driver class... error*/
conn = ds.getConnection();
stmt = conn.createStatement();
return CONSTANT.SUCCESS;

} catch (Exception e) {
return CONSTANT.FAILURE;
}finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
if (ctx != null)
ctx.close();
} catch (Exception ex) {
// do nothing
return CONSTANT.FAILURE;
}
}
}

Any ideas would be greatly appreciated.

View 17 Replies View Related

MS JDBC Driver Incompatibility With JDBC 3.0 Specs

Aug 8, 2006

Hi all,





We've just stumbled on a 1.0 version incompatibility with the JDBC specs.





Problem: A table with SMALLINT column. According to JDBC specs version 3.0


(p.B-179) and 4.0 (p.221)), the value should be converted to Integer type.





Unfortunatelly we get a Short object :(





Now, I remember, this case was also affecting old JSQLConnect driver from


DataDirect. Could that problem sneak to new MS driver too?





Please let me know any resolution to this problem if exists.


The issue has not been fixed in CTP 1.1 version. Any ideas if it can be fixed??




Cheers,


Piotr

View 1 Replies View Related

2000 JDBC Vs 2005 JDBC

Mar 12, 2008



I've got an import app written in Java. One table I'm importing from contains 22 million records. When I run the app in a 2000 environment, I have my max heap set at 512, and the table gets imported. When I run in a 2005 environment, I have to change the max heap to 1152 or it will error out with a similiar error:

com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:854,269,999. JVM total memory size:1,065,484,288. (<--this is with max heap at 1024)

what is the difference between the 2000 and 2005 JDBC that I have to set max heap in one and not the other?

View 3 Replies View Related

Sql Server 7 And JDBC

Nov 26, 2001

Are there separate jdbc drivers for Sql Server 7 or should I use the 2000 drivers?

View 1 Replies View Related

JDBC Drivers

Aug 6, 2001

Is anyone out there using Unix/Java/Sun app server to connect to SQL Server via JDBC? If so, what drivers are you using? We are currently testing with WebLogic's BEA JDBC driver. Does anyone have any feedback on it?

View 2 Replies View Related

JDBC Driver

Feb 6, 2004

Is oracle and Microsoft JDBC drivers are same? Oracle 9i comes with JDBC, can I use the same driver to access the Microsoft SQL Server?

View 1 Replies View Related

Need JDBC Driver For MS SQL 7.00.847

Jun 1, 2004

Hi
I just downloaded the Microsoft JDBC Driver for SQL Server 2000. Now it tells me SQL Server 7 is not supported. :o I already checked the JDBC Driver list on suns site. Its quite long and only states MS SQL Server without any version number. Can you recommend a particular driver? Preferrably without any cost. :D
Or am I better of with the jdbc/odbc bridge?

Thanks

Shabassa

View 1 Replies View Related

Jdbc Problem

May 21, 2008

Hi
I have a java program which uses sql.It supports sql 2000 and 2005.The user can enter a command from which he can choose to what server type he wants to connect.It is something like :

" c:connect [jdbc driver for 2005] [server] "

Now i have to do some different operations when user connects to 2000 and other operations when user connects to 2005.
The problem is that when using jdbc driver for 2005 he can also connect to mssql server 2000.
How can i find out to what server type he is connecting to?

i hope i explained it clearly enough
thanks

View 1 Replies View Related

JDBC Driver?

Dec 7, 2004

Hi,

Does SQL Server Express ship with a JDBC driver? ... Hardly so, but is it available somewhere?

Rgds and thanks, PP

View 3 Replies View Related

JDBC Driver For MS SQL 7

Mar 8, 2006

Hi guys,

Where can I download the JDBC Driver for our MS SQL Server 7 ?

Thank you.

View 3 Replies View Related

JDBC Connection

Apr 5, 2006

this url is having problem while making connection to SQL Server

jdbc:XXX:sqlserver://<host>:1433;DatabaseName=<name>
error:
"The requested instance is either invalid or not running."

View 5 Replies View Related

Help With JDBC Driver

Jul 23, 2005

Hello all,I have a question about the SQL Server JDBC driver. I was wondering ifanyone knows what the default prefetch size is (in number of rows).Also, does anyone know if an entire packet is sent (i.e. padded withnull values) if there is not enough data to fill it?Thanks for your help!ty

View 2 Replies View Related

Problems With JDBC

Jul 20, 2005

I downloaded the latest version on JDBC from Microsoft and I got problems.Indeed, when fetching a smallint from SQL Server i got instead an Integer.How can i do to avoid such a problem?Some hints?many tanks, gaetano

View 1 Replies View Related

JDBC With MSDE

Jul 20, 2005

I have a MSDE database that I am trying to connect to with JDBC. I don'thave SQL Server installed. Is this possible? When I try to connect I getthe error:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver forJDBC][SQLServer]Login failed for user 'sa'. Reason: Not associated with a trusted SQL Serverconnection.From what I've read on the web it looks like I need to set the securitysettings to allowfor SQL authentication as well as NT authentication. Is that correct? Andcan thatbe done with just MSDE?I think that I have MSDE 2000 installed (it actually came with a differentapplication).I just downloaded the latest SQL Server JDBC driver.Thanks,Bruce

View 1 Replies View Related

Need A JDBC Driver

Jul 20, 2005

I need a free, redistributable JDBC driverfrom MS SQL Server. (MSDE2000a)

View 2 Replies View Related

JDBC For MS SQL Server

Jul 20, 2005

Dear all,Where can I find JDBC for MS SQL server?Thx for reply.Victoria

View 2 Replies View Related

Jdbc Driver

Oct 2, 2006

what is the jdbc driver for sql express? is it "sqljdbc"? then what is its "complete" name in the class path:"com.microsoft.sqlexpress.sqljdbc"? and for the conection url is it "jdbc:microsoft:sqlexpress://localhost/sqlexpress"?

View 3 Replies View Related

JDBC With SQL Express

Oct 12, 2006

The java code :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
"instance=SQLEXPRESS;databaseName=UPM;integratedSecurity=true;";
Connection con = DriverManager.getConnection(connectionUrl);


returns "connection refused" no matter what I do.



I've used the surface config tool to enable TCP, I've added sqlsrvr.exe to the firewall exceptions, I've tried a dozen or so variations on the connection string.

I can connect to the database using the Management Studio.



Any ideas what I'm missing ????

Thanks.

View 7 Replies View Related

Help: JDBC 1.22 And SQL 2000

Sep 26, 2006

HI, all

I need to write a program, which using VM 1.1.8.

So I need to find the JDBC driver version 1.22, Can anyone help me???

Please Reply, a link for download jdbc 1.22 or any other solutions .

Thank you!

View 1 Replies View Related

Driver Jdbc

Nov 19, 2007

Hi Mixxo,
I also am having this problem! if you get an solution,
send me an answer or add my msn: griciolli@hotmail.com

View 1 Replies View Related

I NEED THE JDBC DRIVER FOR MS SQL SERVER

Jan 18, 2001

please help it's urgent i badly need the jdbc driver for mssql for my project
thank u

View 1 Replies View Related

Regarding JDBC Connectivity In MSSQL

Jul 10, 2007

Hi,

Am using Mcafee and it has a built in MSSQL Database. Am not able to get data frrom database. Can anyone let me know how to connect to Built in MSSQL database using JAVA? Need very quick reply. Any help is greatly appreciated.

thanks,
Jags

View 3 Replies View Related

Sql Server Jdbc Driver

Jan 18, 2005

Hi at all, i'm new here, your communitiy seems too much preparated.
My question is simple...
i must to connect via jdbc to a ms sql server 2000 database, but i'm in doubt to select which jdbc driver to use. your suggest over microsoft'drivers? i found on the internet the open source drivers jdts...what about them?

thank you and excuse for by bad english! :p

View 2 Replies View Related

SQL Server, JBoss And JDBC

Dec 30, 2003

I am having difficulty connecting to a SQL Server 7.0 database. I have an application running on JBoss 3.2.x, and which connects to an Oracle database without problem. However, I also need to have the application connect to a SQL Server 7.0 database.

I have a set of jar files which are added to my classpath for SQL Server. There were three jars (msutil.jar, msbase.jar and mssqlserver.jar) that had to be added to the classpath to get that far. However the application reported that the connection could not be established.

So, I wrote a java class to see if I could replicate the problem, or establish a connection and possibly figure out whether it was the application or the database. The class threw an exception and indicated that the driver was for SQL Server 2000, and was incompatible with SQL Server 7.0.

Has anyone used SQL Server 7.0 with JBoss and can point me in the right direction?

Thanks.

View 1 Replies View Related

Weblogic JDBC Driver And SQL

Mar 30, 2004

I appear to have an issue here with WebLogic and SQL Server. The WebLogic server is set up to create a pool of connections (max 15, min 4) to the SQL server. The idea is that connections are "leased" to the application for the duration of a transaction and then returned to the pool at the end of the lease. The pool can grow to up to 15 simultaneous connections -- after that the applications must wait for the next available connection in the pool.

As activity subsides, connections are supposed to be dropped down to the minimum number of 4 connections (which are maintained open continuously).

The problem that I can see from SQL is that there are tens of connections that are open (should not be more than 15, right?) and some of these connections have been held open for several days (going back to the last time we rebooted the server 2 weeks ago). I am told that this is a relatively new phenomenon; new within the last 3 months, anyway. I have tried killing some of these processes manually and now they show up in "Current Activity" as being in a "Killed/Rollback" state.

Does anyone have any experience with JDBC/Weblogic and SQL? Any experiences similar to this?

More information:
SQL Server:
SQL Server 2000 (SP3)
Windows NT (SP6)

WebLogic Server:
Weblogic 6.1
Windows 2000 (SP4)
Weblogic JDBC Driver (6.1)


Any input would be welcome.

Regards,

hmscott

View 2 Replies View Related

JDBC Connectivity With SQL Server

May 19, 2006

How do I configure the MS-SQLSERVER to accept JDBC connections andprocess the sql?I'm using SQLSERVER 2000 on Windows NT.Thanks for your help

View 2 Replies View Related

MS SQL Using TSQL Via JAVA Jdbc

Feb 5, 2007

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.

View 2 Replies View Related

JDBC Connections W/ AD Login?

Sep 6, 2007

I'm a newbie w/ SQL Server 2005.We will be connecting to SQL Server 2005 via JDBC. We have made thiswork by using an SQL Server account, rather than an Active Directory(AD) account, even though SQL Server IS in mixed authentication mode.Does mixed mode mean you can connect either way, but only using MS SQLServer interfaces (like Mgmt Studio), and not something like JDBC?Can we connect using JDBC w/ an AD login? If so, what form does thelogin take? Is the login not in 'DOMAIN/LOGIN' form?What is the best practice for this sort of thing? We would prefer touse AD logins (that a sysadmin creates) rather than having a separate(and probably equal) set of SQL Server logins.Any help appreciated.thanksaj

View 1 Replies View Related

JDBC With Mulitple Databases

Jul 20, 2005

Dear all,Our application needs a bit of database redundancy.Our application only accesses database for reading purposes.We want to have two databases in separate machines. Incase one databaseserver dies the application should automatically extract data from theother server.Is there any JDBC driver available so that it can detect failure in themain database server and then tries to extract data from the standbydatabase server?Kind regards--Posted via http://dbforums.com

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved