March 5, 2003 at 3:56 pm
This may be in the wrong forum - if so I apologize and will accept a redirection.
I am connecting to a SQL 2000 database from Delphi 6. The goal was to have a generic maintenance format where a background routine created an ADO object for add and edit calls via stored procedures. I always need to read values as a result of the call.
My understanding is that the ADO object only passes parameters by order - not with a **@paramname = value** type of syntax. This causes a number of problems in upgrades and supportability.
Of course, I could create a string and execute it, but then I need a recordset to read my results rather than using output variables...a bit more painful and slower.
The Questions:
1) Is there another object that will use the @paramname = value syntax?
2) Is there any way to pass DEFAULT as the value to the parameter? (I can do NULL - but that is not the same)
3) Is there a better way to create this generic routine?
Thanks
Guarddata-
March 6, 2003 at 12:05 am
Not sure if this is the right forum either but anyway... The ADO Command class has a Parameters collection whose members can be accessed by name. You can add them manually using the Parameters.Add method or automatically populate the collection using Parameters.Refresh
March 6, 2003 at 4:45 am
Just create a variable and do not set the value. Then pass in and it will use the default.
Here is a VB example
SQL Code
create proc ip_tx
@var char(1) = '1',
@var2 char(1)
as
Declare @xx char(2)
SET @xx = @var + @var2
raiserror(@xx, 16, 1)
return
and the VB call (assumes connection and command objects created already)
cmdSW.CommandText = "ip_tx"
' Submit our Parameters for our Command object.
Dim X
cmdSW.Parameters.Append cmdSW.CreateParameter("var", adChar, adParamInput, 1, X)
cmdSW.Parameters.Append cmdSW.CreateParameter("var2", adChar, adParamInput, 1, "A")
cmdSW.Execute ,,adExecuteNoRecords ' Use adExecuteNoRecords to optimize as we expect no result back from process.
Note the above will throw an error based on Defalut + "A"
if you set X = a value or NULL then that value will replace Default.
March 6, 2003 at 4:54 am
Seems like there was a change in 2.6 to support named parameters, though maybe it was only that the parameters didnt have to be loaded into the collection in the exact order. Guess Im not sure of your intent, if you're writing code to build/use a command object on the fly based on the proc description, I dont know how much that accomplishes. I prefer to code one sub per stored proc in most cases, explicitly coding the parameters collection to save the round trip.
Andy
March 6, 2003 at 5:27 am
This perhaps may be over-the-top for your requirements, but the method I have used in the past to send a variable number of parameters in any order to a stored procedure is in the form of an XML document. The stored procedure then extracts the parameter values from the XML document before it continues with it's normal processing. The example procedure below includes the code to extract the parameters from the XML document, but there's no reson why this could not be exported to another stored procedure or function to prevent code duplication.
An example call to the example procedure would be this. You should be able to easily translate this to become ADO friendly.
declare @outputvalue INT
exec param_test '<Parameters>
<Parameter>
<Name>Param1</Name>
<Value>1</Value>
</Parameter>
<Parameter>
<Name>Param2</Name>
<Value>2</Value>
</Parameter>
</Parameters>', @outputvalue output
SELECT @outputvalue
I know it's probably quite a wacky idea, but I hope it helps a little.
Stored procedure script:
CREATE PROCEDURE param_test
@params NTEXT,
@outputvalue INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @returncodeINT,
@dochandleINT,
@param1INT,
@param2INT
EXEC @returncode = sp_xml_preparedocument @dochandle OUTPUT, @params
SELECT
@param1 = ISNULL(value, 0)
FROM
OPENXML(@dochandle, '/Parameters/Parameter', 2)
WITH
(Name VARCHAR(20),
Value VARCHAR(50))
WHERE
name = 'param1'
SELECT
@param2 = ISNULL(value, 0)
FROM
OPENXML(@dochandle, '/Parameters/Parameter', 2)
WITH
(Name VARCHAR(20),
Value VARCHAR(50))
WHERE
name = 'param2'
EXEC sp_xml_removedocument @dochandle
SET @outputvalue = @param1 + @param2
SET NOCOUNT OFF
March 6, 2003 at 7:04 am
I think a decent technique for passing in what is really an array of parameters - a bunch of order details would be a common example. I think you can get this to work with a proc call, not sure its a good idea. Along a similar line I've seen developers change their methods to accept one param - an XML string/doc, using the reasoning that if the params change, it doesn't break the interface. True, but its miserable to use compared with strong typed, individually listed params.
Andy
March 6, 2003 at 8:32 am
What you are looking for guardata is the NamedParameters property on the command object, by setting it to true, ADO will use named parameters. By default this property is false.
From MSDN :
quote:
NamedParameters PropertyIndicates whether parameter names should be passed to the provider.
Remarks
When this property is true, ADO passes the value of the Name property of each parameter in the Command’s Parameter collection. The provider uses a parameter name to match parameters in the CommandText or CommandStream properties. If this property is false (the default), parameter names are ignored and the provider uses the order of parameters to match values to parameters in the CommandText or CommandStream properties.
For example this was captured in profiler (Using ADO 2.6, VB6, SQL 2K) :
With NamedParameters = False OR not set
exec spTestParam 5, 12
With NamedParameters = True
exec spTestParam @param1 = 5, @param2 = 12
To answer question 2, if you pass the VB / VBScript keyword Empty (Not sure of Delphi equivalent) then the TSQL keyword "default" is sent in for that parameter, and the stored proc will use its default value for that parameter.
Tim C.
//Will write code for food
Tim C //Will code for food
March 6, 2003 at 8:50 am
I hope you intention to be able to use named parameters is so that you can
modify the stored procedure signature at a later date. If other people are using
your stored procedure, they may not be using named parameters, and changing the
signature would cause them to break. Very rarely do I do this, if I do it all I
add an argument on to the end of the procedure call, and give it a default value
so that it does not break other users code. More often than not I develop a
whole new procedure that may or may not use the old one as its base. Since
inheritance is not possible in TSQL (LOL @ myself for using those 2 words in the
same sentence!). You often have to reproduce the original sql from the first
stored proc into a totally new one. Maybe the next version of "TSQL" using C# or
VB.Net will prove otherwise. Back to the subject, I regard a stored procedure as
a published interface, and immutable. Can you imagine what havoc MSFT would
cause if they had not followed strict guidelines in keeping their API's
signatures static? The agony of developing an app to only see it break on each
new release of their operating system? This is merely a rant, and if you are the
only developer coding against your database, then you can maybe safely get away
with breaking signatures. But I would shy away from it.
The above is a rant, and me jumping up on a soapbox, ignore me if you know and
understand all about signatures and the dangers of breaking then.
Tim C.
//Will write code for food
Tim C //Will code for food
March 6, 2003 at 9:18 am
Thanks Tim.
Sounds like the *NamedParameters* property is what I am looking for. Delphi 6 ships with ADO 2.1 and this property is not available here (unfortunately, neither is the *EMPTY* default indicator). Looks like Borland is not quite up to speed.
I do understand about the signatures, but it never hurts to emphasize that point. The standard in our shop is to always use the parameter names in the call to avoid that problem. I don't plan on changing the order of the parameters and would only add one if it had a default value. I don't call the *refresh* method in order to avoid getting the new parameters in the list.
The issue is really more programming style. I am using a common class to track the changed values from the interface and just send the changes into the database *change* proc. The utility is written in such a way that it can also call my *add* proc. It just makes the code a little more clear if I can create all parameters common to both procedures at the same time. If the order is different, the 2.1 version of TADOStoredProc won't work.
Thanks for the feedback everyone. I'll go looking for an upgrade to ADO
Guarddata-
March 6, 2003 at 10:23 am
Thanks guarddata, you should be able to install the MDAC upgrades at http://www.microsoft.com/data and it would work fine. I reccomend either the MDAC 2.7 SP1 or the MDAC 2.6 SP2 Refresh. My shop is currently standardized on MDAC 2.6 SP2 Refresh for VB6 developement. The reason I said use "empty" is because VB does not support NULL. UGHHH, this has always been a VB issue, as every recordset value has to checked for null before assigning to a VB variable, else you get the stupid "Invalid use of Null" error. Try setting the value to Null as you first suggested and watch in SQL profiler if it sends the "default" keyword to SQL. As a long time developer of VB(10+ years) I have great admiration for Delphi, and would have switched long ago if there were more Delphi jobs. I would have followed in Bruce McKinneys shoes long ago elsewise(no comparing my skills to his, just the disgust with VB's limitations). Thank goodness for .Net as it will be my salvation.
Tim C.
//Will write code for food
Tim C //Will code for food
March 6, 2003 at 2:13 pm
Tim - I appreciate the link. I do have the .NET environment on my machine and am using the MDAC 2.7 (I think - it might still be 2.6). My delphi components still don't have the property. It's not a major issue - but it is a bit of a pain.
VB - Delphi - every language has its own area where we quietly grit our teeth and wonder.... Still - it's hard not to be amazed when things are working.
Thanks Guarddata-
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply