j2ee -jdbc driver error

sir,
kindly solve the error.When I do a database updation in the
ejbcreate function the driver is working properly but when
I tried to use a user defined function I am thrown an the
exception listed below.This means that teh classpath
settings are fine .Probably the access writes are not
defined properly so I am posting you server.policy file
also just in case there is some problem.
=20
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC]Can't start a
cloned connection while in manual transaction mode.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.base.BaseConnection.getImplConn ection(Unknown
Sour
ce)
at
com.microsoft.jdbc.base.BaseStatement.setupImplCon nection(Unknown
Sou
rce)
at com.microsoft.jdbc.base.BaseStatement.<init>(Unkno wn Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.<ini t>(Unknown
Source)
at
com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
Sourc
e)
at
com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
Sourc
e)
at
com.sun.enterprise.resource.JdbcXAConnection$JdbcC onnection.prepareSt
atement(JdbcXAConnection.java:263)
at Loginsessionbean.checklogin(Loginsessionbean.java: 36)
at
Loginsessionbean_EJBObjectImpl.checklogin(Loginses sionbean_EJBObjectI
mpl.java:19)
at _Loginsessionbean_EJBObjectImpl_Tie._invoke(Unknow n Source)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatchToServant(Ge
nericPOAServerSC.java:423)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.i nternalDispatch(Gen
ericPOAServerSC.java:137)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatch(GenericPOAS
erverSC.java:98)
at com.sun.corba.ee.internal.iiop.ORB.process(ORB.jav a:227)
at
com.sun.corba.ee.internal.iiop.LocalClientRequestI mpl.invoke(LocalCli
entRequestImpl.java:90)
at
com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
entSC.java:142)
at
com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
entSC.java:183)
at
org.omg.CORBA.portable.ObjectImpl._invoke(ObjectIm pl.java:297)
at _Loginsessionremote_Stub.checklogin(Unknown Source)
at Loginbean.getLogin(Loginbean.java:34)
at
javaproject.login._0005cjavaproject_0005clogin_000 5clogin_0002ejsplog
in_jsp_1._jspService(_0005cjavaproject_0005clogin_ 0005clogin_0002ejsplogi=
n_jsp_1
..java:140)
at
org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:126)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
at
org.apache.jasper.runtime.JspServlet$JspServletWra pper.service(JspSer
vlet.java:161)
at
org.apache.jasper.runtime.JspServlet.serviceJspFil e(JspServlet.java:2
47)
at
org.apache.jasper.runtime.JspServlet.service(JspSe rvlet.java:352)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
at
org.apache.tomcat.core.ServiceInvocationHandler.me thod(ServletWrapper
..java:626)
at
org.apache.tomcat.core.ServletWrapper.handleInvoca tion(ServletWrapper
..java:534)
at
org.apache.tomcat.core.ServletWrapper.handleReques t(ServletWrapper.ja
va:378)
at
org.apache.tomcat.core.Context.handleRequest(Conte xt.java:644)
at
org.apache.tomcat.core.ContextManager.service(Cont extManager.java:440
)
at
org.apache.tomcat.service.http.HttpConnectionHandl er.processConnectio
n(HttpConnectionHandler.java:144)
at
org.apache.tomcat.service.TcpConnectionThread.run( TcpEndpoint.java:31
0)
at java.lang.Thread.run(Thread.java:484)
Loginsessionbean.java
import javax.ejb.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.transaction.*;
import java.rmi.RemoteException;
public class Loginsessionbean implements SessionBean
{
private Connection con;
public void ejbCreate() throws CreateException ,
RemoteException ,SQLException , NamingException
{
try
{
InitialContext ic =3D new InitialContext();
DataSource ds =3D
(DataSource)ic.lookup("java:comp/env/jdbc/master");
con =3D ds.getConnection();
}
catch(Exception ex)
{
throw new CreateException(ex.getMessage());
}
}
public String checklogin(String login,String password)
throws RemoteException , SQLException , NamingException ,
CreateException
{
String selectstatement=3D"select * from table1 where login =3D?
and password =3D ?";
ResultSet result;
try
{
PreparedStatement prep =3D con.prepareStatement(selectstatement);
prep.setString(1,login);
prep.setString(2,password);
result =3D prep.executeQuery();
if(result.next()=3D=3D true)
{
prep.close();
return(null);
}
}
catch(SQLException ex)
{
ex.printStackTrace();
}
return "login is correct";
}
public void ejbRemove(){}
public void ejbActivate(){}
public void ejbPassivate(){}
public void setSessionContext(SessionContext session){}
}
server.policy
// Standard extensions get all permissions by default
grant codeBase "file:${java.home}/lib/ext/-" {
permission java.security.AllPermission;
};
grant codeBase "file:${java.home}/../lib/tools.jar" {
permission java.security.AllPermission;
};
grant codeBase "file:${com.sun.enterprise.home}/lib/classes/" {
permission java.security.AllPermission;
};
// Drivers and other system classes should be stored in this
// code base.
grant codeBase "file:${com.sun.enterprise.home}/lib/system/-" {
permission java.security.AllPermission;
};
grant codeBase
"file:${com.sun.enterprise.home}/public_html/-" {
permission java.lang.RuntimePermission "loadLibrary.*";
permission java.lang.RuntimePermission
"accessClassInPackage.*";
permission java.lang.RuntimePermission "queuePrintJob";
permission java.lang.RuntimePermission "modifyThreadGroup";
permission java.io.FilePermission "<<ALL FILES>>",
"read,write";
permission java.net.SocketPermission "*", "connect";
// "standard" properies that can be read by anyone
permission java.util.PropertyPermission "*", "read";
// set the JSSE provider for lazy authentication of app.
clients.
permission java.security.SecurityPermission
"putProviderProperty.JSSE";
permission java.security.SecurityPermission
"insertProvider.JSSE";
};
grant codeBase "file:${com.sun.enterprise.home}/lib/j2ee.jar" {
permission java.security.AllPermission;
};
// default permissions granted to all domains
grant {
permission java.lang.RuntimePermission "queuePrintJob";
// Additional properties needed RI...
permission java.io.FilePermission "*", "read";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}-", "read";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}reposi tory${file.separator}-"=
,
"read,write,delete";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}logs${ file.separator}-",
"read,write,delete";
permission java.io.FilePermission
"${java.io.tmpdir}${file.separator}-", "read,write,delete";
permission java.io.FilePermission
"${user.home}${file.separator}-", "read,write,delete";
// allows anyone to listen on un-privileged ports
permission java.net.SocketPermission "*:0-65535", "connect";
// "standard" properies that can be read by anyone
permission java.util.PropertyPermission "*", "read";
// A version of Merant driver needs this permission.
// permission java.io.FilePermission "<<ALL FILES>>", "read";
// permission java.lang.RuntimePermission "modifyThreadGroup";
};
// permissions granted to all domains
grant {
permission java.lang.RuntimePermission "modifyThread";
permission java.lang.RuntimePermission "modifyThreadGroup";
// DataSource access
permission java.io.FilePermission "<<ALL FILES>>","read,write";
permission java.util.PropertyPermission "java.naming.*",
"read,write";
// Adjust the server host specification for your environment
permission java.net.SocketPermission
"*.microsoft.com:0-65535", "connect";
};
rajat agrawal wrote:

> sir,
> kindly solve the error.When I do a database updation in the
> ejbcreate function the driver is working properly but when
> I tried to use a user defined function I am thrown an the
> exception listed below.This means that teh classpath
> settings are fine .Probably the access writes are not
> defined properly so I am posting you server.policy file
> also just in case there is some problem.
>
Hi. Add a property to the jdbc connection request: selectMethod=cursor
Joe Weinstein at BEA

>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
> for JDBC]Can't start a
> cloned connection while in manual transaction mode.
> at
> com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at
> com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseConnection.getImplConn ection(Unknown
> Sour
> ce)
> at
> com.microsoft.jdbc.base.BaseStatement.setupImplCon nection(Unknown
> Sou
> rce)
> at com.microsoft.jdbc.base.BaseStatement.<init>(Unkno wn Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.<ini t>(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
> Sourc
> e)
> at
> com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
> Sourc
> e)
> at
> com.sun.enterprise.resource.JdbcXAConnection$JdbcC onnection.prepareSt
> atement(JdbcXAConnection.java:263)
> at Loginsessionbean.checklogin(Loginsessionbean.java: 36)
> at
> Loginsessionbean_EJBObjectImpl.checklogin(Loginses sionbean_EJBObjectI
> mpl.java:19)
> at _Loginsessionbean_EJBObjectImpl_Tie._invoke(Unknow n Source)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatchToServant(Ge
> nericPOAServerSC.java:423)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.i nternalDispatch(Gen
> ericPOAServerSC.java:137)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatch(GenericPOAS
> erverSC.java:98)
> at com.sun.corba.ee.internal.iiop.ORB.process(ORB.jav a:227)
> at
> com.sun.corba.ee.internal.iiop.LocalClientRequestI mpl.invoke(LocalCli
> entRequestImpl.java:90)
> at
> com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
> entSC.java:142)
> at
> com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
> entSC.java:183)
> at
> org.omg.CORBA.portable.ObjectImpl._invoke(ObjectIm pl.java:297)
> at _Loginsessionremote_Stub.checklogin(Unknown Source)
> at Loginbean.getLogin(Loginbean.java:34)
> at
> javaproject.login._0005cjavaproject_0005clogin_000 5clogin_0002ejsplog
> in_jsp_1._jspService(_0005cjavaproject_0005clogin_ 0005clogin_0002ejsplogin_jsp_1
> .java:140)
> at
> org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:126)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
> at
> org.apache.jasper.runtime.JspServlet$JspServletWra pper.service(JspSer
> vlet.java:161)
> at
> org.apache.jasper.runtime.JspServlet.serviceJspFil e(JspServlet.java:2
> 47)
> at
> org.apache.jasper.runtime.JspServlet.service(JspSe rvlet.java:352)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
> at
> org.apache.tomcat.core.ServiceInvocationHandler.me thod(ServletWrapper
> .java:626)
> at
> org.apache.tomcat.core.ServletWrapper.handleInvoca tion(ServletWrapper
> .java:534)
> at
> org.apache.tomcat.core.ServletWrapper.handleReques t(ServletWrapper.ja
> va:378)
> at
> org.apache.tomcat.core.Context.handleRequest(Conte xt.java:644)
> at
> org.apache.tomcat.core.ContextManager.service(Cont extManager.java:440
> )
> at
> org.apache.tomcat.service.http.HttpConnectionHandl er.processConnectio
> n(HttpConnectionHandler.java:144)
> at
> org.apache.tomcat.service.TcpConnectionThread.run( TcpEndpoint.java:31
> 0)
> at java.lang.Thread.run(Thread.java:484)
>
> Loginsessionbean.java
> import javax.ejb.*;
> import java.util.*;
> import java.sql.*;
> import javax.sql.*;
> import javax.naming.*;
> import javax.transaction.*;
> import java.rmi.RemoteException;
>
> public class Loginsessionbean implements SessionBean
> {
> private Connection con;
> public void ejbCreate() throws CreateException ,
> RemoteException ,SQLException , NamingException
> {
> try
> {
> InitialContext ic = new InitialContext();
> DataSource ds =
> (DataSource)ic.lookup("java:comp/env/jdbc/master");
> con = ds.getConnection();
> }
> catch(Exception ex)
> {
> throw new CreateException(ex.getMessage());
> }
> }
> public String checklogin(String login,String password)
> throws RemoteException , SQLException , NamingException ,
> CreateException
> {
> String selectstatement="select * from table1 where login =?
> and password = ?";
> ResultSet result;
> try
> {
> PreparedStatement prep = con.prepareStatement(selectstatement);
> prep.setString(1,login);
> prep.setString(2,password);
> result = prep.executeQuery();
> if(result.next()== true)
> {
> prep.close();
> return(null);
> }
> }
> catch(SQLException ex)
> {
> ex.printStackTrace();
> }
> return "login is correct";
> }
> public void ejbRemove(){}
> public void ejbActivate(){}
> public void ejbPassivate(){}
> public void setSessionContext(SessionContext session){}
> }
>
> server.policy
>
> // Standard extensions get all permissions by default
> grant codeBase "file:${java.home}/lib/ext/-" {
> permission java.security.AllPermission;
> };
> grant codeBase "file:${java.home}/../lib/tools.jar" {
> permission java.security.AllPermission;
> };
> grant codeBase "file:${com.sun.enterprise.home}/lib/classes/" {
> permission java.security.AllPermission;
> };
> // Drivers and other system classes should be stored in this
> // code base.
> grant codeBase "file:${com.sun.enterprise.home}/lib/system/-" {
> permission java.security.AllPermission;
> };
> grant codeBase
> "file:${com.sun.enterprise.home}/public_html/-" {
> permission java.lang.RuntimePermission "loadLibrary.*";
> permission java.lang.RuntimePermission
> "accessClassInPackage.*";
> permission java.lang.RuntimePermission "queuePrintJob";
> permission java.lang.RuntimePermission "modifyThreadGroup";
> permission java.io.FilePermission "<<ALL FILES>>",
> "read,write";
> permission java.net.SocketPermission "*", "connect";
> // "standard" properies that can be read by anyone
> permission java.util.PropertyPermission "*", "read";
> // set the JSSE provider for lazy authentication of app.
> clients.
> permission java.security.SecurityPermission
> "putProviderProperty.JSSE";
> permission java.security.SecurityPermission
> "insertProvider.JSSE";
> };
> grant codeBase "file:${com.sun.enterprise.home}/lib/j2ee.jar" {
> permission java.security.AllPermission;
> };
> // default permissions granted to all domains
> grant {
> permission java.lang.RuntimePermission "queuePrintJob";
> // Additional properties needed RI...
> permission java.io.FilePermission "*", "read";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}-", "read";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}reposi tory${file.separator}-",
> "read,write,delete";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}logs${ file.separator}-",
> "read,write,delete";
> permission java.io.FilePermission
> "${java.io.tmpdir}${file.separator}-", "read,write,delete";
> permission java.io.FilePermission
> "${user.home}${file.separator}-", "read,write,delete";
> // allows anyone to listen on un-privileged ports
> permission java.net.SocketPermission "*:0-65535", "connect";
> // "standard" properies that can be read by anyone
> permission java.util.PropertyPermission "*", "read";
> // A version of Merant driver needs this permission.
> // permission java.io.FilePermission "<<ALL FILES>>", "read";
> // permission java.lang.RuntimePermission "modifyThreadGroup";
> };
> // permissions granted to all domains
> grant {
> permission java.lang.RuntimePermission "modifyThread";
> permission java.lang.RuntimePermission "modifyThreadGroup";
> // DataSource access
> permission java.io.FilePermission "<<ALL FILES>>","read,write";
> permission java.util.PropertyPermission "java.naming.*",
> "read,write";
> // Adjust the server host specification for your environment
> permission java.net.SocketPermission
> "*.microsoft.com:0-65535", "connect";
> };
|||Please see the below KB for detailed info:
313181 PRB: Cannot Start a Cloned Connection While in Manual Transaction
Mode
http://support.microsoft.com/?id=313181

