Squall_FF8

The hardest part for me is - how to do Next on SQL query inside procedure. Also is it possible to do Lookup, without re-running the same SQL statement each iteration

Hey guys, I have written a procedure in Delphi, but would like to port it to MS SQL. Unfortunately I'm new to MSSQL so If you have time to lift a hand, even with parts of it - that would be of great help!!! The structure: 3 tables - Contract - with contract info. It has Date_Sign, Date_Start, Date_Stop, MonthlyTax - Payment - keep records of paid taxes for each Month/Contract. It has DatePayment, Year, Month, Amount - Annex - keep track when the tax has been changed. It has Date and Amount I'm trying to create a procedure in MSSQL that takes ContractID and returns - Months - number of months without payment - Owes- sum of all taxes that hasn't been paid procedure TfmContractEdit.BuildChrono; var y, m, d: word; d1, d2: tDate; pay: Currency; // keeps track of what current tax that needs to be paid begin if deStart.Enabled then d1 := deStart.Date else d1 := deSign.Date; d1 := RecodeDay(d1, 1); if deStop.Enabled then d2 := deStop.Date else d2 := Date; d2 := RecodeDay(d2, 1); DM.Qry.Open(format(sql_GetAnnex, [ID])); // ID is ContractID DM.Qry2.Open(format(sql_GetPaymentTax, [ID])); pay := cuMonthTax.Value; // Current Contract tax var Sum: Currency := 0; var MonthDebt: integer := 0; while d1 < d2 do begin if (not DM.Qry.Eof) and (d1 >= DM.Qry.FieldByName('Date').AsDateTime) then begin pay := DM.Qry.FieldByName('Amount').AsCurrency; DM.Qry.Next; end; DecodeDate(d1, y, m, d); var v: variant := DM.Qry2.Lookup('Year;Month',VarArrayOf([y, m]), 'MonthlyTax'); if VarIsNull(v) then begin Sum := Sum + pay; inc(MonthDebt); end; d1 := IncMonth(d1); end; // Returns Sum, MonthDebt

Wow it worked! Thank you VERY MUCH, @weirdo12 !!!! I had never created variables in FR itself (just trough Delphi code). That is a very neat trick! Thank you for taking time for the screenshots and step by step how to do it!

Not in RichText:

@weirdo12 Great example and practical illustration!!! May we adapt it, if the text is in RichView (instead of Memo) and it is more complicated, like: xxxxx [Ticket."ticket_date"] xxxxxx

Good old Convert ... I'm wondering is there more convenient way - to set format per session, like in Oracle (you set some local settings only for "your" session). The reason is that I have tables with 20+ fields (at least 6 are in date format) and that will make huge Select statements.

Hey guys, I have old MS SQL 2008 and a Query (FireDAC), that returns some fields in Date format. In a Delphi application all work great, it seems FireDac takes care to visualize Date to FormatSettings. However when I open a FastReport document, it seems it convert Date to a the PC local settings (different from my changes in FormatSettings). And that is not good, because on different machines, results could look differently. I wonder, is there a way, to force Dates in FastReport to be in my desired format regardless of the PC local? A possible solution, could be if I force the Query to return the desired format, I just wonder how?

Hmm are you suggesting, that because Main is the owner of the 2 windows (and the Edit), the Actions have higher precedence? (and thus difference in behavior)

Yes, to both questions, The 2 are created on demand and reused after that (if/when needed)

Hey guys, I found the following mystery. Could you help me to solve it? A VCL application with Main form. On that form we have Edit and panel that different forms attach to it (one at a time, depending on a context). - The Edit has OnKeePress that handle, when Enter is used. - Attached forms have Action that has short-key: Enter. - 7 of Attached forms are Auto created (in the project). - 2 of Attached forms are created only once per use in run time. The mystery: What will happen if the Edit has a focus and I hit Enter? Whose handler will be triggered - Edit or attached window? Well at run time, if I have attached one of the 7 - Edit handler respond. If I have attached one of the 2 - their handler respond. So I guess my question is WHY? Why the difference between 7 and 2 windows? What is going on under the hood?

Thank you very much!!! Usually thigs like this have default name that is good enough (it works, the name = you dont use/dont care) Are you talking for Aggregates in general, or my example of use?

Hey guys, I wanted to do simple thing - use Aggregates. So following the help: procedure TForm.Button1Click(Sender: TObject); begin if Qry.Aggregates.Count = 0 then with Qry.Aggregates.Add do begin Expression := 'COUNT(Town)'; Active := True; Qry.AggregatesActive := true; end; end; Should be pretty simple, right? But I get error (on Activate := true): Do you know why? What I'm doing wrong?

Hey guys, I have a shared folder on the local net that I want to be able to Copy/Move/Delete files from it with a Delphi application. It works great when I share that folder with unlimited access. For security requirements I cant do that - I need to make it accessible only with user/pass Do you know how I can that?

If I remember correctly, the whole concept/interface with Post, Edit, EoF, First, Next,... was created for BDE, but was so loved/easy to use by developers that its functionality was replicated in general and every new DBset specific for specific DB (DAC, FireDAC, I even had components for Postgre and Oracle implementing those). It doesn matter for the thread, but for general DB perspective, MSSQL as a spiritual successor of Access, comes natively packed with auto-inc. So you dont need triggers for that. Later versions added Sequence (I guess influenced by Oracle ). Even IBX adopted Sequence, which if used makes Generators a little obsolete.

THANK you very much for that explanation!!! It clears my confusion. I assume that is the BDE way, but I missed that class (academic, not Delphi) 🙂 BTW what happens with the ID after Insert? Is it automatically received or you need to make sure that Insert statement have the right things (like 'OUTPUT INSERTED ID' in MS SQL)