UPDATE / SELECT on MS SQL 2000

Classic Servoy
1

Does anyone else have issues trying to run the UPDATE SQL function on MS SQL 2000 back end?

2

No. What is your problem?

3

It works fine in Query Analyzer but the exact same syntax generates an error from a method.

I’m positive that the logon to the back end I am using has full read and write capability.

4

And how does the method look like and what error do you get?

5 
		if ( plugins.rawSQL.executeSQL( globals.gCalendarStates, "sostrs", sQuery) ) {
			var sQuery =  "UPDATE sostrs SET drequest='" + utils.dateFormat(d, "MM/dd/yyyy") + "' WHERE csono='" + nCSONO + "' AND drequest='" + utils.dateFormat(dRDate, "MM/dd/yyyy") + "';";
			application.output(sQuery);
			plugins.rawSQL.flushAllClientsCache( globals.gCalendarStates, "sostrs")
			// Keep a trace of the changes
			globals.AddToAuditTrail(globals.gCalendarStates,"sostrs","drequest", globals.gUsersID,dRDate,d, "csono='" + nCSONO + "' AND drequest='" + utils.dateFormat(dRDate, "MM/dd/yyyy") + "'" );
			// redraw HTML
			setCalendarHTML();		
		} else {
			application.output("VAM Update Error: " + plugins.rawSQL.getExceptionMsg() + "\n" + sQuery);
			application.output("Server:" + globals.gCalendarStates);
		}
6

I have noticed some time ago that rawSQL doesn’t process well fields of ‘date’-type.

Therefore I rewrote my coding so that I use a foundset in stead of rawSQL. Also when using rawSQL, your data is not refreshed on other clients, because this update runs outside of Servoy.

But if you really want to use rawSQL for SQL 2000, perhaps you have to use

SET drequest = convert(datetime, dRDate, 101)

This 101 means mm/dd/yyyy. If your database-type is smalldatetime, then replace datetime by smalldatetime.

7

First of all: use the RawSQL plugin only as a last resort: most updates can be done through Servoy as well, keeping the datacache in tact!!!

If you absolutely have to use the RawSQL plugin for some reason:
Which version of Servoy do you run? Because in 3.5, you can also use prepared statements, thus avoiding filling in the variables into the SQL statement yourself, which is dangerous, because it can awwlo users to do SQL inserts and secondly, each query will then be seen as a unique statement, of which the DB needs to figure out how to best execute it, which takes time, so it makes your solution slower.

Paul

8

I’m using Servoy 2.2.7 and the i-net Merlia driver v 7.01

9

OK, I would suggest then the following:

  • First of all: try not to use the RawSQL updates. That avoids this whole issue of parsing your variables into the SQL string, which is not good practice anyway and makes your Solution database dependant
  • Secondly: If you really need to use the RawSQL plugin: upgrade to 3.5.2 to get the latest version with prepared statement support

I looked at the code you provided: Looks like this is an update that can be perfectly executed using the FoundSetUpdater functionality that Servoy offers.

Paul