Delphi

Home

About the Author

General Programming

Pascal

Delphi

C&C++

Visual Basic

SQL

JAVA Script

Links

SQL

1. How to write SQL commands in Delphi
2. Connected to internet?
3. MP3 Tag
4. Current user name
5. Shutdown Windows
6. Using Help Files

Any questions mail them to delphi@teentwo.8m.com

1. How to write SQL commands in Delphi

SQL (Structured Query Language) is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is perceived as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more base tables.

Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth.

in Delphi ... TQuery
If you are going to use SQL in your applications, you will become very familiar with the TQuery component. Delphi enables your applications to use SQL syntax directly though TQuery component to access data from:
Paradox and dBase tables (using local SQL - subset of ANSI standard
SQL), Databases on the Local InterBase Server, and Databases on remote
database servers.Delphi also supports heterogeneous queries against more than one server or table type (for example, data from an Oracle table and a Paradox table).

TQuery has a property called SQL, which is used to store the SQL statement.
TQuery encapsulates one or more SQL statements, executes them and provides methods by which we can manipulate the results. Queries can be divided into two categories: those that produce result sets (such as a SELECT statement), and those that don't (such as an UPDATE or INSERT statement). Use TQuery.Open to execute a query that produces a result set; use TQuery.ExecSQL to execute queries that do not produce result
sets.

The SQL statements can be either static or dynamic, that is, they can be set at design time or include parameters (TQuery.Params) that vary at run time. Using parameterized queries is very flexible, because you can change a user's view of and access to data on the fly at run time.

All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL. At design time a query is prepared and executed automatically when you set the query component's Active property to True. At run time, a query is prepared with a call to Prepare, and executed when the application calls the component's Open or ExecSQL methods.

A TQuery can return two kinds of result sets: "live" as with TTable component (users can edit data with data controls, and when a cool to Post occurs changes are send to database), "read only" for displaying purposes only. To request a live result set, set a query component's RequestLive property to True, and be aware that SQL statement must meet some specific requirements (no ORDER BY, SUM, AVG, etc.)

A query behaves in many ways very much like a table filter, and in some way a query is even more powerful than a filter because it lets you access: more than one table at a time ("join" in SQL), a specified subset of columns (rows and) in its underlying table(s), rather than always returning all columns (and rows).

Simple example
We could now see some SQL in action. Even if we can use Database Form Wizard to create some "SQL example", let's do it step by step:

1. Place a TQuery, TDataSource, TDBGrid, TEdit, and a TButton component on the main form.
2. Set TDataSource component's DataSet property to Query1.
3. Set TDBGrid component's DataSource property to DataSource1.
4. Set TQuery component's DatabaseName property to DBDEMOS.
5. Double-click on SQL property of a TQuery to assign the SQL statement to it.
6. To make the grid display data at design time, change TQuery component's Active property to True.

As you can see, grid displays data from Employee.db table in three
columns (FirstName, LastName, Salary) even if Emplyee.db has 7 fields,
and result set is restricted to those records where FirstName begins
with 'R'.

7. Now assign the following code to the OnClick event of the Button1.

procedure TForm1.Button1Click(Sender: TObject);
begin

Query1.Close;{close the query}
//assign new SQL expression
Query1.SQL.Clear;
Query1.SQL.Add ('Select EmpNo, FirstName, LastName');
Query1.SQL.Add ('FROM Employee.db');
Query1.SQL.Add ('WHERE Salary > ' + Edit1.Text);
Query1.Open; {open query + display data}

end;

8. Run your application. When you click on Button (with some valid
   currency value in it), grid will display EmpNo, FirstName and LastName
   fields with records where Salary is greater than specified currency
   value.

In this example we created simple static SQL statement with live result
set (we haven changed any of displayed records) just for displaying
purposes.

And beyond...
What SQL is and what SQL/Delphi combination has to offer to Delphi
developer is far beyond the scope of this article.

Any questions mail them to delphisql@teentwo.8m.com

2. Connected to internet?

interface 
uses 
  Windows, SysUtils, Registry, WinSock, WinInet; 

type 
  TConnectionType = (ctNone, ctProxy, ctDialup); 

function ConnectedToInternet : TConnectionType; 
function RasConnectionCount : Integer; 
  

implementation 

//For RasConnectionCount ======================= 
const 
  cERROR_BUFFER_TOO_SMALL = 603; 
  cRAS_MaxEntryName       =  256;  
  cRAS_MaxDeviceName      =  128; 
  cRAS_MaxDeviceType      =  16; 
type 
  ERasError = class(Exception); 

  HRASConn = DWord; 
  PRASConn = ^TRASConn; 
  TRASConn = record 
     dwSize: DWORD; 
     rasConn: HRASConn; 
     szEntryName: Array[0..cRAS_MaxEntryName] Of Char;  
     szDeviceType : Array[0..cRAS_MaxDeviceType] Of Char;  
     szDeviceName : Array [0..cRAS_MaxDeviceName] of char;  
  end; 

  TRasEnumConnections = 
      function (RASConn: PrasConn; { buffer to receive Connections data }  
                var BufSize: DWord;    { size in bytes of buffer }  
                var Connections: DWord { number of Connections written to buffer }  
                ): LongInt; stdcall;  
//End RasConnectionCount ======================= 
  

