Wiki Home

Remote View FAQ


Namespace: WIN_COM_API
Q: I get a "connection busy" error..

A: A connection busy error occurs when you use Progressive Fetching. Basically, when you set FetchSize to something other than ALL (-1 in the set prop function). If you are using progressive fetching you can determine if the connection is busy, and if it is, cancel the activity, to do your next operation. Try something like this:
IF SQLGetProp(nConn, 'ConnectionBusy')
   SQLCancel(nConn)
ENDIF


Q: What do you do when it is a remote view and you don't have a connection handle nConn to check?

I use a similar test, only instead of cancelling, I loop. Either a specified number of times, or for a specified number of seconds. I have seen certain remote views that will ALWAYS cause this problem whenever they are USEd, and, if given enough time, they will complete successfully. The time factor can exceed one minute in some cases. -- Ray Kirk

Check MSDN Q191343 Offsite link to http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q191343 there is the "How To not" and also the "How To" produce it. I just copy/paste it here

Steps to Reproduce Behavior ...

Use the following code snippet to create a database container and two tables that will serve as a data source:

CREATE DATABASE TESTODBC
CREATE TABLE mytable_a ( ;
FLD_A I NULL, FLD_C C(15) NULL)
CREATE TABLE mytable_b ( ;
FLD_B I NULL,FLD_A I NULL,FLD_C C(15) NULL)
INSERT INTO mytable_a (FLD_A,FLD_C) VALUES (10452,'TEST1')
INSERT INTO mytable_b (FLD_B,FLD_A,FLD_C) VALUES (1,22345,'TEST1')
CLOSE ALL


Open the 32-bit ODBC Data Source Administrator and create an ODBC datasource called "CONNECT" with a path to the Testodbc.dbc, created in step 1.


Create a .prg file named Odbcdemo.prg, using the following code:
      * Begin Code
      CLOSE ALL
      SET SAFETY OFF
      CREATE DATABASE odbcdemo
      CREATE CONNECTION CONN1 DATASOURCE 'CONNECT' USERID 'SA' PASSWORD ''
      CREATE SQL VIEW "MYVIEW1"  ;
         REMOTE CONNECT "CONN1" SHARED ;
         AS SELECT * ;
         FROM mytable_a
      DBSETPROP('MYVIEW1', 'View', 'FETCHSIZE', 1)
      DBSETPROP('MYVIEW1', 'View', 'MAXRECORDS', 1)
      USE MYVIEW1 IN 0
      * The code fails here with a Connection Busy Message.
      CREATE SQL VIEW "MYVIEW2" ;
         REMOTE CONNECT "CONN1" SHARED ;
         AS SELECT * ;
         FROM mytable_b
      DBSETPROP('MYVIEW2', 'View', 'FETCHSIZE', 1)
      DBSETPROP('MYVIEW2', 'View', 'MAXRECORDS', 1)
      CLOSE ALL
      RETURN
      * End Code

Now run that prg .... you'll get the Connection Busy error.

Well it's for VFP but I think you can easily adept it to an SQLServer back end.

Markus Voellmy

Can anyone provide a table to give us guidelines on when to tick certain features (progressive fetching, asyncronous execution, batch updates etc) and when they are best left unticked? Am I the only one who even after using fox for years and reading through mountains of documentaton is still ticking and unticking boxes almost at random to see what seems to work best? can we lay out a few common database/user setups along with the ideal settings for the views?

My database (foxpro dbc until I can get the boss to buy a computer to run sql from) consists of 4-5 tables of a few hundred records each which change very rarely, they hold job profiles. there are then 4-5 tables which change very often as they hold the details of each job that runs, these tables grow by at least couple of hundred records a day (usually all in the first 2 hours of the day). adding records to any of the tables can only happen from one computer which runs the jobs however viewing, editing and using the data for reports happens from 5-10 computers throughout the day. I have indexes set up so that the users generally only retrieve data for jobs that have not completed.

so:
10 users, 5 base tables, 5 running tables, 100 new records per hour, 200 records in each request.
How do I best setup my remote views?


Ken.
---

Category Data

---
When cancelling an asynchronous - fetch on demand query via SQPT, every subsequent sqlexec call results in "Invalid cursor state".

I found out that this is resolved by issuing :
sqlcancel(nHanlde)
SQLFix(nHandle)

FUNCTION SQLFix(nHandle)
#define SQL_CLOSE 0
#define SQL_DROP  1
#define SQL_UNBIND 2
#DEFINE SQL_RESET_PARAMS 3
LOCAL nHstmt
nHstmt = SQLGETPROP(nHandle,"ODBChstmt")
DECLARE SHORT SQLFreeStmt IN odbc32.dll INTEGER, SHORT
SQLFreeStmt(nHstmt,SQL_RESET_PARAMS)
SQLFreeStmt(nHstmt,SQL_CLOSE)
ENDFUNC


ps: SQLFix function was retrieved from www.profox.ro site.

TheoF.
---
( Topic last updated: 2007.07.31 06:43:12 AM )