Getting error in update query

Classic Servoy
1

I will explain the problem in details that i am struggling in,

I had place a image media and a combo box.Depending upon the records the number of images that’s varies.The combo box contains the path names thats need to display in the image media,shortly multiple selection of images.There is button thats placed in the form to delete the image.

What i really need to do is update the pathname thats present in the combo box after the delection.I can get the updated path name after the delection.But after the deletion if i go to another form (or) go forward and i came to the image form the same thing is there(without the deletion of that particular pathname)

Incase if there 6 pathnames then i deleted one of them.Immediatly i will get the 5 pathname but after that only the issue starts.In the sense the updation query does not hit the table.

var query = "UPDATE repair_replacement_items SET image_name = image_name + “+ globals.greplaced_path +” WHERE repair_replacement_itemsid = " + globals.grecordid + “”;

var dataset = databaseManager.getDataSetByQuery (controller.getServerName() ,query ,null ,100);

I am using sybase.Helps are invited from all ends.Thanks in advance.

2

What happens to all :?:

Is it possible to update the table by using the query type that i had used here.The error here is , i am not getting anything in the dataset.There is a information that there is a bug in the combo box,is it true.Please respond me to tackle this isssue.

I am eagerly waiting for the reply.

3

rifluxyss:
I am eagerly waiting for the reply.

I don’t think you can use an UPDATE statement in a loadDatasetByQuery instruction.
Probably the RAWSql Plugin is more indicated.

Look up the documentation for more details.

4

Well the reason nobody answers is, I think:

  1. read the manual;
  2. you will find out that you can not do an update like that, this way;
  3. you need to use the rawsql plugin;
  4. but you should simply change the value via the dataprovider;
  5. if you don’t Servoy doesn’t see the changes;

As said in point 1, this is all very well documented…

5

Cool 8)

Already i go through the documentation and i check it in different angle to execute the RawSQL.But i failed to get that “true” from there at all times i received the “false”.After that i think that its a sybase so that its not allowing to run that plugin there.Then only i go for the above experiment and update that in the forum to get the reaction from the experts.

If i am asking any blunder i am sorry.Any suggestions regarding to the update (or) plugin.What ever it may be please respond.

