Syntax question

[Richard Hane]

________________________________
 From: Kenneth Brody <kenbrody at spamcop.net>
To: Richard Hane <yoresoft at sbcglobal.net> 
Cc: filepro support <fpsupport at fptech.com>; fplist <filepro-list at lists.celestial.com> 
Sent: Wednesday, June 5, 2013 4:38 PM
Subject: Re: Syntax question
  
On 6/5/2013 4:33 PM, Richard Hane wrote:
>> fpODBC 1.0.15
>> 
>> DSN = fp-sqlserver
>> 
>> Which one of the following two lines is correct?  Both pass syntax check
>> and will run when tested.
>> 
>> Connection = new ODBC_CONNECTION("fp-sqlserver")
>> 
>> or
>> 
>> Connection = new ODBC_CONNECTION("fp-sqlserver ; ")
>> 
>> The question has to do with the semi colon at the end of the statement.
>> 
>> I am still trying to resolve why the connection sometimes fails.  Then if
>> you immediately run it again it works correctly.

Ken,

Thanks for replying.  I was getting very frustrated.  I have fallen in love with the low level ODBC.  My processes use to take me 2 hours a day.  The new batch file tested last night only takes 22 minutes.  It is great.  I was about to close this project but the problem I described would not allow me to make this process automatic via a scheduled task run at night.

> filePro doesn't do any "syntax check" on the value that you pass to NEW ODBC_CONNECTION(), as the data can contain driver-
> specific information. Both of the above statements are valid filePro syntax, regardless of whether the DSN is valid or not.  The value 
> you give is passed as-is to the ODBC library.
>

I am sorry.  I knew this and just was not thinking.

> Note that all of my tests use something like "DSN=fp-sqlserver;" rather than just the raw "fp-sqlserver".  However, if your value 
> works most of the time, then it must be valid to use that syntax.

I typed to fast yesterday when I sent this email.  I do have the 'DSN=' in my defined ODBC source.

[ snip ]
Now, as to why it sometimes fails...

> Obviously, if you pass the same DSN, and it works most of the time, but fails sometimes (and works if you try it a few seconds 
> later), then it's probably either a network or a timing issue.
>
> Have you considered checking the @ODBCERROR[] system array to see what it says failed?  That might point you to the root
> cause.
>
> Also, if you know that it will work if you wait a few seconds and try it again, consider doing that in your processing.  That is, if 
> NEW ODBC_CONNECTION() fails, use SLEEP to wait a few seconds, and try creating the connection again.  (If this is interactive, > perhaps use a SHOW POPUP to display a message to the user, to let them know what's going on.)  You may want to put a counter in > there, to limit the number of retries, in case it's not a transient error that time.
>
> -- Kenneth Brody

You are probably correct.  Most responses I received from SQL programmers on my MRP users group also said that is what it sounded like.  I will set up and try the SLEEP command today.  I'll report the results back to the list in case anyone else has the same situation.

BTW...  I do use the 2 lines that are to report if the connection isn't found and report what the reason is.  The first list displays the 'Show' statement.  But I never see the 2nd line.

Thanks again.  I think this will solve the problem.

Rick Hane
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130606/69e51818/attachment.html