Resultset from JAVA to Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Resultset from JAVA to Stored Procedure

Author

Topic

sujith_dba
Starting Member

8 Posts

Posted - 2005-11-22 : 12:44:51

Hi guys,

I will be greatful if someone pointout how to process a resultset from Java based web application to MS SQL SERVER 2000 Stored Procedure.

Which datatype I need to use in the SP and what all related factors involved in this ?

In the present scenario I use concatinating columns and records into a single string using delimiters at the Java end and pass this to a parameter with varchar(8000) length. But this solution will fail when the number of records are huge and again unnecessary overhead for string parsing at the db side.

Thank you.
SKM.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 01:04:01

What are you trying to do?
When you pass the data to sp, what will you do based on that data?
Post the coding used in the sp

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 02:20:54

There is the data type called Text which can store more then 8000 chars...

but this has the limited functionality..

for detail you can look out for the Text in Book online.

Sucess Comes to those who Believe in Beauty of their Dream..

sujith_dba
Starting Member

8 Posts

Posted - 2005-11-23 : 11:30:22

Following is the code sample. I am getting 6 parameters to be parsed and process further. In order to eliminates this string parsing I am trying to find a way to get resultset passed from Java front-end to dbo.spr_procedure1. Which datatype in SQL can directly hold resultset from java ?
Please help.

CREATE PROCEDURE dbo.spr_procedure1
(
@strRInput VARCHAR(8000),
@strPInput VARCHAR(8000),
@strSInput VARCHAR(8000),
@strLInput VARCHAR(8000),
@strSRInput VARCHAR(8000),
@strCInput VARCHAR(8000),
@RDelimiter CHAR(1) = '#',
@CDelimiter CHAR(1) = '|',
)
AS

DECLARE @NewId INT

BEGIN
SET NOCOUNT ON

EXEC dbo.spr_procedure2 @strSRInput , @RDelimiter , @CDelimiter
-- begins other processes

....................
....................

END
GO

-------------------------------------------

CREATE PROCEDURE dbo.spr_procedure2
(
@strSRInput VARCHAR(8000),
@RDelimiter CHAR(1) = '#',
@CDelimiter CHAR(1) = '|'
)

AS

DECLARE @var1 VARCHAR(8000)
DECLARE @var2 VARCHAR(5)
DECLARE @var3 VARCHAR(5)
DECLARE @var4 VARCHAR(25)
DECLARE @var5 NUMERIC(9,2)
DECLARE @var6 CHAR(3)
DECLARE @var7 DATETIME
DECLARE @var8 INT
DECLARE @var9 NUMERIC(9,2)
DECLARE @var10 INT
DECLARE @var11 VARCHAR(5)
DECLARE @var12 INT
DECLARE @var13 INT

DECLARE @tablevar1 TABLE (Id INT PRIMARY KEY , FId INT NOT NULL)
DECLARE @tablevar2 TABLE (CId INT, CData VARCHAR(300))

BEGIN
SET NOCOUNT ON

WHILE CHARINDEX(@RDelimiter,@strSRInput,0) <> 0
BEGIN
Select @var1 = RTRIM(LTRIM(SUBSTRING(@strSRInput,1,CHARINDEX(@RDelimiter,@strSRInput,0)-1))),
@strSRInput=RTRIM(LTRIM(SUBSTRING(@strSRInput,CHARINDEX(@RDelimiter,@strSRInput,0)+1,LEN(@strSRInput))))

IF LEN(@strItem) > 0
BEGIN

-- Deletes any record if exists in the @table variable

DELETE FROM @tablevar2

-- Calls Multistatement Table-valued UDF for spliting a record into columns

INSERT @tablevar2 SELECT * FROM fn_parse_row_into_columns (@var1, @CDelimiter)

-- a record is parsed and stored into corresponding varibales

SELECT @var2 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 1
SELECT @var3 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 2
SELECT @var4 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 3
SELECT @var5 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 4
SELECT @var6 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 5
SELECT @var7 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 6
SELECT @var8 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 7
SELECT @var9 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 8
SELECT @var10 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 9
SELECT @var11 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 10
SELECT @var12 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 11
SELECT @var13 = RTRIM(LTRIM(CData)) FROM @tablevar2 WHERE CId = 12

