JDBC Driver issue: tomcat

Showing posts with label tomcat. Show all posts

Friday, February 24, 2012

JDBC slow in the morning?!

Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/*******************************************************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
********************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel = EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType = CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser = @.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /*******************************************************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ********************************************************************/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel => EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType => CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/*******************************************************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>********************************************************************/
>if exists (select * from dbo.sysobjects where id =>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
> >Hello,
> >
> >I have a strange problem...
> >
> >I manage a web application on Tomcat, Struts, SQLServer with the last
> >jdbc driver from Microsoft.
> >
> >So, each morning one stored proc is very slow (around 20 sec to
> >execute) and around 10:00 AM, the stored proc accelerate (around 500
> >ms)!!
> >
> >This stored proc is executing one query, and if necessary, insert
> >records for caching in a cache table.
> >
> >Here is my code...
> >
> >/*******************************************************************
> >* DocumentsOfEDMChanelForUser -
> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> >TRUNCATE TABLE cache_EDMRights
> >********************************************************************/
> >if exists (select * from dbo.sysobjects where id => >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> >GO
> >
> >SET QUOTED_IDENTIFIER ON
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >
> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
> > @.TopCount int
> > ,@.LanguageOID int
> > ,@.SecurityUserOID int
> > ,@.ChanelOID int
> >AS
> > SET NOCOUNT ON
> >
> > SELECT DISTINCT
> > Document.DOCUMENT_PK AS OID,
> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> >DocumentName,
> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> >Description,
> > Document.LastModify AS CreationDate,
> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> >FolderPath,
> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> >@.SecurityUserOID) AS IsReaded,
> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> >0, 1) AS Rights
> > INTO
> > #ResultSet
> > FROM
> > Document Document INNER JOIN
> > EDMChanel EDMChanel ON Document.EDMChanel => >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> > CodeBasis CodeBasis ON EDMChanel.PeriodType => >CodeBasis.CODEBASIS_PK
> > WHERE
> > Document.EDMChanel = @.ChanelOID AND
> > Document.Active = 1 AND
> > CASE
> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> >DATEADD(month, EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> >EDMChanel.Period, Document.LastModify)
> > END >= getdate()
> > --ORDER BY
> > -- CreationDate DESC
> >
> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> > BEGIN
> > -- LEFT JOIN with cache because there are index violation with
> >insert
> > INSERT INTO cache_EDMRights
> > SELECT
> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> > FROM
> > #ResultSet R LEFT OUTER JOIN
> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => >@.SecurityUserOID AND C.OID = R.OID
> > WHERE
> > C.OID IS NULL AND
> > (R.Rights & 65536) > 0
> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> >65536) > 0
> > END
> >
> > DECLARE @.SQL varchar(5000)
> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> > EXEC(@.SQL)
> > DROP TABLE #ResultSet
> >GO
> >SET QUOTED_IDENTIFIER OFF
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >I'm not understanding?!
> >
> >Anyone has idea?
> >
> >Thanks for help.|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
>> As Uri already indicated, you may not want to assume it's
>> just a JDBC issue. In addition to profiler, you would want
>> to check the activity on the server (using PerfMon) as well
>> as the activity in SQL Server. When something runs slow at
>> particular times, it's often due to other activities on the
>> server or in SQL Server that are using a lot of resources.
>> -Sue
>> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
>> (crabouif) wrote:
>> >Hello,
>> >
>> >I have a strange problem...
>> >
>> >I manage a web application on Tomcat, Struts, SQLServer with the last
>> >jdbc driver from Microsoft.
>> >
>> >So, each morning one stored proc is very slow (around 20 sec to
>> >execute) and around 10:00 AM, the stored proc accelerate (around 500
>> >ms)!!
>> >
>> >This stored proc is executing one query, and if necessary, insert
>> >records for caching in a cache table.
>> >
>> >Here is my code...
>> >
>> >/*******************************************************************
>> >* DocumentsOfEDMChanelForUser -
>> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>> >TRUNCATE TABLE cache_EDMRights
>> >********************************************************************/
>> >if exists (select * from dbo.sysobjects where id =>> >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>> >GO
>> >
>> >SET QUOTED_IDENTIFIER ON
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >
>> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
>> > @.TopCount int
>> > ,@.LanguageOID int
>> > ,@.SecurityUserOID int
>> > ,@.ChanelOID int
>> >AS
>> > SET NOCOUNT ON
>> >
>> > SELECT DISTINCT
>> > Document.DOCUMENT_PK AS OID,
>> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>> >DocumentName,
>> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>> >Description,
>> > Document.LastModify AS CreationDate,
>> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>> >FolderPath,
>> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>> >@.SecurityUserOID) AS IsReaded,
>> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>> >0, 1) AS Rights
>> > INTO
>> > #ResultSet
>> > FROM
>> > Document Document INNER JOIN
>> > EDMChanel EDMChanel ON Document.EDMChanel =>> >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
>> > CodeBasis CodeBasis ON EDMChanel.PeriodType =>> >CodeBasis.CODEBASIS_PK
>> > WHERE
>> > Document.EDMChanel = @.ChanelOID AND
>> > Document.Active = 1 AND
>> > CASE
>> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>> >DATEADD(month, EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>> >EDMChanel.Period, Document.LastModify)
>> > END >= getdate()
>> > --ORDER BY
>> > -- CreationDate DESC
>> >
>> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
>> > BEGIN
>> > -- LEFT JOIN with cache because there are index violation with
>> >insert
>> > INSERT INTO cache_EDMRights
>> > SELECT
>> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
>> > FROM
>> > #ResultSet R LEFT OUTER JOIN
>> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>> >@.SecurityUserOID AND C.OID = R.OID
>> > WHERE
>> > C.OID IS NULL AND
>> > (R.Rights & 65536) > 0
>> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>> >65536) > 0
>> > END
>> >
>> > DECLARE @.SQL varchar(5000)
>> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
>> > EXEC(@.SQL)
>> > DROP TABLE #ResultSet
>> >GO
>> >SET QUOTED_IDENTIFIER OFF
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >I'm not understanding?!
>> >
>> >Anyone has idea?
>> >
>> >Thanks for help.

