Mega Code Archive
Databaseops
<![CDATA[
VARIOUS DATABASE TABLE OPERATIONS
*********************************
update a record in a table with values
from a user input form
procedure TOrderDetailsForm.UpDateOrderDetails;
var
orderTable : TTable;
exeFileName : String;
pathToExe : String;
dbFullFileName: String;
ordRefStr : String;
tempString : String;
keyFound : Boolean;
begin
exeFileName := Application.ExeName;
pathToExe := ExtractFilePath(exeFileName);
dbFullFileName := pathToExe + dbTablesDataDir + ordersTableFileName;
if FileExists(dbFullFileName) then
begin
orderTable := TTable.Create(nil);
orderTable.TableName := ordersTableFileName;
orderTable.DatabaseName := ExtractFileDir(dbFullFileName);
orderTable.Open;
ordRefStr := OrderReferenceEdit.Text;
keyFound := orderTable.FindKey([ordRefStr]);
if (keyFound = True) then
begin
orderTable.Edit;
//put database table data into the
//EditOrderDetails form's edit boxes...
try
//more code: validation of dates input by the user is required
orderTable.FieldByName('OrderDate').AsDateTime :=
StrToDate(OrderDateEdit.Text);
orderTable.FieldByName('StyleName').AsString := StyleNameEdit.Text;
//etc
//etc
orderTable.Post;
except
orderTable.Cancel;
tempString := 'Problems have been encountered with the Fox Orders database table' + Chr(13)
+ 'Please contact SSI support';
Application.MessageBox(PChar(tempString), ' Inconsistent Data', mb_OK);
end;
end; //end if key found
orderTable.Close;
orderTable.Free;
end; //end if file exists
end;
***************************************************************
pass a key field value to a procedure
to read fields from the record with
that value in it's key field:
procedure TOrderDetailsForm.ReadFieldsFromTable(ordRef: String);
var
orderTable : TTable;
exeFileName : String;
pathToExe : String;
dbFullFileName: String;
tempString : String;
keyFound : Boolean;
begin
exeFileName := Application.ExeName;
pathToExe := ExtractFilePath(exeFileName);
dbFullFileName := pathToExe + dbTablesDataDir + ordersTableFileName;
if FileExists(dbFullFileName) then
begin
orderTable := TTable.Create(nil);
orderTable.TableName := ordersTableFileName;
orderTable.DatabaseName := ExtractFileDir(dbFullFileName);
orderTable.Open;
keyFound := orderTable.FindKey([ordRef]);
if (keyFound = True) then
//put database table data into the
//EditOrderDetails form's edit boxes...
try
OrderReferenceEdit.Text := orderTable.FieldByName('OrderReference').AsString;
OrderDateEdit.Text := orderTable.FieldByName('OrderDate').AsString;
except
tempString := 'Problems have been encountered with the Fox Orders database table' + Chr(13)
+ 'Please contact SSI support';
Application.MessageBox(PChar(tempString), ' Inconsistent Data', mb_OK);
end;
end;
orderTable.Free;
end;
*********************************************
or if you want to loop through records:
[having opened the table etc]
sssTable.First;
while (not(sssTable.EOF)) do
begin
tempString := sssTable.FieldByName('STYLENAME').AsString;
if (tempString = ssStyleName) then
begin
unique := False;
break;
end;
sssTable.Next;
end;
************************************
const
stylesDatabaseName = 'myDataBase';
Firstly, put the following 'visual'
components on your form (linked variously)
type
TStyleDataForm = class(TForm)
WinStylesDataSource: TDataSource;
WinStylesTable: TTable;
WinStylesDBGrid: TDBGrid;
WinStylesDBNavigator: TDBNavigator;
{various funcs here of course}
end;
THEN THERE'S VARIOUS OPS HERE
function TStyleDataForm.InitThisDatabase: Boolean;
{open the WINSTYLES database table. Note that as soon as we
open it the DBGrid on the form here is populated with data...}
var
dataFilePresent: Boolean;
tempString: String;
begin
WinStylesTable.DatabaseName := pathToDBTable;
pathToDBTable := pathToDBTable + stylesDatabaseName;
if FileExists(pathToDBTable) then
begin
WinStylesTable.Open;
dataFilePresent := True;
end
else
begin
tempString := 'The program cannot find the required database file (';
tempString := tempString + stylesDataBaseName;
tempString := tempString + '). Terminating the program.';
Application.MessageBox(PChar(tempString), ' Unknown File Location', mb_OK);
dataFilePresent := False;
end;
Result := dataFilePresent;
end;
PUT DATA IN
procedure TStyleDataForm.AppendRecord;
{add a new record to the Winstyles database table...}
begin
WinStylesTable.Append;
WinStylesTable.FieldByName('STYLE_NO').AsString := NewStyleForm.newStyleRec.styleNo;
WinStylesTable.FieldByName('SHORTNAME').AsString := NewStyleForm.newStyleRec.shortName;
WinStylesTable.FieldByName('LONGNAME').AsString := NewStyleForm.newStyleRec.longName;
WinStylesTable.FieldByName('PROD_GROUP').AsString := NewStyleForm.newStyleRec.productGroup;
WinStylesTable.FieldByName('BLOCKS_W').AsInteger := NewStyleForm.newStyleRec.blocksW;
WinStylesTable.FieldByName('BLOCKS_H').AsInteger := NewStyleForm.newStyleRec.blocksH;
WinStylesTable.FieldByName('DRAW_CODE').AsString := NewStyleForm.newStyleRec.drawCode;
WinStylesTable.FieldByName('GLASS_CODE').AsString := NewStyleForm.newStyleRec.glassCode;
WinStylesTable.FieldByName('HINGE_CODE').AsString := NewStyleForm.newStyleRec.hingeCode;
WinStylesTable.Post;
end;
TAKE DATA OUT
procedure TStyleDataForm.WinStylesDBGridDblClick(Sender: TObject);
//var
//lineNumber: longInt;
//styleIndex: String;
begin
newStyle := False;
{Delphi makes things very easy for us here. If we were dealing with a Memo
or RichEdit component then we would need to use an API call to find the line
number of the line that was clicked. Here Delphi sets the clicked line to
be the 'current' record so we don't need to worry about anything like that.
The first thing we do here is, we put database 'style' data into the
NewStyleRec record structure which holds data for the whole of the style
(in one record)...}
NewStyleForm.newStyleRec.styleNo := WinStylesTable.FieldByName('STYLE_NO').AsString;
NewStyleForm.newStyleRec.shortName := WinStylesTable.FieldByName('SHORTNAME').AsString;
NewStyleForm.newStyleRec.longName := WinStylesTable.FieldByName('LONGNAME').AsString;
NewStyleForm.newStyleRec.productGroup := WinStylesTable.FieldByName('PROD_GROUP').AsString;
NewStyleForm.newStyleRec.blocksW := WinStylesTable.FieldByName('BLOCKS_W').AsInteger;
NewStyleForm.newStyleRec.blocksH := WinStylesTable.FieldByName('BLOCKS_H').AsInteger;
NewStyleForm.newStyleRec.drawCode := WinStylesTable.FieldByName('DRAW_CODE').AsString;
NewStyleForm.newStyleRec.glassCode := WinStylesTable.FieldByName('GLASS_CODE').AsString;
NewStyleForm.newStyleRec.hingeCode := WinStylesTable.FieldByName('HINGE_CODE').AsString;
{having filled out the record we then use that data to draw the style...}
NewStyleForm.DrawStyleFromRecord;
end;
***********************
CHECK TO SEE IF A VALUE IS UNIQUE
BEFORE ADDING IT TO A DATABASE TABLE
THIS IS THE ssslowww WAY:
procedure TDBHandler.AppendSideSectionStyleRecordOld(ssStyleName,
ssFileName: String);
{add a new record to the side section styles database table...}
var
unique: Boolean;
tempString: String;
begin
unique := True;
sssTable.First;
while (not(sssTable.EOF)) do
begin
tempString := sssTable.FieldByName('STYLENAME').AsString;
if (tempString = ssStyleName) then
begin
unique := False;
break;
end;
sssTable.Next;
end;
if (unique = True) then
begin
sssTable.Append;
sssTable.FieldByName('STYLENAME').AsString := ssStyleName;
ssFileName := ssFileName + '.bmp';
sssTable.FieldByName('FILENAME').AsString := ssFileName;
sssTable.Post;
end;
end;
THIS IS THE FAST-ACCESS WAY:
function TDBHandler.AppendSideSectionStyleRecord(ssStyleName,
ssFileName: String): Boolean;
{add a new record to the side section styles database table...}
var
tempString: String;
begin
if (not(sssTable.FindKey([ssStyleName]))) then
begin
sssTable.Append;
sssTable.FieldByName('STYLENAME').AsString := ssStyleName;
ssFileName := ssFileName + '.bmp';
sssTable.FieldByName('FILENAME').AsString := ssFileName;
sssTable.Post;
Result := True;
end
else Result := False;
end;
******************
USING AN SQL QUERY
PREAMBLE: we use a DIY 'detail line' object in the function
below, so we need to understand how that works before we
look at the SQL code proper...
since we don't know how many result lines (ie database records)
we will want to store at the outset, things are set up here so
that we dynamically create a detail line object each time we
encounter a new record
type
{object to temporarily hold database details before
handing them to the ListView component for display}
TDetailLine = class(TObject)
styleName: String;
fileName: String;
end;
private
{private declarations}
detailLine: TDetailLine;
end;
FUNCTION WITH SQL CODE IN
-use a dynamically created object
to hold records, and free up the
objects at the end
we need a
dLineList.Create;
somewhere up here
procedure TSideSectionStyleForm.LoadSideSectionStyleTable;
{load data from SSStyle.db (using SQL) and store it in
the 'detail line' variable so we can display it in the
ListView later...}
var
dbFullFileName: String;
sssTQuery: TQuery;
exeFileName, pathToExe: String;
queryString: String;
begin
dLineList.Clear;
sssTQuery := TQuery.Create(nil);
exeFileName := Application.ExeName;
pathToExe := ExtractFilePath(exeFileName);
dbFullFileName := pathToExe + dbTablesDataDir + sideSectionStylesTableName;
sssTQuery.DatabaseName := ExtractFileDir(dbFullFileName);
if (FileExists(dbFullFileName)) then
begin
{note that #39 is a single apostrophe (and #34 is a double one)...}
queryString := 'SELECT StyleName, FileName FROM '
+ #39 + dbFullFileName + #39;
sssTQuery.sql.Add(queryString);
{open the table}
sssTQuery.Open;
if sssTQuery.RecordCount > 0 then
begin
sssTQuery.First;
while not sssTQuery.EOF do
begin
detailLine := TDetailLine.Create;
detailLine.refNo := (sssTQuery.FieldByName('OrderReference').AsString);
detailLine.custName := (sssTQuery.FieldByName('CustomerName').AsString);
detailLine.styleName := (sssTQuery.FieldByName('StyleName').AsString);
detailLine.orderDate := (sssTQuery.FieldByName('OrderDate').AsString);
detailLine.thumbFileName := (sssTQuery.FieldByName('ThumbFileName').AsString);
dLineList.Add(DetailLine);
sssTQuery.Next;
end;
end;
end;
sssTQuery.Free;
end;
procedure TSideSectionStyleForm.FormDestroy(Sender: TObject);
var
listIdx: Integer;
dListPointer: TDetailLine;
begin
for listIdx := 0 to (dLineList.Count - 1) do
begin
dListPointer := dLineList.Items[listIdx];
dListPointer.Free;
end;
end;
]]>