-- begins other processes

....................
....................

END
GO

--------------------------------------------

-- this fun parses a record string into columns

CREATE FUNCTION fn_parse_row_into_columns ( @sData VARCHAR(3000), @CDelimiter CHAR(1))
RETURNS @tabvariable1 TABLE
( CId INT IDENTITY,
CData VARCHAR(300)
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @SCData VARCHAR(300)

WHILE CHARINDEX(@CDelimiter,@sData,0) <> 0
BEGIN
SET @SCData = NULL

SELECT @SCData=RTRIM(LTRIM(SUBSTRING(@sData,1,CHARINDEX(@ColDelimiter,@sData,0)-1)))

SELECT @sData=RTRIM(LTRIM(SUBSTRING(@sData,CHARINDEX(@ColDelimiter,@sData,0)+1,LEN(@sData))))

IF LEN(@SData) > 0
BEGIN
INSERT INTO @tabvariable1 SELECT @SData
END
RETURN
END
)

quote:
Originally posted by madhivanan

What are you trying to do?
When you pass the data to sp, what will you do based on that data?
Post the coding used in the sp

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 00:09:58

Can you explain more what you are trying to do?
Why do you want to split data?

Madhivanan

Failing to plan is Planning to fail

sujith_dba
Starting Member

8 Posts

Posted - 2005-11-24 : 07:39:41

quote:
Originally posted by madhivanan

Can you explain more what you are trying to do?
Why do you want to split data?

Madhivanan

Failing to plan is Planning to fail

Hi,

I work on a web based application that transfers data from oracle to MS SQL Server. I get data via MQ then Java program currently appends all records into a string and send to SP. Like this 5 strings I get into sp for a single event which happens every seconds. So inside the SP I parse this string into records and columns..then proceed to busniess process againist another SQL database and populate tables. This database is very huge with more than 100 million records in tables. This is very time critical application which invokes this database procedure more than 35,000 perday. So now I am trying to remove the overhead of string parsing from the db...to do this I should be able to accept recordset from JAVA in the SP. More if the size of the string is more than 8000 then the present code will break. Please write to me your comments......

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 07:49:48

>>I work on a web based application that transfers data from oracle to MS SQL Server.

Then Why cant you use DTS Scripts?
Refer this www.sqldts.com

You can add Oracle as a Linked Server then copy data to SQL Server
In BOL,SQL Server help file, look for OpenRowset or OpenDataSource for the scipts
I think in this way you can easily transfer data

Madhivanan

Failing to plan is Planning to fail

sujith_dba
Starting Member

8 Posts

Posted - 2005-11-24 : 08:43:41

quote:
Originally posted by madhivanan

>>I work on a web based application that transfers data from oracle to MS SQL Server.

Then Why cant you use DTS Scripts?
Refer this www.sqldts.com

You can add Oracle as a Linked Server then copy data to SQL Server
In BOL,SQL Server help file, look for OpenRowset or OpenDataSource for the scipts
I think in this way you can easily transfer data

Madhivanan

Failing to plan is Planning to fail

DTS will not help since there are huge amount of busniess process... I mentioned only string parsing part. Its not a straight data transfer from oracle..as there are complicated business requirements to be satisfied before populating these tables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 08:48:05

What type of business requirements?
Cant you filter data before transfer?

Madhivanan

Failing to plan is Planning to fail

sujith_dba
Starting Member

8 Posts

Posted - 2005-11-24 : 08:59:15

Currently 10000 lines of code is used in 25 sps to process this data transfer.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 09:08:36

quote:
Originally posted by sujith_dba

Currently 10000 lines of code is used in 25 sps to process this data transfer.

Strange. I am not sure why you use 25 sps for data transfer
Read my previous reply and read the details in BOL. I think this may be done easily than what you are doing now

Madhivanan

Failing to plan is Planning to fail

sujith_dba
Starting Member

8 Posts

Posted - 2005-11-24 : 09:20:12

Thank you Madhivanan atleast you were trying to help me out. This application is bridging two databases of a huge airline company with heavy amount of data transfer with lot of validations and checks on both oracle and MS SQL Server databases.

SKM.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources