Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Passing Information Message to VFP via CursorAdapter without using RAISERROR
- Thread starter carolx
- Start date
Olaf Doschke
Programmer
What would you like to use instead? What's your goal?
Is it about using a scalar valued function? For example you can SELECT Getdate() via a cursoradapter and get a result cursor with one record, one field containing the server datetime and you can make use of any scalar valued function in the same way.
Technically the cursoradapter receives data in it's cursor and errormessages, native or via RAISERROR. What would be so bad about using RAISEERROR?
Bye, Olaf.
Is it about using a scalar valued function? For example you can SELECT Getdate() via a cursoradapter and get a result cursor with one record, one field containing the server datetime and you can make use of any scalar valued function in the same way.
Technically the cursoradapter receives data in it's cursor and errormessages, native or via RAISERROR. What would be so bad about using RAISEERROR?
Bye, Olaf.
Olaf Doschke
Programmer
I see in your profile you had many questions about triggers and stored procedures in forum183.
Maybe you're putting too much business logic there, maybe not.
Anyway, a cursoradapters main function of a cursoradapter is to have a VFP cursor acting towards any RDBMS or other data source in the same way as a view or even a native DBF, eg REQUERY works on such a cursor and TABLEUPDATE(). Having the cursoradapter class and all it's events is an add on to that, but mainly you act on remote data as if it were DBFs.
In a DBC you can also have triggers and stored procedures cascading changes triggered by an update or insert in other tables and also have no way to return that to the initialising query or Tableupdate() or flush or whatever caused it. If that matters and you need information back at the client, then maybe it would be better to have a business logic layer between the VFP app and the database, which would mean a paradigm change of how your VFP app interacts with the database. Less directly storing anything to single tables, but instead using API requests providing all needed information and getting back whatever you want to pass back.
And one more technical aspect: If you don't want to use RAISERROR, because that would rollback uncommited changes, then put whatever changes you want to persist into a transaction, which you commit before you RAISEERROR.
Bye, Olaf.
Maybe you're putting too much business logic there, maybe not.
Anyway, a cursoradapters main function of a cursoradapter is to have a VFP cursor acting towards any RDBMS or other data source in the same way as a view or even a native DBF, eg REQUERY works on such a cursor and TABLEUPDATE(). Having the cursoradapter class and all it's events is an add on to that, but mainly you act on remote data as if it were DBFs.
In a DBC you can also have triggers and stored procedures cascading changes triggered by an update or insert in other tables and also have no way to return that to the initialising query or Tableupdate() or flush or whatever caused it. If that matters and you need information back at the client, then maybe it would be better to have a business logic layer between the VFP app and the database, which would mean a paradigm change of how your VFP app interacts with the database. Less directly storing anything to single tables, but instead using API requests providing all needed information and getting back whatever you want to pass back.
And one more technical aspect: If you don't want to use RAISERROR, because that would rollback uncommited changes, then put whatever changes you want to persist into a transaction, which you commit before you RAISEERROR.
Bye, Olaf.
- Thread starter
- #4
Thanks Olaf. I was thinking about committing the changes and then issue RAISEERROR. The thing is that VFP rolls back the changes made to the cursor. What I can do, though, is requery the cursor. I was just curious if it could be done another way.
- Thread starter
- #5
Olaf Doschke
Programmer
I don't know what your save routine does, but indeed the buffered changes of a VFP cursor remain at unmodified, modified, new, or new and modified status, if an error happens. You would have to change how the VFP application reacts to the raised error anyway, as you want to process this information passed back via the error, I assume. There are simple ways to change the status via Setfldstate(). But indeed that's playing a bit more tricks also on the VFP side.
Are you really sure you can't achieve your overall goal in a more standard way of working with SQL Server and VFP and cursoradapters, buffers and triggers? I'd say you're at a point of the sorcerer's apprentice. "Spirits that I've cited / My commands ignore."
What are you trying to achieve?
Do you really need the message back to VFP? Is one of your triggers or stored procs in play here? What for do you need it? Is it perhaps even a question about your database design? Triggers updating related data are often a sign of missing normalisation, having redundant data.
Bye, Olaf.
Are you really sure you can't achieve your overall goal in a more standard way of working with SQL Server and VFP and cursoradapters, buffers and triggers? I'd say you're at a point of the sorcerer's apprentice. "Spirits that I've cited / My commands ignore."
What are you trying to achieve?
Do you really need the message back to VFP? Is one of your triggers or stored procs in play here? What for do you need it? Is it perhaps even a question about your database design? Triggers updating related data are often a sign of missing normalisation, having redundant data.
Bye, Olaf.
- Thread starter
- #7
Mike Lewis
Programmer
I've been following this thread from a distance, as Olaf usually answers this kind of question better than I can. So my apologies if this post is off-target. But, from the above remark, I don't understand what your problem has got to do with "information messages" or RAISEERROR.
Your database presumably contains the information that you need to determine the paid-up status of an account (anount due, current balance, whatever). Why can't you use a simple query to find out if the the account is overpaid or paid up?
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
Olaf Doschke
Programmer
Mike is right. All you need to do is check the status after a payment. I assume there aren'T many places your application allows making payments, so it's just a change at this one place. I don't see the need to do that from within SQL Server.
The main thing is you want to inform who? The customer himself? A data typist using the application?
You can send a mail from SQL Server via EXEC master..xp_sendmail 'JohnDoe', 'You overpaid.'
Bye, Olaf.
The main thing is you want to inform who? The customer himself? A data typist using the application?
You can send a mail from SQL Server via EXEC master..xp_sendmail 'JohnDoe', 'You overpaid.'
Bye, Olaf.
Olaf Doschke
Programmer
Ok, you answered one of my questions beforehand, the user needs to be informed. Well, the recommendation of a mail then is not the first thing I'd do, instead go with the other recommendation, this is part of your application business logic, not of the database. So put it in your VFP application to inform about overpaying or paying up.
I don't know how you store a customers balance, but for accounting sake you should save each payment or withdrawal into a booking table, so the account balance always results from summing all bookings. made. Maybe at some time you delete all bookings older than 1,2,5, or 10 years and store that as a balance of that date to not let this bookings table grow too large. It's also a matter of laws. So in the simplest case I'd design a bookings table with fields customerid, payment, datetime and maybe a checksum you compute with a 'secret' algorithm, so noone can fake booking records from elsewhere.
Anyway, after any booking is made you can (re)query the balance by SELECT Sum(payment) as balance FROM bookings WHERE customerid = ?m.lnCustomerID and if that's 0 it's paid up and if it's positive it's overpaid. There is no need for a trigger just because that is of interest in the very moment a payment is stored. There typically are deadlines for an invoice, so it'll be sufficient to check at deadlines for the payment, for cashback or for reminders or whatever, but that's not the responsibility of the database, that's business logic of the application.
As I said in my second post: "Maybe you're putting too much business logic there, maybe not." From the current knowledge I'd say you indeed put too much business logic into the database.
Think about point and time of failures. If a trigger fails in the worst case, not because of a programming error but say a hdd head crash, power outage or anything beyond your planning. Then you're left with a wrong account status and since you only check in the moments of an update or insert, you'll never process this customers balance again. Instead of the database trigger action, checking an account /invoice status should be a function of your application, which can be done at any moment by the user and may be done at first startup of the day, during the night form a scheduled task or hourly while the app runs or whatever you like. And it should run over all customers and give a report of what needs to be applied, eg cashbacks (if you do) or reminders or whatever you need.
Also think about user errors. Once a trigger is set off and sends a mail, informs the army or congratulates about the millionth invoice paid and send out a price, not all of that can be reversed easily. If a user enters a payment for the wrong customer, there should be the possibility to stornate that. Triggers are really for the things you need to be in sync with whatever data was changed or inserted, not for this kind of business logic.
Bye, Olaf.
I don't know how you store a customers balance, but for accounting sake you should save each payment or withdrawal into a booking table, so the account balance always results from summing all bookings. made. Maybe at some time you delete all bookings older than 1,2,5, or 10 years and store that as a balance of that date to not let this bookings table grow too large. It's also a matter of laws. So in the simplest case I'd design a bookings table with fields customerid, payment, datetime and maybe a checksum you compute with a 'secret' algorithm, so noone can fake booking records from elsewhere.
Anyway, after any booking is made you can (re)query the balance by SELECT Sum(payment) as balance FROM bookings WHERE customerid = ?m.lnCustomerID and if that's 0 it's paid up and if it's positive it's overpaid. There is no need for a trigger just because that is of interest in the very moment a payment is stored. There typically are deadlines for an invoice, so it'll be sufficient to check at deadlines for the payment, for cashback or for reminders or whatever, but that's not the responsibility of the database, that's business logic of the application.
As I said in my second post: "Maybe you're putting too much business logic there, maybe not." From the current knowledge I'd say you indeed put too much business logic into the database.
Think about point and time of failures. If a trigger fails in the worst case, not because of a programming error but say a hdd head crash, power outage or anything beyond your planning. Then you're left with a wrong account status and since you only check in the moments of an update or insert, you'll never process this customers balance again. Instead of the database trigger action, checking an account /invoice status should be a function of your application, which can be done at any moment by the user and may be done at first startup of the day, during the night form a scheduled task or hourly while the app runs or whatever you like. And it should run over all customers and give a report of what needs to be applied, eg cashbacks (if you do) or reminders or whatever you need.
Also think about user errors. Once a trigger is set off and sends a mail, informs the army or congratulates about the millionth invoice paid and send out a price, not all of that can be reversed easily. If a user enters a payment for the wrong customer, there should be the possibility to stornate that. Triggers are really for the things you need to be in sync with whatever data was changed or inserted, not for this kind of business logic.
Bye, Olaf.
- Thread starter
- #11
The user has access to a query that can to determine if a customer account is paid up or overpaid.
But what the user wants, is to know immediately after he enters the payment if the account is paid up i.e. on saving the transaction. For example, if the user enters incorrect information an error message would be sent to the VFP application stating 'Payments must be entered in ascending date sequence'. The user can then input the correct date and resend or cancel the payment. Similarly, if the customer account is paid up this would be detected at the database level and a message would be sent stating 'The account is paid up'. At this point the changes are committed whereas in the previous example the changes were not committed.
Normally RAISERROR outputs an error message after an error is detected, and changes are rolled rolled back if they are in a transaction, and this is apprehended by VFP.
What Olaf suggested is that I can use RAISERROR to send the completion message but make sure the changes are committed in the database.
But what the user wants, is to know immediately after he enters the payment if the account is paid up i.e. on saving the transaction. For example, if the user enters incorrect information an error message would be sent to the VFP application stating 'Payments must be entered in ascending date sequence'. The user can then input the correct date and resend or cancel the payment. Similarly, if the customer account is paid up this would be detected at the database level and a message would be sent stating 'The account is paid up'. At this point the changes are committed whereas in the previous example the changes were not committed.
Normally RAISERROR outputs an error message after an error is detected, and changes are rolled rolled back if they are in a transaction, and this is apprehended by VFP.
What Olaf suggested is that I can use RAISERROR to send the completion message but make sure the changes are committed in the database.
Olaf Doschke
Programmer
> For example, if the user enters incorrect information an error message would be sent to the VFP application stating 'Payments must be entered in ascending date sequence'.
Couldn't that only be detected if multiple payments are made with several dates?
>But what the user wants, is to know immediately after he enters the payment if the account is paid up i.e. on saving the transaction.
That doesn't need a trigger, that simply needs a query of the account status after payments are stored.
>What Olaf suggested is that I can use RAISERROR to send the completion message but make sure the changes are committed in the database.
Yes, and I thought you already tried that and found out it won't work out good with the save method, as the VFP cursor isn't following the database changes, because it's reverted. Even though you stated one hour later "code within the save routine did that., I still assume the VFP cursor reverts to an earlier state at least in some or one record, if the TABLEUPDATE() or whatever you use to save the cursoradapter changes fails with an error.
To not overcomplicate things, I'd suggest you simply use the already existing "query that can to determine if a customer account is paid up or overpaid." to check after the user committed one or some payments. What's too complicated about that for you?
Bye, Olaf.
Couldn't that only be detected if multiple payments are made with several dates?
>But what the user wants, is to know immediately after he enters the payment if the account is paid up i.e. on saving the transaction.
That doesn't need a trigger, that simply needs a query of the account status after payments are stored.
>What Olaf suggested is that I can use RAISERROR to send the completion message but make sure the changes are committed in the database.
Yes, and I thought you already tried that and found out it won't work out good with the save method, as the VFP cursor isn't following the database changes, because it's reverted. Even though you stated one hour later "code within the save routine did that., I still assume the VFP cursor reverts to an earlier state at least in some or one record, if the TABLEUPDATE() or whatever you use to save the cursoradapter changes fails with an error.
To not overcomplicate things, I'd suggest you simply use the already existing "query that can to determine if a customer account is paid up or overpaid." to check after the user committed one or some payments. What's too complicated about that for you?
Bye, Olaf.
- Thread starter
- #13
- Status
