Hi,
Servoy 3.5.10
I’ve never seen this before. Most of the time when I write SQL I use the ANSI/92 Syntax, i.e. JOIN instead of listing comma-separated tables in the FROM clause and then linking them in the WHERE clause. I also have used it many times before with Servoy. I know you can’t use it with the FormByQuery but I’ve never seen a problem with GetDataSetByQuery until now. Basically I ran the code below:
if(globals.g_sched_prov)
{
var sql_query = "select npv_id from onc_np n JOIN onc_visits v ON n.mrn = v.mrn " +
"JOIN ONC_NP_EXTENSION ne ON ne.NPV_ID=n.NPV_ID " +
"LEFT OUTER JOIN DISEASE_TYPE d ON d.DISEASE_CODE=ne.DISEASE_TYPE " +
"WHERE (ne.DISEASE_TYPE IS NULL OR d.DISEASE_DEPT IN ('Lymphoma','Pending')) " +
"AND v.FIRST_DEPT_VISIT = 1 " +
"AND SCHED_PROV = '" + globals.g_sched_prov + "' ";
}
var MDdataset = databaseManager.getDataSetByQuery('onc_patient', sql_query, null, 1000)
forms.data_entryNP.controller.showRecords(MDdataset)
This runs fine through getting the MDdataset. That shows me that I have the right number of records in the dataset. However as soon as I go to showRecords the MDdataset size goes to zero (0) and no records load. I’ve never seen that where the dataset is correct when the query executes but then somehow gets changed when that same dataset is used to load records. So then just playing around I ran the same query using ANSI/86:
if(globals.g_sched_prov)
{
var sql_query = "select n.npv_id from onc_np n, onc_visits v, ONC_NP_EXTENSION ne, DISEASE_TYPE d " +
"WHERE n.mrn = v.mrn AND ne.NPV_ID=n.NPV_ID AND d.DISEASE_CODE (+) = ne.DISEASE_TYPE " +
"AND (ne.DISEASE_TYPE IS NULL OR d.DISEASE_DEPT IN ('Lymphoma','Pending')) " +
"AND v.FIRST_DEPT_VISIT = 1 " +
"AND v.SCHED_PROV = '" + globals.g_sched_prov + "' ";
}
var MDdataset = databaseManager.getDataSetByQuery('onc_patient', sql_query, null, 1000)
forms.data_entryNP.controller.showRecords(MDdataset)
This runs exactly the same through the ‘var MDdataset’ line, i.e. returns the same number of rows without a problem. However when I then load this dataset everything works! The showRecords works as expected. Have I just been lucky all this time? I much prefer the ANSI/92 JOIN syntax and have used it pretty much exclusively for the last 4 years or so. Isn’t a dataset a dataset however it was derived? Any insight greatly appreciated! If I have just been lucky for some reason and ANSI/92 really isn’t supported with Servoy 3.5 and my queries have just happened to work, does anyone know if JOIN syntax is supported in Servoy 4 and/or 5.
John,
Ansi/92 joins are supported in dbmgr.getDataSetByQuery, in Servoy 3.5 and higher.
And indeed, controller.showRecords(ds) does not alter the dataset, it just takes the pks in it.
There must be something else wrong, are you sure MDdataset has the expected size?
Maybe there is an error in the logs?
Rob
Hi Rob,
Thanks for the quick response and sorry I missed getting to ServoyCamp and seeing you again!
Mystery is at least partially solved I think. I believe I might have inadvertently left off the ‘n’ qualifier in the “SELECT n.NPV_ID” in the ANSI/92 query. The relationship between ONC_NP and ONC_NP_EXTENSION is a straight one to one and every record in ONC_NP has a matching record in ONC_NP_EXTENSION. I’m not sure that that is what I did but this morning I was planning one doing some more testing and in particular leaving out an ‘OnRecordSelect’ method that ran right after the ShowRecords line. I left that out the “JOIN” syntax ran fine. I then put it back in and it ran fine again! Then I left out the ‘n’ qualifier and got the exact same response as yesterday. I’m not sure that that was what I did but it acts exactly the same so I think that must have been what happened. The ONC_NP_EXTENSION table is new (we added it recently to keep all changes out of the ONC_NP table) and I might have left it out, not having needed that qualifier before. I can’t verify it but I think that must have been what happened. What was strange (and still is strange) is that the query runs ‘correctly’ at least thru the GetDataSetByQuery part. I’ve attached a screen shot when running in Debugger mode. If it comes through all right notice that the query is showing that the correct number of records have been returned (41). And I haven’t yet run the ShowRecords command. As soon as that runs the dataset is returned to zero and nothing shows in the form. So I’m thinking this has to have been my mistake but I am curious as to why Servoy seems to return a valid result from the query (which isn’t properly written). And yet clearly doesn’t have a valid dataset of PKs to load the records with…[attachment=0]BadQuery.pdf[/attachment]
John,
If the database driver throws an sql exception on the query, Servoy will log this and send the error to the solution on-error method if it exists.
The method that called getDataSetByQuery will just continue, the dataset returned will contain no data but it will contain the exception (ds.getException()).
If the driver does not throw an sql exception, then servoy simply thinks there are no results.
You can check the logs for sql errors and try the same query in a db-tool directly in the database.
Rob
John,
Note that in Servoy 4+, dbmgr.getDataSetByQuery() does not return with an exception-dataset, instead an exception is thrown and method execution is stopped.
Rob
Hi Rob,
I do have the on-error method running on this solution but no error comes back when I run the ‘faulty’ SQL outlined earlier. There is no sign of anything going wrong until I run showRecords(pkdataset). Then all hell breaks loose! There is really nothing very relevant in the servoy_log either I don’t think ("2009-11-18 14:14:22,750 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - "). And as mentioned the dataset IS there and with the right number of records after running GetDataSetByQuery. So what I now did is run application.output(GetDataSetByQuery) and that shows what is going on.
When running the correct SQL the output is: BufferedDataSet{[10092323][10094675][10096827…]}. When running the incorrect SQL, there is still on output of the dataset but it looks like this: BufferedDataSet{[1.0092323E7][1.0094675E7][1.0096827E7…]}. Notice the numbers are actually the same but done as decimals and with the ‘E7’ tagged on at the end of each PK.
So the error was mine but what is interesting from the Servoy end is that:
a) the query ran at all (if I try to run it that way in a regular query tool like ADS, I’ll always get back the Oracle error and no results).
b) the fact that Servoy isn’t giving an error message.
John
P.S. I know I should be working in 4 or now 5 but I’ve got to get this main application out that I’ve been working off and on for the last year and haven’t had time to learn it sufficiently. And lord I’ve still got a great little application running 2.2.8 that has to deal with patient care and that’s been running perfectly since 2004 - Servoy is too good! If these things come off though that I’m working on, once I’m done I’ll hopefully have time to migrate everything to Servoy 5.
John,
These results must come from the driver, if the driver does not report an error and returns data, Servoy uses that.
If ADS returns an error and Servoy does not, you may want to check the driver version.
Regarding the different data in the data set, if you do not prefix the column with ‘n.’, you probably select another column from another table which may not have the same type in the db.
And in showRecords() these apparently do not match against the pks in the db, so Servoy shows no records.
Rob
Hi Rob,
Again just to be clear I am quite sure it was me who made the mistake of not properly identifying the particular column with two tables having the same column name. So all I’m curious about here is how Servoy is handling what should be a straightforward SQL error since there seems to be some inconsistency. I’m hoping this is useful.
rgansevles:
These results must come from the driver, if the driver does not report an error and returns data, Servoy uses that.
If ADS returns an error and Servoy does not, you may want to check the driver version.
I switched to using the same driver I use with Aqua Data Studio. What was interesting is when I did this the dataset from the faulty SQL was returned as before for 41 records. However this time 34 of the records (PKs) were actually accepted as valid by Servoy and used to create the foundset, 7 were not. Showing just a limited set of those 41 you can see what happened with that:
BufferedDataSet{[9978759.0][9982362.0][1.0006087E7][1.0002195E7][1.0002012E7][9972556.0][9974007.0]}
Notice that the numbers that had 7 or fewer digits were returned more or properly (as ‘double’ rather than integers but still valid). But those numbers that had 8 digits had the same ‘E7’ at the end and obviously Servoy was not able to load those in the foundset and thus only 34 of the 41 were loaded. Then I went on to your second suggestion:
Regarding the different data in the data set, if you do not prefix the column with ‘n.’, you probably select another column from another table which may not have the same type in the db.
And in showRecords() these apparently do not match against the pks in the db, so Servoy shows no records.
I knew the two columns in question had the same ‘general’ type of NUMBER in Oracle. But one of them had a precision of 19 and a scale of 0 while the other just left both of those undefined. So I changed it so that both were simply NUMBER and everything else undefined. Restarted Servoy again and ran the same ‘faulty’ query. This time as far as the ‘output’ was concerned I got the same result as before: 34 PKs returned ‘correctly’ - albeit as double - and 7 returned as decimals with the trailing ‘E7’. But this time all 41 PKs were returned in the foundset…
As I said I’m happy that I know what I did wrong - silly typo that I didn’t spot right away - and I’m happy to let it go at that. But I’m also happy to pursue this if you think it is worthwhile from a Servoy point of view. It’s certainly strange:
- I don’t understand why the faulty SQL works at all with seemingly no error message and
- now why, when changing the precision on one of the PK columns, would that mean that all 41 PKs would ‘load’ when 7 of them were still showing in the output as decimals and with the ‘E7’ tacked on at the end, not even numbers, let alone integers.