Visual FoxPro SQL Queries - sharable connections in vfp9

Author

sharable connections in vfp9

lamlinks

2006-02-14, 8:26 pm

A vfp9 database accessing SQL via ODBC with sharable remote view 'A'
will share
the connection (IE have the same connection handle) only when view 'A'
is
opened multiple times.
View 'B' in the same database, with the same connection name that is
also
sharable will create a new connection handle when used. A second use of
'B'
will share the connection originally used by 'B". But 'A' and 'B do not
share
a connection.
This is easily demonstrated
? CURSORGETPROP('Share
')
? CURSORGETPROP('Conne
ctHandle')
Sharable connections are required to use transaction management
functions commit or rollback, so this is a problem.

Cindy Winegarden

2006-02-15, 3:25 am

Hi Alan,

You haven't really asked a question. Are you wanting to know how to share a
connection so your transactions will work?

Have you considered making one connection, and storing it in a form property
or something else to make it global to the form. Then use SQL Pass-through
for each "view" using that same connection. Make the SQL Pass-through
cursors updatable with CursorSetProp(), etc.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"lamlinks" <alan.kliner@gmail.com> wrote in message
news:1139966440.789175.245500@g44g2000cwa.googlegroups.com...
>A vfp9 database accessing SQL via ODBC with sharable remote view 'A' will
>share the connection (IE have the same connection handle) only when view
>'A' is opened multiple times. View 'B' in the same database, with the same
>connection name that is also sharable will create a new connection handle
>when used. A second use of 'B' will share the connection originally used by
>'B". But 'A' and 'B do not
share a connection.

This is easily demonstrated
? CURSORGETPROP('Share
')
? CURSORGETPROP('Conne
ctHandle')
Sharable connections are required to use transaction management functions
commit or rollback, so this is a problem.

lamlinks

2006-02-15, 11:25 am

Hi Cindy,

Thanks for your suggestion of using SQL Pass-through.
This would be fine for a new app.
But the app in question was developed with VFP6 over a period of 6
years. There are hundreds of dbc-based remote views, so it would
require a significant re-write.
It is possible to write a single module to translate the 'views' to SQL
pass-through and cursorsetprop commands, as we have done that to tweak
parameters on 'common' views.
But we would still have to change hundreds of "use 'view' .." to
function calls.

I am wondering whether the change between vfp6 and vfp9 is by design or
an oversight that may be corrrected at some time. Or whether there is
some new setting or feature of vfp9 that we are not using properly.

Alan

Cindy Winegarden wrote:
> Hi Alan,
>
> You haven't really asked a question. Are you wanting to know how to share a
> connection so your transactions will work?
>
> Have you considered making one connection, and storing it in a form property
> or something else to make it global to the form. Then use SQL Pass-through
> for each "view" using that same connection. Make the SQL Pass-through
> cursors updatable with CursorSetProp(), etc.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
>
>
> "lamlinks" <alan.kliner@gmail.com> wrote in message
> news:1139966440.789175.245500@g44g2000cwa.googlegroups.com...
> share a connection.
>
> This is easily demonstrated
> ? CURSORGETPROP('Share
')
> ? CURSORGETPROP('Conne
ctHandle')
> Sharable connections are required to use transaction management functions
> commit or rollback, so this is a problem.

Christian Ehlscheid

2006-02-15, 1:24 pm

Hello,

have a look @
http://www.leafe.com/archives/showF...95&cList=profox

there is still just one connection,
just the handles that CURSORGETPROP returns are different.

Regards
Christian

lamlinks

2006-02-16, 11:24 am

Hi Christian,

Thanks for the link.
It provided a wealth of information on the changes that occured
beginning with VFP8 and developer reactions to the changes.

Our main concern is access to SQL commit and rollback functionality
with VFP.
The syntax of the SQLCOMMIT Function has been changed between vfp6 and
9 (we never used 8):
SQLCOMMIT(nConnectio
nHandle) && vfp6
SQLCOMMIT(nStatement
Handle) && vfp9
I don't think that it's a good idea to change the parameters of a
language function.
Adding optional parameters that don't impact existing code is OK.
But if MS wanted to improve vfp's support for SQL (by adding statement
handles) they should have also added new function calls for commit and
rollback.

Alan

Christian Ehlscheid

2006-02-17, 7:27 am

Hi,

> Our main concern is access to SQL commit and rollback functionality
> with VFP.
> The syntax of the SQLCOMMIT Function has been changed between vfp6 and
> 9 (we never used 8):
> SQLCOMMIT(nConnectio
nHandle) && vfp6
> SQLCOMMIT(nStatement
Handle) && vfp9
> I don't think that it's a good idea to change the parameters of a
> language function.
> Adding optional parameters that don't impact existing code is OK.
> But if MS wanted to improve vfp's support for SQL (by adding statement
> handles) they should have also added new function calls for commit and
> rollback.

they just changed the internal implementation & help files,
but the behaviour is still the same!

e.g.

&& use some views which share a connection
USE view1 IN 0
USE view2 IN 0

lnStmtHandle1 = CURSORGETPROP("ConnectHandle","view1")
lnStmtHandle2 = CURSORGETPROP("ConnectHandle","view2")

? lnStmtHandle1, lnStmtHandle2 && these are different

lnODBCConn1 = SQLGETPROP(lnStmtHan
dle1,"ODBChdbc")
lnODBCConn2 = SQLGETPROP(lnStmtHan
dle2,"ODBChdbc")

? lnODBCConn1, lnODBCConn2 && but both point to the same connection

in the end it doesn't matter which statement handle you use for
SQLCOMMIT/SQLROLLBACK,
either one will commit/rollback all SQL statement that were sent over
their shared connection.

Regards
Christian

Sponsored Links