cancel
Showing results for 
Search instead for 
Did you mean: 

Executing Store Procedure random timeouts

Former Member
0 Kudos

We have some simple stored procedures that run find when run under the Advantage Data Architect, but randomly times out when we call them from our ASP web site using the OLEDB driver.

When we try to call them using the Advantage Java database driver we get and error code 9105 SQL state S0000 error with no descripive message.

Does anyone have any insights into either of these issues?

Dave Anderson

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Dave,

Regarding the first - How long does the SP take in ARC?  Does it ever take over the timeout for OLEDB (30 seconds)?

Maybe try adding in command.CommandTimeout=0

If you already have this, I think IIS also has a timeout.  I *think* this is the setting though it has been a while.  http://technet.microsoft.com/en-us/library/bb632464.aspx

For the JDBC error are you using the latest server/client?

9000 class errors (9000-9999) are internal Advantage errors.  In other words errors that the developers believe that should not be reachable.

Here is the help file for the errorAdvantage Database Server

If I recall there was an issue with using UDFs and earlier versions of ADS (8.1 and early 9.0) that could return this error.  If you are on something newer I would recommend opening a support case with a small re-creation and / or the server dump file (assuming one is created)

Former Member
0 Kudos

Edgar,

Thanks for your response.

When use Advantage Data Architect to execute the stored procedure that is timing out when called by our website (OLEDB), it takes between 5 and 9 ms.

Where would we add CommandTimeout=0?

As for the Java issue, adsjdbc.jar is dated 6/26/2013.

Could this have anything to do with how the Advantage Database server software is configured?

Former Member
0 Kudos

Your stored procedure only takes Milliseconds to finish normally?

How do you know it is timing out?  The default is 30 seconds so seeing it timeout I would have assumed it was a longer running one.  Are you getting the ADS error 7209 back or Run-Time error

'3712' Operation has been cancelled by the user?

It's been ages since I've looked at VB, but I think it should be something like this

cmd = New ADODB.Command

cmd.CommandText = "execute procedure ....."

cmd.CommandType = adCmdText

cmd.CommandTimeout = 0

rs = cmd.Execute

For the JDBC issue, it is difficult to tell by the date, but I think that date indicates likely a 10.x or 11.x driver.  The issue I was thinking of I believe was fixed in 9.x.

You can try the latest 11.1 version (11.10.0.20) as I think that was released in July of this year.  Maybe it will help?  If not a support incident would be a good idea (particularly if you can duplicate it)

Out of curiosity are you using the varying output type stored procedures?  (Not that I have anything to offer as a workaround, but the error is indicating there was a problem retrieving column informaiton and this is the only thing I can think of off the top of my head).

Edgar

Former Member
0 Kudos

Edgar,

We are using variable output type for these stored procedures.

I have looked at the error logs and I see all my Java 9105 errors, but no others.  The web site developer is reporting he is getting a database connection timeout message on random pages.  All pages are executing very similar, or the same queries.  No one else is using the database or web site except the developer.

One question I have regards the UDL we are using.  We are specifying a machine name rather than an IP address.  Will this cause the OLEDB driver to perform a DNS lookup each time a connection is made?

The web developer will add the CommandTimeOut = 0 tomorrow and we will see what happens.

JAVA

I have downloaded and installed the latest Java driver, but still get the same results. I believe that meta data is not coming back through the driver for stored procedures but it is for standard queries.

I should note that we are using FoxPro tables that have been brought into a FoxPro Database, then converted to an Advantage Database catalog.

Thanks again for all your assistance!

Former Member
0 Kudos

A connection timing out and executing a statement are likely two different things.  Would be good to have the developer give us the real ADS error code.

I would recomend using the IP:PORT in order to bypass discovery.  Using just the hostname will have to do a DSN Look up and will perform a discovery process each time.

Something similar to "Provider=Advantage OLE DB Provider; Data Source=\\10.10.10.10:6262\sharename"

Former Member
0 Kudos

Edgar,

I noticed that the following properties are set to false in the Java Driver (11.1.0.20) after I connect to our database:

  • Schemas in procedure calls
  • Catalogs in procedure calls

Is there a way to override these properties?

Former Member
0 Kudos

Edgar,

We are specifying the port, and will change to using the ip address to see if that helps.

I have also talked to the developer and asked for any error codes that he might be getting back.

Thanks.

Former Member
0 Kudos

I don't think you can.  ADS does not use schemas or catalogs.

Former Member
0 Kudos

Ok.

When I execute a query using the Java driver, I do get the column names back.  When I execute that same query as a stored procedure, I get the error.  My guess is that metadata is not coming back from variable output stored procedures, or I have a configuration error.

Former Member
0 Kudos

I suspect the former.  I think variable output SPs are new in 11 so there may be a bug.  A support incident would be best.

Former Member
0 Kudos

hello

i,m oracle Developer

i have problem in  ADS 10

how can read Data from Oracle11G how can connect  it

file data in ADS  = .ADD

thank you

Former Member
0 Kudos

sorry From ADS TO ORACLE 11G

thank you

Former Member
0 Kudos

Edgar,

We have found that if you get an error in the query, you will get a result set that is marked as opened, the column names exist but the EOF marker is missing.  That was causing us to loop forever and time out.