Ultimate++ forum RDF feed

questions about sqlArray

papascalientes — 2012-02-09T09:24:29-00:00

Hello all,

I have to revise an application somebody else wrote. The application is for sqlite databases and uses sqlArray a lot. I never used sqlArray before, therefore I need help concerning two problems.

1. How can I detect if the sqlArray encountered an error so that the application can react according to the errror?

Reason:
If there was an sql error, I get an error message and after that the application crashes sometimes. As far as I understand it, there is an ClearError() in UPP after showing the error message so that I can't use WasError() after a query. My idea was to use WhenPostQuery because it seems not be invoked if there was an error. Is that a good idea or is there a better way?
And what about editing, appending, deleting in the sqlArray? How can I detect an error there?

2. Is there a way to use an exclusive transaction for the sqlArray? Like I can use BEGIN EXCLUSIVE to start an exlusive transaction when using sql commands without sqlArray?

Reason:
In very rare cases it might happen that two applications try to write to the same database at the same time. So we decided that writing to the database always should be done in exclusive transactions, locking the database during the whole writing operation. Editing, appending, deleting in an sqlArray means writing to the database, so I have to find a way to lock the database during the operation.

I would be very glad, if somebody could help me with this problems. Thank you in advance.

Best regards,
Petra

Re: questions about sqlArray

BioBytes — 2012-02-10T09:22:40-00:00

Hello papascalientes,

Perhaps you could have a look to the link below:

http://www.webmonkey.com/2010/02/manage_transactions_in_mysq l_-_lesson_2/

This concerns MySql but could be extapolated to SqLite.

Regards

Biobytes

Re: questions about sqlArray

mirek — 2012-02-10T10:47:16-00:00

papascalientes wrote on Thu, 09 February 2012 04:24

1. How can I detect if the sqlArray encountered an error so that the application can react according to the errror?

Reason:
If there was an sql error, I get an error message and after that the application crashes sometimes. As far as I understand it, there is an ClearError() in UPP after showing the error message so that I can't use WasError() after a query. My idea was to use WhenPostQuery because it seems not be invoked if there was an error. Is that a good idea or is there a better way?
And what about editing, appending, deleting in the sqlArray? How can I detect an error there?

SqlArray is 'high-level' widget that is supposed to manage all errors and transactions on its own.

So I would rather concentrated on why the app is crashing after error message - the most likely cause is that it expects some data to be somewhere where there are none (e.g. after emoty query).

That said, I am not really opposed to adding some finer control about these issues, perhaps by adding Callbacks to perform alternate transaction management and error reporting. Just nobody really needed this so far...

Quote:

2. Is there a way to use an exclusive transaction for the sqlArray? Like I can use BEGIN EXCLUSIVE to start an exlusive transaction when using sql commands without sqlArray?

Reason:
In very rare cases it might happen that two applications try to write to the same database at the same time.

But two application writing to same rows of single database, that is pretty much normal and that is what normal transactions are supposed to solve. No need and not really any advantage in most cases to lock the whole DB...

All that said, I would like to repeat that SqlArray is high-level thing, which imposes some restrictions.

Generally, I have to say that over years, I am gradually using it less and less and mostly for configuration dialogs (where normal users of my apps usually do not really visit) or only using it for queries, not doing any actual inserts/updates/deletes (which I then implement in my own code) - pretty standard approach for me now is to use dialogs for row content, with great help of SqlCtrls to manage inserts/updates.

Mirek

Re: questions about sqlArray

BioBytes — 2012-02-10T17:26:58-00:00

Hi papascalientes,

Mirek has given very good explanations as usual. Sure SqlArray can do some actions "magically" but cannot solve all your problems of errors that could happen in code. It is the way I go in my own applications.

The update, insert and delete operations are managed by SqlCtrls and generally, my application menu functions are written on the following basis:


void MainWin::DefineDevices()
{
	SQL.Begin();

	DevicesDlgWin.FillList(); 
        //Initialization of ctrls in the dialog window
	
	if(DevicesDlgWin.ExecuteOK())SQL.Commit();
	else SQL.Rollback();
}

So if the user presses ok (validation) button, the transaction is committed. If cancel button is pressed, modifications are not recorded in database.

Biobytes

Re: questions about sqlArray

mirek — 2012-02-10T17:59:18-00:00

BioBytes wrote on Fri, 10 February 2012 12:26


void MainWin::DefineDevices()
{
	SQL.Begin();

	DevicesDlgWin.FillList(); 
        //Initialization of ctrls in the dialog window
	
	if(DevicesDlgWin.ExecuteOK())SQL.Commit();
	else SQL.Rollback();
}

So if the user presses ok (validation) button, the transaction is committed. If cancel button is pressed, modifications are not recorded in database.

Biobytes

BTW, this a bad practice. You should not use transactions whose length depends on user GUI interaction. Transactions should be short.

Re: questions about sqlArray

papascalientes — 2012-02-10T18:22:25-00:00

Hello Mirek,

thank you very much for your answer. In the meantime I decided I don't need to know the kind of error because if there is any sql error the application should just close. The application is kind of a special database editor for the users (forgot to mention that in my first mail) and if something is wrong with the databases, it makes no sense to use the editor any longer.

So I tested using WhenPrequery and WhenPostquery to detect an error. That works pretty well for my purposes: I have a boolean variable set true in prequery and set false in postquery. After a query I check the variable and if it is still true, there was an error.

Then I tried to close the application in case of error - and you were definitely right when you said:

Quote:

...why the app is crashing after error message - the most likely cause is that it expects some data to be somewhere where there are none (e.g. after emoty query).

I always get an error from an asset in VCont.h - so far mostly one of the two following:

 { ASSERT(i >= 0 && i < items); return vector[i]; }
{ ASSERT_(items >= 0, "Broken pick semantics"); }

Most of the crashes appear in a grid control which I also didn't use so far. And I have not the slightest idea where to start looking for the problem and find a way how to close the application.

Revising under time pressure a big, complicated application without documentation really is one of my favorites

Concerning your answer to my second question:

Quote:

I know, but there is a policy and ....

Anyway, if it is not possible, I can't help it.

Thank you again for your help.

Re: questions about sqlArray

BioBytes — 2012-02-11T08:19:32-00:00

Thanks Mirek for your advice about transaction and GUI.

To go further, do you recommend to put the code in the close function of the dialog window so the user can choose to quit the dialog and save the modifications or discard these modifications ?

Biobytes

Re: questions about sqlArray

mirek — 2012-02-11T08:55:23-00:00

BioBytes wrote on Sat, 11 February 2012 03:19

Actually, I think it is usually best:

- load data, do not start trasaction
- GUI editing
- if user presses Cancel, do nothing
- if OK, update/insert to DB; only if there is more than single statement involved, use transaction (otherwise single statement is transaction of its own)

Re: questions about sqlArray

BioBytes — 2012-02-11T09:04:02-00:00

Ok Mirek

That is clear for me and I understand that it is safer.

Thank you very much

Biobytes