Here is a very simplistic tutorial on how to make stored procedures in SQL SERVER. You can apply the same concept to most other RDBMS you want to use.
https://www.mssqltips.com/sqlservertutorial/168/different-options-for-creati...
If you do this you can stop writing SQL CODE in VFP and pass it to the backend where that db engine has to make a plan to execute the query every time. If you have that SQL Code in a stored procedure the db engine can look it its stats to determine the best plan to execute the code.
I have some long stored procedures that are in the 3000 lines long arena that are called for every Bill of Lading report we print. Granted it combines Sales data, testing data, item specifications data as well as customer and our employee system data. This generates a CofA or Certificate of Analysis report for every lot number on the truck.
Sprocs are great because they make it much harder to hack into your backend data. Nothing new here, right?
Holy Crap Stephen - that's a DAMN Funny comic! I love it!!! Even my son is familiar with XKCD!
To make my reply even MORE Interesting - and a comment I am DAMN SURE that Ed would appreciate - truth is - my son is a bit of a computer expert now, in some way. Since, you see - he's actually a Python programmer! And, has been using Python for over 2 years - and has done a TON of programming in Python for Condensed Physics Matter research and data analysis!
Although - honestly - I should make a New Thread about this. Since there is a bit of additional info I could relate about it!
-K-
On 2/28/2019 6:45 AM, Stephen Russell wrote:
Sprocs are great because they make it much harder to hack into your backend data. Nothing new here, right?
On 2019-02-28 09:45, Stephen Russell wrote:
I have some long stored procedures that are in the 3000 lines long arena
Jesus...that makes my head hurt just thinking about that. I get the efficiency angle but I really enjoy breaking work up into much more manageable/readable units in the DataObject code; plus, my approach means I'm not stuck with SQL Server in case I want to switch out the backends. There's varying schools of thought on that, and of course, pros and cons to each. I recall somebody (Ted?) saying something years ago about treating that backend as just big dumb iron in the n-tier system approach, which is what I use.
All you need is a case statement to fire the correct code going forward.
It really sucks to bring in another back end but the customer must pay for what THEY WANT.
You could really consider putting up an AWS cloud environment and getting your data from there. Or Azure. Now you get a little bit of redundancy going forward.
On Thu, Feb 28, 2019 at 3:42 PM mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2019-02-28 09:45, Stephen Russell wrote:
I have some long stored procedures that are in the 3000 lines long arena
Jesus...that makes my head hurt just thinking about that. I get the efficiency angle but I really enjoy breaking work up into much more manageable/readable units in the DataObject code; plus, my approach means I'm not stuck with SQL Server in case I want to switch out the backends. There's varying schools of thought on that, and of course, pros and cons to each. I recall somebody (Ted?) saying something years ago about treating that backend as just big dumb iron in the n-tier system approach, which is what I use.
[excessive quoting removed by server]
+1 for SP's but not in VFP, apart from triggers which I use to create an audit trail of table modifications.
Dave Crozier Software Development Manager Flexipol Packaging Ltd.
﴾⚆ᨎ⚆﴿
Flexipol® Packaging Ltd T 01706 222 792 E DCrozier@flexipol.co.uk W https://www.flexipol.co.uk/ Follow us: Unit 14 Bentwood Road, Carrs Industrial Estate, Haslingden, Lancashire, BB4 5HH
This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.
It is the responsibility of the recipient to ensure that they have adequate virus protection.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Terms & Conditions: Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time -----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: 28 February 2019 14:45 To: profoxtech@leafe.com Subject: [NF] Learn about Stored Procedures
Here is a very simplistic tutorial on how to make stored procedures in SQL SERVER. You can apply the same concept to most other RDBMS you want to use.
https://www.mssqltips.com/sqlservertutorial/168/different-options-for-creati...
If you do this you can stop writing SQL CODE in VFP and pass it to the backend where that db engine has to make a plan to execute the query every time. If you have that SQL Code in a stored procedure the db engine can look it its stats to determine the best plan to execute the code.
I have some long stored procedures that are in the 3000 lines long arena that are called for every Bill of Lading report we print. Granted it combines Sales data, testing data, item specifications data as well as customer and our employee system data. This generates a CofA or Certificate of Analysis report for every lot number on the truck.
Sprocs are great because they make it much harder to hack into your backend data. Nothing new here, right?
-- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN
901.246-0159 cell
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
From that changelog you can see what you need to populate in your DW on the
change side of trasactions.
I was told that the one in my ERP will no longer be available if we go to the cloud with our data. That didn't go over well on why do we have to move to the cloud.
On Tue, Mar 19, 2019 at 12:19 PM Dave Crozier DCrozier@flexipol.co.uk wrote:
+1 for SP's but not in VFP, apart from triggers which I use to create an audit trail of table modifications.
Dave Crozier Software Development Manager Flexipol Packaging Ltd.
﴾⚆ᨎ⚆﴿
Flexipol® Packaging Ltd T 01706 222 792 E DCrozier@flexipol.co.uk W https://www.flexipol.co.uk/ Follow us: Unit 14 Bentwood Road, Carrs Industrial Estate, Haslingden, Lancashire, BB4 5HH
This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.
It is the responsibility of the recipient to ensure that they have adequate virus protection.
Terms & Conditions: Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time -----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: 28 February 2019 14:45 To: profoxtech@leafe.com Subject: [NF] Learn about Stored Procedures
Here is a very simplistic tutorial on how to make stored procedures in SQL SERVER. You can apply the same concept to most other RDBMS you want to use.
https://www.mssqltips.com/sqlservertutorial/168/different-options-for-creati...
If you do this you can stop writing SQL CODE in VFP and pass it to the backend where that db engine has to make a plan to execute the query every time. If you have that SQL Code in a stored procedure the db engine can look it its stats to determine the best plan to execute the code.
I have some long stored procedures that are in the 3000 lines long arena that are called for every Bill of Lading report we print. Granted it combines Sales data, testing data, item specifications data as well as customer and our employee system data. This generates a CofA or Certificate of Analysis report for every lot number on the truck.
Sprocs are great because they make it much harder to hack into your backend data. Nothing new here, right?
-- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN
901.246-0159 cell
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
-
Dave Crozier -
Kurt ＠ Gmail -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell