Intermittent ExecuteSQL questions marks '?'

Questions
1

I seems to be getting intermittent "?" with some auto-calcs against a key field that is set via script.

I will change the auto-calcs field to be included in the script but I was wondering if anyone can shed some light on this?

field:

request::ucode   //text

calc:

ExecuteSQL ( "

SELECT
	ucode    //text
FROM
	resident
WHERE
	id_contact = ?

" ; "" ; "" ; request::id_contact )

macos: 14.6.1
fmp: 21.0.2

2

Aside from the usual things like:

  • field or table name changed
  • reserved word accidentally used in query

The less usual way this has happened to me also this happened to be a case where it would intermittently work/not work:

  • A bad index that needed to be rebuilt

I suppose that some permissions scenarios could also possibly cause something like this.

EDIT: Amending to clarify that the bad index case stumped me hard for a while before I decided to test that idea. But -- it was in a purely scripted scenario; there was no auto-enter calc involved.

3

After further investigation it seems the issue occurs when the ExecuteSQL function calls an external file. Not just auto-calc but script steps as well.

The scripts are running PSOS, so I am curious if there is a lag opening the external file.

Regardless I will have a look at both permission and indexing as well. - thanks.

4

Might not be relevant but…

request::ucode or resident::ucode?

5

I think this might have just happened to me yesterday too!

A script running via PSOS, but in our case I don't think referencing an external file.

Unless there was some confounding factor in our logging/error trapping process, I found an inexplicable sql error in our log. I tested the same query with the same exact params on client and it ran just fine, so no syntax issues that should cause ?.

We're on FMS 19.6.4. I think this PSOS call was initiated by a Data API session.

6

Isn't there some records where id_contact is some text rather than number ?

7

I know we are talking ancient history here, but somewhere around v14 or v15, I worked on a system that was just starting to use PSOS. We noticed that our PSOS scripts would have their ExecuteSQL calls that reached into another file fail the very first time we used them. Second time and after within that same PSOS script session, they worked. It's tough for me to imagine that 6 or 7 versions later that hasn't changed, but since it is relevant, and for the sake of being thorough, I mention it.

Our workaround at the time was to have an on-first-window open script that checked to see if the current environment was server, and further scraped the user name to determine which script had invoked the PSOS session, and based on that information it would conditionally run the ExecuteSQL commands once, just to "prime the pump".

Edit: FWIW, we were being careful about the "must first open that external file on client first" requirement.

8

Recent security changes on server mean that PSOS scripts only have access to files that the user has open at the time PSOS is triggered. You can’t rely on the user permissions to allow server to access the files. What you might be seeing is a situation where the file was not open at first but running the scripts caused it to open and then be accessible.

3 Likes