Calling Web Service or Java program

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)

Calling Web Service or Java program

Author

Topic

graylinc
Starting Member

6 Posts

Posted - 2004-07-01 : 15:39:00

I need to write a stored procedure in SQL Server that can be used in a view to call a web service or java program and return data to the SQL Server view.

I'm not even sure where to start on this one!

Thanks!

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-01 : 16:00:19

Do some research on XP_CmdShell. I'm not a big fan of it because if the app that you call dies / error's out, you might hang up that SPID and cause all sorts of problems.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

graylinc
Starting Member

6 Posts

Posted - 2004-07-01 : 17:45:43

Some clarification - we need to call a Java class directly (not through the host command). In other words, the Java class must pass data (i.e. an object) back to SQL Server. I not sure it's just a matter of running a Java class. In other words, xp_cmdshell won't do the trick, I believe we need to use the API? Just not sure how to go about setting that up...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-01 : 18:34:04

Actually, that's exactly what you need. You then parse the xp_cmdshell to analyze the Java output. Here is an example:


--Declare and populate variable to run at command prompt.
DECLARE 
	@cmd NVARCHAR(2500),
	@sql_server NVARCHAR(55)

SELECT @sql_server = (SELECT SUBSTRING((CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(55))),1,(SELECT CHARINDEX('\',CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(55)),1))-1))

CREATE TABLE #cmdshell ( 
	ident INT IDENTITY(1,1) PRIMARY KEY,
	results NVARCHAR(2500))

CREATE TABLE #drivelist (
	drive_letter CHAR(1),
	disk_id NVARCHAR(47))

--Test for the host_name to see where this is being executed from.
IF UPPER(@host_name) = UPPER(@sql_server)
	BEGIN
		--Deactivates the clone specified.
		SELECT @cmd = 'c:\progra~1\EMC\Navisp~3\admsnap flush -o ' + @drive_letter + ':'

		INSERT #cmdshell(results)
			EXEC master..xp_cmdshell @cmd
	END
ELSE
   BEGIN
		SELECT @cmd = 'EXEC ' + @host_name + '.master.dbo.xp_cmdshell ''' + 'c:\progra~1\EMC\Navisp~3\admsnap flush -o ' + @drive_letter + ':'''

		INSERT #cmdshell(results)
			EXEC master..sp_executesql @cmd
   END

--Select the results.
SELECT results FROM #cmdshell

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-01 : 18:45:39

Derrick, that looks like it's calling something from Navisphere for your EMC box. Can you tell me what that thing is doing?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-01 : 20:26:58

graylinc ,

Can you elaborate on the source of the java data which needs to be returned to SQL?
Where is the Java program getting the data from?

graylinc
Starting Member

6 Posts

Posted - 2004-07-02 : 10:47:36

Good question! The data is coming from an Oracle database.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-02 : 10:52:48

quote:
Originally posted by graylinc

Good question! The data is coming from an Oracle database.

Can you establish a linked server connection between oracle and SQL Server?

graylinc
Starting Member

6 Posts

Posted - 2004-07-02 : 11:01:42

Sorry - some clarification...

Unfortunately it's not quite that easy. The java program we need to call is a virtualization tool that is designed to pull data from any source via JDBC, we are just setting up the SQL Server prototype to pull from Oracle - not the long term solution.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-02 : 18:43:05

Hi Michael. That particular call flushes a drive, prepping it to be dismounted. We then dismount the drive, attach it to it's clone set, clone it, flush it again, mount it, and use it. blah, blah, blah.

Gray, can you give an example of the java call and what it would return? Have you tried my example?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources