DBC Size - Remote View

[davidpmiller]

Hi,

I have a DBC which access a remote SQL Server 2000 table at regular
intervals. Each time the connection is queried and a new record is present in
the remote table I get another entry in the DBC (which is deleted).

I get a new ObjectID, ParentId, ObjectType etc. - therefore the size of my
DBC becomes very large (over 1Gig at present). Packing cures the problem,
however is there a way to prevent these entries from being added?

Any help would be appreciated.

Regards
David Miller

[AA]

So what are you doing to "query the connection"? Do you have an example of
the code that is run?

There are two ways to access a table in a remote database through ODBC, a
remote view or an SPT (SQL Pass-Through) call.
A remote view and the connection type it uses are created and stored in a
dbc-file, but only once. The remote view is then opened the same way a VFP
table is opened:
OPEN DATABASE x
USE myview [AGAIN [IN 0 [ALIAS whatever]]]

An SPT call is programmatic and no DBC file is needed:
h=SQLCONNECT('dsn')
or h= SQLSTRINGCONNECT(<connection string>)
SQLEXEC(h, [SELECT * FROM SQLTable], [mycursor] )
-Anders

Post by davidpmiller
Hi,
I have a DBC which access a remote SQL Server 2000 table at regular
intervals. Each time the connection is queried and a new record is present in
the remote table I get another entry in the DBC (which is deleted).
I get a new ObjectID, ParentId, ObjectType etc. - therefore the size of my
DBC becomes very large (over 1Gig at present). Packing cures the problem,
however is there a way to prevent these entries from being added?
Any help would be appreciated.
Regards
David Miller

[davidpmiller]

Anders,

The code I am using is as follows:

*-------------------------------------------
* Set up the require connections string
*-------------------------------------------
Create Connection cnEtm3 Connstring "&pcDSNEtm3"
Create Sql View vwRemote Remote Connection cnEtm3 As Select
Trademark.ApplicationNo From dbo.Trademark Trademark

*------------------------
* Re-Read external tables
*------------------------
Requery("vwRemote")

*--------------------------------------
* Find all the unprocessed applications
*--------------------------------------
Select * From vwRemote Where ApplicationNo Not In ;
(Select Substr(WebReference,4,13) As ApplicationNo From Apps) ;
INTO Cursor curNewApplications

The connection string is created each time as it is different for each test
environment (therefore this is read from an INI file).

Any further help would be appreciated.

Regards
Dave

Post by AA
So what are you doing to "query the connection"? Do you have an example of
the code that is run?
There are two ways to access a table in a remote database through ODBC, a
remote view or an SPT (SQL Pass-Through) call.
A remote view and the connection type it uses are created and stored in a
dbc-file, but only once. The remote view is then opened the same way a VFP
OPEN DATABASE x
USE myview [AGAIN [IN 0 [ALIAS whatever]]]
h=SQLCONNECT('dsn')
or h= SQLSTRINGCONNECT(<connection string>)
SQLEXEC(h, [SELECT * FROM SQLTable], [mycursor] )
-Anders

Post by davidpmiller
Hi,
I have a DBC which access a remote SQL Server 2000 table at regular
intervals. Each time the connection is queried and a new record is present in
the remote table I get another entry in the DBC (which is deleted).
I get a new ObjectID, ParentId, ObjectType etc. - therefore the size of my
DBC becomes very large (over 1Gig at present). Packing cures the problem,
however is there a way to prevent these entries from being added?
Any help would be appreciated.
Regards
David Miller

[Stefan Wuebbe]

Create Sql View vwRemote Remote Connection cnEtm3 As Select ...

This line re-creates the view. Instead, you can create the view
only once at design-time, and at runtime just open the existing one
Use myDatabase!vwRemote In 0


hth
-Stefan
--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

Anders,
*-------------------------------------------
* Set up the require connections string
*-------------------------------------------
Create Connection cnEtm3 Connstring "&pcDSNEtm3"
Create Sql View vwRemote Remote Connection cnEtm3 As Select
Trademark.ApplicationNo From dbo.Trademark Trademark
*------------------------
* Re-Read external tables
*------------------------
Requery("vwRemote")
*--------------------------------------
* Find all the unprocessed applications
*--------------------------------------
Select * From vwRemote Where ApplicationNo Not In ;
(Select Substr(WebReference,4,13) As ApplicationNo From Apps) ;
INTO Cursor curNewApplications
The connection string is created each time as it is different for each test
environment (therefore this is read from an INI file).
Any further help would be appreciated.
Regards
Dave

Post by AA
So what are you doing to "query the connection"? Do you have an example of
the code that is run?
There are two ways to access a table in a remote database through ODBC, a
remote view or an SPT (SQL Pass-Through) call.
A remote view and the connection type it uses are created and stored in a
dbc-file, but only once. The remote view is then opened the same way a VFP
OPEN DATABASE x
USE myview [AGAIN [IN 0 [ALIAS whatever]]]
h=SQLCONNECT('dsn')
or h= SQLSTRINGCONNECT(<connection string>)
SQLEXEC(h, [SELECT * FROM SQLTable], [mycursor] )
-Anders

Post by davidpmiller
Hi,
I have a DBC which access a remote SQL Server 2000 table at regular
intervals. Each time the connection is queried and a new record is present in
the remote table I get another entry in the DBC (which is deleted).
I get a new ObjectID, ParentId, ObjectType etc. - therefore the size of my
DBC becomes very large (over 1Gig at present). Packing cures the problem,
however is there a way to prevent these entries from being added?
Any help would be appreciated.
Regards
David Miller

[AA]

Hi David

Both CREATE CONNECTION and CREATE VIEW create new rows in your database. If
you combine this with DROP VIEW and DELETE CONNECTION and PACK DATABASE you
can keep the size from growing.
An easier way may be to create a temporary database each time and DELETE
DATABASE x when you're done.
It's not necessary to store the connection properties in the database. You
can use, and reuse, any valid connection to the database with a remote view,
whatever connection you used when you created the remote view:

handle123= SQLSTRINGCONNECT("driver=
xxx,;server=yyy;database=zzz,trusted_connection=yes")
OPEN DATABASE myviews
USE myremoteview1 CONNSTRING handle123
You can also create your query programmtically ad hoc

lcTable='data123'
lcTable2 ='Apps'
TEXT TO lcSQL NOSHOW TEXTMERGE
Select * From <<lcTable>> Where ApplicationNo Not In
(Select Substr(WebReference,4,13) As ApplicationNo From <<lcTable2>>)
ENDTEXT

SQLEXEC(handle123, lcSQL, 'mycursor' )

-Anders

Post by davidpmiller
Anders,
*-------------------------------------------
* Set up the require connections string
*-------------------------------------------
Create Connection cnEtm3 Connstring "&pcDSNEtm3"
Create Sql View vwRemote Remote Connection cnEtm3 As Select
Trademark.ApplicationNo From dbo.Trademark Trademark
*------------------------
* Re-Read external tables
*------------------------
Requery("vwRemote")
*--------------------------------------
* Find all the unprocessed applications
*--------------------------------------
Select * From vwRemote Where ApplicationNo Not In ;
(Select Substr(WebReference,4,13) As ApplicationNo From Apps) ;
INTO Cursor curNewApplications
The connection string is created each time as it is different for each test
environment (therefore this is read from an INI file).
Any further help would be appreciated.
Regards
Dave

Post by AA
So what are you doing to "query the connection"? Do you have an example of
the code that is run?
There are two ways to access a table in a remote database through ODBC, a
remote view or an SPT (SQL Pass-Through) call.
A remote view and the connection type it uses are created and stored in a
dbc-file, but only once. The remote view is then opened the same way a VFP
OPEN DATABASE x
USE myview [AGAIN [IN 0 [ALIAS whatever]]]
h=SQLCONNECT('dsn')
or h= SQLSTRINGCONNECT(<connection string>)
SQLEXEC(h, [SELECT * FROM SQLTable], [mycursor] )
-Anders

Post by davidpmiller
Hi,
I have a DBC which access a remote SQL Server 2000 table at regular
intervals. Each time the connection is queried and a new record is
present
in
the remote table I get another entry in the DBC (which is deleted).
I get a new ObjectID, ParentId, ObjectType etc. - therefore the size of my
DBC becomes very large (over 1Gig at present). Packing cures the problem,
however is there a way to prevent these entries from being added?
Any help would be appreciated.
Regards
David Miller