Delphi

Question:
What can I do to help avoid server side locking (including DEADLOCK)
problems when working with Microsoft SQL Server (DBLIB) and Sybase
SQL Server (DBLIB)?

Answer:
The following suggestions may help you tune your application and
server. The server side suggestions may not apply to all server
and database installations.

From the client application you may want to take greater control
over the size of results sets (this may mean using TQueries),
minimize the length of transactions (usually not an issue if the
SQLPASSTHRU MODE is set to ...AUTOCOMMIT), and only open dbaware
controls when necessary to help minimize resource drain on the
server and, possibly in this case, deadlock potential.

The means by which the BDE selects data from each of the supported
servers does not make assumptions about how each server chooses
to ensure data integrity.

Delphi/BDE suggestions:

Work with smaller result sets (TQueries, server views, etc.)
also see above form more info.
Check the SQL Links MSSQL Driver "TDS Packet Size" param
making sure that it is set to, at least, 4096.
Minimize the length of transactions.
Investigate creating appropriate indexes.
Filter results before opening a dataset or use tqueries
(live or otherwise) to limit the number of rows selected.
Investigate using the BDE SQL PASSTHRU MODE parameter
"NOT SHARED" (please see BDEADMIN.HLP and BDE32.HLP for
addtional information on the SQL PASSTHRU MODE parameter)

Please note:
BDE/SQL Links 4.01 will not only detect and raise a deadlock
error but it will "reset" its database transaction state when
it detects an MSSQL error 1205. It is not necessary to rollback
the explicit transaction (Database1.rollback) after the
deadlock has been detected.

The error 1205 signals to the client that the server has
"resolved" a deadlock and chosen one of the users to end the
deadlock. This user's transaction is automatically rolled back.
Please refer to the MS SQL Server documentation for more
information on deadlock detection and server error 1205.

MS SQL and Sybase Server topics: (the following is by no means a
comprehensive list. Please check your Sybase and MS SQL Server
docs for tips on optimizing your server and databases)

Create indexes on the remote tables where possible (the
server may require more locks for unindexed tables.)
TEXT and IMAGE columns can take up more pages (columns
can be omitted from a SELECT statement if working with TQueries
whose REQUEST LIVE property is false)
page sizes on the server can be adjusted to better match
expected row sizes (this can help prevent the server from locking
adjacent rows.)
The server will create a table lock if the LOCK ESCALATION
level is reached (part of sp_configure)

Please also see:
MS SQl Server documentation (printed or Books Online)
If using TQueries:
TABLOCKX
UPDLOCK

