how to set Cursorgetprop("connecthandle",Alias) same connecthandle 1

[Susan Antoni]

on VPF7 i shared connect in remote view with odbc on MySQL
i got Cursorgetprop("connecthandle",Alias) same value


but on VPF9
i shared connect in remote view with odbc on MySQL
i got Cursorgetprop("connecthandle",Alias) difference value

please tell me about this difference why it worked on VPF7
and how to same 'connecthandle' on VPF9

 

[Mike Lewis]

Do you mean that the actual number returned by SQLGETPROP() is different? If so, that's nothing to be concerned about. The connection handle is a simple sequence number. The actual value is immaterial. The important thing is to use that number consistently in other calls, such as to SQLEXEC().

If it different in VFP 9 compared to VFP 7, that's probably because you are creating connections in a different order, or are doing something else differently. If the application is still working as expected, there is no problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I have a current project working with remote views, though I prefer cursoradapter which allows you to share a VFP connection handle.

I see what you see, but when you look into SQLGETProp(handle,"ODBChdbc") is the same value for the differing VFP handles. That's the limiting factor on the server side if a license only allows limited connections. So indeed nothing to worry, as Mike says, as long as this doesn't vary.

You also find out whether the views share a connection looking into the SQL definition of the views. It should include this line:

DBSetProp(ThisView,"View","ShareConnection",.T.)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Besides that, I also only see one connection from Foxpro in SQL Server via executing sp_who2. There only is one (in my case) with Microsoft Visual FoxPro as ProgramName no matter how many ConectHandles views create.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Susan Antoni]

SQL in remote view

SELECT Orderdetail.OrderID, Orderdetail.ItemID, Orderdetail.Quantity,;
  Orderdetail.Unit, Item.ItemName AS itemnm;
 FROM ;
     orderdetail Orderdetail ;
    INNER JOIN item Item ;
   ON  Orderdetail.ItemID = Item.ItemID;
 WHERE  Orderdetail.OrderID = ( ?vOrdNm )

DBSetProp(ThisView,"View","SendUpdates",.F.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
[COLOR=#CC0000]DBSetProp(ThisView,"View","ShareConnection",.T.)[/color]
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","orderdetail,item")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".orderid","Field","DataType","I")
DBSetProp(ThisView+".orderid","Field","UpdateName","orderdetail.OrderID")
DBSetProp(ThisView+".orderid","Field","KeyField",.T.)
DBSetProp(ThisView+".orderid","Field","Updatable",.F.)

DBSetProp(ThisView+".itemid","Field","DataType","I")
DBSetProp(ThisView+".itemid","Field","UpdateName","orderdetail.ItemID")
DBSetProp(ThisView+".itemid","Field","KeyField",.T.)
DBSetProp(ThisView+".itemid","Field","Updatable",.F.)

DBSetProp(ThisView+".quantity","Field","DataType","I")
DBSetProp(ThisView+".quantity","Field","UpdateName","orderdetail.Quantity")
DBSetProp(ThisView+".quantity","Field","KeyField",.F.)
DBSetProp(ThisView+".quantity","Field","Updatable",.T.)

DBSetProp(ThisView+".unit","Field","DataType","I")
DBSetProp(ThisView+".unit","Field","UpdateName","orderdetail.Unit")
DBSetProp(ThisView+".unit","Field","KeyField",.F.)
DBSetProp(ThisView+".unit","Field","Updatable",.T.)

DBSetProp(ThisView+".itemnm","Field","DataType","C(200)")
DBSetProp(ThisView+".itemnm","Field","UpdateName","item.ItemName")
DBSetProp(ThisView+".itemnm","Field","KeyField",.F.)
DBSetProp(ThisView+".itemnm","Field","Updatable",.T.)

If Used('OrdMain')
	=Tablerevert(.t., 'OrdMain')
	=Requery('OrdMain')
Else
	Use Ord!OrdMn in 0 alias OrdMain EXCLUSIVE
ENDIF
=CursorSetProp('Buffering',5,'OrdMain')
MESSAGEBOX(cursorgetprop( 'connecthandle', 'OrdMain'))


If Used('Orddetail')
	=Tablerevert(.t., 'Orddetail')
	=Requery('Orddetail')
Else
	Use Ord!OrdDt in 0 alias Orddetail EXCLUSIVE
ENDIF
=CursorSetProp('Buffering',5,'Orddetail')
MESSAGEBOX(cursorgetprop( 'connecthandle', 'Orddetail'))


If Used('OrderID')
	=Tablerevert(.t., 'OrderID')
	=Requery('OrderID')
Else
	Use Ord!OrdID in 0 alias OrderID EXCLUSIVE
ENDIF
=CursorSetProp('Buffering',5,'OrderID')
MESSAGEBOX(cursorgetprop( 'connecthandle', 'OrderID'))

i set share connect with 3 remote view
on messagebox return difference value or something wrong please tell me

 

[Olaf Doschke]

You haven't looked into [tt]SQLGETProp(handle,"ODBChdbc")[/tt], all these 3 different VFP handles share the same connection to the server, you have three handles, but they all are reusing the first connection made, that's the point, it doesn't matter these three VFP handles differ. They are just logical numbers to a physical handle. In detail, that's also no concrete memory address or anything and just an id for the database server, but the point is you don't use up multiple CALs.

SELECT somecursor
MESSAGEBOX(sqlgetprop(cursorgetprop('connecthandle'),'ODBChdbc'))

Do this and you'll see the same ODBC handle. Change views to DBSetProp(ThisView,"View","ShareConnection",.F.) and you get different values here.

So it's ok, it's not a bug. Yes, VFP is making a logical VFP connection per remote view, but they all use the same CAL - client access license - only the first remote view causes a connection in SQL Server.

The help also hints on this behavior.

The SHARE keyword specifies that Visual FoxPro uses a new statement handle for the shared connection, if available. If a shared connection is not available, Visual FoxPro creates a shared connection when the view opens, which you might be able to share with other views.

So what you see here is new statement handles per view and cursorgetprop('connecthandle') actually just returns this VFP internal statement handle. Or in other words, even the handles you get from SQLConnect or SQLStringConnect are never connection handles, they all are statement handles and the nomenclature is just wrong, including that the cursor property is called connecthandle.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Susan Antoni]

You haven't looked into SQLGETProp(handle,"ODBChdbc"), all these 3 different VFP handles share the same connection to the server, you have three handles, but they all are reusing the first connection made, that's the point, it doesn't matter these three VFP handles differ. They are just logical numbers to a physical handle. In detail, that's also no concrete memory address or anything and just an id for the database server, but the point is you don't use up multiple CALs.
CODE
SELECT somecursor
MESSAGEBOX(sqlgetprop(cursorgetprop('connecthandle'),'ODBChdbc'))
Do this and you'll see the same ODBC handle. Change views to DBSetProp(ThisView,"View","ShareConnection",.F.) and you get different values here.

So it's ok, it's not a bug. Yes, VFP is making a logical VFP connection per remote view, but they all use the same CAL - client access license - only the first remote view causes a connection in SQL Server.

The help also hints on this behavior.
Quote (help topic CREATE SQL VIEW)
The SHARE keyword specifies that Visual FoxPro uses a new statement handle for the shared connection, if available. If a shared connection is not available, Visual FoxPro creates a shared connection when the view opens, which you might be able to share with other views.

So what you see here is new statement handles per view and cursorgetprop('connecthandle') actually just returns this VFP internal statement handle. Or in other words, even the handles you get from SQLConnect or SQLStringConnect are never connection handles, they all are statement handles and the nomenclature is just wrong, including that the cursor property is called connecthandle.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

OK

when i used this with 3 cursors
and

DBSetProp(ThisView,"View","ShareConnection",.F.)

and use

SELECT somecursor
MESSAGEBOX(sqlgetprop(cursorgetprop('connecthandle'),'ODBChdbc'))

i got

95570936
95530816
95532526

after change

DBSetProp(ThisView,"View","ShareConnection",.T.)

i got

95524776
95524776
95524776

Thanks you

 

[Olaf Doschke]

Yes, which shows you do share the connection with DBSetProp(ThisView,"View","ShareConnection",.T.) and you not only get different statement handles, but also different connection handles with DBSetProp(ThisView,"View","ShareConnection",.F.), so everything was fine and you don't need to get this to also have the same statement handle.

I also don't see a way to let remote views even share that logical statement handle, you can CREATE SQL VIEW REMOTE without specifying connection object or DSN, but the question then is what connection handle will be used.

You can control a singe SQLCONNECT/SQLSTRINGCONNECT is done and its statement handle also is used by all queries, when you use the cursoradapter. You can then make the connection either in a base class init, as the code done by the builder of cursoradapter adds, or you do this outside and just let cursoradapters init take the handle from outside, some goApp property or such, the cursor adapter needs the DataSourceType ODBC and DataSource being set to that statement handle.

Cursor adapters are a bit of a different beast, but all in all, they need the same specifications as remote views, connection, remote and cursor field name mapping, keyfield(s), updateable fields, etc. and last not least a query. There are builders by Bernard Bout but even the native CA builder isn't bad, as long as you leave hands off the generated code or only change it when you know what you're doing. It's a bit less elegant and easy to understand its concept of the added property __VFPSetup. But in the end, you're mostly concerned with picking fields and that's all done in the tabs of the builder dialog.

Usage of cursoradapters can be both in DE just as remote views or in code via oCA=createobject("caclassname") and calling oCA.cursorfill() instead of USE remoteview. Further usage then can be via the cursor generated, that's why it's called cursoradapter, so you act on an alias including to do TABLEUPDATE()/TABLERECVERT()/REQUERY() and more you also could do with remote view alias names.

So one thing is for sure, all code you have to USE or query remote views needs to change, making it not as simple as replacing USE of DBFs with USE of remote views. It pays in more aspects than using a single statementhandle, though, as you gain events like BeforeDelete, which allows doing things before a commit of the buffered cursor changes are submitted to the remote database, for each single record. Even if you only do one TableUpdate(.T.,.T.,"alias") for submitting all changes you get events per single record, just take a look at the methods tab of the cursoradapter class to see what Before/After events are there for your disposal.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Susan Antoni]

Sorry i'm newbie

what is DE

 

[Olaf Doschke]

DE = Data Environment, sorry.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Susan Antoni]

Thanks you

can you example for "REQUERY()"
how to use this function
i not understand this function

or website for read more.

 

[Olaf Doschke]

REQUERY() is a function you may not need to use, as you can also close a view and the [tt]USE viewname[/tt], to reuse the view SQL. What I pointed out is if you're already using such functions made for views, you can stay with such code. It won't matter mcuh, if you even don't know this function.

Once you have a view open and have either committed or rolled back all its buffered changes, you can tell VFP to redo the query of the view definition to update the data loaded into the view cursor.
And that simply needs REQUERY("aliasname")

Say you have a view [tt]rvOrders[/tt] getting a list of current orders, then you may use that with USE rvOrders in 0 as Orders and later at some point refresh that list by closing it with [tt]USE IN Orders[/tt] and then doing USE rvOrders again, but you can also redo the query with REQUERY("Orders") - you can also choose workarea/alias names to be like the view names, I just want to make clear the two things can differ and when you don't differentiate this you can get wrong ideas about what things should work which way.

And I said this is also possible with cursors resulting from cursoradapter object uses. The objects do have the CursorRefresh() method as a new way for REQUERY(), but when you already have code about views doing REQUERY(), that code doesn't need refactoring or changing when using cursoradapters instead of views. That's the intention of this "entanglement" of classic functions and OOP methods of this class.

If you're new to all of this, you don't gain much of this. But it's something that helps to make modernization easier for the VFP developers, who want to transit from using views to cursoradapters. Cursoradapters were introduced later, views exist for much longer already.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Susan Antoni]

Nice !!

your answer is so clearly

Thanks you

 

[Mike Lewis]

Susan,

Good to see that Olaf has answered your question and given you some other useful information.

Did you know that we have a system in the forum for flagging particularly helpful posts? If you click the "Great post" link at the bottom of the post, a red star will appear next to it in the main forum page. This is useful, partly to signify that you are happy with the answer(s) you received, and partly to alert other people with the same problem that the problem has been solved.

I hope you find this useful, and that you will come here again if you have any further questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Susan Antoni]

Susan,

Good to see that Olaf has answered your question and given you some other useful information.

Did you know that we have a system in the forum for flagging particularly helpful posts? If you click the "Great post" link at the bottom of the post, a red star will appear next to it in the main forum page. This is useful, partly to signify that you are happy with the answer(s) you received, and partly to alert other people with the same problem that the problem has been solved.

I hope you find this useful, and that you will come here again if you have any further questions.



Mike

Oh , sorry i never know

i hope this topic will help another person with same problem
every post is helpful about foxpro

i clicked "Great Post" already.

Thanks you for help me and everyone.

regard Susan