You are not logged in.
did you mean like this..?
class procedure TSQLToken.InitializeTable(Server: TSQLRestServer;
const FieldName: RawUTF8);
var
aSQLTrigger:RawUTF8;
begin
inherited InitializeTable(Server, FieldName);
if FieldName = '' then begin
aSQLTrigger='CREATE TRIGGER ....';
Server.EngineExecuteAll(aSQLTrigger);
end;
end;i think i understand now. did you mean like this..?
class procedure TSQLToken.InitializeTable(Server: TSQLRestServer;
const FieldName: RawUTF8);
begin
inherited InitializeTable(Server, FieldName);
if FieldName = '' then begin
Server.EngineExecuteAll(FieldName);
end;
end;that FieldName Contain complete SQL Trigger syntact..?
thanks..
thank you, i'll try to learn more abaut the example.
when the InitializeTable called?
i'll be realy appreciate if you could give more detail example, since i'm not understand about RTTI.
thanks.
thanks for the quick fix..
i was learn the method InitializeTable, but i don't know how to implement in the TSQLRecord level..
and i found TSQLRestServer.CreateSQLMultiIndex method, then i try to do these (i use maindemo project)
in the filetables unit i declare some types like this:
TTriggerEvent=(teInsert, teUpdate, teDelete, teUpdateOf);
TTriggerTime=(ttBefore, ttAfter);
TTriggerKind=(tkForEachRow, tkForEachStatement);
TSQLTrigger=record
IsTemporary:Boolean;
TriggerEvent:TTriggerEvent;
TriggerTime:TTriggerTime;
TriggerKind:TTriggerKind;
WhenClause:RawUTF8;
TriggerSQL:RawUTF8;
end;
const
EventSQL:array[TTriggerEvent] of string=('INSERT','UPDATE','DELETE','UPDATE OF');
TimeSQL:array[TTriggerTime] of string=('BEFORE','AFTER');
KindSQL:array[TTriggerKind] of string=('FOR EACH ROW','FOR EACH STATEMENT');then, in the FileServer unit i add two public functions
function TFileServer.CreateSQLTrigger(aTable:TSQLRecordClass; aSQLTrigger:TSQLTrigger):Boolean;
const
CR=#13;
var
TriggerName:RawUTF8;
SQL: RawUTF8;
TableIndex: integer;
Props: TSQLRecordProperties;
begin
result:=False;
if Self=nil then
exit;
TableIndex := Model.GetTableIndex(aTable);
if (TableIndex<0) or
((fStaticData<>nil) and (fStaticData[TableIndex]<>nil)) then
exit; // invalid Table or in Static data (Trigger not needed)
Props := Model.TableProps[TableIndex];
with aSQLTrigger do
begin
TriggerName:=FormatUTF8('TRG_%_%%R',
[Props.SQLTableName,LeftStr(TimeSQL[TriggerTime],1),LeftStr(EventSQL[TriggerEvent],1)]);
SQL:=FormatUTF8('DROP TRIGGER IF EXISTS [%];%',[TriggerName,CR]);
SQL:='CREATE ';
if IsTemporary then
SQL:=SQL+'TEMPORARY ';
SQL := SQL+FormatUTF8('TRIGGER IF NOT EXISTS [%]%',[TriggerName,CR]);
SQL := SQL+FormatUTF8('% %%',[TimeSQL[TriggerTime],EventSQL[TriggerEvent],CR]);
SQL := SQL+FormatUTF8('ON %%',[Props.SQLTableName,CR]);
SQL := SQL+FormatUTF8('%%',[KindSQL[TriggerKind],CR]);
SQL := SQL+FormatUTF8('%%',[WhenClause,CR]);
SQL := SQL+FormatUTF8('%%',['BEGIN',CR]);
SQL := SQL+FormatUTF8('%%',[TriggerSQL,CR]);
SQL := SQL+FormatUTF8('%%',['END',CR]);
end;
result := EngineExecuteAll(SQL);
end;
function TFileServer.AccountCreateTriggerBUR:Boolean;
const
CR=#13;
var
aTrigger:TSQLTrigger;
begin
with aTrigger do
begin
TriggerEvent:=teUpdate;
TriggerTime:=ttAfter;
TriggerKind:=tkForEachRow;
IsTemporary:=False;
WhenClause:='';
TriggerSQL:=
' UPDATE Account SET '+CR+
' IsCreditBalance=1 '+CR+
' WHERE ID=OLD.ID '+CR+
' AND AccountClass IN(1,2,4,6); '+CR+
' UPDATE Account SET '+CR+
' IsCreditBalance=0 '+CR+
' WHERE ID=OLD.ID '+CR+
' AND AccountClass IN(0,3,5,7); '+CR;
end;
result:=CreateSQLTrigger(TSQLAccount,aTrigger);
end;and then i call AccountCreateTriggerBUR on then TFileServer Constructor.
and everything work as expected
but i think maybe this is not the simple way.
could you suggest me the better and more simple and more object orientation?
maybe if possible can implemented in the TSQLRecord level..
thanks..
another thing,
if Field declared as TSQLRecord class, the framework will automaticly add index coresponding to the field, right?
//account types
TAccountType= (atHeader,atDetail,atBank,atSystem);
//account classifications
TAccountClass= (acAsset,acLiability,acEquity,acCoS,acIncome,acExpense,acOtherIncome,acOtherExpense);
TSQLAccount= class(TSQLBaseMaster)
private
fOpeningBalance: Currency;
fCurrentBalance: Currency;
fParenAccount: TSQLAccount;
fAccountLevel: Integer;
fIsCreditBalance: Boolean;
fAccountType: TAccountType;
fAccountClass: TAccountClass;
fAccountSubClass: TSQLAccountSubClass;
//fOnCalcFields: TSQLRecordNotifyEvent;
protected
public
published
property OpeningBalance: Currency read fOpeningBalance write fOpeningBalance;
property CurrentBalance: Currency read fCurrentBalance write fCurrentBalance;
property ParenAccount: TSQLAccount read fParenAccount write fParenAccount;
property AccountLevel: Integer read fAccountLevel write fAccountLevel;
property IsCreditBalance: Boolean read fIsCreditBalance write fIsCreditBalance;
property AccountType: TAccountType read fAccountType write fAccountType default atDetail;
property AccountClass: TAccountClass read fAccountClass write fAccountClass;
property AccountSubClass: TSQLAccountSubClass read fAccountSubClass write fAccountSubClass;
//property OnCalcFields: TSQLRecordNotifyEvent read fOnCalcFields write fOnCalcFields;
end;framework will automaticly create an index for fields ParenAccount, and AccountSubClass..
i see it when i open DB file with SQLiteExpert
i imagine that i can write some Properties for the trigger on TSQLAccount and then i can see it on the e.g SQLiteExpert..
is it possible..?
thanks.
if no side effects, i think, i'll do this method.
write tables defenition with the framework, and then write stored proc, trigger, etc on the external DB Administrator, e.g SQLiteExpert.
i think i rather understand what you mean as described above.
but will be realy dificult to implemented for real application with my ability.
but i tried to write test trigger manualy on SQLiteExpert (http://www.sqliteexpert.com) as such
CREATE TRIGGER [TRG_Account_AUR_TEST]
AFTER UPDATE
ON Account
FOR EACH ROW
BEGIN
UPDATE Account SET
IsCreditBalance=1
WHERE ID=OLD.ID
AND AccountClass IN(1,2,4,6);
UPDATE Account SET
IsCreditBalance=0
WHERE ID=OLD.ID
AND AccountClass IN(0,3,5,7);
ENDi tried to edit some records directly on the SQLiteExpert and from then maindemo app,
and the trigger work as expected.
but i don't know what is the side efect with the framework..
thanks..
so, what sould to be corection? because it checked on the keypress event.
if IsValid(TempString,Temp) and (Temp>MaxValue) then begin
Key := #0;
Beep;
end;I try to trace the code of TSynLabeldEdit.
i think the problem is on MaxValue property, there set to 100 on constructor.
but i don't know how to set it dinamically depending on Kind:TSynLabeledEditKind property.
thanks for the response.
I need some event for insert,update,delete (before and after) on TSQLRecord,
to calculate some internal fields or update value on other TSQLRecord class.
thanks..
But, i want to implemented on the server side (database level) not on the client side. so, every new client app i make, no need to write it any more.
i'm not an expert with class
hi..
I Created new form inherited from TRecordEditForm (part of the framework),
but with Currency Field Type, i can only type just one digit numerical value, but i can type "00000"...
what is the problem?
note:
i use the MainDemo from the framework..
Hallo every one
I have readed the documentation about Stored Procedure,
and i found TOnSQLStoredProc and TSQLRestServerDB.EngineExecute,
but i don't know how to use it.
I imagine can write code like this (like regular database engine)
TSQLSales = class(TSQLRecord)
private
protected
public
fLineTotal: Currency;
published
end;
TSQLSaleLines = class(TSQLRecord)
private
procedure OnBeforeInsert;
procedure OnBeforeUpdate;
procedure OnAfterInsert;
procedure OnAfterUpdate;
procedure OnAfterDelete;
protected
public
fSale:TSQLSales;
fItem: TSQLItem;
fQuantity: Double;
fPrice: Currency;
fSubtotal: Currency;
published
property Item: TSQLItemread fItem write fItem;
property Quantity: Doubleread fQuantity write fQuantity;
property Price: Currencyread fPrice write fPrice;
property Subtotal: Currency read fSubtotal write fSubtotal;
end;
..
..
..
implementation
procedure TSQLSaleLines.OnBeforeInsert;
begin
Subtotal:=NEW.Quantity * NEW.Price;
end;
procedure TSQLSaleLines.OnBeforeUpdate;
begin
if NEW.Quantity<>OLD.Quantity or NEW.Price<>OLD.Price then
Subtotal:=NEW.Quantity * NEW.Price;
end;
procedure TSQLSaleLines.OnAfterInsert;
begin
with fSale do
fLineTotal:=fLineTotal+NEW.Subtotal;
(*in regular db engine i use this code
update TSQLSale Set
LineTotal=LineTotal+NEW.Subtotal
where SaleID=NEW.SaleID;
*)
end;
procedure TSQLSaleLines.OnAfterUpdate;
begin
if NEW.Subtotal<>OLD.Subtotal then
with fSale do
fLineTotal:=fLineTotal+NEW.Subtotal-OLD.Subtotal;
(*in regular db engine i use this code
if NEW.Subtotal<>OLD.Subtotal then
update TSQLSale Set
LineTotal=LineTotal+NEW.Subtotal-OLD.Subtotal
where SaleID=OLD.SaleID
endif;
*)
end;
procedure TSQLSaleLines.OnAfterDelete;
begin
with fSale do
fLineTotal:=fLineTotal-OLD.Subtotal;
(*in regular db engine i use this code
update TSQLSale Set
LineTotal=LineTotal-OLD.Subtotal
where SaleID=OLD.SaleID;
*)
end;
how can we do that with our ORM?
i'm sorry about my english.
thanks
hallo AB, i have some request,
1. Can you complete TSQLTableToGrid, so i can add,edit,and delete record directly on the grid, is it possible?
2. Can you create TSynLookupComboBox component?
i imagine i can create the component like this:
TSQLCountries=class(TSQLRecord)
private
protected
public
ID:RowUTF8;
Code:RowUTF8;
Name:RowUTF8;
end;
TSQLPeoples=class(TSQLRecord)
private
protected
public
Country:TSQLCountry;
end;
var
aCountries:TSQLCountries;
aPeoples:TSQLPeoples;
lcb:TSynLookupComboBox;
begin
lcb:=TSynLookupComboBox.Create(aPeoples,Country,aCountries,'Code,Name');
//i want lcb will automaticaly filled with aCountries Data (records).
end;thanks
In the main demo, when i mark all record, and then Click Delete, the grid still display All Record and add one row header after actual header.
Click Refresh does no efect.
Also Picture can not display as expected. i don't know where to fix it.
How to implement a Recursif Record Reference in the same table?
is Record Class Defininition like bellow correct?
TSQLAccount= class(TSQLRecord)
private
fAccountNumber: TRawUTF8;
fAccountName: TRawUTF8;
fAccountLevel: Integer;
...
...
fHeaderAccount: TSQLAccount;
published
...
HeaderAccount: TSQLAccount read fHeaderAccount write fHeaderAccount;
AccountLevel: Integer read fAccountLevel write fAccountLevel;
...
end;so i can do something like this
var
aAccount:TSQLAccount;
aHeaderAccount:TSQLAccount;
begin
...
...
aHeaderAccount:=aAccount.HeaderAccount;
aAccount.Level:=aHeaderAccount.Level+1;
...
...
end;thanks.
This is exactly what i need.
thanks..
You've some new methods, perhaps the most powerful could be DestGetJoinTable(), which creates a TSQLTable, containing all specified Fields, after a JOIN associated to the current or specified Source ID. The JOIN extend is specified by an enumeration:
/// the kind of fields to be available in a Table resulting of // a TSQLRecordMany.DestGetJoinedTable() method call // - Source fields are not available, because they will be always the same for // a same SourceID, and they should be available from the TSQLRecord which // hold the TSQLRecordMany instance TSQLRecordManyJoinKind = ( jkDestID, jkPivotID, jkDestFields, jkPivotFields, jkPivotAndDestFields);
If i use this function bellow, i'll get all Dest Field.
DestGetJoinedTable(Client,'',AID,jkDestFields);for example, i'll get ID,Field1,Field2,Field3,Field4,Field5,...
but how i can get only part of Dest Field (not all Dest field)
maybe if possible, i can write the code like
DestGetJoinedTable(Client,'',AID,jkDestFields,[Field1,Field2,Field3]);or more simple parameter than [Field1,Field2,Field3]
Thanks AB.
Tanks AB for the response, i'll try the functions that you suggest.
Is it possible to Get Record from CurrentFocusedRow from TDrawGrid (TSQLTableToGrid)?
so i can write code like bellow (maybe)
procedure TForm1.DrawGrid1FocusedRow(Sender: TObject; aFocusedRow:TSQLRecord);
var
aPeople:TSQLPeople;
begin
aPeople:=aFocusedRow.
end;and, if possible to Select multiple row.
var
i:integer;
aPeople:TSQLPeople;
begin
for i:=0 to Drawgrid1.selectedrows.count-1 do
begin
Drawgrid1.selectedrows[i].focused;
aPeople:=Drawgrid1.FocusedRow;
end;
end;thanks.