Board index » delphi » ADO, stored proc and MSSQL question.

Dodgy

Delphi Developer
Mon, 31 Oct 2005 20:57:52 GMT

ADO, stored proc and MSSQL question.

Hi,

I'm completely new to accessing stored procedures from Delphi, and I'm
having some problems.

My procedure has 1 in, and 4 outs, not including the return value.

All the parameters are of type varchar, and the result code is
integer.

The stored proc appears to execute, but I can't work out how to read
any of the parameters except the first one (the input one!).

The example code for createparameter says to use nil if you don't have
a value, but that doesn't even compile! Dull seems to compile, but I
wouldn't be surprised if that wasn't right either!

  adostoredproc1.ProcedureName:='cccheck';
  adostoredproc1.Parameters.Clear;

adostoredproc1.Parameters.CreateParameter('@card',ftstring,pdinput,24,edit1.text);

adostoredproc1.parameters.CreateParameter('@issue',ftstring,pdoutput,2,null);

adostoredproc1.parameters.CreateParameter('@start',ftstring,pdoutput,2,null);

adostoredproc1.parameters.CreateParameter('@accept',ftstring,pdoutput,2,null);

adostoredproc1.parameters.CreateParameter('@oklen',ftstring,pdoutput,2,null);
  adostoredproc1.execproc;

// the following line works, and shows my input param

memo1.lines.add(adostoredproc1.parameters.parambyname('@card').value);

// but this gives me an invalid variant type conversion.

memo1.lines.add(adostoredproc1.parameters.parambyname('@issue').value);

Can anyone help me out on this, oh, and how do I read the result code?

TIA.

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES

 
Andreas Schmid

Delphi Developer
Tue, 01 Nov 2005 19:35:19 GMT

Re:ADO, stored proc and MSSQL question.


"Dodgy" <Do...@earth.planet.universe> schrieb im Newsbeitrag
news:7q27cv4jbt585jbei52si073upcachmhke@4ax.com...

Quote
> // the following line works, and shows my input param

> memo1.lines.add(adostoredproc1.parameters.parambyname('@card').value);

> // but this gives me an invalid variant type conversion.

> memo1.lines.add(adostoredproc1.parameters.parambyname('@issue').value);

Maybe ...parambyname('@issue').value has the
value NULL.

You should check the varianttype:
ShowMessage(VarVarTypeAsString(parambyname('@issue').value));

here are the helper functions:

function VarTypeAsString(const vt: Integer): string;
begin
 case vt and varTypeMask of
  varEmpty:    Result := 'Empty';
  varNull:     Result := 'Null';
  varSmallint: Result := 'SmallInt';
  varInteger:  Result := 'Integer';
  varSingle:   Result := 'Single';
  varDouble:   Result := 'Double';
  varCurrency: Result := 'Currency';
  varDate:     Result := 'Date';
  varOleStr:   Result := 'OleStr';
  varDispatch: Result := 'Dispatch';
  varError:    Result := 'Error';
  varBoolean:  Result := 'Boolean';
  varVariant:  Result := 'Variant';
  varUnknown:  Result := 'Unknown';
  varString:   Result := 'String';
  varAny:      Result := 'Any';
      varByte:     Result := 'Byte';
   else
      //raise Exception.CreateFmt('VarTypeAsString(0x%.8x) - Invalid
vartype',[vt]);
      Result := Format('0x%.8x',[vt]);
 end;

   if (vt and varArray) <> 0 then
      Result := 'array of ' + Result;
end;

function VarVarTypeAsString(const v: Variant): string;
begin
   Result := VarTypeAsString(VarType(v));
end;

hth
Andreas

John Beste

Delphi Developer
Mon, 31 Oct 2005 23:59:57 GMT

Re:ADO, stored proc and MSSQL question.


Remember that a null variant is different to a string variant. If your
procedure returns a null in a parameter and then Lines.Add(...) expects a
string, then you might expect problems. First check the value of
VarType(adostoredproc1.parameters.parambyname('@card').value) and see what
type of varient you are getting.

--
Regards

John Bester
mailto:john.bes...@adept.co.za

Quote
"Dodgy" <Do...@earth.planet.universe> wrote in message

news:7q27cv4jbt585jbei52si073upcachmhke@4ax.com...
Quote
> Hi,

> I'm completely new to accessing stored procedures from Delphi, and I'm
> having some problems.

> My procedure has 1 in, and 4 outs, not including the return value.

> All the parameters are of type varchar, and the result code is
> integer.

> The stored proc appears to execute, but I can't work out how to read
> any of the parameters except the first one (the input one!).

