Solved

Problem in getting complete content of stored procedure.

Posted on 2004-10-21
21
671 Views
Last Modified: 2012-08-13
Hi Experts,

I'm developing a little program that shows me all my stored procedures and its content (text)
I'm using delphi 5 Ent.
My DB-environment is SQL Server 7 + SQL Server 2000.
OS of my servers is a mix of WinNT/2000.
I'm connecting to the DB using ADO (MDAC 2.8)

My Query looks like this :
       SELECT so.ID, so.Name, so.crdate, so.refdate, sc.text
       FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
       WHERE so.xtype = 'P' -- All Stored Procedure-objects
             AND so.name NOT LIKE 'dt%'  -- Exclude 'system stored procs (all begin with dt....).
       ORDER BY so.Name
In 'sysObjects' I can get the Name, CreationDate of a Stored Procedure.
In 'SysComments' I can find the content (text) of the Stored Procedure.

NOW MY PROBLEM :

All this works just fine, except when the content of a Stored-procedure contains a lot of text.
Then only the first xxxx (I suppose 4000 or something like that) charachters of the content is showed.
I use a TDBRichEdit ( linked to field 'SysComments.Text') to show the content of the SP.
Is there anyone who knows how I can ALWAYS get the COMPLETE content of my stored procedure, even if it contains a lot of text ?

This is very annoying because I have a lot of SP that contain pretty long code.
Thanks in advance.

Best regards,

The Mayor.
0
Comment
Question by:wimmeyvaert
  • 11
  • 9
21 Comments
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12369006
Hm, just noticed something :
when a SP contains very much data, it is splitted into > 1 Syscomment-records !!!

This results in my DBGrid in 2 rows that each hold a part of the SP !
The first record in the Grid shows e.g. the first part of the SP and the second record shows the second part of the SP, ...

So I'm going to add a calculated field to my query which concatenates all the parts.
I'll post a comment if I have more news.

Best regards,

The Mayor.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12369878
Have you tried to ask this question in the SQL Server group? I think the problem may be in your query.
0
 
LVL 11

Expert Comment

by:calinutz
ID: 12369985
Try to show your SP text in a DBMemo or DBRichEdit control (not in a DBGrid). The DBGrid probabely has it's limitations
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12370049
try something like:

SELECT CAST(SC.ctext as VARCHAR(8000)), LEN(SC.ctext), SC.ID FROM syscomments SC
 JOIN sysobjects SO on SC.id=SO.id where SO.xtype='P'
  ORDER BY SC.id
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12370056
calinutz:
He IS!!!
[quote]I use a TDBRichEdit...[/quote]

0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12370139
Hi,

About the comment of EddieShipman : This is not going to solve my problem, because appearantly the complete text of a 'large' stored procedure is splitted into several syscomments-records.

so the only thing I can do (I assume) is to create a calculated field which concatenates all the records of syscomments that points to a certain ID (= SPID).

I had some problems when using a calculated field of type 'ftMemo'. I always get an error like : 'field %s cannot be a calculated or lookup field'.

I'm working on another solution now : just place a normal TRichEdit (instead of a tDBRichedit) and call a self-written function every time when the 'OnDataChange' event occurs of my datasource (linked to my DBGrid).
I almost have a working solution. If I have a complete one, I'll post my code here.

If you have other possible solutions, please post them. They are probaly better then what I'm conctructing right now.

I'll be back

The Mayor.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12370163
Look's like you are going to have to do this in two queries.

First, get the names or id's using this query:

SELECT * from sysobjects WHERE xtype = 'P'

then, iterate through the results and do this:

 SELECT so.ID, so.Name, so.crdate, so.refdate, cast(sc.ctext as VARCHAR(8000))
       FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
       WHERE so.xtype = 'P' -- All Stored Procedure-objects
             AND so.name= :SPName
       ORDER BY so.Name

Set SPName to the name of the procedure you want to retrieve. You will then have to
combine the cText records into one calculated field and display that field in your
DBRichText.


0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12370176
Actually, you could write a stored procedure to do this for you and ignore it in your list.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12370448
I think I got it !!!!

I wrote a separate function which needs an ID as input (ID of SP) and returns a TStringList (= complete SP-code).

This is the function (need to use unit Classes) :

Function GetCompleteSPText(SPID: Integer): TStringList;
var
  strTmp : String;
begin
  Result := TStringList.Create; { Initialise result as blank. }
  Result.Text := '';
  Try
    {
    Query to get all the syscomment-records for a given ID
    The concatenation of all these records = the complete SP-Code !!
    Query = 'select text from SysComments where id = :ID order by colid'
    }
    With qryGetCompleteSPText do
    begin
      Close;
      Parameters.ParamByName('ID').Value := SPID; { ID of Stored Procedure }
      Open;
      strTmp := ''; { temp var to hold the complete SP-Code. }
      While not Eof do
      begin
        strTmp := strTmp + FieldByName('text').Value; { Add a part of SP. }
        Next; { get next record in syscomments-query }
      end; { While not Eof do }
      Result.Text := strTmp; { Fill the result with the concatenated parts. }
      Close;
    end; { With qryGetCompleteSPText do }
  except
    Result.Text := '!!! Error !!!'; { when error -> Fill result with '!!! Error !!!' }
  end; { Try except }
end;

On my form I have a regular TRichEdit (reCompleteSP)
On the OnDataChange-event of my dataSource I put :

procedure TfrmStoredProcs.srcMasterDataChange(Sender: TObject ; Field: TField);
var
  SPID : Integer;
begin
  SPID := srcMaster.DataSet.FieldByName('ID').Value;
  reCompleteSP.Lines.Assign( GetCompleteSPText( SPID ) );
end;

This results in a TRichEdit which holds the complete SP-Code !!!!!

The reason I use a seaparate string-variable in my function 'GetCompleteSPText' is because otherwise my Result would have a CR/LF between every concatenation of 2 parts.

I'll leave this question open for a while. Maybe there are better solutions than mine.
If you have any comments, please provide them here.

Best regards,

The Mayor.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12370591
Hi EddieShipman,

that's where my problem lies.
When I create a Calculated field in Delphi, I get an error 'field %s cannot be a calculated or lookup field'.

The reason must have something to do with the DataFieldType (ftMemo). Looks like this is a kind of 'buggy' in Delphi5.

That's why I made a little workaround for it, and wrote my own function to concatenate all the SP-parts into 1 stringlist, which could then be assigned to my RichEdit on my Form.

Thanks anyway for your input.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 26

Expert Comment

by:EddieShipman
ID: 12370762
Did you try writing a stored procedure to do all the concatenation for you and use TADOStoredProc to get the values?
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12378411
Hi EddieShipman,

No I haven't, but the problem there is that the Stored procedure should return a variable (output pramater) which holds the complete SP-Code.

The largest varchar you can declare is a varchar(8000), and in some cases this is not large enough.

Thanks for you input.

The Mayor.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12380608
I still think you should use the query I posted to get upto 8000 char vs using the text field,
which returns much less, in your GetCompleteSpText function.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12572866
Hi EddieShipman,

I'm very sure that there are some stored Procedures that hold > 8000 charchters.
So your suggestion won't have the complete stored procedure code in it.

I'm thinking of closing the question, since there are no more suggestions since a while.

Best regards,

The Mayor.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12576475
Did you try my suggestion to write a stored proc to concatenate and return the results? You can return a binary that should hold all the text, if I'm not mistaken.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12581632
EddieShipman,

Binary you say ?
Hm, Do you know what kind of Field I should then use in Delphi (to link to my TTable).
I have to create a Calculated Field which should be filled with the complete content of a Stored Procedure. Whixh type should this Calculated field be ?
Any ideas ?

Thanks,

The Mayor.

PS : I have to say that my proposed way of working does the trick very well also, but I'm willing to evaluate your comment too if it works.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12584295
I THINK I'VE GOT IT!!!!

Use the query I showed above but cast as sql_variant data type:

SELECT
  so.ID, so.Name, so.crdate, so.refdate, cast(sc.ctext as sql_variant) as sptext
FROM
  sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
WHERE
  so.xtype = 'P' -- All Stored Procedure-objects
  AND
    so.name= :SPName
ORDER BY so.Name

Then iterate through your records and concatenate them.
Still working on a stored proc to do it.

0
 
LVL 26

Accepted Solution

by:
EddieShipman earned 250 total points
ID: 12589095
OK, try this stored procedure. It works just like you want and returns the value in one field.
CREATE proc spgetSPText(@sp_name sysname) as
SET NOCOUNT ON
declare C1 cursor for
     SELECT sc.text
     FROM sysobjects so
     INNER JOIN syscomments sc ON so.id = sc.id
     WHERE so.xtype = 'P' and so.name = @sp_name
     ORDER BY sc.colid
declare @sp_line nvarchar(4000), @ptr varbinary(16)
select cast(N'' as ntext) as sp_text into #temp
select @ptr = textptr(sp_text) from #temp
open C1
fetch C1 into @sp_line
while @@fetch_status = 0
begin
     updatetext #temp.sp_text @ptr null 0 @sp_line
     fetch C1 into @sp_line
end
close C1
deallocate C1
select sp_text from #temp
GO
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12592092
Hi EddieShipman,

Don't you need to declare an output-parameter ?
I need a returnvalue to do something about it in Delphi, don't I ?

I setup a little delphi-application with 1 TEdit, 1 TButton and 1 TMemo (and of cource a TADOConnection and a TADOStoredProc).
In Edit1 there is the SP-Name to get the complete text  from.
In Memo1, the complete SP-Text should be visualised.
In the Onclick of the button I have the following code :

  Try
    With ADOStoredProc1 do
    begin
      Close;
      Parameters.ParamByName('@sp_name').Value := Edit1.Text;
      ExecProc;
      { HERE LIES MY PROBLEM : I DON'T HAVE A PARAMETER THAT HOLDS THE COMPLETE SP-TEXT ! }
      Memo1.Text := Parameters.ParamByName('@RETURN_VALUE').Value;  { ??? Integer ??? }
      Close;
    end;
  except
    On E:Exception do
      Memo1.Text := 'ERROR: ' + E.Message;
  end;


What should I do to fill the Memo with the complet SP-Text ?
This is what I'm trying to explain. You can't (as far as I know) declare an output-variable in the Stored Procedure that is big enough to hold > 8000 characters. And you really need an output-variable to use it after the ExceProc-Method in order to fill the Memo-Component with the value of this output-parameter.

So, If you can arrange to have an output-parameter in the Stored Procedure that can hold > 8000 characters, then I'm willing to grant you the points !
If not, then I'm preferring my way of working (to have a Calculated field and manually concatenate all the SP-parts into 1 big string-variable by using a Query in Delphi itself).

Thanks again for you input.

Best regards,

The Mayor.

0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12593486
I used a TADOQuery with the sql "exec spGetSPText spchargeInsertInternal" and it returned one column with one row with
the complete sp test. I hooked up a DBMemo and it was all there.

No ADOStoredProc.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12642740
Hi EddieShipman,

Sorry for my late relpy.
I tested out you suggestion and it works like it should.
I already had another way of working that works too.
But since you gave me a correct/working solution for my problem, I grant you the points.

Thanks for your help and support.

Best regards,

The Mayor.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now