IUSR_ to run function, drop table..

Helo all,

i dont know if that is the right way, but i was wanted to make kind of sql profiler on a webpage :) and cool features would be to usa ajax to fire it async in some time secvence.. ok, ok, here goes my code and expla... i have sql2005 (express edition,, cose i think its so cool and for a lot of projects can be used for), attached startup procedure, whit>sp_procoption 'kreni_instanco_moja', startup, true,and kreni_insta... is like:

------
setANSI_NULLSON
setQUOTED_IDENTIFIERON
GO
ALTERPROCEDURE [dbo].[kreni_instanco_moja]
AS
BEGIN
exec kp.dbo.start_trace
END
------

ok and my start_trace is:

------
ALTERPROCEDURE [dbo].[start_trace]
AS
BEGIN
DECLARE
@.traceidnumINT,@.onBIT,@.file_pathNVARCHAR(50),@.maxsizebigint;
SET @.on= 1;SET @.maxsize=5;
SET @.file_path='O:\BU_DATABASE\'+convert(varchar,getdate(), 102);
EXEC sp_trace_create @.traceid= @.traceidnumOUTPUT, @.options= 2, @.tracefile= @.file_path, @.maxfilesize= @.maxsize, @.stoptime=null, @.filecount=0;
EXEC sp_trace_setevent 2, 12, 1, @.on-- ili 12 ili 13
EXEC sp_trace_setstatus 2, 1-- 1 = START, 0 = STOP
END
------

