|
Home
About the Author
General Programming
Pascal
Delphi
C&C++
Visual Basic
SQL
JAVA Script
Links
| |
SQL
- How to write SQL commands in Delphi
- Connected to internet?
- MP3 Tag
- Current user name
- Shutdown Windows
- 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:
- Place a TQuery, TDataSource, TDBGrid, TEdit, and a TButton component
on the main form.
- Set TDataSource component's DataSet property to Query1.
- Set TDBGrid component's DataSource property to DataSource1.
- Set TQuery component's DatabaseName property to DBDEMOS.
- Double-click on SQL property of a TQuery to assign the SQL statement
to it.
- 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'.
- 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;
- 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
| |
| Newsgroups |
comp.lang.c
comp.lang.c++
comp.programming |
|