Board index » delphi » Delphi and MS SQL 6.0 stored procedures

Delphi and MS SQL 6.0 stored procedures

I'm having some difficulty getting the stored procedures component
to work with Delphi.   For some reason, when I attach it to a
stored procedure in MS SQL 6.0 which has one parameter for output,
I get two parameters in the list: the one I was expecting and
"RETURN_VALUE".  (I can't remove it, since the Add, Delete, and
Clear buttons are all greyed out.)  When I try to run it, I get
an exception about "Too many arguments supplied for procedure autokey"
(Autokey is the name of my stored procedure).  Is there a way around
this?

I've also tried setting it up programatically,  but I ran into a
wall when it came to creating the TParam, since the Create function
is not documented. (Is there a decent book which documents some of
the slightly esoteric aspects of Delphi, and is the documentation
better in 2.0?) I came up with:

  var
    newparam : TParam;

  begin
    newparam := TParam.create;  { WRONG, but how do you do it? }
    newparam.Name := '@next_id';  { the name of the stored proc }
    newparma.DataType := ftInteger;  { I think this is right }
    newparam.AsInteger := 0;

    StoredProc1.clear;    { clear out the stuff that isn't right }
    StoredProc1.AddParam(newparam);
    StoredProc.execproc;
    newkey := StoredProc1.ParamByName('@next_id');

    ....

Assuming I someone can tell me how to create a TParam, will this work?

Comments, suggestions, smart remarks, or any kind of feedback
will be appreciated.

--
Mike Conner
Milwaukee Software Co, Inc
mcon...@execpc.com

 

Re:Delphi and MS SQL 6.0 stored procedures


Quote
mcon...@earth.execpc.com (Mike Conner) wrote:
>I'm having some difficulty getting the stored procedures component
>to work with Delphi.

Me too.

Quote
>I've also tried setting it up programatically,  but I ran into a
>wall when it came to creating the TParam, since the Create function
>is not documented.

I also set up stored procedures programatically, I even made an utility
to create this code a while ago (if there is interest, I may upload it
somewhere).
If you don't get any answer regarding your problem, I will post some
examples tomorrow (if I remember to bring some code with me...).

I think I also ran into a problem regarding string parameters, but with a
sort of hack I eventually got around it.

Quote
>    newparam := TParam.create;  { WRONG, but how do you do it? }
>    newparam.Name := '@next_id';  { the name of the stored proc }
>    newparma.DataType := ftInteger;  { I think this is right }
>    newparam.AsInteger := 0;

I think there is a constructor for TParam which takes all this
parameters.

Quote
>Assuming I someone can tell me how to create a TParam, will this work?

Yes.

Regards,

Jarle stabell

Re:Delphi and MS SQL 6.0 stored procedures


Quote
mcon...@earth.execpc.com (Mike Conner) wrote:
>I've also tried setting it up programatically,  but I ran into a
>wall when it came to creating the TParam, since the Create function
>is not documented.

I'm pasting in an example:(g_session is my own global object)
--------------------------------

Function Wrap_L0GetUserID
(Var o_bOpplyser: Boolean; Var o_bSelger: Boolean; Var
o_bKontormedarbeider: Boolean;
                                 Var o_bJobbadministrator: Boolean):
Longint;
Var sp: TStoredProc;
Begin
        sp:= TStoredProc.Create(nil);
        WITH sp DO BEGIN
                TRY
                        DatabaseName:=g_session.db.DatabaseName;
                        StoredProcName:='sp_L0GetUserID';

                        Params.CreateParam(ftInteger, '@o_nUserID',
ptOutput);

                        Params.CreateParam(ftInteger, '@o_bOpplyser',
ptOutput);
                        Params.CreateParam(ftInteger, '@o_bSelger',
ptOutput);
                        Params.CreateParam(ftInteger,
'@o_bKontormedarbeider', ptOutput);
                        Params.CreateParam(ftInteger,
'@o_bJobbadministrator', ptOutput);

                        ExecProc;

                        GetResults;
                        Result:= Params[0].AsInteger;
                        o_bOpplyser:= Params[1].AsBoolean;
                        o_bSelger:= Params[2].AsBoolean;
                        o_bKontormedarbeider:= Params[3].AsBoolean;
                        o_bJobbadministrator:= Params[4].AsBoolean;

                FINALLY
                        sp.Free;
                END;
        END;
End;

I hope this will help you.

Regards,

Jarle stabell

Re:Delphi and MS SQL 6.0 stored procedures


Why force the use of the BDE at all? As you can tell from the THOUSANDS
of complaints in this newsgroup alone that we don't want to use BDE. We
want native access or ODBC access WITHOUT BDE (on the latter note, I
would recommend to all developers to check out Dan Guernsey's
DirectODBC Components).

For desktop users thinking that the C/S version of Delphi will solve
this - C/S SqlLinks also requires BDE. Don't waste your money.

Question to Borland - will you consider making BDE optional? The stated
marketing case - that of a developer working on his database app while
on an airplane - is silly and is no reason to add this burden to the
other 99.9999% of development.

Delphi is a great overall product. It is too bad that we are
hand-cuffed to BDE.

Re:Delphi and MS SQL 6.0 stored procedures


Quote
Sam Stone wrote:

> Why force the use of the BDE at all? As you can tell from the THOUSANDS
> of complaints in this newsgroup alone that we don't want to use BDE. We
> want native access or ODBC access WITHOUT BDE (on the latter note, I
> would recommend to all developers to check out Dan Guernsey's
> DirectODBC Components).

Native access - can you really imagine needing to change Table components just
because you use a different database program? That is why BDE is there; it provides a
common access method for _any_ supported database.  

I started programming in windows instead of dos because of the drivers; anything I
want to do is pretty much independent of what graphics card, printer, network, etc.
is running at the time.  BDE and ODBC do the same for database access; it doesn't
matter what the data source is, it looks pretty much the same to the program.

ODBC access would be nice, yes, but can you really expect Borland to provide access
to Microsoft's competing database access standard?  I don't think so.  It's up to us
to do it.  Dan's package is nice, but I would really like to see one based on the
Delphi DB components, so that TDBEdit et al. would still work.

OK, it is a pain to supply two diskettes in addition to your product deployment; but
if you use ODBC, you would still have to deploy that!

Brian

Re:Delphi and MS SQL 6.0 stored procedures


In article <313491F3.1...@ix.netcom.com> "R. Brian Lindahl" <blind...@ix.netcom.com> writes:

Quote
>ODBC access would be nice, yes, but can you really expect Borland to provide access
>to Microsoft's competing database access standard?  I don't think so.  It's up to us
>to do it.  Dan's package is nice, but I would really like to see one based on the
>Delphi DB components, so that TDBEdit et al. would still work.
>OK, it is a pain to supply two diskettes in addition to your product deployment; but
>if you use ODBC, you would still have to deploy that!

I am trying to decide which to move to. I have done 4 major applications with
a Paradox engine. I love it. It is fast, and have not had any problems.

A Firm I will be working for wants a DATABASE done in MSACCESS. I have heard a
lot of BAD things about ODBC connections. I have done work in ACCESS a few
years back, ( VB included, ok so I sinned a little ).

Which platform should I use. I need the DATABASE to be quick, over 10,000
records searched on a daily basis.

I see a new component which bypasses the BDE and does direct ODBC calls, is it
fast?

Please lend me some of your insight. I do not want to fall flat on my face for
this firm...

Sterling.

Stealth Signature Start

Stealth Signature End

Re:Delphi and MS SQL 6.0 stored procedures


Quote
Sam Stone (Netcom) wrote:
>Why force the use of the BDE at all? As you can tell from the THOUSANDS
>of complaints in this newsgroup alone that we don't want to use BDE. We
>want native access or ODBC access WITHOUT BDE (on the latter note, I
>would recommend to all developers to check out Dan Guernsey's
>DirectODBC Components).

Personally, I think you have a very myopic view.  ODBC access would be
great for the small minorty who want it.  For those people, you can get it
as you pointed out.  I know I am very happy with the BDE.  For me, it was a
trivial step from using the Paradox Engine to using the BDE (mainly because
I had already written an object oriented interface to the Paradox Engine).

Adding ODBC would be a good idea.  But in no way should Borland break a
great product because a few people want to use ODBC.  You can always use
ODBC through the BDE (I have done this).

I think all the complaints about the BDE requiring two disks to be stupid.
The stuff I work on will not fit on a single disk anyway.  We now routinely
create CD-ROM even for a demo install.  I have tried to put a 200 Meg
database onto a set of floppies (one of our clients still tries to sell the
21 disk set).  But it is totally foolish.  But my standards have changed
over the years.  To me, any database that will fit on a Zip disk is now
considered small.  I have some that will not fit on a zip disk even if you
compress them first.
----
    Jeffrey M\kern-.05em\raise.5ex\hbox{\b c}\kern-.05emArthur
    a.k.a. Jeffrey McArthur          email: j_mcart...@bix.com
    phone: (301) 306-5188                   JMcart...@gnn.com
    home:  (410) 290-6935

The opinions expressed are mine.  They do not reflect the opinions
of my employer.  My access to the Internet is NOT paid for by my
employer. My access to the Internet is on my own time at my own
expense.

Re:Delphi and MS SQL 6.0 stored procedures


Quote
Sterling Moses (SilverSoftware) wrote:
>Which platform should I use. I need the DATABASE to be quick, over 10,000
>records searched on a daily basis.

>I see a new component which bypasses the BDE and does direct ODBC calls, is
>it
>fast?

We have an application we are currently working on that has over 100,000
records.  Using Paradox tables and the BDE it is fast.  On a 486SX/25 with
only 8 meg of RAM and the database located on the network a find takes
about a second.

By the way, we have the same database in a Visual FoxPro database.  Doing
the exact same find using Visual Foxpro takes about 30 seconds.  (This is
one reason we threw away three months work done in Visual Foxpro and redid
the entire thing in less than a month using Delphi.  Now the FoxPro
solution could probably been speeded up, but from what we saw, Delphi was
at least and order of magnitute faster doing what we needed to you.  Of
couse, your experience may be different from ours.)
----
    Jeffrey M\kern-.05em\raise.5ex\hbox{\b c}\kern-.05emArthur
    a.k.a. Jeffrey McArthur          email: j_mcart...@bix.com
    phone: (301) 306-5188                   JMcart...@gnn.com
    home:  (410) 290-6935

The opinions expressed are mine.  They do not reflect the opinions
of my employer.  My access to the Internet is NOT paid for by my
employer. My access to the Internet is on my own time at my own
expense.

Re:Delphi and MS SQL 6.0 stored procedures


Quote
>Sterling Moses wrote:

>> I am trying to decide which to move to. I have done 4 major applications with
>> a Paradox engine. I love it. It is fast, and have not had any problems.

>> A Firm I will be working for wants a DATABASE done in MSACCESS. I have heard a
>> lot of BAD things about ODBC connections. I have done work in ACCESS a few
>> years back, ( VB included, ok so I sinned a little ).

>> Which platform should I use. I need the DATABASE to be quick, over 10,000
>> records searched on a daily basis.

Are we considering a multi user application? If we are, then I'm fairly
certain that MS Access should be ruled out as an option. A Borland comparison
between Access and Paradox hints that Access is not suited for multi-user
purposes, it bombs out and has awkward record locking (it locks an entire
4K page according to MS' whitepapers).

Those that I've talked to so far, seems to agree that more than one
simultanously user is not an environment where you'd put an Access database.
It seems to me that MS is trying to push Foxpro as an alternative for those
environments (to a certain number of users where they would suggest
SQL server).

The next obstacle you face is the ODBC driver itself. Deployment is
difficult, and you might hit a few snags (read features/bugs) on the way.
My experience is with version 2.0.2317 and not the one that came with
Office (which won't allow you to write to the database). Also there is a
reason why MS finally included a direct way of accessing an Access database
in VC++ 4.0: the Access odbc driver "sucks" speedwise.

OTOH Access is better than Paradox if you use SQL a lot... :-)

--
=\
 *=- R.Moberg, CD-Player Pro info @ http://www.sn.no/~mobergru/
=/

Re:Delphi and MS SQL 6.0 stored procedures


Quote
>I think all the complaints about the BDE requiring two disks to be stupid.

        Me too. It's only 50K over a disk. When people state the above, it sounds like
it is 2.8 Megs worth of files.  This kept me away from the BDE for a while, till
I looked for myself.

_
******************************************************************
NOTE: This software is currently in early alpha. If you notice any
problems, or RFC non-compliance, please report it to p...@pobox.com
Please do not report duplicates, as this is usually a manual resend
+------------------------------------------------------------+
|Chad Z. Hower  -  phoe...@pobox.com                         |
|Phoenix Business Enterprises - p...@pobox.com - www.pbe.com  |
|Physically in Church Hill, TN - Logically Not Sure          |
+------------------------------------------------------------+

Quote
>>SQUID - The ultimate offline databasing reader

**Special Compile: 2.003A (Alpha)

Re:Delphi and MS SQL 6.0 stored procedures


In article <4ho26d$...@news-s01.ny.us.ibm.net>,
stev...@ibm.net (Steven R. Zuch) wrote:

Quote
>>Those that I've talked to so far, seems to agree that more than one
>>simultanously user is not an environment where you'd put an Access database.

>Those I have talked to state that Access can handle small multi-user
>systems (several users).  For large, transaction oriented systems,

They said that?

No problems with frequent database corruption when a VB app crashes hard?

I've had a two user solution bombing out with little or no load at
all... (that doesn't really qualify as a multi-user system, does it?)

The ODBC driver is slow (and buggy, version 2.00.2317), so even if you
get it up and running, it's going to be slow. From what I've seen,
Delphi/Paradox is faster than VB/Access, so why bother with Access?

--
=\
 *=- R.Moberg, CD-Player Pro info @ http://www.sn.no/~mobergru/
=/

Other Threads