actual code that brings table and shows sql executed is:

------
use kp
droptable trace
select textdatainto tracefrom
fn_trace_gettable(N'O:\BU_DATABASE\'+convert(varchar,getdate(), 102)+'.trc',1)
select textdatafrom trace
go
------

and it works fine,, but when its fired from my local admin connection, and try that whit IUSR*?? not going... ok, i think that IUSR have to have privilage to open that O:\BU_DATABASE ... file, and to have priv to drop tabel and to fire that function..fn_trace_get ... ?? so, that is where i got stuck up... any help would be veryyyy apreciated... hey, we can put some kind of project if anybody is interested! :) to have admin page and to monitor sql code that is executed,,, and remotely

if i was not clear in some explaining, you can ask me and ill try better

thenx, kpendic

Yes, the connecting user needs all the privileges you stated (file, drop etc.), plus system wide ALTER TRACE permission.

|||

Yes Gunteman, and even ALTER TRC permission... but do you know how to achive this maybe??

thenx

sql

Iterator Table and rand() function

A couple of people asked that I post my iterator table and rand() function. The iterator table is basically a list of numbers 1-32767 and serves the same use as a "table of numbers." You can find good information about tables of numbers here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

To create an iterator table:

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go

/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */

truncate table SMALL_ITERATOR

insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255

insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iter

delete from small_iterator where iter = 0

select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR

go

dbcc dbreindex (small_iterator, '', 100)
go

update statistics small_iterator
go

exec sp_recompile small_iterator
go

Here is my RAND scalar UDF; it comes in handy at times for generating mock data:

create view dbo.vRand
as
select rand () as vRand
go

create function dbo.rand ()
returns float
as
begin

return (select vRand from dbo.vRand)

end

go

create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin

declare @.upperBound integer

set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))

insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0

return

end

Iterating through tables

I want to create a stored procedure that will list all the tables for a
given database. I can get the databases using the sp_database function, but
the sp_tables requires that I be in database to use it. I want to create a
stored procedure that will take in the database name as a variable and
return a list of the tables for that database. I need to do this so as new
databases and tables are added and removed my program will have the up to
date data for the database. thanks.
JohnDECLARE @.dbname NVARCHAR(128), @.sql NVARCHAR(4000)
SET @.dbname = 'pubs'
SET @.sql =
'SELECT [table_name]
FROM '+QUOTENAME(@.dbname)+'.[information_schema].[tables]'
EXEC (@.sql)
David Portas
SQL Server MVP
--|||>> take in the database name as a variable and return a list of the tables f
or that database. <<
Have you considered that this is a METADATA problem and should be done
with sp_ routines and never in your own stored procedure? This will
lead to kludgfes with dynamic SQL, etc.
Gee, do you do that a lot? Do your users get to create tables, columns
or entire databases on the fly? Your whole approach sounds screwed up.
Would you like to expalin what you are doing so you can get a few
thousand dollars of freee consulting or do you just want a stinking
dirty little kludge?
No, your data dictionary should contain this kinfd of information, not
the programs.

It seems SQL editor do not support "Format" function. Is it right?

When I write SQL command in the editor, I can not find a menu to format my command.

