Issue the following line somewhere in the beginning of your program

(before connecting to SQL Server):

SQLSetProp(0,"Shared",.T.)

This will force VFP to force first connection that will be made to be share=

d.

On Fri, May 1, 2009 at 3:02 AM, Rafael Copquin <rcopquin@ciudad.com.ar> wro=

te:

> I am rapidly updating my knowledge of VFP to start using SQL Server as a

> back end (a long overdue issue in my programmer life)

>

> I am using among other sources the book:

>

> Client-Server Applications with Visual FoxPro 6.0 and SQL Server 7.0 by

> Chuck Urwiler and others.

>

> In reference to transactions, the authors recommend to use shared

> connections.

>

> I am building a one to many form, basically an invoice header and its

> corresponding details. Both cursors can be generated using views and a

> connection can be shared with the following command:

>

> create sql view v_customers ;

> connection myconn share as..........

>

> My question is:

>

> How can I share a connection if instead of views I use cursor adapters? I

> tried this approach:

>

> start the first CA

> once the cursor is obtained, get its connection handle with cursorgetprop

> try using this handle in the datasource property of the second CA

>

> thisform.oCA.DataSource =A0 =3D

> sqlstringconnect([dsn=3Dtest;trusted_connection=3Dyes]) =A0 && for the fi=

rst CA

> nHandle =3D cursorgetprop('connecthandle', thisform.oCA.Alias)

> thisform.oCA1.DataSource =3D sqlstringconnect(nHandle) =A0 =A0 && in the =

second CA

>

> but somehow, it did not work

>

> BTW, CAs can be used in the above scenario, almost the same as with DBF's=

.

> One can issue the requery command after a tableupdate (with buffering set=

to

> 5), and they work very well. However, I can't get them to share a

> connection.

>

> TIA

>

> Rafael Copquin

>

>

>

[excessive quoting removed by server]

I am rapidly updating my knowledge of VFP to start using SQL Server as a

back end (a long overdue issue in my programmer life)

I am using among other sources the book:

Client-Server Applications with Visual FoxPro 6.0 and SQL Server 7.0 by

Chuck Urwiler and others.

In reference to transactions, the authors recommend to use shared

connections.

I am building a one to many form, basically an invoice header and its

corresponding details. Both cursors can be generated using views and a

connection can be shared with the following command:

create sql view v_customers ;

connection myconn share as..........

My question is:

How can I share a connection if instead of views I use cursor adapters? I

tried this approach:

start the first CA

once the cursor is obtained, get its connection handle with cursorgetprop

try using this handle in the datasource property of the second CA

thisform.oCA.DataSource =

sqlstringconnect([dsn=test;trusted_connection=yes]) && for the first CA

nHandle = cursorgetprop('connecthandle', thisform.oCA.Alias)

thisform.oCA1.DataSource = sqlstringconnect(nHandle) && in the second CA

but somehow, it did not work

BTW, CAs can be used in the above scenario, almost the same as with DBF's.

One can issue the requery command after a tableupdate (with buffering set to

5), and they work very well. However, I can't get them to share a

connection.

TIA

Rafael Copquin

Another issue I have with SQLExpress is this:

A SQLSE table has a price field with a numeric(10,2) type.

When it comes to VFP through an SPT call it comes with the correct structure

(apparently)

But after transforming it with the cursortoxml function to produce a cXML

variable, the opposite function, xmltocursor fails in particularly that

field: the error message is that it is getting the incorrect data type

If I browse the cursor, it shows 23,50 ( not 23.50 as should be)

I solved it with the cast function in a select statement:

select cast(price as N(10,2)) as price from mycursor into ......

However, the price is 23.50 but I only get 23.00. No matter what I do, the

cents are discarded.

?????

Rafael Copquin

On Thu, Apr 30, 2009 at 7:24 PM, Rafael Copquin <rcopquin@ciudad.com.ar> wr=

ote:

> Another issue I have with SQLExpress is this:

>

> A SQLSE table has a =A0price field with a numeric(10,2) type.

>

> When it comes to VFP through an SPT call it comes with the correct struct=

ure

> (apparently)

> But after transforming it with the cursortoxml function to produce a cXML

> variable, the opposite function, xmltocursor fails in particularly that

> field: the error message is that it is getting the incorrect data type

>

> If I browse the cursor, it shows =A023,50 ( not 23.50 as should be)

-----------------------------------

What are the language settings on that workstation? that may be why

you see the comma instead of the decimal.

> I solved it with the cast function in a select statement:

>

> select cast(price as N(10,2)) as price from mycursor into ......

