Re: Vexing, un clear SQL error

[Kirk Brooks via 4D_Tech]

Tim,
I am also thinking about setting a semaphore at the start and end - but
that seems like the wrong path because I can't believe the SQL server can
only accomodate one operation from the entire database at a time.

On Mon, Feb 20, 2017 at 12:57 PM, Tim Nevels via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> The Error Stack caught my eye. To me it indicates 4D Server, for whatever
> reason, did not respond in an expected way. Maybe it was not quick enough.
> Maybe 4D Server and the SQL Server was a bit busy at that exact point in
> time and thus returned error 1009 which then cascaded to error 1002.
>

-- 
Kirk Brooks
San Francisco, CA
===
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Vexing, un clear SQL error

[Kirk Brooks via 4D_Tech]

Hi Tim,
Yeah, I was wondering about that error code too. It hadn't occurred to me
the problem could be the transit lag between the client the server but that
would account for what I'm seeing. Interesting idea about the wrapper.
Maybe I'll make a specific error handling method for these sorts of
functions...

BTW this is v15.4. The clients seems to be most windows machines.

Thanks for the insight.

On Mon, Feb 20, 2017 at 12:57 PM, Tim Nevels via 4D_Tech <
4d_tech@lists.4d.com> wrote:

>
> The Error Stack caught my eye. To me it indicates 4D Server, for whatever
> reason, did not respond in an expected way. Maybe it was not quick enough.
> Maybe 4D Server and the SQL Server was a bit busy at that exact point in
> time and thus returned error 1009 which then cascaded to error 1002.
>
> You said this code is called many hundreds of times each day, yet
> occasionally if fails. So the program code is good and passing it bad data
> in $partner_id should not cause an error like this.
>
> I would suggest you write a wrapper method for this code and check for the
> 1002 or 1009 error code returned. If you get that, just wait a bit and try
> again. Maybe try 3 times before you give up.
>
> Maybe do a PAUSE PROCESS(Current process;10) to wait 1/10 of a second,
> then run the code again and if there is still an error, repeat. Keep an
> error count so when you reach your limit of 3 attempts, then you record the
> error to your error logging and to the user.
>
> I would also record a special info type message every time you run into
> this situation where you have to try more than once and record success or
> failure and the number of attempts. Then you’ll be able to track how often
> this fix/hack is working and being successful. You might find you need to
> try 5 times before it is always successful.
>
> I had to implement a similar fix/hack for a situation where I was checking
> if a shared folder was mounted using the “Test path name” command.
> Sometimes it would report the share was not available, but it really was.
> If you did the same operation a second time it worked. By simply checking
> several times in a row if the first Test path name failed solved the
> problem and eliminated the user seeing any error message.
>
> For anyone interested, this error can occur when you are using macOS 10.7
> to share a directory with a Windows user via SMB. Apple threw out SAMBA as
> their Windows SMB code base and wrote their own from scratch with 10.7.
> Whatever they did caused this issue for me. Might have been fixed in later
> macOS versions.
>
> Tim
>
> 
> Tim Nevels
> Innovative Solutions
> 785-749-3444
> timnev...@mac.com
> 
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **




-- 
Kirk Brooks
San Francisco, CA
===
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Vexing, un clear SQL error

[Kirk Brooks via 4D_Tech]

**Apologies for double posting - just noticed I neglected to add a subject.

I need some help with this from some of you SQL gurus.

Here's a simple look up function (Partner_get_name) that uses SQL:

If ($partner_id>0)

If ([PARTNERS]ID#$partner_id)

Begin SQL

SELECT

Name_dba,

Name_display,

Name1_first,

Name1_middle,

Name1_last,

Name2_first,

Name2_middle,

Name2_last,

Type_Entity,

__ROW_ID


FROM PARTNERS

WHERE PARTNERS.ID  = :$partner_id


INTO

:$dba,

:$display,

:$n1_first,

:$n1_middle,

:$n1_last,

:$n2_first,

:$n2_middle,

:$n2_last,

:$entity,

:$recNum ;

End SQL . <= Line 51

​End if

End if​

​I've trimmed out the declarations and such for clarity and because I don't
think they are the issue. This is a method that gets called a lot. I dare
say several hundred times a day, probably more. But I keep seeing 1 or 2
errors. Here's a sample error report:

​Partner_get_name :
[db engine] Invalid table number requested by a Plug-In
  Error #   -1
  Line  #   51
  Process:11 [ORDER_EDIT_RECORD]_ORDERS_124045
  Environment:[RM C]
  Session:
{"id":32044,"userName":"***","4dUser":"***","dbUser":-26,"
userId":4048,"machine":"***","machineName":"***_sched","partnerId":130646,"
boundPartnerId":101,"boundVendorId":101,"salesRepId":4177,"registerId":
"***_32044"}
  Error Stack:
1002 : SQCO; Failed to connect to the remote server.
1009 : SQLS; Remote SQL Server is not available.


There's nothing in the info from 4D that helps me. The table is clearly
valid. But I'm not entirely clear what the SQL errors are trying to tell
me. I've attempted to recreate the error by feeding the method various
levels of junk but I can't get it to barf.

Any ideas?

-- 
Kirk Brooks
San Francisco, CA
===
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**