trying to update a column in a table

Questions and answers regarding general SQL and backend databases

trying to update a column in a table

Postby tkilshaw1553613063 » Thu Mar 24, 2022 7:22 pm

Using Servoy_202006

the following code works without error but the MySQL database is not updated:

Code: Select all
function onAction$updateUsers( event )
{
  /** @type {JSFoundSet} */
  var fs = datasources.db.qfi.t_user.getFoundSet( );
  application.output('fs: ' + fs );
  var fsUpdater = databaseManager.getFoundSetUpdater( fs );
  application.output('fsUpdater: ' + fsUpdater);
  var a = fsUpdater.setColumn( 'featuresfixesviewed', 0 );
  application.output('setColumn a: ' + a);
  var b = fsUpdater.performUpdate( );
  application.output('performUpdate b: ' + b);
}


Console output:

fs:
fsUpdater: com.servoy.j2db.dataprocessing.JSFoundSetUpdater@70e9c4cf
setColumn a: true
performUpdate b: true

In the interactive console fs is:

Code: Select all
=>fs
{_records_:,_selection_:0,alldataproviders:["id","accessbusinessid","accesscount","accesslevel","active","airtax","allowproductcontrol","amountdisplay","batterylevy","clearfinancialrep1id","crosscompanycopy","displaytypecashdeals","displaytypefinancedeals","displaytypeleasedeals","docfeecash","docfeefinance","docfeelease","dtdmssalespersonid","dtdmssalespersontype","emailaddress","etchingid","featuresfixesviewed","financepmtsperyear","firstname","fk_businessid","fk_companyid","fk_financebankid","fk_leasebankid","fk_taxcodeid","frontlineuserid","gapid","insalgorithm","insapplicantah","insapplicantci","insapplicantlife","insapplicantloe","inscompany","inscompanyloe","insdisretrofinance","insdisretrolease","insnamefinance","insnamefinanceloe","insnamelease","insnameleaseloe","lastaccess","lastname","leasepmtsperyear","levyonusedunits","licencefee","loginname","lubricousername","maintenanceid","otherfee1","otherfee2","otherfiid","othertax1","othertax2","password","propackid","rebate1","rebate2","salgapprogramid","salgappromocode","securityid","showunitcosts","spallowdeletecontact","spallowdeletedeal","spallowdeleteunit","spshowalldeals","tirelevy","tirewheelid","unitcategory","userqprintaccess","useservoy","warrantyid"],allnames:,length:0,multiSelect:false}


This is correct for the t_user table,

The return values say there is no problem but the column in the t_user table is not changed!

Can you help?

thanks,

Terry
tkilshaw1553613063
 
Posts: 47
Joined: Tue Mar 26, 2019 5:11 pm

Re: trying to update a column in a table

Postby mboegem » Fri Mar 25, 2022 4:41 pm

HI Terry,

Code: Select all
var fs = datasources.db.qfi.t_user.getFoundSet()

will just give you an instance of the foundset.
By default this has no records selected, so yes the current update is performed without errors, but on 0 records

Based on the foundset you can either perform a find/search or case you want to update all records, add a loadAllRecords() to your script.

So for the latter:
Code: Select all
var fs = datasources.db.qfi.t_user.getFoundSet()
fs.loadAllRecords()
var fsUpdater = databaseManager.getFoundSetUpdater( fs )
etc.
etc.


Hope this helps
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: trying to update a column in a table

Postby tkilshaw1553613063 » Fri Mar 25, 2022 5:00 pm

Greatly appreciated. I'll give it a try.

Servoy may want to update their documentation as what I did was based on their example code for getFoundSetUpdater!

cheers,

Terry
tkilshaw1553613063
 
Posts: 47
Joined: Tue Mar 26, 2019 5:11 pm

Re: trying to update a column in a table

Postby tkilshaw1553613063 » Mon Mar 28, 2022 8:15 pm

Sorry to say that your proposed fix did not work but this from Tuan did:

"when you pass the foundset, can you try to use the form's foundset? eg."

Code: Select all
databaseManager.getFoundSetUpdater(forms.myform.foundset)


thanks again,

Terry
tkilshaw1553613063
 
Posts: 47
Joined: Tue Mar 26, 2019 5:11 pm

Re: trying to update a column in a table

Postby mboegem » Tue Mar 29, 2022 2:23 am

Hi Terry,

pls check the size of the foundset.
it should be simple, the updater only runs on the records which are loaded into a foundset.
So if the foundset has 0 records, the updater doesn't do a thing.

