Saturday, February 24, 2007

Differences between CHAR and VARCHAR

Many people believe that VARCHAR is better because it stores only actual data, while CHAR is stored in full length. It is not true. In fact, both CHAR and VARCHAR are stored in memory buffer in their full, declared length; when the row is stored on disk, then RLE compression algorithm is used to compress whole row, i.e. CHARs, VARCHARs, INTEGERs, DATEs, etc. all together. So if you want to save space, CHARs are slightly better than VARCHARs (the differenece is that VARCHAR stores string length in two bytes).
There is also a bug that causes that VARCHAR does not properly clean string tail if you assign shorter string, thus causing worse compression. (this problem is fixed in Firebird-0.9.4)

Many people also believe that VARCHAR sends over network only actual data, while CHAR is sent in full length. It is not true either. Communication between client and server is done via messages of fixed length. For this reason both CHAR and VARCHAR are sent in their full declared length. (this problem is fixed in IB-6.5)

So decision whether use CHAR or VARCHAR should be based solely on application's requirements. E.g. store fixed length codes in CHAR, store names in VARCHAR (to allow correct concatenating).

Date and Time Calculations (Firebird)

Date and Time Calculations
When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where

* the integral fraction is the number of days
* the decimal fraction is the part of a day

For example:

* Two days: 2.0
* One hour: 1.0/24.0
* One minute: 1.0/1440.0
* One second: 1.0/86400.0

Extracting the smaller units from a TIMESTAMP value:

* Number of seconds: VALUE*86400.0
* Number of minutes: VALUE*1440.0
* Number of hours: VALUE*24.0

Don't forget the decimal point in the numbers (e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.
Posted by Dan : 2:04 PM


---------------
firebird:
tgl jam server sekarang : current_timestamp
tgl : CAST ('today' AS TIMESTAMP)
jam : extract(hour from current_timestamp)||':'||extract(minute from current_timestamp)||':'||extract(second from current_timestamp)

Export StringGrid to An Excel File (way two)

#144: Fast data transfer to MS Excel

Anyone who worked with OLE automation, know that OLE is very slowly. Especially if you work using late binding (which have a lot of other advantages which early binding haven't)

A reason of bad performance is the next: every command (method or property) which you access (no matter in read or write mode) will be interpretated (a-la script). I mean that this command must be found in table of available methods/properties by string name and only if found, a physical memory address for execution will be calculated.

So if your code contain a lot of access to methods/properties, your code will be slow.

For example, you need transfer some data from Delphi application into xls-spreadsheet.

You can solve a task in two different ways (now I describe only late binding for OLE automation and don't describe another methods):

* to navigate thru own data and export every data in required cell
* to prepare a variant array with copied data and apply this array with data into desired range of cells

I must say that second method will be faster than first because you'll call less commands from OLE object and main code will be executed without OLE automation.

Small sample: to export some StringGrid into xls-file.

var
xls, wb, Range: OLEVariant;
arrData: Variant;
begin
{create variant array where we'll copy our data}
arrData := VarArrayCreate([1, yourStringGrid.RowCount, 1, yourStringGrid.ColCount], varVariant);

{fill array}
for i := 1 to yourStringGrid.RowCount do
for j := 1 to yourStringGrid.ColCount do
arrData[i, j] := yourStringGrid.Cells[j-1, i-1];

{initialize an instance of Excel}
xls := CreateOLEObject('Excel.Application');

{create workbook}
wb := xls.Workbooks.Add;

{retrieve a range where data must be placed}
Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
wb.WorkSheets[1].Cells[yourStringGrid.RowCount, yourStringGrid.ColCount]];

{copy data from allocated variant array}
Range.Value := arrData;

{show Excel with our data}
xls.Visible := True;
end;

Of course, you must understand that such method is not good for large data arrays because to allocate in memory large array is not easy task. You must find some optimal size for data transfer (for example, to copy every 10 rows) and as result you'll receive an optimal code both for memory use and performance.

Anyway more faster way to transfer data is not use OLE at all:-) You can use my TSMExportToXLS component from SMExport suite for this task. There is implemented a direct xls-file creation which doesn't require installed MS Excel at all..

Export StringGrid to An Excel File (way one)

{1. With OLE Automation }

uses
ComObj;

function RefToCell(ARow, ACol: Integer): string;
begin
Result := Chr(Ord('A') + ACol - 1) + IntToStr(ARow);
end;

function SaveAsExcelFile(AGrid: TStringGrid; ASheetName, AFileName: string): Boolean;
const
xlWBATWorksheet = -4167;
var
Row, Col: Integer;
GridPrevFile: string;
XLApp, Sheet, Data: OLEVariant;
i, j: Integer;
begin
// Prepare Data
Data := VarArrayCreate([1, AGrid.RowCount, 1, AGrid.ColCount], varVariant);
for i := 0 to AGrid.ColCount - 1 do
for j := 0 to AGrid.RowCount - 1 do
Data[j + 1, i + 1] := AGrid.Cells[i, j];
// Create Excel-OLE Object
Result := False;
XLApp := CreateOleObject('Excel.Application');
try
// Hide Excel
XLApp.Visible := False;
// Add new Workbook
XLApp.Workbooks.Add(xlWBatWorkSheet);
Sheet := XLApp.Workbooks[1].WorkSheets[1];
Sheet.Name := ASheetName;
// Fill up the sheet
Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount,
AGrid.ColCount)].Value := Data;
// Save Excel Worksheet
try
XLApp.Workbooks[1].SaveAs(AFileName);
Result := True;
except
// Error ?
end;
finally
// Quit Excel
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
end;
end;
end;

