SQL Table Open Error

#1

Is there a way to suppress the File Access Error Message and the subsequent Halt when the application is unable to open a SQL Table? I would like to grab the error and allow the application to either continue without opening the table or gracefully shutdown.

Here is an image of the error I would like suppress

#2

Is the code that is triggering that message your own or template generated?

Can you post a sample of how you are calling open on the table?

#3

Good evening, I am newly signed up but have been lurking on here for awhile. I am on a bit of a time crunch and don’t have the time to experiment so I thought one of you may have already ran into this situation and found a solution. I saw this post and figured this was a good time to jump in.

Let me give you a little background on the situation. We use Clarion 6 and we handcode, we do not use ABC templates. We are wanting to migrate from .dat/tps files to MSSQL. The current .dat/tps tables are in use 24/7 (manufacturing plants). We want to write to both the .dat/tps files and the MSSQL tables at the same time as the first step in this transition.

We attempted this last year and the workstations on the production floor threw errors (This process works correctly in both our Dev and Tst environments.) There was ‘panic in the streets’ and they immediately ‘rolled back’ to a previous Clarion build and did not provide us with any feedback. I have a slight opening in my work load and have been assigned to revisit this week.

In order to generate feedback but not cause the panic, I am logging any error that is received, setting a global variable and if the global variable is set to false than the code that writes to the tables will not attempt to write to MSSQL. All of this is working.

The problem is that the user receives the Microsoft SQL error and I have not been able to figure out a way to suppress this.

My code is an extension that sets the base variables and then a .inc that pulls together the login info, server name, etc. I then do a simple OPEN() call. Immediately afterwards I have Error() handling which works fine but of course it doesn’t handle the Microsoft error. Is there any way to suppress these?

Thank you for your time.

#4

Can you show a screenshot of this error? I think that might help.

If it’s the logon screen then maybe this will help:

AFile{PROP:LogonScreen}=FALSE

Also, welcome to the hub! :slight_smile:

#5

Hi Cindy
In my experience, errors in prod that are ok in test is almost always security permissions of one form or another.
Unfortunately with SQL the possible causes are legion and restricted to your site.

#6

Cindy,
Just to let you know, the error that I get comes from the ABC File Manager class. This occurs when I do Access:Table.Open or Access:Table.TryFetch. According to some people that I have talked to, the only way to suppress this error is to modify the File Manager class, which I do not want to do. What I have done is write a small C# program that I call SQLPing and test the Database connection prior to performing the Open or Fetch, and if the Ping fails then I do my error handling.

#7

Thanks for letting me know. I was tempted to create a new app using the ABC templates and use that TryOpen but it doesn’t sound like that would work either.

#8

These are the errors I receive in my testing (Dev) and I am purposely passing the incorrect password to make it fire. I added your suggestion for the PROP:LogonScreen but it didn’t make a difference.

I have been able to get an agreement with the manufacturing IT to run our application on an unused machine in production tonight in order to receive accurate feedback. If that falls through, then I guess I am going to write up a web service and make a call to it.

#9

Hi Cindy,

You need to ensure that both SQL Server and Windows Authentication login modes are switched on. See this link…

https://support.microsoft.com/en-gb/help/2121736/error-message-when-you-try-to-authenticate-an-odbc-connection-to-your

#10

Can you show some code of how you did this?
The problem is, the exact reason for setting {PROP:LogonScreen}=False is to hide those messages :slight_smile:

Alternatively, you may need to add it to the driver options like /LOGONSCREEN=FALSE

#11

Small function that checks server status without any error message;

SETCURSOR(CURSOR:Wait)
SQLQuery{PROP:LogonScreen}=False     ! Any SQL based table
OPEN(SQLQuery,0)
IF ERRORCODE() OR FILEERRORCODE() THEN
    Loc:ServerStatus=Level:Fatal
    ASSERT(~ERRORCODE())
    !HALT     ! If you want to stop program execution
ELSE
    CLOSE(SQLQuery)
    Loc:ServerStatus=Level:Benign
END
SETCURSOR()

RETURN(Loc:ServerStatus)
#12

Ok, here it is. Let me know if you have any questions.

My app has the Microsoft SQL driver within the database driver libraries.

Added by Extension template to the Main frame of my app.

MSSQLCONNECT.INC - I believe this is the main info you need from here.

  DBCON::DumFile{Prop:Driver} = 'MSSQL'
  SetFile(DBCON::DumFile, 'DUMMY')  ! SetFile populates PROP:OWNER and PROD:NAME
  DBCON::FileName = DBCON::DumFile{Prop:Name}
  OPEN(DBCON::DumFile)

SetFile procedure - Assigns the Owner info

CASE UPPER(pFileName)
OF ! Tables we don’t care about

ELSE ! We fall here

pFile{PROP:Owner} = CLIP(GloMSSQLServerName) & ',' & CLIP(GloMSSQLDatabase) & ',' & |
                    CLIP(GloMSSQLUserName)   & ',' & CLIP(GloMSSQLUserPassword)

END

pFile{PROP:Owner} = pFile{PROP:Owner} |
& ‘;app=’ & GloMSSQLApplicationName |
& ‘:’ & GloMSSQLNetworkUserName |
& ‘;wsid=’ & GloMSSQLWSID

pFile{PROP:Name} = pFileName

#13

Trying this one now. So far so good…keep your fingers crossed for me! :grin:

#14

Hi Cindy,

AFAIK Prop:Driver is read-only unless you’re using the Dynamic File Driver.

Graham

#15

I use the following in the Driver Options of the table in the Dictionary.

/BUSYHANDLING=2 /LOGONSCREEN=FALSE /TRUSTEDCONNECTION=TRUE

#16

Our .dct is at it’s max so I did not add the table to the .dct. I connect to at Dummy table just to complete the login but from that point I use all Stored Procedure calls. But thank you for the recommendation.

So far, SerhatSatir’s recommendation is working. Testers are running the code through it’s paces right now and will go into Production next week and I will know for sure if it was successful.

#17

Hello Cindy,

I am curious about what you find is the DCT max?
I’d be happy to avoid future problems with an early warning.

#18

This is Clarion 6. It is my understanding this was resolved in future Clarion releases.

With Clarion 6, there is a limit on the number of objects that can be in the defseg. You can have multiple defsegs if you are using the Capesoft templates but I don’t remember the limit on those, maybe 3 or 4.

#19

Technically, SerhatSatir’s recommendation worked. Unfortunately another developer had added a SP call in a linked application and didn’t utilize my template/include file where the SQLQuery{PROP:LogonScreen}=False was stored. So, Production got a nice jolt this morning when processes hit his code. :joy:

But the logging I added pointed out the issue with the connection and we were able to get it resolved and production is running smoothly and connected to the database.

Thank you all for your help.

#20

We’ve also had to deal with this before. The solution I used was to check the SQL connection outside of Clarion’s connection, by using ADO. In our case, the connection is checked before the Clarion connection is established, which helps to circumvent unwanted Clarion messages. I don’t have a concise portion of code to share yet, but I can post if here if you still need it?

Two other ideas that may be helpful:

  • Suppress Clarion messages until after the Clarion connection has been done, by using something like system{PROP:MessageHook} = address (MyMessage) where MyMessage is your own function to trap Clarion messages. The Clarion help has a good description of this.

  • If you were using the ABC classes, there is a way to override the methods in the global embeds. This way helps you to trap the error message during the Open method.

Thys