You can also check the servoy admin page (localhost:8080/servoy-admin)
In the database performance area, you can see what SQL Servoy has generated and was executed on the database.
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: trying to update a column in a table

Postby tkilshaw1553613063 » Tue Apr 05, 2022 8:35 pm

Sorry to say that we still have a problem.

The form _119_Features_Fixes_List_admin has a dataSource of qfi.t_user. It also has a Servoyextra-table whose foundset is qfi.t_features_fixes.

The code to update t_user records is in the onAction for a button and looks like this:

Code: Select all
/**
* @param {JSEvent} event the event that triggered the action
*
* @properties={typeid:24,uuid:"A3959F54-B531-4F12-82E6-D200D4FD9F77"}
*/
function onAction$updateUsers( event )
{
  /** @type {JSFoundSetUpdater} */
  var fsUpdater = databaseManager.getFoundSetUpdater( foundset );
  fsUpdater.setColumn( 'featuresfixesviewed', 0 );
  fsUpdater.performUpdate( );
}


Before the call to getFoundSetUpdater() the foundset shows its datasource as t_user and it has the correct number of records; in my development system's case that is 32. This is true the first time through as well as all the other times.

This code works without error every time AFTER the first time the button is clicked. The first time it is clicked it produces the error shown below. This is an error for the view 'v_user_home' that has nothing to do with this form. v_user_home is used by one form _10_Home_User. That form is reached when logging in as a user but the form _119_Features_Fixes_List_admin is on the administration side of the solution that users never see.

Also, I am not trying to do an INSERT.

Any help appreciated.

Terry

Code: Select all
ERROR com.servoy.j2db.util.Debug - Error executing sql: insert into v_user_home (ID, AccessLevel, `Active`, EmailAddress, FirstLastNames, LoginName, CompanyDBA, CompanyStatus, DefaultBusinessDBA) values (?, ?, ?, ?, ?, ?, ?, ?, ?) with params: [NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue]
   at C:\Users\terry.QUANTECH\git_202006\qfi\qfi\forms\_119_Features_Fixes_List_admin.js:272 (onAction$updateUsers)
   at C:\Users\terry.QUANTECH\git_202006\qfi\quantech_base\forms\base.js:896 (openFormDialog)
   at C:\Users\terry.QUANTECH\git_202006\qfi\quantech_base\qfi.js:1090 (showFormFromEvent)
   at C:\Users\terry.QUANTECH\git_202006\qfi\quantech_base\forms\base.js:270 (onAction$Clickable_GoToNextScreen)
