MS SQL jdbc driver problem in Connection pool in TOMCAT 5

I have problem in creating connection pool with MS SQL Server JDBC
Driver. I use Apache 2.0.49, Tomcat 5.0.19, windows 2000 server, MS
SQL server 2000 + sp4
My configuration is as following. The error occurs at
envCtx.lookup("jdbc/dbname");
1) I copy the three jar files to \common\lib in %catalina_home% from
C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC
2) MY webapps/ROOT/web-inf/web.xml is:
...
<resource-ref>
<description> JDBC Driver:
com.microsoft.jdbc.sqlserver.SQLServerDriver</description>
<res-ref-name>jdbc/dbname</res-ref-name>
<res-type>com.microsoft.jdbcx.sqlserver.SQLServerDataSo urce</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
3) my JSP file is in /ROOT/zuo, it contains code:
<%@. page import="java.sql.*" %>
<%@. page import="java.lang.reflect.*" %>
<%@. page import="java.io.*" %>
<%@. page import="javax.naming.*" %>
.....
<%....
envCtx = (Context) initCtx.lookup("java:comp/env");
ds = (ConnectionPoolDataSource ) envCtx.lookup("jdbc/dbname");
if (ds != null){
if(ds instanceof com.microsoft.jdbcx.sqlserver.SQLServerDataSource) {
SQLServerDataSource temp =
(com.microsoft.jdbcx.sqlserver.SQLServerDataSource )ds;
con = temp.getPooledConnection("dzuo","dzuo").getConnect ion();
...%>
4) I add text in apache2/conf/server.xml :
<GlobalNamingResources>
.....
<Resource name="jdbc/dbname" auth="container"
type="com.microsoft.jdbcx.sqlserver.SQLServerDataS ource" />
<ResourceParams name="jdbc/dbname">
<parameter>
<name>factory</name>
<value>com.microsoft.jdbcx.sqlserver.SQLServerData SourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbcx.sqlserver.SQLServerData Source</value>
</parameter>
<parameter>
<name>driverName</name>
<value>SQLServer</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server DataSource</value></parameter>
<parameter>
<name>serverName</name><value>"serverNmae"</value>
</parameter>
<parameter>
<name>portNumber</name><value>1433</value>
</parameter>
<parameter><name>user</name><value>"dzuo"</value></parameter>
<parameter><name>password</name><value>"dzuo"</value></parameter>
</ResourceParams>
(... other resources ...)
</GlobalNamingResources>
.....
</Host>
</Engine>
</Service>
</Server>
Please forward instruction, or any suggestion. Thanks a lot.
Did you had any luck. I am trying to do the same thing under tomcat. So far no luck with connection pooling.
|||use com.microsoft.jdbc.sqlserver.SQLServerDriver instead of com.microsoft.jdbcx.sqlserver.SQLServerDataSource as the jdbc driver.
I use the admin UI to config
step 1: create context;
step 2: create datasource, like following:
JNDI Name: jdbc/mnr
Data Source URL: jdbc:microsoft:sqlserver://mnrserver:1433;
JDBC Driver Class: com.microsoft.jdbc.sqlserver.SQLServerDriver
User Name: mnr
Password: xxxx
Max. Active Connections: 4
Max. Idle Connections: 2
Max. Wait for Connection: 5000
Validation Query: select * from MNR_Estm
step 3: jsp
<%@.page import="java.sql.*"%>
<%@.page import="javax.sql.*"%>
<%@.page import="javax.naming.*"%>
<%
DataSource ds = null;
InitialContext initContext = new javax.naming.InitialContext();
out.print("Getting datasource(jdbc/css)... ");
ds = (javax.sql.DataSource)initContext.lookup("java:com p/env/jdbc/mnr");
out.println("successfully");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rst = stmt.executeQuery(" select * from MNR_Estm");
while(rst.next()){
out.println(rst.getString("Estno"));
}
conn.close();
%>
step 4: access http://127.0.0.1:9000/quartz/scripts/edi/b.jsp
It's ok.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

MS SQL getDate() Function remove Time

Hi,

I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.

The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.

In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.

Any help will be appricated.

Thank you!

YasOn Jul 4, 3:53 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.
>
The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.
>
In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.
>
Any help will be appricated.
>
Thank you!
>
Yas

AFAIK a there is no DATE type in MS SQL, only DATETIME so you cannot
store only the date part.
You can use SELECT CONVERT(VARCHAR(8),datevalue,112) to return the
datetime in YYYYMMDD format without the time but it's stored as a
VARCHAR not a DATETIME.|||On 4 Jul, 17:14, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

To remove the time from a datetime such as getdate():
>
SELECT dateadd(day,datediff(day,0,getdate()),0)
>
I strongly suggest not storing a date column as a string. Use a
datetime and just set the time to zeroes if you only need the date.

Hi I am storing the coumn as datetime and not string. However, using
the above suggestion (day,datediff(day,0,getdate()),0) I get a column
with Date+Time set to Zeros. OK, but the problem is when I run the
update/insert records command again It doesn't overwrite the columns
with today's date, its as if sql is secretly inserting the time by it
self and even though to my eyes the rows is exactly the same SQL adds
a new row thinking it is distinct.

I would like that if the table had a rowOld with: (ColValue1,
ColValue2,2007-07-04 00.00.00.000)
If I use the above suggestion and insert a rowNew with same values
(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
...It should overwrite rowOld with rowNew, not insert rowNew as a new
row.
...and only insert as a new rowNew2 when this row has a different date
eg. 2007-07-05 00.00.00.000

I thought it would as time is now set to Zeros, but it doesn't. Is SQL
marking each row in the examples above with a time stamp? even though
it is not shown in the row value?

Thanks again :-)

Yas|||On Wed, 04 Jul 2007 08:55:49 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

>I would like that if the table had a rowOld with: (ColValue1,
>ColValue2,2007-07-04 00.00.00.000)
>If I use the above suggestion and insert a rowNew with same values
>(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
>...It should overwrite rowOld with rowNew, not insert rowNew as a new
>row.
>...and only insert as a new rowNew2 when this row has a different date
>eg. 2007-07-05 00.00.00.000

You can write an INSERT for a new row, or an UPDATE for an existing
row, but you have to choose which it is to be. In your case you have
to find out if the row exists and then run INSERT or UPDATE depending
on what you find.

Microsoft is adding MERGE to the next release of SQL Server, which
would allow you to write one command to accomplish both functions, but
it is not available today.

Roy Harvey
Beacon Falls, CT

MS SQL 2005 identity lost new database

Hello

I am creating a TABLE

CREATE TABLE [dbo].[TbTest](
[id_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Title] [varchar](100) NULL,
) ON [PRIMARY]

when looking at the Column Properties in MS Server Management Studio I get :
Identity Specification = Yes
(Is Identity) = Yes
Increment = 1
Seed = 1

now if I want to make a copy of that database, importing datas and tables

I get :
Identity Specification = No
(Is Identity) = No

How can I avoid this problem ? my application is not working anymore

ALTER TABLE [dbo].[TbTest] WITH NOCHECK ADD
CONSTRAINT [PK_TbTest] PRIMARY KEY CLUSTERED
(
[id_TbTest]
) ON [PRIMARY]

is not solving that problem

thank youit sounds like you copied your data using SSIS or DTS or whatever you want to call it. This will copy your data but not your schema. You likely lost all of your indexes and constraints as well. What you want to do if you want to copy all of your data and your schema, is to take a backup of the database you want to copy and restore that database to a new location. If it is a new server, you will have to remap you logins as well.|||With SQL Server 2000 Enterprise Manager you could quickly and easily copy both data and schema between servers. With 2005, you can't. This is Microsoft's idea of progress...

A work-around is to generate a script for you schema and execute it on the target server, and then you SSIS to transfer just the data.|||thank you

what is the code line for ALTER COLUMN to create IDENTITY (1,1)
I must do it now with code ?|||there is not one.

I am a little concerned from what you said in first and last post that you are going to mess up all of your primary key and foreign key relationships.

however if you must you need to create a new table and in your create table statement you must define an identity column. copy the data from your current table to the new one. drop the old table, and rename the new one.|||in that way maybe ?:

ALTER TABLE [dbo].[TbTest] ADD
[id1_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED;
ALTER TABLE [dbo].[TbTest] DROP COLUMN [id_TbTest];
EXEC sp_rename '[TbTest].[id1_TbTest]', 'id_TbTest', 'COLUMN';

thank you