SQL UPDATE

Classic Servoy
1

I have assigned global address variables and want to update the table. With PHP and mysql I would use:

UPDATE addresses SET postcode=x,address1=y…where addressesid=id

how do I do it in Servoy?

Thanks

2

use the rawSQL plugin for that!

3

Hi,

Or, on the assumption that you have triggered the option to modify the address fields from the the record in the current form, just use:

forms.address.postcode = globals.input01
forms.address.address1 = globals.input02
forms.address.controller.saveData()

Cheers
Harry

4

Harry, I am updating from a form in a dialog.

I am getting this error message when using the following code to update. 594 is the correct ID of the address being updated. I belived this was updating the columns of addresses if the addressesid = 594…am I mistaken?

java.sql.SQLException: Duplicate entry ‘594’ for key 1 Duplicate entry ‘594’ for key 1

addressesid = globals.gAddressesID
addresstype = globals.gAddressType
address1 = globals.gAddress1
address2 = globals.gAddress2
suburb = globals.gSuburb
postcode = globals.gPostcode
city = globals.gCity
country = globals.gCountry
state = globals.gState

var done = plugins.rawSQL.executeSQL("AEC01", "addresses", "UPDATE addresses SET postcode = postcode,address1 = address1 WHERE addressesid = addressesid")
if(done)
{ 
plugins.rawSQL.flushAllClientsCache("AEC01","addresses")
}
	else
	{ 
	plugins.dialogs.showErrorDialog('Error', 'SQL error: '+plugins.rawSQL.getExceptionMsg(), 'Ok')
	}

application.closeFormDialog();

Ta

5

I don’t really understand why you want to do this with raw sql. That is NEVER necessary with ‘normal’ use of Servoy and HARDLY necessary in other cases.

You can have a form in a dialog, based on the addresses tabel and let the user update the data. It will be pushed to the database by Servoy, no need to do the job ‘by hand’.

6

Maybe I put him on the wrong track here :oops:

I thought he would update a whole bunch of records, by SQL, this way.

Than a better way would be to use the getFoundSetUpdater
Or indeed, do it the way, Marcel describes.

7

OK,

This is what I thought. When I added a edit button to the line item with a method to show form in dialog I thought all would be OK but a record with an incorrect addressid is presented. The line item form is within a tab panel.

I have tried so many other ways now I am just confused…but I know it is simple…everything else is.

One if the things I tried was:

forms.address_edit.addressesid = forms.address_list.addressesid;
application.showFormInDialog(forms.address_edit,-1,-1,-1,-1,'Edit Address',false,false,false);

But obviously this was incorrect.

Can you point me in the right direction?

Thanks

8

What you do is indeed wrong.

When you use the frontmost foundset you don’t need to set anything. Otherwise you would do something like

forms.a.loadRecords(foundset/relationship) before opening the dialog.

Don’t forget to check the ‘userseperatefoundset’ property of the form if you do as suggested.

Hope this helps a little…

9

4xjbh:
I have tried so many other ways now I am just confused…but I know it is simple…everything else is.

I often feel like that when working with Servoy. Hang in there. =) Thanks so much to all the people that help out on the forums.

10

Marcel, it helped alot. Thanks.

When creating new addresses into the same form I find if I press my cancel button or the close button on the dialog I am left with a blank line item record . Is there a way not to create the record until I press a save button? Using transactions works, is there a better way?

11

I am happy I could be of help :)

You can work with globals but I would use a transaction.

12

Hi 4xjbh,

remember that unlike PHP, Servoy has automatic data binding, so unless you are doing something very strange or unusual you should never have to update anything using SQL.

SQL for searching is another matter --databaseManager.getDataSetByQuery – is very useful for looking up records, but it is possible to avoid SQL alltogether.

Not sure why you are using these globals to edit the address?

You could simply create a new record in your address table.

ondatasave in the form you verify the address is some way and
If it is incomplete, you can
a) force the user to fix the address or
let the user abort and
b) delete the new address