java.sql.SQLException: The target table v_user_home of the INSERT is not insertable-into
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998) ~[mysql-connector-java-5.1.41-bin.jar:5.1.41]
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[org.apache.commons.commons-dbcp2_2.7.0.jar:2.7.0]
   at jdk.internal.reflect.GeneratedMethodAccessor276.invoke(Unknown Source) ~[?:?]
   at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
   at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
   at com.servoy.j2db.datasource.Zf.invoke(Zf.java:7) ~[j2db_server_2020.6.0.3582.jar:?]
   at com.sun.proxy.$Proxy56.executeUpdate(Unknown Source) ~[?:?]
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[org.apache.commons.commons-dbcp2_2.7.0.jar:2.7.0]
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[org.apache.commons.commons-dbcp2_2.7.0.jar:2.7.0]
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[org.apache.commons.commons-dbcp2_2.7.0.jar:2.7.0]
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[org.apache.commons.commons-dbcp2_2.7.0.jar:2.7.0]
   at com.servoy.j2db.server.dataprocessing.Zv.Za(Zv.java:356) [j2db_server_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.dataprocessing.Zv.performUpdates(Zv.java:108) [j2db_server_2020.6.0.3582.jar:?]
   at com.servoy.j2db.dataprocessing.AbstractDelegateDataServer.performUpdates(AbstractDelegateDataServer.java:374) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.dataprocessing.ValidatingDelegateDataServer.performUpdates(ValidatingDelegateDataServer.java:307) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.dataprocessing.AbstractDelegateDataServer.performUpdates(AbstractDelegateDataServer.java:374) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.debug.ProfileDataServer.performUpdates(ProfileDataServer.java:471) [servoy_debug_2020.6.0.3582.jar:?]
   at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:665) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.dataprocessing.EditRecordList.stopEditing(EditRecordList.java:240) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.dataprocessing.JSFoundSetUpdater.js_performUpdate(JSFoundSetUpdater.java:172) [servoy_shared_2020.6.0.3582.jar:?]
   at jdk.internal.reflect.GeneratedMethodAccessor275.invoke(Unknown Source) ~[?:?]
   at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
   at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
   at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:292) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1487) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:672) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.debug.RemoteDebugScriptEngine.executeFunction(RemoteDebugScriptEngine.java:393) [servoy_debug_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.component.EventExecutor.executeEvent(EventExecutor.java:252) [servoy_ngclient_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.DataAdapterList.executeEvent(DataAdapterList.java:126) [servoy_ngclient_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.WebFormComponent$FormcomponentEventHandler.executeEvent(WebFormComponent.java:241) [servoy_ngclient_2020.6.0.3582.jar:?]
   at org.sablo.BaseWebObject.doExecuteEvent(BaseWebObject.java:335) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.BaseWebObject.executeEvent(BaseWebObject.java:324) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.services.server.FormServiceHandler.executeEvent(FormServiceHandler.java:137) [sablo_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.NGFormServiceHandler.executeEvent(NGFormServiceHandler.java:549) [servoy_ngclient_2020.6.0.3582.jar:?]
   at org.sablo.services.server.FormServiceHandler.executeMethod(FormServiceHandler.java:84) [sablo_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.NGFormServiceHandler.executeMethod(NGFormServiceHandler.java:528) [servoy_ngclient_2020.6.0.3582.jar:?]
   at org.sablo.websocket.WebsocketEndpoint$5.run(WebsocketEndpoint.java:408) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.Event$1.run(Event.java:102) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.websocket.CurrentWindow.runForWindow(CurrentWindow.java:76) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.Event.execute(Event.java:92) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.EventDispatcher.dispatch(EventDispatcher.java:125) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.EventDispatcher.suspend(EventDispatcher.java:236) [sablo_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.NGRuntimeWindow.doOldShow(NGRuntimeWindow.java:549) [servoy_ngclient_2020.6.0.3582.jar:?]
   at com.servoy.j2db.scripting.RuntimeWindow.doShow(RuntimeWindow.java:304) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.scripting.RuntimeWindow.show(RuntimeWindow.java:298) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.BasicFormController.showForm(BasicFormController.java:2376) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.BasicFormController$JSForm.js_show(BasicFormController.java:2599) [servoy_shared_2020.6.0.3582.jar:?]
   at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
   at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
   at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
   at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
   at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:292) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1487) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:399) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3534) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:107) [org.eclipse.dltk.javascript.rhino_2020.6.0.3582.jar:?]
   at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:672) [servoy_shared_2020.6.0.3582.jar:?]
   at com.servoy.j2db.debug.RemoteDebugScriptEngine.executeFunction(RemoteDebugScriptEngine.java:393) [servoy_debug_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.component.EventExecutor.executeEvent(EventExecutor.java:252) [servoy_ngclient_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.DataAdapterList.executeEvent(DataAdapterList.java:126) [servoy_ngclient_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.WebFormComponent$FormcomponentEventHandler.executeEvent(WebFormComponent.java:241) [servoy_ngclient_2020.6.0.3582.jar:?]
   at org.sablo.BaseWebObject.doExecuteEvent(BaseWebObject.java:335) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.BaseWebObject.executeEvent(BaseWebObject.java:324) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.services.server.FormServiceHandler.executeEvent(FormServiceHandler.java:137) [sablo_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.NGFormServiceHandler.executeEvent(NGFormServiceHandler.java:549) [servoy_ngclient_2020.6.0.3582.jar:?]
   at org.sablo.services.server.FormServiceHandler.executeMethod(FormServiceHandler.java:84) [sablo_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.NGFormServiceHandler.executeMethod(NGFormServiceHandler.java:528) [servoy_ngclient_2020.6.0.3582.jar:?]
   at org.sablo.websocket.WebsocketEndpoint$5.run(WebsocketEndpoint.java:408) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.Event$1.run(Event.java:102) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.websocket.CurrentWindow.runForWindow(CurrentWindow.java:76) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.Event.execute(Event.java:92) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.EventDispatcher.dispatch(EventDispatcher.java:125) [sablo_2020.6.0.3582.jar:?]
   at org.sablo.eventthread.EventDispatcher.run(EventDispatcher.java:89) [sablo_2020.6.0.3582.jar:?]
   at com.servoy.j2db.server.ngclient.eventthread.NGEventDispatcher.run(NGEventDispatcher.java:55) [servoy_ngclient_2020.6.0.3582.jar:?]
   at java.lang.Thread.run(Unknown Source) [?:?]
tkilshaw1553613063
 
Posts: 47
Joined: Tue Mar 26, 2019 5:11 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 4 guests

cron