function ConnectedToInternet: TConnectionType; 
var 
  Reg       : TRegistry; 
  bUseProxy : Boolean; 
  UseProxy  : LongWord; 
begin 
  Result := ctNone; 
  Reg := TRegistry.Create; 
  with REG do 
  try 
    try 
      RootKey := HKEY_CURRENT_USER; 
      if OpenKey('\Software\Microsoft\Windows\CurrentVersion\Internet settings',False) then begin  
        //I just try to read it, and trap an exception  
        if GetDataType('ProxyEnable') = rdBinary then  
          ReadBinaryData('ProxyEnable', UseProxy, SizeOf(LongWord) )  
        else begin 
          bUseProxy := ReadBool('ProxyEnable');  
          if bUseProxy then  
            UseProxy := 1  
          else 
            UseProxy := 0;  
        end; 
        if (UseProxy <> 0) and ( ReadString('ProxyServer') <> '' ) then Result := ctProxy;  
      end; 
    except 
      //Obviously not connected through a proxy  
    end; 
  finally 
    Free; 
  end; 

  //We can check RasConnectionCount even if dialup networking is not installed  
  //simply because it will return 0 if the DLL is not found.  
  if Result = ctNone then begin 
    if RasConnectionCount > 0 then Result := ctDialup;  
  end; 
end; 

function RasConnectionCount : Integer; 
var 
  RasDLL    : HInst; 
  Conns     : Array[1..4] of TRasConn;  
  RasEnums  : TRasEnumConnections; 
  BufSize   : DWord; 
  NumConns  : DWord; 
  RasResult : Longint; 
begin 
  Result := 0; 

  //Load the RAS DLL 
  RasDLL := LoadLibrary('rasapi32.dll'); 
  if RasDLL = 0 then exit; 

  try 
    RasEnums := GetProcAddress(RasDLL,'RasEnumConnectionsA');  
    if @RasEnums = nil then 
      raise ERasError.Create('RasEnumConnectionsA not found in rasapi32.dll');  

    Conns[1].dwSize := Sizeof (Conns[1]); 
    BufSize := SizeOf(Conns); 

    RasResult := RasEnums(@Conns, BufSize, NumConns);  

    If (RasResult = 0) or (Result = cERROR_BUFFER_TOO_SMALL) then Result := NumConns;  
  finally 
    FreeLibrary(RasDLL); 
  end; 
end;

Any questions mail them to delphi@teentwo.8m.com

3. MP3 Tag

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls;

type
  TID3Tag = record
    ID:string[3];
    Titel:string[30];
    Artist:string[30];
    Album:string[30];
    Year:string[4];
    Comment:string[30];
    Genre:byte;
  end;

  TForm1 = class(TForm)
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    procedure Button1Click(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;
  ID3Tag:TID3Tag;

implementation

{$R *.DFM}

procedure Lese_ID3Tag(Filename:string);
var
  Buffer:array[1..128] of char;
  F:File;
begin
  AssignFile(F, Filename);
  Reset(F,1);
  Seek(F,FileSize(F)-128);
  BlockRead(F, Buffer, SizeOf(Buffer));
  CloseFile(F);
  with ID3Tag do begin
    ID:=copy(Buffer,1,3);
    Titel:=copy(Buffer,4,30);
    Artist:=copy(Buffer,34,30);
    Album:=copy(Buffer,64,30);
    Year:=copy(Buffer,94,4);
    Comment:=copy(Buffer,98,30);
    Genre:=ord(Buffer[128]);
  end;
end;


procedure TForm1.Button1Click(Sender: TObject);
begin
  if Opendialog1.Execute then
  begin
    Lese_ID3Tag(opendialog1.filename);
    showmessage(id3tag.ID);
    showmessage(id3tag.Titel);
    showmessage(id3tag.Artist);
    showmessage(id3tag.Album);
    showmessage(id3tag.Year);
    showmessage(id3tag.Comment);
    showmessage(id3tag.Genre);
  end;
end;

end.

Any questions mail them to delphi@teentwo.8m.com

4. Current user name

function GetCurrentUserName : string;  
const 
  cnMaxUserNameLen = 254; 
var 
  sUserName : string; dwUserNameLen : DWord; 
begin 
  dwUserNameLen := cnMaxUserNameLen-1; 
  SetLength( sUserName, cnMaxUserNameLen ); 
  GetUserName( PChar( sUserName ), dwUserNameLen ); SetLength( sUserName,   dwUserNameLen );  
  Result := sUserName; 
end;

Any questions mail them to delphi@teentwo.8m.com

5. Shutdown Windows

Parameters:

EWX_SHUTDOWN  :  shut down windows (Power off)
EWX_REBOOT    :  reboot windows
EWX_POWEROFF  :  Standby mode

procedure TForm1.Button1Click(Sender: TObject);
begin
  ExitWindowsEx(EWX_SHUTDOWN,0);
end;

Any questions mail them to delphi@teentwo.8m.com

6. Using Help Files

procedure TForm1.Button1Click(Sender: TObject);

begin
  Application.HelpFile := 'vcl.hlp';
  Application.HelpJump('TApplication_HelpJump');
end;

Any questions mail them to delphi@teentwo.8m.com

This page is best viewed in 800x600x256. This page was last edited Monday, July 31, 2000