JDBC slow in the morning?!

jdbc permissions

seems precious few answers in the jdbc group...
as I'm trying to connect Tomcat 5 to MSDE, according to the help system, I
have to give the jdbc driver some read/write perms, but as the java is
actually a servlet, there is no codebase being used.
Anyone got any ideas on where I can set the permissions?Make sure you give sql permissions to the sql user you are
using in your servlet to connect to sql server. There is
no concept of giving read/write permissions to a jdbc
driver.
Let us know if you need more clarification on this or how
to do it.
hth.
>--Original Message--
>seems precious few answers in the jdbc group...
>as I'm trying to connect Tomcat 5 to MSDE, according to
the help system, I
>have to give the jdbc driver some read/write perms, but
as the java is
>actually a servlet, there is no codebase being used.
>Anyone got any ideas on where I can set the permissions?
>.
>|||<anonymous@.discussions.microsoft.com> wrote in news:1493501c3fa41
$8cbdc0d0$a301280a@.phx.gbl:
well, this is the page from the helpfile thats caused me to ask:
''
Using the SQL Server 2000 Driver for JDBC on a Java 2 Platform
----
--
When using the SQL Server 2000 Driver for JDBC on a Java 2 Platform with
the standard security manager enabled, you must give the driver some
additional permissions. Refer to your Java 2 Platform documentation for
more information about the Java 2 Platform security model and
permissions.
You can run an application on a Java 2 Platform with the standard
security manager using:
"java -Djava.security.manager application_class_name"
where application_class_name is the class name of the application.
Web browser applets running in the Java 2 plug-in are always running in a
Java Virtual Machine with the standard security manager enabled. To
enable the necessary permissions, you must add them to the security
policy file of the Java 2 Platform. This security policy file can be
found in the jre/lib/security subdirectory of the Java 2 Platform
installation directory.
To use JDBC data sources, all code bases must have the following
permissions:
// permissions granted to all domains
grant {
// DataSource access
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";
};
To use insensitive scrollable cursors, and perform client-side sorting of
some DatabaseMetaData ResultSets, all code bases must have access to
temporary files. If access to temporary files is not available, the
driver may throw an exception indicating that it is unable to set up a
static cursor cache.
For JDK 1.1 environments, access to "current working directory" must be
granted.
For Java 2 environments, access to the temporary directory specified by
the VM configuration must be granted.
The following is an example of permissions being granted for the C:\TEMP
directory:
// permissions granted to all domains
grant {
// Permission to create and delete temporary files.
// Adjust the temporary directory for your environment.
permission java.io.FilePermission "C:\\TEMP\\-", "read,write,delete";
};
'''
> Make sure you give sql permissions to the sql user you are
> using in your servlet to connect to sql server. There is
> no concept of giving read/write permissions to a jdbc
> driver.
> Let us know if you need more clarification on this or how
> to do it.
>
via the tomcat log, its failing to load the jdbc driver, but whether its
authentication or unable to find the driver, I'm not sure
> hth.
>>--Original Message--
>>seems precious few answers in the jdbc group...
>>as I'm trying to connect Tomcat 5 to MSDE, according to
> the help system, I
>>have to give the jdbc driver some read/write perms, but
> as the java is
>>actually a servlet, there is no codebase being used.
>>Anyone got any ideas on where I can set the permissions?
>>.
>|||I apologige for it. There is nothing special you need to
do on sql server side. I will post more if I can find more
information on this.
Thanks.
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in
news:1493501c3fa41
>$8cbdc0d0$a301280a@.phx.gbl:
>well, this is the page from the helpfile thats caused me
to ask:
>''
>Using the SQL Server 2000 Driver for JDBC on a Java 2
Platform
>----
--
>--
>When using the SQL Server 2000 Driver for JDBC on a Java
2 Platform with
>the standard security manager enabled, you must give the
driver some
>additional permissions. Refer to your Java 2 Platform
documentation for
>more information about the Java 2 Platform security model
and
>permissions.
>You can run an application on a Java 2 Platform with the
standard
>security manager using:
>"java -Djava.security.manager application_class_name"
>where application_class_name is the class name of the
application.
>Web browser applets running in the Java 2 plug-in are
always running in a
>Java Virtual Machine with the standard security manager
enabled. To
>enable the necessary permissions, you must add them to
the security
>policy file of the Java 2 Platform. This security policy
file can be
>found in the jre/lib/security subdirectory of the Java 2
Platform
>installation directory.
>To use JDBC data sources, all code bases must have the
following
>permissions:
>// permissions granted to all domains
>grant {
>// DataSource access
>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";
>};
>To use insensitive scrollable cursors, and perform client-
side sorting of
>some DatabaseMetaData ResultSets, all code bases must
have access to
>temporary files. If access to temporary files is not
available, the
>driver may throw an exception indicating that it is
unable to set up a
>static cursor cache.
>For JDK 1.1 environments, access to "current working
directory" must be
>granted.
>For Java 2 environments, access to the temporary
directory specified by
>the VM configuration must be granted.
>The following is an example of permissions being granted
for the C:\TEMP
>directory:
>// permissions granted to all domains
>grant {
>// Permission to create and delete temporary files.
>// Adjust the temporary directory for your environment.
>permission java.io.FilePermission "C:\\TEMP\\-
", "read,write,delete";
>};
>'''
>
>
>
>> Make sure you give sql permissions to the sql user you
are
>> using in your servlet to connect to sql server. There
is
>> no concept of giving read/write permissions to a jdbc
>> driver.
>> Let us know if you need more clarification on this or
how
>> to do it.
>via the tomcat log, its failing to load the jdbc driver,
but whether its
>authentication or unable to find the driver, I'm not sure
>
>> hth.
>>--Original Message--
>>seems precious few answers in the jdbc group...
>>as I'm trying to connect Tomcat 5 to MSDE, according to
>> the help system, I
>>have to give the jdbc driver some read/write perms, but
>> as the java is
>>actually a servlet, there is no codebase being used.
>>Anyone got any ideas on where I can set the permissions?
>>.
>>
>.
>

Monday, February 20, 2012

JDBC issues on infoview (Tomcat)

Hi All,

I'm experiencing an issue with a report which was originally designed for use with CR 7.5 but then, for security reason, we had to adapt it for use with CRXI.

Basically, when updating the datasource settings, using the native oracle driver available on the Crystal report application, I get an "unrecognised characterset" error message.

A colleague suggested that this is probably an application issue as if instead of the native Oracle connectivity, I update the datasource to use a JDBC connection, I do get results printed on the screen.

However when I load the modified and saved template into infoview, I get a "Failed to open connection" error.

I've been looking into both the tomcat and businessobject documentation but couldn't find anything to help me.

Does anyone has a clue on what do I need to look at in order to fix this issue?

Thanks a lot

LaurenceForgot to mention, BusinessObject is installed under tomcat on a unix machine...|||Open the report and do verify database and try.
Also search for your solution here
http://support.businessobjects.com/

Friday, February 24, 2012

JDBC slow in the morning?!

JDBC slow in the morning?!

JDBC slow in the morning?!

jdbc permissions

Monday, February 20, 2012

JDBC issues on infoview (Tomcat)

JDBC Driver issue

Blog Archive

About Me