// Example:

procedure TForm1.Button1Click(Sender: TObject);
begin
if SaveAsExcelFile(stringGrid1, 'My Stringgrid Data', 'c:\MyExcelFile.xls') then
ShowMessage('StringGrid saved!');
end;


{**************************************************************}
{2. Without OLE }

procedure XlsWriteCellLabel(XlsStream: TStream; const ACol, ARow: Word;
const AValue: string);
var
L: Word;
const
{$J+}
CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
{$J-}
begin
L := Length(AValue);
CXlsLabel[1] := 8 + L;
CXlsLabel[2] := ARow;
CXlsLabel[3] := ACol;
CXlsLabel[5] := L;
XlsStream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
XlsStream.WriteBuffer(Pointer(AValue)^, L);
end;


function SaveAsExcelFile(AGrid: TStringGrid; AFileName: string): Boolean;
const
{$J+} CXlsBof: array[0..5] of Word = ($809, 8, 00, $10, 0, 0); {$J-}
CXlsEof: array[0..1] of Word = ($0A, 00);
var
FStream: TFileStream;
I, J: Integer;
begin
Result := False;
FStream := TFileStream.Create(PChar(AFileName), fmCreate or fmOpenWrite);
try
CXlsBof[4] := 0;
FStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
for i := 0 to AGrid.ColCount - 1 do
for j := 0 to AGrid.RowCount - 1 do
XlsWriteCellLabel(FStream, I, J, AGrid.cells[i, j]);
FStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
Result := True;
finally
FStream.Free;
end;
end;

// Example:

procedure TForm1.Button2Click(Sender: TObject);
begin
if SaveAsExcelFile(StringGrid1, 'c:\MyExcelFile.xls') then
ShowMessage('StringGrid saved!');
end;

{**************************************************************}
{3. Code by Reinhard Schatzl }

uses
ComObj;

// Hilfsfunktion für StringGridToExcelSheet
// Helper function for StringGridToExcelSheet
function RefToCell(RowID, ColID: Integer): string;
var
ACount, APos: Integer;
begin
ACount := ColID div 26;
APos := ColID mod 26;
if APos = 0 then
begin
ACount := ACount - 1;
APos := 26;
end;

if ACount = 0 then
Result := Chr(Ord('A') + ColID - 1) + IntToStr(RowID);

if ACount = 1 then
Result := 'A' + Chr(Ord('A') + APos - 1) + IntToStr(RowID);

if ACount > 1 then
Result := Chr(Ord('A') + ACount - 1) + Chr(Ord('A') + APos - 1) + IntToStr(RowID);
end;

// StringGrid Inhalt in Excel exportieren
// Export StringGrid contents to Excel
function StringGridToExcelSheet(Grid: TStringGrid; SheetName, FileName: string;
ShowExcel: Boolean): Boolean;
const
xlWBATWorksheet = -4167;
var
SheetCount, SheetColCount, SheetRowCount, BookCount: Integer;
XLApp, Sheet, Data: OLEVariant;
I, J, N, M: Integer;
SaveFileName: string;
begin
//notwendige Sheetanzahl feststellen
SheetCount := (Grid.ColCount div 256) + 1;
if Grid.ColCount mod 256 = 0 then
SheetCount := SheetCount - 1;
//notwendige Bookanzahl feststellen
BookCount := (Grid.RowCount div 65536) + 1;
if Grid.RowCount mod 65536 = 0 then
BookCount := BookCount - 1;

//Create Excel-OLE Object
Result := False;
XLApp := CreateOleObject('Excel.Application');
try
//Excelsheet anzeigen
if ShowExcel = False then
XLApp.Visible := False
else
XLApp.Visible := True;
//Workbook hinzufügen
for M := 1 to BookCount do
begin
XLApp.Workbooks.Add(xlWBATWorksheet);
//Sheets anlegen
for N := 1 to SheetCount - 1 do
begin
XLApp.Worksheets.Add;
end;
end;
//Sheet ColAnzahl feststellen
if Grid.ColCount <= 256 then
SheetColCount := Grid.ColCount
else
SheetColCount := 256;
//Sheet RowAnzahl feststellen
if Grid.RowCount <= 65536 then
SheetRowCount := Grid.RowCount
else
SheetRowCount := 65536;

//Sheets befüllen
for M := 1 to BookCount do
begin
for N := 1 to SheetCount do
begin
//Daten aus Grid holen
Data := VarArrayCreate([1, Grid.RowCount, 1, SheetColCount], varVariant);
for I := 0 to SheetColCount - 1 do
for J := 0 to SheetRowCount - 1 do
if ((I + 256 * (N - 1)) <= Grid.ColCount) and
((J + 65536 * (M - 1)) <= Grid.RowCount) then
Data[J + 1, I + 1] := Grid.Cells[I + 256 * (N - 1), J + 65536 * (M - 1)];
//-------------------------
XLApp.Worksheets[N].Select;
XLApp.Workbooks[M].Worksheets[N].Name := SheetName + IntToStr(N);
//Zellen als String Formatieren
XLApp.Workbooks[M].Worksheets[N].Range[RefToCell(1, 1),
RefToCell(SheetRowCount, SheetColCount)].Select;
XLApp.Selection.NumberFormat := '@';
XLApp.Workbooks[M].Worksheets[N].Range['A1'].Select;
//Daten dem Excelsheet übergeben
Sheet := XLApp.Workbooks[M].WorkSheets[N];
Sheet.Range[RefToCell(1, 1), RefToCell(SheetRowCount, SheetColCount)].Value :=
Data;
end;
end;
//Save Excel Worksheet
try
for M := 1 to BookCount do
begin
SaveFileName := Copy(FileName, 1,Pos('.', FileName) - 1) + IntToStr(M) +
Copy(FileName, Pos('.', FileName),
Length(FileName) - Pos('.', FileName) + 1);
XLApp.Workbooks[M].SaveAs(SaveFileName);
end;
Result := True;
except
// Error ?
end;
finally
//Excel Beenden
if (not VarIsEmpty(XLApp)) and (ShowExcel = False) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
end;
end;
end;

//Example
procedure TForm1.Button1Click(Sender: TObject);
begin
//StringGrid inhalt in Excel exportieren
//Grid : stringGrid, SheetName : stringgrid Print, Pfad : c:\Test\ExcelFile.xls, Excelsheet anzeigen
StringGridToExcelSheet(StringGrid, 'Stringgrid Print', 'c:\Test\ExcelFile.xls', True);
end;

10 of Most Delphi Used Function

Here is 10 functions mostly used

procedure TForm1.Button1Click(Sender: TObject);
var
S: String;
begin
{ 1 }
S := Copy('Delphi Rulezz', 5, 6); { Reads the text }
ShowMessage(S);
{ 2 }
S := Concat('1 ',' Two, ','3'); { Connects the text }
ShowMessage(S);
{ 3 }
ShowMessage('Length: '+IntToStr(Length('Good'))); { Shows the length of the
string in numbers [integer] }
{ 4 }
S := 'The Example'; {Deletes the text }
Delete(S, 8, 7);
ShowMessage(S);
{ 5 }
S := 'Ralph is a dog';
Insert('good ', S, 12); { Inserts the text }
ShowMessage(S);
{ 6 }
S := 'Delphi';
ShowMessage(UpperCase(S)); { Converts the text to upper case }
{ 7 }
S := 'Delphi';
ShowMessage(LowerCase(S)); { Converts the text to lower case }
{ 8 }
S := 'Delphi';
ShowMessage('''P'' in ''Delphi'' is:'+IntToStr(Pos('p',S))+'th'); { Gets
the position of the string in the string }
{ 9 }
S := 'a1, a2, a3, a4, a5.';
ShowMessage('a change to b: ' + StringReplace(S, 'a', 'b', [rfReplaceAll]));
{ Changes the text from one to another }
{ 10 }
S := 'This text:goes lower.';
S := WrapText(S, #13#10, [':'], 12);
ShowMessage(S); { Moves the text lower }
end;