U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » questions about sqlArray

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

questions about sqlArray [message #35396]

Thu, 09 February 2012 10:24

papascalientes
Messages: 5
Registered: February 2011

Promising Member

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

Report message to a moderator

Re: questions about sqlArray [message #35400 is a reply to message #35396]

Fri, 10 February 2012 10:22

BioBytes
Messages: 307
Registered: October 2008
Location: France

Senior Member

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 Smile

Biobytes

Report message to a moderator

Re: questions about sqlArray [message #35401 is a reply to message #35396]

Fri, 10 February 2012 11:47

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

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

Report message to a moderator

Re: questions about sqlArray [message #35409 is a reply to message #35401]

Fri, 10 February 2012 18:26

BioBytes
Messages: 307
Registered: October 2008
Location: France

Senior Member

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

Report message to a moderator

Re: questions about sqlArray [message #35410 is a reply to message #35409]

Fri, 10 February 2012 18:59

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

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.

Report message to a moderator

Re: questions about sqlArray [message #35412 is a reply to message #35401]

Fri, 10 February 2012 19:22

papascalientes
Messages: 5
Registered: February 2011

Promising Member

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 Wink

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.

Report message to a moderator

Re: questions about sqlArray [message #35414 is a reply to message #35412]

Sat, 11 February 2012 09:19

BioBytes
Messages: 307
Registered: October 2008
Location: France

Senior Member

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 ? Confused

Biobytes

Report message to a moderator

Re: questions about sqlArray [message #35416 is a reply to message #35414]

Sat, 11 February 2012 09:55

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

BioBytes wrote on Sat, 11 February 2012 03:19

Biobytes

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)

Report message to a moderator

Re: questions about sqlArray [message #35417 is a reply to message #35416]

Sat, 11 February 2012 10:04

BioBytes
Messages: 307
Registered: October 2008
Location: France

Senior Member

Ok Mirek

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

Thank you very much

Biobytes

Report message to a moderator

Previous Topic:	CLOB data handling
Next Topic:	Is it possible to call a stored procedure with an output parameter?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Thu Apr 18 07:25:45 CEST 2024

Total time taken to generate the page: 0.02440 seconds