Storing CursorAdapter Connection Info 2

[Ed Andres]

I am developing an application where I am using CursorAdapters and SQL passthrough for most of the data heavy lifting. I have been told that the data will be moving to a new server so all the conection info will change. With the SQL passthrough it is simple to change the connection info, I have defined it in my program and call it each time I need a connection, one line change. However, the CursorAdapters classes were created using the builder so the connection info is not so easy to get at. What is the best practice for storing and assigning the database connection info with CursorAdapters?

Keep in mind that I have only developed applications for internal use so defining where the data is located has always been simple (as long as they don't move it to a new server with a new name). I am using VFP9sp2 and the backend is SQL Server 2005. I'd like to learn the proper way to make these connections so server changes are painless, even thought they don't happen very often.



Ed

 

[DougHennig]

Hi Ed.

Letting the CursorAdapter Builder store the connection information in the class isn't really a good idea. It's fine for testing, but this can cause multiple connections to the database, which can chew up licenses. It's better to have a single connection class, such as something based on Custom that makes the connection and has a nConnectionHandle property other objects can use to share the connection). Then, the Init of your CursorAdapters would simply use This.DataSource = oConnection.nConnectionHandle.

In addition to sharing a single connection, this also has the benefit that the connection information is stored in one place rather than hard-coded in all of your CursorAdapter classes.

Doug

 

[Ed Andres]

Doug,
Thanks for the reply. That is exactly what I am looking for but am still not sure how to attach connection info to a custom class. Here is an idea I have been toying with but need to do some more testing; create a CA class with only the connection info, then create another CA class using the first class as the base and then adding the other parts of the information. That way the connection info should be inherited from the parent class, then changes could be made in one place, the parent class.

Anyone tried anything like this?? Am I way off base with this approach?



Ed

 

[Olaf Doschke]

Hi Ed,

inheritance is the wrong concept in this case. If you eg have code in your base class CA that makes a connection each instance of this CA, also subclassed CAs will make this connection again and create another handle. It's not, that each instance will find what the connection from the first run instance.

What you need is inheritance in time, not in space, so to say.

You could do have a base class which connects in it's init and stores the handle to some public property, eg store it to goApp.nConnectionhandle of a global application object. If you don't have one, then use _screen perhaps.

If you would store the connection handle in your CA class, a new instance would not see it, as that value is not in the class definition but was changed at runtime, you inherit the class, not the running object. But if your init checks if the global goApp.nConnectionhandle exists and is valid, each CA would reuse it or create a new handle if needed. So the idea of having a base class CA with such code isn't totally wrong, but you need something public, outside of the CA object here, too, to shar that info.

Bye, Olaf.

 

[DougHennig]

Hi Ed.

I agree with Olaf. Don't use inheritance. Instead, do something like this (greatly simplified for illustration only):

define class ConnectionMgr as Custom
nConnectionHandle = 0
cConnectionString = ''

function Connect
This.nConnectionHandle = sqlstringconnect(This.cConnectionString)
endfunc

function Destroy
This.Disconnect()
endfunc

function Disconnect
sqldisconnect(This.nConnectionHandle)
endfunc
enddefine

Application startup:

public poConnection
poConnection = createobject('ConnectionMgr')
poConnection.cConnectionString = 'whatever'
poConnection.Connect()

CursorAdapter Init:

This.DataSource = poConnection.nConnectionHandle

Doug

 

[Ed Andres]

Thanks guys for the info, my testing proved out exactly what you are saying about inheritance in this case. I will look into your suggestions and should be able to implement some sort of solution based on your input. I will report back if there are any other snags.

Thanks Again


Ed

 

[Ed Andres]

Ok - so I am making progress here but have run into a little snag that is strange. I have created a connection class as Doug has recommended and am then making the connection using the folowing code in my startup program:

WITH _Screen
.AddProperty([sqlConnection])
.sqlConnection = NewObject([ConnectionMgr],[\libs\cts_service_app])
.sqlConnection.cConnectionString = "Driver={SQL Server};Server=CTSCRM;Database=CTS_Products"
.sqlConnection.Connect()
ENDWITH

This works very well for my transact SQL line I am using in my program but for the CA's, there is a little snag. Here is how I am using the CA's. I have created classes for each data table that I am using and then in a Method of the form I am using some code like this to get the data:

thisform.AddProperty([MainCursor])
thisform.MainCursor = NewObject([Maindata],[\libs\cts_service_app])
thisform.MainCursor.SelectCMD = "SELECT * FROM Service_Main WHERE SRno = '" + ALLTRIM(thisform.txtRmano.Value) + "'"
thisform.MainCursor.CursorFill()

In the class for Maindata, I have the
DataSource =_Screen.sqlConnection.nConnectionHandle
DataSourceType = ODBC

This works very well and is very fast but It does not seem to want to update the data. I have all the usual Send updates stuff set for the CA but it errors using this update command:

IF USED('main')
IF !TABLEUPDATE(.T., .T., 'Main')
MESSAGEBOX("Error saving Main Data", 64, "Error")
ENDIF
ENDIF

Now, I originally had all my CA's using a ADO connection with all the connection info in the builder and this worked with no problem.

What am I missing??



Ed

 

[DougHennig]

Hi Ed.

Use AERROR() to determine what the error is. That will likely lead you to the cause of the problem.

Doug

 

[Ed Andres]

Doug,
Thanks for the suggestion. However, I used the wrong term when I said error. I should have said, The TABLEUPDATE() is returning .F. which you cannot use AERROR() to determine the problem. The error I was referring to is the MESSAGEBOX() I am popping to tell me the save did not work.

Basically the problem is that the TABLEUPDATE() seems to work fine on the CA that is setup using ADO and the connection string is in the INIT from the builder. If I use ODBC and assign the DataSource, it always returns .F.

Hope I am not being too confusing and thanks for your help.

Ed

 

[DougHennig]

Hi Ed.

Sure you can use AERROR(). Try it.

Doug

 

[Ed Andres]

My bad again on the AERROR(), guess I misread the help for TABLEUPDATE(). Sorry for wasting your time.

Ed

 

[DougHennig]

No problem, Ed. We've all been there.

Doug