For more information on the options above:
Analyzing locks Topic
(also see Database Developer's Companion Errata)

Question:
What version of ODBC is supported by the BDE?

Answer:
Versions 3.x and earlier are now supported by BDE 4.0.

Question:
I get the impression that in order to register the server
interfaces with the client I have to execute the server at each
client. Is this true?

Answer:
No. On the client, fill in the ComputerName and ServerGUID
properties of the TRemoteServer. This allows the GUID to be
located in the remote computer's registry in the CLSID section.

The GUID will have a ProgID section containing the server name
that is used to populate the ServerName property of
TRemoteServer drop down list.

Question:
Can we get information in who's login this application running?

Answer:
I have the code to check whether the application running in administrator login or not. And tested successfully using windows NT/2000. You can explore by yourself to get your goal through this example.

const
SECURITY_NT_AUTHORITY: TSIDIdentifierAuthority =
(Value: (0, 0, 0, 0, 0, 5));
SECURITY_BUILTIN_DOMAIN_RID = $00000020;
DOMAIN_ALIAS_RID_ADMINS = $00000220;

function IsAdmin: Boolean;
var
hAccessToken: THandle;
ptgGroups: PTokenGroups;
dwInfoBufferSize: DWORD;
psidAdministrators: PSID;
x: Integer;
bSuccess: BOOL;
begin
Result := False;
bSuccess := OpenThreadToken(GetCurrentThread, TOKEN_QUERY, True,
hAccessToken);
if not bSuccess then
begin
if GetLastError = ERROR_NO_TOKEN then
bSuccess := OpenProcessToken(GetCurrentProcess, TOKEN_QUERY,
hAccessToken);
end;
if bSuccess then
begin
GetMem(ptgGroups, 1024);
bSuccess := GetTokenInformation(hAccessToken, TokenGroups,
ptgGroups, 1024, dwInfoBufferSize);
CloseHandle(hAccessToken);
if bSuccess then
begin
AllocateAndInitializeSid(SECURITY_NT_AUTHORITY, 2,
SECURITY_BUILTIN_DOMAIN_RID, DOMAIN_ALIAS_RID_ADMINS,
0, 0, 0, 0, 0, 0, psidAdministrators);
{$R-}
for x := 0 to ptgGroups.GroupCount - 1 do
if EqualSid(psidAdministrators, ptgGroups.Groups[x].Sid) then
begin
Result := True;
Break;
end;
{$R+}
FreeSid(psidAdministrators);
end;
FreeMem(ptgGroups);
end;
end;

Question:
How do I display a bitmap to the client area of an MDI parent
form?

Answer:
Here are the necessary steps to add wallpaper to a MDI parent
form:

Create a new project
Set the form's FormStyle to fsMDIForm
Drop an image on the form and select a bitmap into it.
Find the { Private Declarations } comment in the form's
definition and add these lines right after it:

FClientInstance : TFarProc;
FPrevClientProc : TFarProc;
procedure ClientWndProc(var Message: TMessage);

Find the "implementation" line and the {$R *.DFM} line that
follows it. After that line, enter this code:

procedure TMainForm.ClientWndProc(var Message: TMessage);
var
Dc : hDC;
Row : Integer;
Col : Integer;
begin
with Message do
case Msg of
WM_ERASEBKGND:
begin
Dc := TWMEraseBkGnd(Message).Dc;
for Row := 0 to ClientHeight div Image1.Picture.Height do
for Col := 0 to ClientWidth div Image1.Picture.Width do
BitBlt(Dc,
Col * Image1.Picture.Width,
Row * Image1.Picture.Height,
Image1.Picture.Width,
Image1.Picture.Height,
Image1.Picture.Bitmap.Canvas.Handle,
0,
0,
SRCCOPY);
Result := 1;
end;
else
Result := CallWindowProc(FPrevClientProc,
ClientHandle,
Msg,
wParam,
lParam);
end;
end;

In the OnCreate method for the form, type the following lines
of code:

FClientInstance := MakeObjectInstance(ClientWndProc);
FPrevClientProc := Pointer(GetWindowLong(ClientHandle,
GWL_WNDPROC));
SetWindowLong(ClientHandle,
GWL_WNDPROC, LongInt(FClientInstance));

Add a new form to your project and set its FormStyle property to
fsMDIChild.

Now you have a working MDI project with "wallpaper" where the image
bitmap is tiled to cover the MDI form's client area.

Question:
How to Set and Retrieve My Computer Name?

Answer:
Simple way is to use GetComputerName and SetComputerName function.
You can clearly check this example.


function GetComputerName: string;
var
buffer: array[0..MAX_COMPUTERNAME_LENGTH + 1] of Char;
Size: Cardinal;
begin
Size := MAX_COMPUTERNAME_LENGTH + 1;
Windows.GetComputerName(@buffer, Size);
Result := StrPas(buffer);
end;

function SetComputerName(AComputerName: string): Boolean;
var
ComputerName: array[0..MAX_COMPUTERNAME_LENGTH + 1] of Char;
Size: Cardinal;
begin
StrPCopy(ComputerName, AComputerName);
Result := Windows.SetComputerName(ComputerName);
end;

Question:
Are there any new BDE API functions?

Answer:
Yes. DbiAddDriver and DbiDeleteDriver let you add and remove
ODBC drivers from the current BDE configuration or session. The
BDE now automatically adds ODBC drivers and data sources as BDE
aliases to the active session when they aren't currently stored
in the configuration file. Also, please look at the

www.borland.com/devsupport/bde/bdeapiex/index.html page for examples not found in
the BDE32 help file.

Question:
Every hardisk has serial number, it will be an advantage to get this serial. Possibly I can use it to prevent my application install by twice at same hard disk.
May i get the code?

Answer:
Sure, actually some application using not only serial of harddisk but also windows serial, cpu serial and network number to do some restriction.
Anyway, here the code you want:

function GetHardDiskSerial(const DriveLetter: Char): string;
var
NotUsed: DWORD;
VolumeFlags: DWORD;
VolumeInfo: array[0..MAX_PATH] of Char;
VolumeSerialNumber: DWORD;
begin
GetVolumeInformation(PChar(DriveLetter + ':\'),
nil, SizeOf(VolumeInfo), @VolumeSerialNumber, NotUsed,
VolumeFlags, nil, 0);
Result := Format('Label = %s VolSer = %8.8X',
[VolumeInfo, VolumeSerialNumber])
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
ShowMessage(GetHardDiskSerial('c'));
end;

Question:
What type of parameter binding is supported?

Answer:
USERHTML
BDE now supports parameter binding for BLObs and strings longer
than 255 characters except for the MSACCESS driver.

Question:
I want to get Windows System Directory, can you show me?

Answer:
Sure, please check this example below. Not only to get Windows System Directory but also Temporary Directory and Windows Directory.



{ Getting the Windows Directory }

function GetWinDir: string;
var
dir: array [0..MAX_PATH] of Char;
begin
GetWindowsDirectory(dir, MAX_PATH);
Result := StrPas(dir);
end;

function WindowsDirectory: string;
var
WinDir: PChar;
begin
WinDir := StrAlloc(MAX_PATH);
GetWindowsDirectory(WinDir, MAX_PATH);
Result := string(WinDir);
if Result[Length(Result)] <> '\' then
Result := Result + '\';
StrDispose(WinDir);
end;

function GetWindowsDirectory(var S: String): Boolean;
var
Len: Integer;
begin
Len := Windows.GetWindowsDirectory(nil, 0);
if Len > 0 then
begin
SetLength(S, Len);
Len := Windows.GetWindowsDirectory(PChar(S), Len);
SetLength(S, Len);
Result := Len > 0;
end else
Result := False;
end;
{ Getting the System Directory }

function SystemDir: string;
var
dir: array [0..MAX_PATH] of Char;
begin
GetSystemDirectory(dir, MAX_PATH);
Result := StrPas(dir);
end;

function SystemDirectory: string;
var
SysDir: PChar;
begin
SysDir := StrAlloc(MAX_PATH);
GetSystemDirectory(SysDir, MAX_PATH);
Result := string(SysDir);
if Result[Length(Result)] <> '\' then
Result := Result + '\';
StrDispose(SysDir);
end;

function GetSystemDirectory(var S: String): Boolean;
var
Len: Integer;
begin
Len := Windows.GetSystemDirectory(nil, 0);
if Len > 0 then
begin
SetLength(S, Len);
Len := Windows.GetSystemDirectory(PChar(S), Len);
SetLength(S, Len);
Result := Len > 0;
end else
Result := False;
end;
{ Getting the Temporary Directory }

function GetTempDir: string;
var
Buffer: array[0..MAX_PATH] of Char;
begin
GetTempPath(SizeOf(Buffer) - 1, Buffer);
Result := StrPas(Buffer);
end;

function GetTempPath: string;
var
TmpDir: PChar;
begin
TmpDir := StrAlloc(MAX_PATH);
GetTempPath(TmpDir, MAX_PATH);
Result := string(TmpDir);
if Result[Length(Result)] <> '\' then
Result := Result + '\';
StrDispose(TmpDir);
end;

function GetTempPath(var S: String): Boolean;
var
Len: Integer;
begin
Len := Windows.GetTempPath(0, nil);
if Len > 0 then
begin
SetLength(S, Len);
Len := Windows.GetTempPath(Len, PChar(S));
SetLength(S, Len);
Result := Len > 0;
end else
Result := False;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
label1.Caption := GetWinDir;
label2.Caption := GetSysDir;
label3.Caption := GetTempDir;
end;

Question:
Is it possible to modify the TQuery.SQL property when it
resides in a remote data module?

Answer:
Yes. There are three different avenues to accomplish this:

1. If it is a parameterized query, you can use the
IProvider.SetParams call. Assuming you have 2 parameters

(Param1 and Param2). The following code fragment
demonstrates matching by index, Value1 goes to the first
parameter, etc...

ClientDataSet1.Provider.SetParams(VarArrayOf(Value1, Value2));

Or matching by param name:


var
V: Variant;
ParamCount: Integer; //used for ease of reading
begin
ClientDataSet1.Close;
ParamCount := 2;
V := VarArrayCreate([0, ParamCount - 1], varVariant);
V[0] := VarArrayOf(['Param1',Value1]);
V[1] := VarArrayOf(['Param2',Value2]);
ClientDataSet1.Provider.SetParams(V);
ClientDataSet1.Open;
end;

For more example code see \demos\midas\setparam.


2. If you want to change the SQL, then you can use the
IProvider.DataRequest method.

On the Client;


CDS.Data := CDS.Provider.DataRequest('select * from customer');


On the server, you must use a TProvider object, and assign
the OnDataRequest event;


function TForm1.Provider1DataRequest(Sender: TObject;
Input: OleVariant): OleVariant;
begin
//assumes DataSet is a TQuery.
Provider1.DataSet.Close;
TQuery(Provider1.DataSet).SQL.Text := Input;
Provider1.DataSet.Open;
Result := Provider1.Data;
end;

For more example code see \demos\midas\adhoc.


3. Create you own interface function that executes the SQL
statement.

In the RemoteDataModule:

a. Edit | Add To Interface "procedure ExecSQL(SQL:
WideString);" This will add a procedure to your
interface.

b. On the server, code the new procedure:


procedure RemoteData.ExecSQL(SQL: WideString);
begin
{ Query1 is kept in the RemoteDatamodule for this purpose}
Query1.SQL.Text := SQL;
Query1.ExecSQL;
end;


c. On the client, call the new procedure:

RemoteServer1.AppServer.ExecSQL('Delete from deal details
where dealnumber=1');

Question:
How to make sure an application are running or not?

Answer:
You should check the tasklist.
Below one example.




uses TlHelp32;


function processExists(exeFileName: string): Boolean;
var
ContinueLoop: BOOL;
FSnapshotHandle: THandle;
FProcessEntry32: TProcessEntry32;
begin
FSnapshotHandle := CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0);
FProcessEntry32.dwSize := SizeOf(FProcessEntry32);
ContinueLoop := Process32First(FSnapshotHandle, FProcessEntry32);
Result := False;
while Integer(ContinueLoop) <> 0 do
begin
if ((UpperCase(ExtractFileName(FProcessEntry32.szExeFile)) =
UpperCase(ExeFileName)) or (UpperCase(FProcessEntry32.szExeFile) =
UpperCase(ExeFileName))) then
begin
Result := True;
end;
ContinueLoop := Process32Next(FSnapshotHandle, FProcessEntry32);
end;
CloseHandle(FSnapshotHandle);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
if processExists('notepad.exe') then
ShowMessage('process is running')
else
ShowMessage('process not running');
end;

Question:
What are the capabilities of the new Access driver?

Answer:
If you have a version of the Microsoft JET engine (included with
Microsoft Access and FoxPro) installed on your system you can
now use the BDE to open or create Microsoft Access tables using
the MSACCESS driver.

Question:
How to capture clipboard content?

Answer:
I give you an example to folow.
Make new project, and place TImage and TMemo.
Those object will use to show clipboard content, if Image will shows at Image1 and if text will shows at Memo1.



uses Clipboard;
...

procedure TForm1.Button1Click(Sender: TObject);
var
MyHandle: THandle;
begin
Clipboard.Open;
if Clipboard.HasFormat(CF_TEXT) then
begin
MyHandle:=Clipboard.GetAsHandle(CF_TEXT);
Memo1.Lines.Add(StrPas(GlobalLock(MyHandle)));
GlobalUnlock(MyHandle);
end;
if (Clipboard.HasFormat(CF_BITMAP)) or
(Clipboard.HasFormat(CF_PICTURE)) then
Image1.Picture.Assign(Clipboard);
Clipboard.Close;
end;

Question:
What is the role of DBClient.DLL?

Answer:
DBCLIENT.DLL contains the low-level implementation of
TClientDataSet. It manages all of the data and implements
functionality like filters, sorting, etc. The relationship
between TClientDataSet and DBCLIENT.DLL is very similar to the
one between TTable and the BDE. The functionality is surfaced

through two COM interfaces (DSBase & DSCursor) and requires OLE
registration. If DBCLIENT.DLL is placed in any directory on the
path or in the EXE directory on the client machine, it will
automatically be registered by the VCL code in TClientDataSet.
Otherwise it must be registered using REGSVR32.EXE or TREGSVR.EXE.

Question:
What are the capabilities of the new FoxPro driver?

Answer:
The dBASE (STANDARD) driver now includes support for FoxPro
version 2.0, 2.5, and 2.6 compressed index (.CDX) and BLOb
(.FPT) files, letting you open and create FoxPro 2.5 tables.

Question:
Do you have method to prevent user pasting something to my memo?

Answer:
You can detect keypress and clear the clipboard.



uses Clipbrd;
...

procedure TForm1.Memo1KeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState);
begin
if ((Key=Ord('V'))and(ssCtrl in Shift)) then
begin
Clipboard.Clear;
Memo1.SelText:='Delphi tricks rules!';
Key:=0;
end;
end;