Are you anger with me then i am :( .

6

I suppose, image_name is a String. When I set globals.greplaced_path to “abc” and globals.grecordid to 1 your expression evalutes to

UPDATE repair_replacement_items SET image_name = image_name + abc WHERE repair_replacement_itemsid = 1

which is not a working SQL statement if you don’t have a column “abc”. How do you want image_name to be updated? At least you will have to quote image_name as in this

What you need to do (at least) is:

var query = "UPDATE repair_replacement_items SET image_name = image_name '"+ globals.greplaced_path +"' WHERE repair_replacement_itemsid = " + globals.grecordid + "";

This evaluates to

UPDATE repair_replacement_items SET image_name = image_name + 'abc' WHERE repair_replacement_itemsid = 1

It will update image_name to image_nameabc where repair_replacement_itemsid is 1. This will NOT work on all databases, by the way. You are trying to concatenate strings using the “+”, which is not supported on all databases (some use || etc.).

In any case: use the debugger! When you send a query to the database you will see the DB error in the debugger and can figure out what is wrong. I have also recommended many times to test queries in a query tool before asking what is wrong with Servoy…

And: you cannot use getDataSetByQuery to update data. If you want to update data, you have to do this:

plugins.rawSQL.executeSQL(controller.getServerName(), 'repair_replacement_items', query)
7

patrick:
In any case: use the debugger! When you send a query to the database you will see the DB error in the debugger and can figure out what is wrong. I have also recommended many times to test queries in a query tool before asking what is wrong with Servoy…

Totally agree: unless you’re a SQL guru, writing SQL queries inside Servoy editor is a bad habit.
Use a query tool like AquaDataStudio, test the query: if it’s ok, then you can use it in Servoy…

8

Thanks for Patrick to handle this issue like a well trained teacher,

Instead of Plugin i arise this query in the Installation,thats a mistake.

Since this issue is expanding like a elastic.I go for different combination on this issue.When i go on the debugger there is nothing wrong in the query.Its executing fine.But when i assign that plugin to a variable.Instead of ‘True’ i am getting ‘False’.

Okay after that i go to DELETE,by thinking that there is some thing in the UPDATE.There also the same thing i am getting to be in.I cant able to use the RawSQL Plugin successfully.

Is this things are happened due to the version that i am using to be.The version i am using is R2 2.2.7 (or) the RawSQL plugin is not accepting the Sybase query.I believe anyone of the reason must be true.

9

It’s a bit hard to understand your problem. Could you post the code you are using?

10

Thanks for the interest that you are taking to solve my issues.

Here is the code patrick.

To update the undelete pathname into the field.

var btn = globals.dialog_warning('Are you sure you want to DELETE this picture?');

var fileName = globals.grecord_fields // selected path name to delete
var dataset = application.getValueListItems(“splitted_image_names”)
// Push the undeleted path name in to array
var undeleted_array = new Array();
if(btn == ‘OK’)
{
for ( i=0 ;i< dataset.getMaxRowIndex();i++)
{
if(fileName != dataset.getValue(i,1))
{
undeleted_array.push(dataset.getValue(i,1))
}
}
repair_image1 = ‘’; //To make the image media blank

//Set that array value into valuelist

application.setValueListItems( “splitted_image_names”, undeleted_array)
globals.grecord_fields = undeleted_array[0]
globals.grecordid = controller.getSelectedIndex()

//for getting the values of the image_name(data provider)

var query = “SELECT image_name FROM repair_replacement_items WHERE repair_replacement_itemsid = " + globals.grecordid
var dataset1 = databaseManager.getDataSetByQuery (controller.getServerName(), query, null, 100);
var image_path =dataset1.getColumnAsArray(1)
var path_name = dataset1.getValue(1,1);
var replaced_pathname = path_name.replace(fileName,‘’)
globals.greplaced_path = replaced_pathname.replace(‘####’,‘##’)
var query1 = “UPDATE repair_replacement_items SET image_name = image_name '”+ globals.greplaced_path +”’ WHERE repair_replacement_itemsid = ‘" + globals.grecordid +"’" ;
var dataset2 = plugins.rawSQL.executeSQL(“contact_mgmt”, “repair_replacement_items”, query1)
}
else {
return;
}

Any more details …

11 
UPDATE repair_replacement_items SET image_name = image_name '"+ globals.greplaced_path +"' WHERE repair_replacement_itemsid = '" + globals.grecordid +"'"

As I said before, for

globals.greplaced_path = abc
globals.grecordid = 1

this evaluates to

UPDATE repair_replacement_items SET image_name = image_name 'abc' WHERE repair_replacement_itemsid = '1'

What I think is wrong is:

  1. … = image_name ‘abc’ … : If you want to set the image_name to ‘abc’, it has to be ```
    SET image_name = ‘abc’
2. itemsid is probably an integer, so here you should not use quotes: repair_replacement_itemsid = 1

If I understand everything right, your String should be constructed as

“UPDATE repair_replacement_items SET image_name = '”+ globals.greplaced_path +“’ WHERE repair_replacement_itemsid = " + globals.grecordid”

12

No way patrick,

I appreciate you on handling this issue,

As i told to you earlier that i had tried for number of combination on this issue these thing i had tried earlier itself.But no use,is there any other way to overcome this issue.If anybody pass through the same situation that i am now in that those struggle in the update query can pass there view on this issue .

13

We try it the other way round: you give me a working SQL statement that you have succesfully sent to the database from a DB tool (not from Servoy) that does what you want and I show you how to do that from Servoy.

14

And I would like to add: there is no issue in using update queries with the rawsql plugin (at least none that I know of). The issue is that you do something wrong.

15

The same query

There are 3 pathname before the execution of the query

They are

C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg##C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Water lilies.jpg##C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Winter.jpg##

After the delection query that the below one

var query1 = “UPDATE repair_replacement_items SET image_name = '”+ globals.greplaced_path +“’ WHERE repair_replacement_itemsid = " + globals.grecordid +”";

There is only 2 pathnames

The pathname according to update query is

UPDATE repair_replacement_items SET image_name = ‘##C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Water lilies.jpg##C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Winter.jpg##’ WHERE repair_replacement_itemsid = 1

Still these things are correct after then where the mistake will be happen

Any ideas…

16

So you want to fire this query

UPDATE repair_replacement_items SET image_name = ‘##C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Water lilies.jpg##C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Winter.jpg##’ WHERE repair_replacement_itemsid = 1

to the database? If yes, have you tried to send this query from a DB tool to your database?

17

I cant able to succeed by using the query then i tried by means of performUpdate().Here whats happening is the particular colunm of the foundset is to be updated.But what i exactly needed is to update the column of the current record only.Is it possiable to update the column of the particular records(current record) by using the performUpdate(). .If yes how its possiable.

Currently i am using the code below

var set =databaseManager.getFoundSetUpdater(foundset)
set.setColumn('image_name',updated_pathname)
set.performUpdate();

I am expecting the help thats allow me to update the record.

Thanks in advance

18

If you want to update a particular record, just get it and set the value:

var record = foundset.getRecord(47)
record.column = value
19

try the foundset updater (see manual)

20

Yes jan i have to be…

The update issue has come to its finishing point.

Thanks for all especially to patrick for rectify this issue.