Cursor adapters with complex select statement

Rafael Copquin, November 21, 2016

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 t...

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

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 = ''

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

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.

A class that builds an object for making cursor adapters

Rafael Copquin, April 1, 2006

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.

How to use a grid as a pick list

Rafael Copquin, April 1, 2002

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, th...

How to use a grid as a pick list (part II)

Rafael Copquin, June 1, 2002

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 th...

Making invoices with grids

Rafael Copquin, October 1, 2002

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 w...

Making invoices with grids and cursor adapters

Rafael Copquin, March 1, 2006

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.

Pagination in Visual FoxPro

Rafael Copquin, July 1, 2006

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 wa...

Using grids to show Account Statements

Rafael Copquin, August 1, 2002

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 tran...

VFP and Accounting (Part II)

Rafael Copquin, June 5, 2013

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 continu...

VFP and Accounting - Part I

Rafael Copquin, March 1, 2007

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 o...

VFP menus and XML

Rafael Copquin, September 1, 2006

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 t...