Question:
How do I handle business rules that are tied to dataset event
handlers (OnBeforePost, etc.) on the server?

Answer:
Install and use the TUpdateSQLProvider component, found in the
\demos\Usqlprov directory). It has an OnUpdateRecord event
that fires for every record getting updated.

Question:
I want to change System time from my application immediatelly.
Do you have the code?

Answer:
Sure,
The input strings for date and time depends on the format you are using.


procedure TForm1.Button1Click(Sender: TObject);
var
SystemTime: TSystemTime;
NewTime, NewDate: string;
begin
NewTime := '12:00:00';
NewDate := '01.01.2006';
DateTimeToSystemTime(StrToDate(NewDate) + StrToTime(NewTime), SystemTime);
SetLocalTime(SystemTime);
// Tell windows, that the Time changed!
PostMessage(HWND_BROADCAST, WM_TIMECHANGE, 0, 0); // *
end;

Question:
Is there a new BDE configuration Utility?

Answer:
Yes. The BDE Configuration Utility (BDECFG32.EXE) has been updated
and is now the BDE Administrator (BDEADMIN.EXE). The administrator
now shares the same "look and feel" of Delphi's database explorer and
should be used in place of the BDE Configuration Utility (BDECFG32.EXE).

Question:
How to Confine The Mouse?

