How to assign a multiline string value without quoting each line?

Question

Is there a way to assign a multiline string value in Delphi without having to quote each line?

Edit (the specific problem): I have some SQL queries which I want to test outside Delphi. When copying the queries it is a bit of overhead to add and replace quotes every time.

Solution 1

Here's code for an application you can add to the IDE's Tools menu that might help. It was posted a while back to one of the CodeGear newsgroups by TeamB member Peter Below:

program ClipToStringConst;

// Remove the dot from the line below for a console app, 
// per Rob Kennedy's comment. It works fine without being
// a console app.
{.$APPTYPE CONSOLE}
uses
  Windows,
  Classes,
  Sysutils,
  APIClipboard;

const
  cIndent = '  '; // 2 spaces
  cSingleQuote = '''';
  EndChar : array [Boolean] of Char = ('+',';');

procedure Process;
var
  SL: TStringlist;
  i, max: Integer;
begin
  if ClipboardHasFormat( CF_TEXT ) then
  begin
    SL := TStringlist.Create;
    try
      SL.Text := ClipboardAsString;
      max := SL.count-1;
      for i:= 0 to max do
        SL[i] := cIndent +
                 AnsiQuotedStr( TrimRight(SL[i])+#32, cSingleQuote ) +
                 EndChar[i = max];
      StringToClipboard( SL.Text );
    finally
      SL.Free;
    end; { Finally }
  end;
end;

begin
  try
    Process;
  except
    on E: Exception do
      ShowException( E, ExceptAddr );
  end;
end.

Just select the text in the SQL management tool after you've tested it and copy it to the clipboard. Switch to the Delphi Code Editor, place the insertion point where you want the constant text to appear, choose 'Clipboard To Const' or whatever you called it from the Tools menu, and then Ctrl+V to paste it into the editor.

It's a pretty handy little tool. You can also modify it to work the opposite way (ConstantToClipboard) to remove the source formatting and revert back to raw SQL, although I haven't bothered to do so yet.

EDIT: Missed a unit (APIClipboard). This needs to be a separate unit, obviously. Again, thanks to Peter Below:

{== Unit APIClipboard =================================================}
{: Clipboard access routines using only API functions
@author Dr. Peter Below
@desc Version 1.0 created 5 Juli 2000<BR>
        Current revision 1.0<BR>
        Last modified 5 Juli 2000<P>

This unit provides simply clipboard access routines that do not rely on
the VCL Clipbrd unit. That unit drags in Dialogs and Forms and a major
part of the VCL as a consequence, not appropriate for simple console
or non-form programs. This unit uses only API routines, the only VCL
units used are Classes (for exceptions and streams) and SysUtils.
}
{=====================================================================}

unit APIClipboard;

interface

uses
  Windows, Classes;

  procedure StringToClipboard( const S: String );
  function ClipboardAsString: String;
  procedure CopyDataToClipboard( fmt: DWORD; const data; datasize: Integer;
                                 emptyClipboardFirst: Boolean = true );
  procedure CopyDataFromClipboard( fmt: DWORD; S: TStream );
  function ClipboardHasFormat( fmt: DWORD ): Boolean;

implementation

uses
  Sysutils;

type
  {: This is an internal exception class used by the <see unit=APIClipboard> }
  EClipboardError = class( Exception )
  public
    constructor Create( const msg: String );
  end;

resourcestring
  eSystemOutOfMemory =
    'could not allocate memory for clipboard data.';
  eLockfailed =
    'could not lock global memory handle.';
  eSetDataFailed =
    'could not copy data block to clipboard.';
  eCannotOpenClipboard =
    'could not open the clipboard.';
  eErrorTemplate =
    'APIClipboard: %s'#13#10+
    'System error code: %d'#13#10+
    'System error message: %s';

{-- EClipboardError.Create --------------------------------------------}
{: Creates a new EclipboardError object
@Param msg is the string to embed into the error message
@Precondition none
@Postcondition none
@desc Composes an error message that contains the passed message and the
  API error code and matching error message. The CreateFmt constructor
  inherited from the basic Exception class is used to do the work.
Created 5.7.2000 by P. Below
}{---------------------------------------------------------------------}

constructor EClipboardError.Create( const msg: String );
begin { Create }
  CreateFmt( eErrorTemplate,
               [msg, GetLastError, SysErrorMessage(GetLastError)] );
end; { EClipboardError.Create }

{-- DataToClipboard ---------------------------------------------------}
{: Copies a block of memory to the clipboard in a given format
@Param fmt is the clipboard format to use
@Param data is an untyped const parameter that addresses the data to copy
@Param datasize is the size of the data, in bytes
@Precondition The clipboard is already open. If not an EClipboardError
  will result. This precondition cannot be asserted, unfortunately.
@Postcondition Any previously exisiting data of this format will have
  been replaced by the new data, unless datasize was 0 or we run into an
  exception. In this case the clipboard will be unchanged.
@desc Uses API methods to allocate and lock a global memory block of
  approproate size, copies the data to it and submits the block to the
  clipboard. Any error on the way will raise an EClipboardError
  exception.<BR>
Created 5.7.2000 by P. Below
@Raises EClipboardError
}{---------------------------------------------------------------------}

procedure DataToClipboard( fmt: DWORD; Const data; datasize: Integer );
var
  hMem: THandle;
  pMem: Pointer;
begin { DataToClipboard }
  if datasize <= 0 then
    Exit;

  hMem := GlobalAlloc( GMEM_MOVEABLE or GMEM_SHARE or GMEM_ZEROINIT, datasize );
  if hmem = 0 then
    raise EClipboardError.Create( eSystemOutOfMemory );

  pMem := GlobalLock( hMem );
  if pMem = nil then
  begin
    GlobalFree( hMem );
    raise EClipboardError.Create( eLockFailed );
  end;

  Move( data, pMem^, datasize );
  GlobalUnlock( hMem );
  if SetClipboardData( fmt, hMem ) = 0 then
    raise EClipboardError( eSetDataFailed );

  // Note: API docs are unclear as to whether the memory block has
  // to be freed in case of failure. Since failure is unlikely here
  // lets blithly ignore this issue for now.
end; { DataToClipboard }

{-- DataFromClipboard -------------------------------------------------}
{: Copies data from the clipboard into a stream
@Param fmt is the clipboard format to look for
@Param S is the stream to copy to
@precondition S <> nil
@postcondition If data was copied the streams position will have moved
@desc Tries to get a memory block for the requested clipboard format.
Nothing
  further is done if this fails (because the format is not available or
  the clipboard is not open, we treat neither as error here), otherwise
  the memory handle is locked and the data copied into the stream. <P>
  Note that we cannot determine the actual size of the data originally
  copied to the clipboard, only the allocated size of the memory block!
  Since GlobalAlloc works with a granularity of 32 bytes the block may be
  larger than required for the data and thus the stream may contain some
  spurious bytes at the end. There is no guarantee that these bytes will
  be 0. <P>
  If the memory handle obtained from the clipboard cannot be locked we
  raise an <see class=EClipboardError> exception.
Created 5.7.2000 by P. Below
@Raises EClipboardError
}{---------------------------------------------------------------------}

procedure DataFromClipboard( fmt: DWORD; S: TStream );
var
  hMem: THandle;
  pMem: Pointer;
  datasize: DWORD;
begin { DataFromClipboard }
  Assert( Assigned( S ));
  hMem := GetClipboardData( fmt );
  if hMem <> 0 then
  begin
    datasize := GlobalSize( hMem );
    if datasize > 0 then
    begin
      pMem := GlobalLock( hMem );
      if pMem = nil then
        raise EclipboardError.Create( eLockFailed );
      try
        S.WriteBuffer( pMem^, datasize );
      finally
        GlobalUnlock( hMem );
      end;
    end;
  end;
end; { DatafromClipboard }

{-- CopyDataToClipboard -----------------------------------------------}
{: Copies a block of memory to the clipboard in a given format
@Param fmt is the clipboard format to use
@Param data is an untyped const parameter that addresses the data to copy
@Param datasize is the size of the data, in bytes
@Param emptyClipboardFirst determines if the clipboard should be emptied,
  true by default
@Precondition The clipboard must not be open already
@Postcondition If emptyClipboardFirst is true all prior data will be
  cleared from the clipboard, even if datasize is <= 0. The clipboard
  is closed again.
@desc Tries to open the clipboard, empties it if required and then tries to
  copy the passed data to the clipboard. This operation is a NOP if
  datasize <= 0. If the clipboard cannot be opened a <see
class=EClipboardError>
  is raised.
Created 5.7.2000 by P. Below
@Raises EClipboardError
}{---------------------------------------------------------------------}

procedure CopyDataToClipboard( fmt: DWORD; const data; datasize: Integer;
                               emptyClipboardFirst: Boolean = true );
begin { CopyDataToClipboard }
  if OpenClipboard( 0 ) then
    try
      if emptyClipboardFirst then
        EmptyClipboard;
      DataToClipboard( fmt, data, datasize );
    finally
      CloseClipboard;
    end
  else
    raise EclipboardError.Create( eCannotOpenClipboard );
end; { CopyDataToClipboard }

{-- StringToClipboard -------------------------------------------------}
{: Copies a string to clipboard in CF_TEXT clipboard format
@Param S is the string to copy, it may be empty.
@Precondition The clipboard must not be open already.
@Postcondition Any prior clipboard content will be cleared, but only
  if S was not empty. The clipboard is closed again.
@desc Hands the brunt of the work off to <See routine=CopyDataToClipboard>,
  but only if S was not empty. Otherwise nothing is done at all.<BR>
Created 5.7.2000 by P. Below
@Raises EClipboardError
}{---------------------------------------------------------------------}

procedure StringToClipboard( const S: String );
begin
  if Length(S) > 0 Then
    CopyDataToClipboard( CF_TEXT, S[1], Length(S)+1);
end; { StringToClipboard }

{-- CopyDataFromClipboard ---------------------------------------------}
{: Copies data from the clipboard into a stream
@Param fmt is the clipboard format to look for
@Param S is the stream to copy to
@Precondition S <> nil<P>
  The clipboard must not be open already.
@Postcondition If data was copied the streams position will have moved.
  The clipboard is closed again.
@desc Tries to open the clipboard, and then tries to
  copy the data to the passed stream. This operation is a NOP if
  the clipboard does not contain data in the requested format.
  If the clipboard cannot be opened a <see class=EClipboardError>
  is raised.
Created 5.7.2000 by P. Below
@Raises EClipboardError
}{---------------------------------------------------------------------}

procedure CopyDataFromClipboard( fmt: DWORD; S: TStream );
begin { CopyDataFromClipboard }
  Assert( Assigned( S ));
  if OpenClipboard( 0 ) then
    try
      DataFromClipboard( fmt , S );
    finally
      CloseClipboard;
    end
  else
    raise EclipboardError.Create( eCannotOpenClipboard );
end; { CopyDataFromClipboard }

{-- ClipboardAsString -------------------------------------------------}
{: Returns any text contained on the clipboard
@Returns the clipboards content if it contained something in CF_TEXT
  format, or an empty string.
@Precondition The clipboard must not be already open
@Postcondition The clipboard is closed.
@desc If the clipboard contains data in CF_TEXT format it is copied to a
  temp memory stream, zero-terminated for good measure and copied into
  the result string.
Created 5.7.2000 by P. Below
@Raises EClipboardError
}{---------------------------------------------------------------------}

function ClipboardAsString: String;
const
  nullchar: Char = #0;
var
  ms: TMemoryStream;
begin { ClipboardAsString }
  if not IsClipboardFormatAvailable( CF_TEXT ) then
    Result := EmptyStr
  else
  begin
    ms:= TMemoryStream.Create;
    try
      CopyDataFromClipboard( CF_TEXT , ms );
      ms.Seek( 0, soFromEnd );
      ms.WriteBuffer( nullChar, Sizeof( nullchar ));
      Result := PChar( ms.Memory );
    finally
      ms.Free;
    end;
  end;
end; { ClipboardAsString }

{-- ClipboardHasFormat ------------------------------------------------}
{: Checks if the clipboard contains data in the specified format
@Param fmt is the format to check for
@Returns true if the clipboard contains data in this format, false
  otherwise
@Precondition none
@Postcondition none
@desc This is a simple wrapper around an API function.
Created 5.7.2000 by P. Below
}{---------------------------------------------------------------------}

function ClipboardHasFormat( fmt: DWORD ): Boolean;
begin { ClipboardHasFormat }
  Result := IsClipboardFormatAvailable( fmt );
end; { ClipboardHasFormat }

end.

Sample use:

Prepare the text in your SQL editor, text editor, or whatever:

SELECT 
  lname,
  fname,
  dob
FROM
  employees

Select all of the text, and copy to the clipboard using Ctrl+C.

Switch to the IDE's Code Editor, run the ClipboardToStringConst application (using the Tools menu item you added, or whatever other means you want). Place the editor's cursor (insertion point) where you want the constant text to appear, and press Ctrl+V to paste in the text.

const
  MySQLText = |            // The pipe indicates the insertion point.

The result:

const
  MySQLText =   'SELECT '+
  '  lname, '+
  '  fname, '+
  '  dob '+
  'FROM '+
  '  employees ';

Solution 2

We had the same problem, and finally we created a small IDE plugin (merged with existing solutions). That creates two extra menu items (Copy and Paste extra). One of this pastes the formatted content of the clipboard to the code editor, the other does the same thing in reverse (copy the content of the selection to the clipboard and removes the extra charachters).

To use this:

1. Create new Package in Delphi
2. Add to "designide" to requires section (and remove anything else)
3. Create new Unit, and copy the code
4. Build and Install

Sample code:

unit ClipboardWizard;

interface

uses
  Windows, SysUtils, Classes, ToolsAPI, 
  {$ifdef VER280} // XE7
  VCL.Menus
  {$else}
  Menus
  {$endif};

type
  TClipboardWizard = class(TInterfacedObject, IOTAWizard)
  private
    FMainMenuItem, FCopyMenuItem, FPasteMenuItem:  TMenuItem;

    // Formatting
    function GetFormattedString: string;
    function RemoveUnneededChars(const Value: string): string;

    // Menu events
    procedure CopyToClipboard(Sender: TObject);
    procedure PasteFromClipboard(Sender: TObject);
  public
    // TObject
    constructor Create;
    destructor Destroy; override;

    // IOTANotifier
    procedure AfterSave;
    procedure BeforeSave;
    procedure Destroyed;
    procedure Modified;

    // IOTAWizard
    function GetIDString: string;
    function GetName: string;
    function GetState: TWizardState;
    procedure Execute;
  end;

procedure Register;

implementation

uses
  Vcl.Clipbrd, System.StrUtils;

procedure Register;
begin
  RegisterPackageWizard(TClipboardWizard.Create);
end;

// Formatting 

function TClipboardWizard.RemoveUnneededChars(const Value: string): string;
var
  List: TStringList;
  q: integer;
  s : string;
begin
  if Trim(Value) <> '' then
  begin
    List := TStringList.Create;
    try
      List.Text := Value;
      for q := 0 to List.Count - 1 do
      begin
        s := Trim(List[q]);
        if Length(s) > 0 then
          if s[1] = '''' then
            s := Copy(s, 2, Length(s));

        s := TrimLeft(ReverseString(s));

        if Length(s) > 0 then
          if s[1] = '+' then
            s := TrimLeft(Copy(s, 2, Length(s)));

        if Length(s) > 0 then
          if s[1] = ';' then
            s := TrimLeft(Copy(s, 2, Length(s)));

        if Length(s) > 0 then
          if s[1] = '''' then
            s := TrimLeft(Copy(s, 2, Length(s)));

        s := StringReplace(s, '''''', '''', [rfReplaceAll]);

        List[q] := ReverseString(s)
      end;

      Result := List.Text;
    finally
      List.Free;
    end;
  end
  else
    Result := '';
end;

procedure TClipboardWizard.CopyToClipboard(Sender: TObject);
begin
  with BorlandIDEServices as IOTAEditorServices do
    if Assigned(TopView) then
      Clipboard.AsText := RemoveUnneededChars(TopView.Block.Text);
end;

function TClipboardWizard.GetFormattedString: string;
const
  FSingleQuote = '''';
  Indent: array [boolean] of string = ('  ', '');
  EndChar: array [boolean] of string = (' +', ';');
var
  List: TStringlist;
  q: Integer;
begin
  if Clipboard.HasFormat(CF_TEXT) then
  begin
    List := TStringlist.Create;
    try
      List.Text := Clipboard.AsText;

      for q := 0 to List.Count - 1 do
        List[q] := Indent[q <> 0] + AnsiQuotedStr(TrimRight(List[q]) + #32, FSingleQuote) +
                   EndChar[q = (List.Count - 1)];

      Result := List.Text;
    finally
      List.Free;
    end;
  end;
end;

procedure TClipboardWizard.PasteFromClipboard(Sender: TObject);
begin
  with BorlandIDEServices as IOTAEditorServices do
    if Assigned(TopView) then
    begin
       TopView.Buffer.EditPosition.InsertText(GetFormattedString);
       TopView.Paint; // invalidation
    end;
end;


{ Anything else }
constructor TClipboardWizard.Create;
var
  NTAServices : INTAServices;
begin
  NTAServices := BorlandIDEServices as INTAServices;

  // Main Menu
  FMainMenuItem := TMenuItem.Create(nil);
  FMainMenuItem.Caption := 'Clibrd Extra' ;
  NTAServices.MainMenu.Items.Add(FMainMenuItem);

  // Sub Menus
  FCopyMenuItem := TMenuItem.Create(nil);
  FCopyMenuItem.Caption := 'Copy to clipboard';
  FCopyMenuItem.OnClick := Self.CopyToClipboard;
  FMainMenuItem.Add(FCopyMenuItem);

  FPasteMenuItem := TMenuItem.Create(nil);
  FPasteMenuItem.Caption := 'Paste from clipboard';
  FPasteMenuItem.OnClick := Self.PasteFromClipboard;
  FMainMenuItem.Add(FPasteMenuItem);
end;

destructor TClipboardWizard.Destroy;
begin
  if Assigned(FPasteMenuItem) then
    FreeAndNil(FPasteMenuItem);

  if Assigned(FCopyMenuItem) then
    FreeAndNil(FCopyMenuItem);

  if Assigned(FMainMenuItem) then
    FreeAndNil(FMainMenuItem);

  inherited;
end;


{ IOTANotifier }
procedure TClipboardWizard.AfterSave;
begin
end;

procedure TClipboardWizard.BeforeSave;
begin
end;

procedure TClipboardWizard.Destroyed;
begin
end;

procedure TClipboardWizard.Modified;
begin
end;

{ IOTAWizard }

function TClipboardWizard.GetIDString: string;
begin
  Result := 'Clipboard.Wizard7';
end;

function TClipboardWizard.GetName: string;
begin
  Result := 'Clipboard Wizard7';
end;

function TClipboardWizard.GetState: TWizardState;
begin
  Result := [];
end;

procedure TClipboardWizard.Execute;
begin
end;


end.

I know the code is not perfect, but it works :-)

Solution 3

You mean something like this?

myStr := 'first line'#13#10'secondline'#13#10'thirdline';

Solution 4

You could consider putting your SQL in TQuery components on Forms or Data Modules.

This solves the copy/paste problem, but it introduces others (such as the diffs between two versions of a query being worse).

Solution 5

The short answer is no, it can't be done. (I know that is not what you want to hear.)

However Andreas Hausladen did develop an extension capable of just this. I googled for it but couldn't find it. I think it was in his DLangExtensions pack, of which he dropped support in late 2007 already. :(

View more solutions

Comments

[Svein Bringsli]

Just out of curiosity, where outside Delphi do you test? If it happens to be in Toad (a common tool) there's a function there for exactly this. Copy the string in Delphi, and in Toad press "Ctrl+P" (strip sql from non-sql code)

[Tihauan]

Thanks, this is not exactly what I needed. I edited the question adding the specific issue.

[Scoregraphic]

That's a completely different question...you should consider adjusting the title or opening a new question for this

[Tihauan]

If someone bothered to write this I assume there is no support for assigning non quoted strings in Delphi. Thank you!

[Rob Kennedy]

It would be even better if it weren't a console program. Remove the Apptype directive so it won't flicker with a console window that it never uses. (It uses the console to display exception messages, but then it exists before you could possibly read it. A non-console program will display the message in a dialog box.)

[Ken White]

@Rob: You're probably right, but I posted it as Peter Below wrote it. I just ignore the flicker of the console window, since it's so fast. :-)

[jrodenhi]

@Ken, What is the difference between the code in the second clipping and the code in the first clipping? The second one has no returns when I try to paste it into my editor.

[Ken White]

@jrodenhi: What 'first' and 'second' clippings? They're two separate units, one for the ClipToStringConst.pas file and one for the APIClipboard.pas file. In order to get returns, the original text should be multi-line, just like you'd want it in the constant; the only difference is that your original text wouldn't have the quotes, CR/LF pairs, and plus signs that the utility adds for you. I'll edit to show the use better.

[Ken White]

@jrodenhi: There are no 'returns' added. It's a multi-line constant in the IDE, meaning it's spread over several lines in the editor. The OP didn't ask for CR/LF pairs, although they'd be pretty simple to add in the code if you need them.

[jrodenhi]

@Ken Sorry for the confusion. I am trying to copy and paste the code from these two code blocks. The first one copies fine. The second one is a bit of a jumble because it copies all onto one line. Also, the procedure DataToClipboard seems incomplete, ending with the line "if datasize nil". The next thing is @postcondition. I don't think that will compile.

[Ken White]

Ah, got it... I don't know what's going on there. I copied and pasted it directly from a compiling unit into the SO editor, and it appears fine. Also, opening the actual unit in a hex editor appears fine, and each line is ended with a CR/LF pair correctly.

[Ken White]

I just tried again with a different type of code formatting. Hopefully that helps (it works if I copy and paste now into Notepad).

[jrodenhi]

Thanks, Ken. Works for me now, too.

[Ken White]

Thanks, Jack, for letting me know things worked out. Still don't know what went screwy with the code formatting the first time...

[Jamie Kitson]

I'm using Delphi 2007 and don't see this behaviour. Do I have to do it in a certain order, or enable it in the options?

[Reversed Engineer]

This is the best way to my mind for including long SQL queries that can't go into a TQuery component (e.g. TFDBatchMoveSQLReader, which only has a string property for it's SQL, not a TStringList). Plus One