Also the "Format Document" function in VS2005 Beta2 seems do not delete blank rows. I found a good Format tool here. I realy hope MSSQL 2005 has build-in function like that.
http://www.sqlinform.com/

it maybe a wrong return value

There is a sql function like this below:
Create Function dbo.abc
(@.ParamOne decimal, @.ParamTwo decimal)
RETURNS Bit
AS
BEGIN
DECLARE @.Result bit
if abs(@.ParamOne-@.ParamTwo)<0.01
SET @.Result=1
else
SET @.Result=0
RETURN @.Result
END
if i execute this sentence: "select dbo.abc (2.12,2.11)" , why does the
function return 1 instead of 0?
Thanks.
Lorry
Please give the precision and scale while using decimal. See the below
example.
ALTER Function dbo.abc
(@.ParamOne numeric(10,2), @.ParamTwo numeric(10,2))
RETURNS Bit
AS
BEGIN
DECLARE @.Result bit
if abs(@.ParamOne-@.ParamTwo)<0.01
SET @.Result=1
else
SET @.Result=0
RETURN @.Result
END
Thanks
Hari
"Lorry Astra" <LorryAstra@.discussions.microsoft.com> wrote in message
news:A86A320C-192C-4321-A4D4-05B32ADC3BD1@.microsoft.com...
> There is a sql function like this below:
> Create Function dbo.abc
> (@.ParamOne decimal, @.ParamTwo decimal)
> RETURNS Bit
> AS
> BEGIN
> DECLARE @.Result bit
> if abs(@.ParamOne-@.ParamTwo)<0.01
> SET @.Result=1
> else
> SET @.Result=0
> RETURN @.Result
> END
> if i execute this sentence: "select dbo.abc (2.12,2.11)" , why does the
> function return 1 instead of 0?
> Thanks.
>
> Lorry

Issues in setDate of PreparedStatement

Hi,
I have recently started using the JDBC MS-SQL driver. I am facing a
problem with the setdate() function of the prepared statement. if we are
using the setDate function of the prepared statment then the date gets
updated with a timestamp.
i.e. pObjpreparedStmtIns.setDate(2, pdCurrentDate);
will add the date in the system as 2004-12-27 :12:00:000
instead of 2004-12-27 :00:00:000.
pdCurrentDate is java.sql.Date() set to todays date.
has anyone come acros this problem.
Thank you
"Shirish Nair" <Shirish Nair@.discussions.microsoft.com> schrieb im
Newsbeitrag news:4BAE3584-2220-4DF3-B66C-A75F3CAB3DF8@.microsoft.com...
> Hi,
> I have recently started using the JDBC MS-SQL driver. I am facing a
> problem with the setdate() function of the prepared statement. if we
are
> using the setDate function of the prepared statment then the date gets
> updated with a timestamp.
> i.e. pObjpreparedStmtIns.setDate(2, pdCurrentDate);
> will add the date in the system as 2004-12-27 :12:00:000
> instead of 2004-12-27 :00:00:000.
> pdCurrentDate is java.sql.Date() set to todays date.
> has anyone come acros this problem.
Two things come to mind:
- it's just a display problem (12am ~ 0:00 ~ 24:00)
- it's a time zone offset thingy (are you +/- 12h?)
- you could also try to use java.sql.Timestamp
Regards
robert

Issue with cast function

Hi

Can someone help me in the following:
An example is shown below, I had assign a date (format: dd/mm/yyyy) to @.vdate. Follow by a cast to vchar. The issue is that the date format is changed after the cast. I would like the date format to remain as dd/mm/yyyy after doing the cast. Hope someone can give some advices. Thanks alot.

Example:

declare @.vdate as datetime
set @.vdate = convert(datetime,'01/10/2005',103)

select @.vdate --> '10/01/2005' (date format required)

cast(varchar(20),@.vdate) --> 'Oct 1, 2005' (date format changed)Dates are not stored in any format. They are actually stored as numeric values, which then are displayed in a requested format.
Read up in Books Online on the topics of datetime datatypes and the CAST and CONVERT functions.