>

> However, the price is 23.50 but I only get 23.00. No matter what I do, th=

e

> cents are discarded.

> ?????

-----------

Where are you doing the cast? In VFP or back on the server?

--=20

Stephen Russell

Sr. Production Systems Programmer

Web and Windows Development

Independent Contractor

Memphis TN

901.246-0159

Thank you Grigore

But I get the message: 'the property is readonly'

What am I doing wrong?

Rafael

----- Original Message -----

From: "Grigore Dolghin" <gdolghin@gmail.com>

To: <profoxtech@leafe.com>

Sent: Thursday, April 30, 2009 7:32 PM

Subject: Re: vfp9 and sql server express

Issue the following line somewhere in the beginning of your program

(before connecting to SQL Server):

SQLSetProp(0,"Shared",.T.)

This will force VFP to force first connection that will be made to be

shared.

I am sorry, my mistake. Guess that's what happens when one talks from

(bad) memory.

You are creating the connection using SQLSTRINGCONNECT() or

SQLCONNECT(). Both commands have a lSharable parameter which, if is

.T., allows multiple CAs to share the same connection.

In order to achieve this you should create the connection and store

the handle in a public variable or (better) a property of a

permanently visible object, such as _Screen.

_Screen.AddProperty("ConnectionHandle")

_Screen.ConnectionHandle = SQLCONNECT("myDNSName",.T.)

or

_Screen.AddProperty("ConnectionHandle")

_Screen.ConnectionHandle = SQLSTRINGCONNECT("ConnectionStringHere",.T.)

To set the cursoradapter classes to use the previously created

connection, open them in class designer, right click, builder, and

check "Use existing connection handle" and enter

_Screen.ConnectionHandle in that textbox.

On Fri, May 1, 2009 at 4:51 PM, Rafael Copquin <rcopquin@ciudad.com.ar> wrote:

> Thank you Grigore

> But I get the message: 'the property is readonly'

> What am I doing wrong?

> Rafael

>

>

> ----- Original Message -----

> From: "Grigore Dolghin" <gdolghin@gmail.com>

> To: <profoxtech@leafe.com>

> Sent: Thursday, April 30, 2009 7:32 PM

> Subject: Re: vfp9 and sql server express

>

>

> Issue the following line somewhere in the beginning of your program

> (before connecting to SQL Server):

>

> SQLSetProp(0,"Shared",.T.)

>

> This will force VFP to force first connection that will be made to be

> shared.

>

>

>

[excessive quoting removed by server]

That is a very smart idea. I shall try it this weekend and let you know if

there are any problems.

Have a nice weekend

Rafael Copquin

----- Original Message -----

From: "Grigore Dolghin" <gdolghin@gmail.com>

To: <profoxtech@leafe.com>

Sent: Friday, May 01, 2009 6:16 PM

Subject: Re: vfp9 and sql server express

>I am sorry, my mistake. Guess that's what happens when one talks from

> (bad) memory.

>

> You are creating the connection using SQLSTRINGCONNECT() or

> SQLCONNECT(). Both commands have a lSharable parameter which, if is

> .T., allows multiple CAs to share the same connection.

> In order to achieve this you should create the connection and store

> the handle in a public variable or (better) a property of a

> permanently visible object, such as _Screen.

>

> _Screen.AddProperty("ConnectionHandle")

> _Screen.ConnectionHandle = SQLCONNECT("myDNSName",.T.)

>

> or

>

> _Screen.AddProperty("ConnectionHandle")

> _Screen.ConnectionHandle = SQLSTRINGCONNECT("ConnectionStringHere",.T.)

>

> To set the cursoradapter classes to use the previously created

> connection, open them in class designer, right click, builder, and

> check "Use existing connection handle" and enter

> _Screen.ConnectionHandle in that textbox.

>

> On Fri, May 1, 2009 at 4:51 PM, Rafael Copquin <rcopquin@ciudad.com.ar>

> wrote:

>> Thank you Grigore

>> But I get the message: 'the property is readonly'

>> What am I doing wrong?

>> Rafael

>>

>>

>> ----- Original Message -----

>> From: "Grigore Dolghin" <gdolghin@gmail.com>

>> To: <profoxtech@leafe.com>

>> Sent: Thursday, April 30, 2009 7:32 PM

>> Subject: Re: vfp9 and sql server express

>>

>>

>> Issue the following line somewhere in the beginning of your program

>> (before connecting to SQL Server):

>>

>> SQLSetProp(0,"Shared",.T.)

>>

>> This will force VFP to force first connection that will be made to be

>> shared.

>>

>>

>>

[excessive quoting removed by server]

Hello Grigore

Only today I could find the time to sit and try your below suggestion. I

made it work as follows:

I created a shared connection using the dataenvironment builder. Then, I

created three one table CA's using the CA builder and checked "Use existing

connection handle" for every one of them..

By running the form, I could see that the connection was in effect shared. I

guess your suggestion of using a public variable or the _screen object would

work in the same manner.

Now, I tried to create the CA's by code, ie, took them out of the DE and

built them in methods of the form, in this manner:

quote

Local cUpdateNameList,cCursorSchema,cSelectCmd,cUpdatableFieldList

text to cSelectCmd noshow pretext 15

select clienteid, codigo, dt, fechaing, razon from clientes

endtext

text to cUpdateNameList noshow pretext 15

clienteid clientes.clienteid, codigo clientes.codigo, dt clientes.dt,

fechaing clientes.fechaing, razon clientes.razon

endtext

text to cCursorSchema noshow pretext 15

clienteid i, codigo c(4), dt t, fechaing t, razon c(50)

endtext

text to cUpdatableFieldList noshow pretext 15

clienteid, codigo, dt, fechaing, razon

endtext

cSelectCmd = Chrtran( cSelectCmd, Chr(13) + Chr(10), " " )

cUpdateNameList = Chrtran( cUpdateNameList, Chr(13) + Chr(10), " " )

cCursorSchema = Chrtran( cCursorSchema, Chr(13) + Chr(10), " " )

cUpdatableFieldList = Chrtran( cUpdatableFieldList, Chr(13) + Chr(10), " " )

Use in Select('curClientes')

If not PemStatus(thisform,'oCA',5)

thisform.AddProperty('oCA')

endif

Set Multilocks on

Try

lOK = .t.

thisform.oCA = Createobject("CursorAdapter")

With thisform.oCA

.Alias = "curClientes"

.Tables = "clientes"

.useDEDataSource = .t.

.SelectCmd = cSelectCmd

.BufferModeOverride = 5

.sendupdates = .t.

.KeyFieldList = "clienteid"

.usecursorschema = .t.

.CursorSchema = cCursorSchema

.UpdateNameList = cUpdateNameList

.UpdatableFieldList = cUpdatableFieldList

.cursorfill()

.cursordetach()

Endwith

Select curClientes

Catch To oException

lOK = .f.

Messagebox("Cursor curClientes could not be created "+Chr(13)+;

"Error message: " + oException.Message)

Finally

Endtry

Return lOK

unquote

I usually create very complex CA's using the above technique, especially

when more than one table is involved in the select command, because the CA

builder will not handle this type of CA. The use of text endtext is meant

for clarity in case the statements are very long (not the case here)

I used, in the above example, the properties as were generated by the

builder, just looking at each CA property window.

The DE contains the following code in its beforeopentables method (as

generated by the DE builder):

*** Select connection code: DO NOT REMOVE

***<DataSource>

This.DataSource =

sqlstringconnect([dsn=provemet;trusted_connection=yes],.t.)

***</DataSource>

*** End of Select connection code: DO NOT REMOVE

But I cannot make the manual creation methods generate the CA's

I realize this is bothering you a lot and apologize for the inconvenience,

but you being a MVP I thought that perhaps you can really give me the

correct reply.

My final objective is to generate CA's with a unique connection handle, so

as to use the SQL Server transactions appropriately. I read in the book by

Chuck Urwiler and others:

Client-Server Applications with Visual FoxPro 6.0 and SQL Server 7.0

that VFP transactions do not work with SQLServer, so one has to use the

SQLServer transactions manually and then issue the appropriate SQLCOMMIT or

SQLROLLBACK statements. And for that, I need to have one shared connection,

right?

Thank you and regards

Rafael Copquin

----- Original Message -----

From: "Grigore Dolghin" <gdolghin@gmail.com>

To: <profoxtech@leafe.com>

Sent: Friday, May 01, 2009 6:16 PM

Subject: Re: vfp9 and sql server express

>I am sorry, my mistake. Guess that's what happens when one talks from

> (bad) memory.

>

> You are creating the connection using SQLSTRINGCONNECT() or

> SQLCONNECT(). Both commands have a lSharable parameter which, if is

> .T., allows multiple CAs to share the same connection.

> In order to achieve this you should create the connection and store

> the handle in a public variable or (better) a property of a

> permanently visible object, such as _Screen.

>

> _Screen.AddProperty("ConnectionHandle")

> _Screen.ConnectionHandle = SQLCONNECT("myDNSName",.T.)

>

> or

>

> _Screen.AddProperty("ConnectionHandle")

> _Screen.ConnectionHandle = SQLSTRINGCONNECT("ConnectionStringHere",.T.)

>

> To set the cursoradapter classes to use the previously created

> connection, open them in class designer, right click, builder, and

> check "Use existing connection handle" and enter

> _Screen.ConnectionHandle in that textbox.

>

> On Fri, May 1, 2009 at 4:51 PM, Rafael Copquin <rcopquin@ciudad.com.ar>

> wrote:

>> Thank you Grigore

>> But I get the message: 'the property is readonly'

>> What am I doing wrong?

>> Rafael

>>

>>

>> ----- Original Message -----

>> From: "Grigore Dolghin" <gdolghin@gmail.com>

>> To: <profoxtech@leafe.com>

>> Sent: Thursday, April 30, 2009 7:32 PM

>> Subject: Re: vfp9 and sql server express

>>

>>

>> Issue the following line somewhere in the beginning of your program

>> (before connecting to SQL Server):

>>

>> SQLSetProp(0,"Shared",.T.)

>>

>> This will force VFP to force first connection that will be made to be

>> shared.

>>

>>

>>

[excessive quoting removed by server]

After many tries, I finally succeeded to share a connection for cursor

adapters.

In the load event of a form I do this:

thisform.nHandle = SQLConnect([TEST]) && named connection (TEST)

I create several adapter methods in the form, one for each cursor adapter I

want to generate and set all the applicable properties manually (ie,

updatenamelist, updatablefieldlist, keyfieldlist, select command)

The following properties, for each one of the cursor adapters thus created,

should read like this:

thisform.oCA .DataSourceType = "ODBC"

thisform.oCA .datasource = thisform.nHandle

thisform.oCA1 .DataSourceType = "ODBC"

thisform.oCA1 .datasource = thisform.nHandle

( a different object for each CA, ie oCA1,oCA2, etc.)

For each one of the cursor adapters I capture the connection handle, just to

verify that they all use the same connection and put them in textboxes on

the form:

thisform.text1.Value = CursorGetProp("ConnectHandle" ,'curClientes')

thisform.text2.Value = CursorGetProp("ConnectHandle" ,'curArticulos')

etc

That's all there is to it !

When the form instantiates, I can see in the textboxes that both CA's share

the same connection.

By the way, if I issue the following statement, I get a VFP message saying

that the property is readonly

SQLSetProp(0,"Shared",.T.)

(why would a property supposedly to be set by SQLSetProp be readonly? If it

cannot be set, why is it a property of a setting function? I can't

understand that.)

Now that I use one single connection for all CA's I can use transactions on

the server, like this:

** form SAVE method

#DEFINE DB_TRANSAUTO 1 && from foxpro.h

#DEFINE DB_TRANSMANUAL 2

Local lOK

SQLSetProp(thisform.nHandle, "TRANSACTIONS", DB_TRANSMANUAL)

lOK = .f.

If TableUpdate(.t.,.f.,'curClientes')

If TableUpdate(.t.,.f.,'curArticulos')

lOK = .t.

EndIf

EndIf

If lOK = .t.

SQLCommit(thisform.nHandle)

Else

SQLRollback(thisform.nHandle)

endif

SQLSetProp(thisform.nHandle, "TRANSACTIONS", DB_TRANSAUTO)

Rafael Copquin

----- Original Message -----

From: "Grigore Dolghin" <gdolghin@gmail.com>

To: <profoxtech@leafe.com>

Sent: Thursday, April 30, 2009 7:32 PM

Subject: Re: vfp9 and sql server express

Issue the following line somewhere in the beginning of your program

(before connecting to SQL Server):

SQLSetProp(0,"Shared",.T.)

This will force VFP to force first connection that will be made to be

shared.

On Tue, May 12, 2009 at 3:03 PM, Rafael Copquin <rcopquin@ciudad.com.ar> wrote:

> By the way, if I issue the following statement, I get a VFP message saying

> that the property is readonly

>

> SQLSetProp(0,"Shared",.T.)

>

> (why would a property supposedly to be set by SQLSetProp be readonly? If it

> cannot be set, why is it a property of a setting function? I can't

> understand that.)

------------------------------------------------------

That property is a token given out FROM the Sql Server, so you cannot edit it.

Does that make sense? Otherwise you could change your credentials and

suddenly have access to the HR data.

--

Stephen Russell

Sr. Production Systems Programmer

Web and Windows Development

Independent Contractor

Memphis TN

901.246-0159