Answer:
procedure TForm1.Button1Click(Sender: TObject);
var
Rect: TRect;
begin
Rect.Left := Left;
Rect.Top := Top;
Rect.Right := Left + Width;
Rect.Bottom := Top + Height;
ClipCursor(@Rect);
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
ClipCursor(nil);
end;

Question:
When I ApplyUpdates on the ClientDataSet, the OnNewRecord for
the Server dataset code is not fired. How can I make this work?

Answer:
Well, you can't really. The updates go through BDE and do not
go through the dataset.

Question:
Please provide easiest way to copy paste from TMemo.

Answer:
Just use VCL command.
Like this ...



procedure TForm1.Button2Click(Sender: TObject);
begin
Memo1.SelectAll;
Memo1.CopyToClipboard;
Memo1.Clear;
end;


procedure TForm1.Button1Click(Sender: TObject);
begin
Memo2.PasteFromClipboard;
end;

Question:
How do you get rid of a "registered" ServerName if I don't
want to use it any longer?

Answer:
Run the executable with the /UNREGSERVER flag:

MYSERVER.EXE /UNREGSERVER

This is the standard way of unregistering a self-registering
OLE automation server.

Question:
I wnat to make application that shutdown windows after task complete.
Any idea how?

Answer:
You can use ShutdownWindows method like here.



uses ComObj;


procedure TForm1.Button1Click(Sender: TObject);
var
shell: Variant;
begin
shell := CreateOleObject('Shell.Application');
shell.ShutdownWindows;
end;

Question:
I want to put the entire BDE DLLs and configuration files on our LAN. Is this possible and is there an easy way to setup the client machines? I don't know what the registry settings are for the BDE.

Answer:
www.borland.com/devsupport/bde/files/bdenet.zip has a BDE network installation example that allows a user to setup the BDE and SQL Links on a client machine without having to install the BDE and SQL Links to the client. Registry entries are
placed into the client machines so that the client can access a network version of the BDE. Two programs are included: one which helps configure the .INI file that is necessary to create the registry entries, and another program that shows how the methods work to create the registry entries.

This is not officially supported by Borland. It was developed by Developer Support to help in BDE network installations.

Question:
Some application has fuction to turn off monitor.
Show me how?

Answer:
Please use this command
{ turn off your monitor }
SendMessage(Application.Handle, WM_SYSCOMMAND, SC_MONITORPOWER, 0);
{ turn on your monitor }
SendMessage(Application.Handle, WM_SYSCOMMAND, SC_MONITORPOWER, - 1);

Question:
Please teach me how to associate extention file to our application?

Answer:
Implementation have to override information inside registry.
Anyway, you can follow example below.


uses
registry, shlobj;

procedure TForm1.RegisterFileType(prefix: string; exepfad: string);
var
reg: TRegistry;
begin
reg := TRegistry.Create;
try
reg.RootKey := HKEY_CLASSES_ROOT;
reg.OpenKey('.' + prefix, True);
try
reg.Writestring('', prefix + 'file');
finally
reg.CloseKey;
end;
reg.CreateKey(prefix + 'file');
reg.OpenKey(prefix + 'file\DefaultIcon', True);
try
reg.Writestring('', exepfad + ',0');
finally
reg.CloseKey;
end;
reg.OpenKey(prefix + 'file\shell\open\command', True);
try
reg.Writestring('', exepfad + ' "%1"');
finally
reg.CloseKey;
end;
finally
reg.Free;
end;
SHChangeNotify(SHCNE_ASSOCCHANGED, SHCNF_IDLIST, nil, nil);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
RegisterFileType('pci', 'c:\file.exe');
end;