How to pass object parameters to a stored proc

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

SQL Server Development (2000)

How to pass object parameters to a stored proc

Author

Topic

Vishakha
Starting Member

40 Posts

Posted - 2006-07-10 : 05:28:38

Hello,
I am running a java application in which I have to do the following.

1. I have my objects in form of javabeans.
2. I run validation on the the java beans.
3. After I have done validation on say 1000 beans, I need to send these
beans to a stored procedure.
4. The stored procedure will either insert the data based on the value of different object variables in the correct table or create an entry in the error database.

FYI --
1. We are using JDBC to connect to the SQL Server.
2. The prime reason for using stored proc is for performance as the number of objects tend to be high say around 50,000 to 1 million.
3. Currently we are inserting 1 record at a time in a loop, using SQL stmts directly from the code. We want to reduce this coming back to application just to get the data to insert.

My Questions are
1. How do I pass these java beans to the stored procedure
2. How do I refer the properties(values) of the passed java bean in the stored procedure.
3. Is there any better approach for achieving above.

Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-10 : 05:41:59

I am afraid, passing object variables to stored procedures is not possible in SQL server since there are no structure variables supported in SQL Server as in Oracle. so you can't write following code:

abc as SomeObject
abc.prop1 = 1
abc.prop2 = 'zzz'

exec stp1 abc

Maybe you have to write a wrapper component in java which can read beans and write those property values to a text file/csv file which can be bulk-inserted into the SQL server table !

Harsh Athalye
India.
"Nothing is Impossible"

Vishakha
Starting Member

40 Posts

Posted - 2006-07-10 : 06:03:30

Thanks Harsh,
but than won't writing to a file and then sending the data to the stored proc result in performance degradation.
the prime reason we are doing this because we want to speed up insertions.
I haven't tried it, but just want to know if will result in any perfomance benefits.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-10 : 06:21:39

quote:
Originally posted by Vishakha

Thanks Harsh,
but than won't writing to a file and then sending the data to the stored proc result in performance degradation.
the prime reason we are doing this because we want to speed up insertions.
I haven't tried it, but just want to know if will result in any perfomance benefits.

Definitely there is performance hit here, but since we are passing entire data in one round, it should be much lesser than what you are doing right now (one by one). But I have one more option to suggest.

Why don't you export your bean properties in xml format using XMLEncoder object (beans persistence). This xml file can then be imported into SQL table using OPENXML command or various other ways.

Refer to following link:
[url]http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp#3[/url]

Harsh Athalye
India.
"Nothing is Impossible"

Vishakha
Starting Member

40 Posts

Posted - 2006-07-10 : 06:35:21

Is OPENXML SQL Server specific or can be used with any database, we don't want to loose portability here.

vishakha

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-10 : 06:49:03

quote:
Originally posted by Vishakha

Is OPENXML SQL Server specific or can be used with any database, we don't want to loose portability here.

vishakha

I am afraid its SQL Server specific.

If you want portability, you can convert the XML file to csv format and import the CSV file (but here again there is performance problem) or check whether DTS can directly import the XML file. But I think you will have to make the tradeoff here - portability vs. performance.

BTW, are you doing this import as a one time activity or repeated activity?

Harsh Athalye
India.
"Nothing is Impossible"

Vishakha
Starting Member

40 Posts

Posted - 2006-07-10 : 07:05:07

Ok, is this possible to do,
I create an array of arrays in my java program with beans and its data as elements of array. and access it through my stored proc.

array structure
bean 1
a[0][0] prop1
a[0][1] prop2
a[0][n] propn

bean 2
a[1][0] prop1
a[1][1] prop2
a[1][n] propn

bean n
a[n][0] prop1
a[n][1] prop2
a[n][n] propn

It might be a clumsy approach but i think will give me best performance.

any thoughts on this

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-10 : 07:20:18

quote:
Originally posted by Vishakha

Ok, is this possible to do,
I create an array of arrays in my java program with beans and its data as elements of array. and access it through my stored proc.

It might be a clumsy approach but i think will give me best performance.

any thoughts on this

Sorry madam !!

Arrays are not supported in the SQL server stored procs.
That's why I suggested the file-based approach. It's the only way known to me to import chunk of data speedily in the SQL Server.

I think for importing beans data in MSSQL, you will have to resort to some kind of hacks only. There is no direct way

Harsh Athalye
India.
"Nothing is Impossible"

Vishakha
Starting Member

40 Posts

Posted - 2006-07-11 : 02:44:55

How about this approach
create a StringBuffer in java like this
val1,val2,val3....valn;val1,val2,val3...valn.....
and so on
then in stored proc, break the string at ";" and insert the values which are already comma separated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-11 : 04:40:01

quote:
Originally posted by Vishakha

How about this approach
create a StringBuffer in java like this
val1,val2,val3....valn;val1,val2,val3...valn.....
and so on
then in stored proc, break the string at ";" and insert the values which are already comma separated.

That seems like a good approach. But you have to split the string twice - 1st for breaking string based on ";" delimeter
2nd for breaking above broken strings further based on "," as a delimeter.

Please go through following articles for help on Split function:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[/url]

[url]http://www.sqlteam.com/item.asp?ItemID=2652[/url]

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-11 : 05:02:42

If you have data in text file then it is easy to import them to sql server table using Bulk insert

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources