Articles
Search: 

Cursor adapters with complex select statement
Monday, November 21st, 2016
Rafael Copquin, Estudio Copquin
Public Accountant and Certified Internal Auditor; Fox programmer since 1987, starting with FPD and upgrading to VFP. Vast experience in conversions from FPD to VFP, specialized in business and accounting systems. Member of the Microsoft Users Group of Argentina's Board of Directors as Treasurer.
Summary
This article demonstrates an ability to parse the select command passed as a parameter, for the development of a cursor adapter that can be used to retrieve records form a SQL Server engine. The class analyzes the select statement and creates the updatablefieldlist, updatenamelist and cursorschema thus enabling the creation of the cursor adapter.
Description
Ever since the appearance of the cursor adapter class in Visual Foxpro 8, I began using it very frequently, because of its obvious usefulness and the fact that it could replace very efficiently both local or remote views.

However, the cursor adapter builder shipped with Visual FoxPro could only create a CA from a single table, with no joins at all and not even a where clause.

The result was that we had to build the cursor adapters by hand, a process that is involved and makes the programmer very prone to error before he gets it right and makes it work.

A few years ago I developed a class to generate cursor adapters automatically, but then it would only accept a single table select statement and in addition worked only with DBFs.

The class, published by the UniversalThread in early April 2006, has been revamped and updated. It now works with any complex select statement and can be used with SQL Server.

Here is a link to that article:

https://www.universalthread.com/ShowHeaderArticleOneItem.aspx?ID=39274

Here is the code which contains interspersed explanations where applicable.

DEFINE CLASS CABUILDER AS SESSION

** Author: Rafael Copquin from Buenos Aires, Argentina
** November 2016


** this class works with SQL SERVER. 

** Example of use:

** Set Procedure to ..\cabuilder.prg additive


*!*	Text to cCmd textmerge noshow flags 2 pretext 15

*!*	Select p.idpurchases,p.purch_date,p.code,p.qty,p.cost,i.iditem,i.descrip as item_name
*!*	from purchases p join items i on p.itemid = i.iditem
*!*	order by p.purch_date,p.code

*!*	endtext

*!*	thisform.oCurAd = CreateObject('cabuilder',thisform.nHandle,cCmd,'curPurchases','p = purchases , i = items',.t.)



**>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

lOK = .t.
cTablesList = ''
cKeyFieldsList = ''
cUFL = ''
cUNL = ''
cSCH = ''
nHandle = -1


DataSession  = 1            && public data session


PROCEDURE INIT(tnHandle,tcCmd,tcAlias,tBaseTables,tlTransactions)

this.nHandle = tnHandle

this.create_adapter(tcCmd,tcAlias,tBaseTables,tlTransactions)

ENDPROC


HIDDEN PROCEDURE CREATE_ADAPTER

Lparameters tcCmd,tcAlias,tBaseTables,tlTransactions

** generates a cursor adapter of one or several tables, empty or not, as per the WHERE sentence


Local cSCH,cUFL,cUNL,cCmd

this.lOK = .T.

cCmd = tcCmd

this.build_structure(cCmd,tBaseTables)


cUFL = This.cUFL       && updatable field list
cUNL = This.cUNL       && update name list	
cSCH = This.cSCH	   && table schema	

this.lOK = .T.

Try

    Use in Select(tcAlias)

    If PemStatus(this,'oCA',5) = .T.
		Removeproperty(This,"oCA")
	Endif


	This.AddProperty("oCA")
	This.oCA = Createobject("cursoradapter")


Catch To oError

	Messagebox("The CursorAdapter object could not be generated"+;
		Chr(13)+oError.Message,16,"Attention",2000)

	this.lOK = .F.

Finally
Endtry

If this.lOK = .F.
	Return
Endif


If this.lOK = .T.
	Try


		With This.oCA
		     
    		        .DataSourceType     = "ODBC"
	                .DataSource         = this.nHandle
		        .Alias 				= tcAlias
		        .Tables 			= this.cTablesList
		        .KeyFieldList       = this.cKeyFieldsList
			.BufferModeOverride = 5
			.SendUpdates 		= .t.
			.usetransactions    = tlTransactions   
			.SelectCmd 			= cCmd
			.UpdatableFieldList = cUFL
			.UpdateNameList 	= cUNL
			.usecursorschema    = .t.
			.CursorSchema       = cSCH
			.CursorFill()
		Endwith

       
       Select (this.oCA.alias)
       
 
	Catch To oError

        MessageBox( = 'Cursor '+tcAlias+' could not be generated'+Chr(13)+oError.Message),16,'Attention',3000)

		this.lOK = .F.

	Endtry
Endif


ENDPROC


HIDDEN PROCEDURE BUILD_STRUCTURE

Lparameters tcCmd,tBaseTables


Local cCmd,nHandle,cFieldName,cFieldType,nFieldLen,nFieldDec,cUFL,cUNL,cSCH,;
      cBaseTables,cColumnName,cField,cKeyFieldsList,cLeft,cTable,cTablesList

Store '' to cCmd,cFieldName,cFieldType,cUFL,cUNL,cSCH,cBaseTables,cColumnName,cField,cKeyFieldsList,cLeft,cTable,cTablesList
Store 0 to nHandle,nFieldLen,nFieldDec


Create Cursor curAssignments(col_name C(30),original C(60),suffix C(30),assigned C(30))

cBaseTables = tBaseTables   && [ example: (p = purchases , i = items) or (purchases,items) ]
cCmd = tcCmd

nHandle = this.nHandle

** find all key fields to build the cKeyFieldsList string

For w = 1 to GetWordCount(cBaseTables,[,])
    
    X = GetWordNum(cBaseTables,w,[,])
    Y = At([=],X)
    If Y > 0
       cTable = Alltrim(Substr(Alltrim(X),Y+1))
    Else
       cTable = X
    endif

    cTablesList = cTablesList + cTable+[,]

    Use in Select('curStructure')
    
    SQLColumns(nHandle,(cTable),[NATIVE],'curStructure')  && see note 3) at the end of this class

    Select curStructure

	***fields column_name ,type_name ,column_size ,decimal_digits are fields of curStructure

	Locate for Lower(Alltrim(type_name)) ='int identity'
	If Found()
	   cKeyFieldsList = cKeyFieldsList + Alltrim(column_name)+[,]
	EndIf

endfor

** remove the comma from the end of the string

this.cKeyFieldsList = Substr(cKeyFieldsList,1,Len(Alltrim(cKeyFieldsList))-1) 
this.cTablesList    = Substr(cTablesList,1,Len(Alltrim(cTablesList))-1)   

Use in Select('curStructure')


*****************************************
*** the following builds the cUNL string
*****************************************

For i = 1 to getwordcount(cCmd)

    cColumnName = GetWordNum(cCmd,i,[,])
    
    If 'select' $ Lower(cColumnName)
    
       cColumnName = Alltrim(Substr(cColumnName,8))		&& remove the word "select" from the select statement
       
    endif
    cField = cField + cColumnName+ [,]
endfor

N=At('from',cField)

cField = Left(Alltrim(cField),N-2)		&& remove everything that follows the word FROM

** buid the cursor with the suffix (eg: purchases.code or p.code--- the suffix is either purchases or c.)

For k = 1 to GetWordCount(cField,[,])

    cColumnName = Alltrim(GetWordNum(cField,k,[,]))
    S = At( [.] , cColumnName)
    If S > 0
       Insert into curAssignments(col_name,suffix) ;
       values(Substr(cColumnName,S+1), Substr(cColumnName,1,S-1))
       If GetWordCount( Alltrim(cColumnName),[ ]) = 3
          Select curAssignments
          replace original with GetWordNum(Alltrim(col_name),1,[ ])
       endif
    endif
endfor


Select curAssignments


Scan all

    If Left(Alltrim(col_name),1) = [.]

       replace col_name with Substr(Alltrim(col_name),2)

    EndIf

    If GetWordCount(col_name,' ') > 1
       
       ** this is for the special case of, for instance, DESCRIP AS ITEM_NAME. Results in ITEM_NAME
       
       replace col_name with GetWordNum(col_name,3,' ')		
       
    endif
EndScan


** place in the ASSIGNED field the field name in the select statement without the suffix


If Vartype(cBaseTables) = 'C' and Len(Alltrim(cBaseTables)) > 0
	Scan all
	    cPrefix = Alltrim(suffix)
	    For z = 1 to GetWordCount(cBaseTables,[,])
	        A = GetWordNum(cBaseTables,z,[,])
	        R = At([=],A)
	        If R > 0
		        cLeft = Alltrim(Substr(Alltrim(A),1,R-1))
		        If cPrefix = cLeft
	              replace assigned with Substr(Alltrim(A),R+1)
		        EndIf
	        Else
                replace assigned with Alltrim(suffix)
	        endif
	    endfor
	EndScan
EndIf


** this puts the original field name of the table in the cUNL string
** Eg: items.descrip as item_name
** the cUNL string would contain: item_name  items.descrip

Select curAssignments
Scan all
     If Empty(original)
        replace original with col_name
     endif
EndScan

** build the cUNL string

Scan all

    cUNL = cUNL + Alltrim(col_name)+' '+Alltrim(assigned)+[.]+Alltrim(original)+[,] 

endscan

*************************************************
** the following builds the strings cSCH and cUFL
*************************************************

Use in Select('curAux')

SQLExec(nHandle,cCmd,'curAux')

Select curAux

AFields(acol_names,'curAux')

Use in Select('curAux')


For j = 1 to Alen(acol_names,1)

   cFieldName = Lower(acol_names(j,1))
   cFieldType = upper(acol_names(j,2))
   nFieldLen  = acol_names(j,3)
   nFieldDec  = acol_names(j,4)
   
   cUFL = cUFL + cFieldName+[,]	

   cSCH = cSCH + cFieldName+[ ]+cFieldType

   Do case
	  Case InList(cFieldType,[C],[D],[I],[T])
	       cSCH = cSCH + [(] +Transform(nFieldLen) +[)]
	  Case cFieldType = [N]
	       cSCH = cSCH + [(] +Transform(nFieldLen)+[,]+Transform(nFieldDec) +[)]
	  Case cFieldType = [M]
	       cSCH = cSCH + [(] +Transform(8000) +[)]
   EndCase
   cSCH = cSCH + [,]


EndFor

** remove the comma from the end of the string

this.cSCH = Substr(cSCH,1,Len(Alltrim(cSch))-1)   && schema
this.cUNL = Substr(cUNL,1,Len(Alltrim(cUNL))-1)   && updatenamelist
this.cUFL = Substr(cUFL,1,Len(Alltrim(cUFL))-1)   && updatablefieldlist


ENDPROC

ENDDEFINE
Notes
  1. The select statement MUST contain the primary keys of all tables involved in the selection in the above example these are IDPURCHASES and IDITEM

  2. All fields of all tables in the select statement can be updated

  3. The following statement works with Microsoft SQLServer. It has not been tested with other DB Engines

    SQLColumns(nHandle,(cTable),[NATIVE],'curStructure')

    If you test it in MYSQL and it does not work, simply add as a parameter a list of all the primary keys in the select statement and assign it to the cKeyFieldsList string and discard the code associated with the search

  4. If you want the class to work with DBFs, send it a list of all keyfields of all tables in the select statement, discard the code to search for them as note 3) above and change the following properties in the CREATE_ADAPTER method:

    .DataSourceType = "NATIVE"
    .DataSource = ''

Conclusion

I trust it will be useful for the creation of cursor adapters on the fly, thus avoiding the complexities entailed by coding them manually.

More articles from this author
Saturday, April 1st, 2006, by Rafael Copquin
This class is especially designed to build cursor adapters by simply passing a few parameters, such as table name, cursor name, cursor adapter object name, updatable or not, empty or not, complex or simple select statement.
Monday, April 1st, 2002, by Rafael Copquin
Those of us who have ever programmed FoxPro DOS applications, are marveled and at the same time overwhelmed with the possibilities of enhancement in the appearance, the performance and the different ways of doing the things that were difficult, or outright impossible to do in DOS, that we observe in Visual Fox Pro. The concept of "object", which enables us to modify the appearance or the behaviour of a screen, control or basic routine, by just a few clicks of t...
Saturday, June 1st, 2002, by Rafael Copquin
In the April 2002 issue, I showed the way to use a grid as a picklist, and in order to make the example clear and readily understandable, I did not worry about code reusability. The example simply showed how to program methods in the form containing the picklist grid in order to enable the user to select a customer name from a customer table, and place the returned value in a textbox on the calling form. In this issue I will demonstrate the use of a generic class, that can be used...
Tuesday, October 1st, 2002, by Rafael Copquin
Background Early in my development experience, back in the days of Fox Dos, I had to develop a better way to make invoices than the one I had been using. What I had been doing was using the SCROLL statement to make the screen move upwards every time a new item was added. Whenever the user wanted to review the invoice, especially if it contained a large number of lines that would disappear from the screen's limited drawing area, I had to create an array dynamically from the records saved...
Wednesday, March 1st, 2006, by Rafael Copquin
In October 2002 the UTMag published the first article on the subject of making invoices with grids. Ever since, I have received numerous emails from readers asking me how to save the invoice thus created in the server tables. This article explains how to use cursor adapters to save the data.
Saturday, July 1st, 2006, by Rafael Copquin
When we are examining data from a table, using generally a grid control, even if we did it with a browse window, we are in reality viewing a small part of a, perhaps, enormous quantity of data. We are literally viewing only the amount of records that fit in the grid or the browse window. If we want do see more records, as Visual FoxPro users used to the ease of handling of the product interfase, we simply click on the page down and page up buttons and, if the cursor is on the top or on the la...
Thursday, August 1st, 2002, by Rafael Copquin
Background Many VFP applications deal with accounting data in ways that, in my opinion, are not very efficient, from the program standpoint, or very clear to the user. An example of this is an account statement. Now, an account statement can be any accounting statement that portrays the transactions made in an account, such as a bank statement, a general ledger account, a detail of cash receipts and disbursements, a customer account, a supplier account, etc. What all these statements ...
Wednesday, June 5th, 2013, by Rafael Copquin
When Microsoft discontinued development of Visual FoxPro, many would-be writers on VFP issues stopped sending articles to the UniversalThread and as a result, no more articles on VFP were published, with a few exceptions. Over the years, I kept receiving requests from many readers for a continuation of the VFP and Accounting article published in March 2007. I extend my thanks to Michel Fournier for allowing me to publish its continuation. In the first part of this article I explained...
Thursday, March 1st, 2007, by Rafael Copquin
Accounting and computer systems should not be tied to any one language in particular. You can design a computer system to be written in Fortran, Pascal, Cobol, VFP or Visual Basic.NET. However, the love of my life as far as computer languages are concerned is Visual FoxPro and that is why the rest of this article will show examples written in VFP.
Friday, September 1st, 2006, by Rafael Copquin
The title sounds quizzical, doesn’t it? What has the Visual FoxPro menu system in common with XML? Not much. However, it is possible to use XML as a means to generate separate menus for every user. It consists of a series of case statements that would define pads, popups or menu bars, according to the status of certain boolean properties. If the properties were set to true, the corresponding lines of code would be “allowed” or “disallowed” if they were set to false.