> The example code for createparameter says to use nil if you don't have
> a value, but that doesn't even compile! Dull seems to compile, but I
> wouldn't be surprised if that wasn't right either!

>   adostoredproc1.ProcedureName:='cccheck';
>   adostoredproc1.Parameters.Clear;

adostoredproc1.Parameters.CreateParameter('@card',ftstring,pdinput,24,edit1.
text);
Quote

adostoredproc1.parameters.CreateParameter('@issue',ftstring,pdoutput,2,null)
;
Quote

adostoredproc1.parameters.CreateParameter('@start',ftstring,pdoutput,2,null)
;
Quote

adostoredproc1.parameters.CreateParameter('@accept',ftstring,pdoutput,2,null
);
Quote

adostoredproc1.parameters.CreateParameter('@oklen',ftstring,pdoutput,2,null)
;

- Show quoted text -

Quote
>   adostoredproc1.execproc;

> // the following line works, and shows my input param

> memo1.lines.add(adostoredproc1.parameters.parambyname('@card').value);

> // but this gives me an invalid variant type conversion.

> memo1.lines.add(adostoredproc1.parameters.parambyname('@issue').value);

> Can anyone help me out on this, oh, and how do I read the result code?

> TIA.

> Dodgy.
> --
> MUSHROOMS ARE THE OPIATE OF THE MOOSES

Dodg

Delphi Developer
Fri, 04 Nov 2005 19:22:08 GMT

Re:ADO, stored proc and MSSQL question.


On Fri, 16 May 2003 13:35:19 +0200, "Andreas Schmidt"
<a_j_schm...@rocketmail.com> waffled on about something:

Quote

>"Dodgy" <Do...@earth.planet.universe> schrieb im Newsbeitrag
>news:7q27cv4jbt585jbei52si073upcachmhke@4ax.com...

>> // the following line works, and shows my input param

>> memo1.lines.add(adostoredproc1.parameters.parambyname('@card').value);

>> // but this gives me an invalid variant type conversion.

>> memo1.lines.add(adostoredproc1.parameters.parambyname('@issue').value);

>Maybe ...parambyname('@issue').value has the
>value NULL.

>You should check the varianttype:
>ShowMessage(VarVarTypeAsString(parambyname('@issue').value));

>here are the helper functions:

>function VarTypeAsString(const vt: Integer): string;
>begin
> case vt and varTypeMask of
>  varEmpty:    Result := 'Empty';
>  varNull:     Result := 'Null';
>  varSmallint: Result := 'SmallInt';
>  varInteger:  Result := 'Integer';
>  varSingle:   Result := 'Single';
>  varDouble:   Result := 'Double';
>  varCurrency: Result := 'Currency';
>  varDate:     Result := 'Date';
>  varOleStr:   Result := 'OleStr';
>  varDispatch: Result := 'Dispatch';
>  varError:    Result := 'Error';
>  varBoolean:  Result := 'Boolean';
>  varVariant:  Result := 'Variant';
>  varUnknown:  Result := 'Unknown';
>  varString:   Result := 'String';
>  varAny:      Result := 'Any';
>      varByte:     Result := 'Byte';
>   else
>      //raise Exception.CreateFmt('VarTypeAsString(0x%.8x) - Invalid
>vartype',[vt]);
>      Result := Format('0x%.8x',[vt]);
> end;

>   if (vt and varArray) <> 0 then
>      Result := 'array of ' + Result;
>end;

>function VarVarTypeAsString(const v: Variant): string;
>begin
>   Result := VarTypeAsString(VarType(v));
>end;

>hth
>Andreas

That's definitely a possibility! I'll give it a try. Thanks for your
help.

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES

Dodg

Delphi Developer
Sat, 05 Nov 2005 17:51:55 GMT

Re:ADO, stored proc and MSSQL question.


On Fri, 16 May 2003 13:35:19 +0200, "Andreas Schmidt"
<a_j_schm...@rocketmail.com> waffled on about something:

Hi again,

That worked wonderfully. What a very useful routine...

One last question if I may...

How do I get the return value?

TIA

Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES

Other Threads

1. ADO & MSSQL Stored proc problem

2. *** Using dataset returnet by a stored proc inside another stored proc ***

3. MSSQL Stored Proc a strange problem

4. Anybody used to write MSSQL 2000 extended stored proc in Delphi

5. Managing Text, Image in Stored Proc on MSSQL

6. Getting MSSQL 7 Stored Proc output parameters

7. mssql extended stored proc in delphi

8. HELP - MSSQL 6.5 Stored Proc Error

9. MSSQL 7.0 - system stored proc problem (sp_Setapprole)

10. Stored proc name and MSSQL 7