I need to submit XML variables into my SQL Query... can anyone hel -- sql server new users

Adam Harding
10/25/2005 4:24:04 AM

I am not a programmer unfortunately and my accesss to the SQL server is on a
limited basis can i still do this with this level of access?

Your solution makes sense but i have no idea how to achieve it, hence in
need of help. I only mentioned the XML as i thought that might be a way in.
The code in the form is actually written in Java so i have plenty of options
to play with.

Cheers Adam

[quoted text, click to view]

Adam Harding
10/25/2005 5:55:02 AM

Thanks ever so much Tom should be a real help.

[quoted text, click to view]

Tom Moreau
10/25/2005 7:01:16 AM

Why must you use XML? Put the code into a stored proc and just call it via
ADO, providing values for the parameters.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
My SQL Query Statement is as follows:

SELECT DISTINCT e.vchrFirst_Name, e.vchrSurname, ecc.vchrCost_Centre,
ac.vchrPersonal_Expenses

FROM tblEmployees e INNER JOIN

tblEmployee_Cost_Centre ecc ON e.vchrCost_Centre =
ecc.vchrCost_Centre INNER JOIN

tblAuthority_Codes ac ON e.vchrAuthority_Code =
ac.vchrAuthority_Code

WHERE (ac.vchrPersonal_Expenses = 'Â£1,000' OR

ac.vchrPersonal_Expenses = 'Â£1000') AND
(e.vchrCost_Centre = '1510000')

to return surname, firstname as the result. I need to put in two variables
from my XML form called '@Project_Cost_Centre' and '@total_amount' in the
place of the '1510000' and 'Â£1000'. can i use my statement to 'pull' the
data or will i need code separately to push it into the query?

Cheers Adam

Tom Moreau
10/25/2005 8:06:07 AM

I'm not a Java coder but basically, here are the steps:

Create a connection to SQL Server
Create a command
Create parameters and add them to the command's Parameters collection
Set the values for the parameters
Execute the command
Retrieve the rows
Close the connection

There are examples of using ADO in the BOL. Although they use VB, you
should be able to convert to Java. Here's a snippet from the BO:

<snippet>
This example shows the creation of an input parameter for a stored procedure
using Transact-SQL syntax:

USE NORTHWIND
GO
drop proc myADOParaProc
GO
CREATE PROC myADOParaProc
@categoryid int(4)
AS
SELECT * FROM products WHERE categoryid = @categoryid
GO
The myADOParaProc stored procedure performs a SELECT query against the
products table of the northwind database, taking one @categoryid input
parameter in its WHERE clause. The data type for the @category parameter is
int, and its size is 4.

Here is the MicrosoftÂ® Visual BasicÂ® code:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
Dim fld As ADODB.Field
Dim provStr As String

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
provStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes"
cn.Open provStr

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myADOParaProc"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15

' Set up a new parameter for the stored procedure.
Set prm = Cmd.CreateParameter("CategoryID", adInteger, adParamInput, 4, 7)
Cmd.Parameters.Append prm

' Create a recordset by executing the command.
Set rs = cmd.Execute
Set Flds = rs.Fields

' Print the values for all rows in the result set.
While (Not rs.EOF)
For Each fld in Flds
Debug.Print fld.Value
Next
Debug.Print ""
rs.MoveNext
Wend

' Close recordset and connection.
rs.Close
cn.Close
</snippet>
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
I am not a programmer unfortunately and my accesss to the SQL server is on a
limited basis can i still do this with this level of access?

Your solution makes sense but i have no idea how to achieve it, hence in
need of help. I only mentioned the XML as i thought that might be a way in.
The code in the form is actually written in Java so i have plenty of options
to play with.

Cheers Adam

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

sql server new users : I need to submit XML variables into my SQL Query... can anyone hel