index

[jpmoschi]

good morning forum:
I am new here, I am getting to xsharp from foxpro and I try to know the degree of completion of the product:

My question is this:
sqlexec ("select * from customers", "mysqlresult")
select mysqlresult
index on customer_id key tag
index on name tag name

The "index on" command is possible in MS -SQl orOoracle context?

best regard
Juan

[robert]

Juan,

Just like FoxPro we create a local cursor (workarea) for the results returned by the SqlExec() function.
You can create indexes, set a filter and virtually do anything on this cursor. You can also use the COPY TO command to copy the data to a DBF.
This resultset is detached, changes will NOT be automatically written back to the backend. I believe FoxPro works just like that.

The only problem may be that the current INDEX command does not support the TAG clause like FoxPro does.
For now you need to call the OrdCreate function:

OrdCreate(cIndexFile, cOrder, cKeyValue, cbKeyValue, lUnique)

cIndexFile is optional and the codeblock and unique flag as welk, so this works:

OrdCreate( , "key", "customer_id")
OrdCreate( , "name", "name")

We will try to update the INDEX ON command for the next build. But maybe someone on this forum wants to help with that too ?
Robert

[Karl-Heinz]

Hi Robert,

a while ago i adapted this from the VO STD.UDC.

Code: Select all

#COMMAND INDEX ON <key>     ;
     [TAG <ORDER>]  		;
     [OF <file>]  			;
     [FOR <fo>]     		;
     [<all:ALL>]    		;
     [WHILE <wh>]   		;
     [NEXT <nx>]    		;
     [RECORD <rec>] 		;
     [<rs:REST>]    		;
     [EVAL <ev>]    		;
     [EVERY <evr>] 			;
     [<u:UNIQUE>]   		;
     [<asd:ASCENDING>]      ;
     [<dsd:DESCENDING>]     ;
     [<lCur:USECURRENT>]    ;
     [<lAdd:ADDITIVE>]      ;
     [<lCus:CUSTOM>]        ;
     [<lNop:NOOPTIMIZE>]    ;
        					; 
    => DbSetOrderCondition( <"fo">, <{fo}> ,		;
     [<.all.>],     								;
     <{wh}>,       									;
     <{ev}>, <evr>, ,								;
     <nx>, <rec>,  									;
     [<.rs.>], [<.dsd.>],   						;
     <.lAdd.>, [<.lCur.>], [<.lCus.>], [<.lNop.>] )	;
    ;;  
    DbCreateOrder(<(ORDER)>,<(file)>,<"key">,<{key}>,IF(<.u.>,.T., NIL)) 

// -------------------------------------

FUNCTION Start() AS VOID
FIELD LAST, AGE 
LOCAL cPath, cDBF, cCDX , c


	cPath = "d:test"
	cDBF = "small.dbf" 
	cCDX = "small.cdx" // "small1x.cdx"  
	
 	c = "O"          
	 
	RddSetDefault("DBFVFP") 		
//	RddSetDefault("DBFCDX") 
	
	
	SELECT a  

	USE (cPath + cDBF) 
	
	
	IF ! File ( cPath + cCDX ) 
		
	    ? "Build CDX"  
	  
//		INDEX ON Upper(LAST) TAG ORDER1 OF (cPath + cCDX ) EVERY 2 EVAL TestEval()
//		?
//		INDEX ON Upper(LAST) TAG ORDER2 OF (cPath + cCDX ) FOR Upper (LAST)= "O" EVERY 6 EVAL TestEval() 
//		?
//		INDEX ON Upper(LAST) TAG ORDER3 OF (cPath + cCDX ) FOR Upper (LAST)= "O" .AND. AGE  > 12 DESCENDING UNIQUE
        
  
		// This creates automatically a cdx with the same name as the dbf 

		INDEX ON Upper(LAST) TAG ORDER1 EVERY 2 EVAL TestEval()      
		?
		INDEX ON Upper(LAST) TAG ORDER2 FOR Upper (LAST)= "O" EVERY 6 EVAL TestEval() 
		?
		INDEX ON Upper(LAST) TAG ORDER3 FOR Upper (LAST)= "O" AND AGE  > 12 DESCENDING UNIQUE
		 	
	ENDIF 
	
	
	SET INDEX TO (cPath + cCDX)

	GO TOP	
	
   
	SET ORDER TO "ORDER1"	 
	? "OrdKeyCount() ORDER1:" , OrdKeyCount()
	? "FOR Condition ORDER1:" , DbOrderInfo(DBOI_CONDITION) 
	? "Descending ?  ORDER1:" , DbOrderInfo(DBOI_ISDESC ) 
	? "Unique ?      ORDER1:" , DbOrderInfo(DBOI_UNIQUE ) ,OrdIsUnique()	
	?
	
	SET ORDER TO "ORDER2"
	? "OrdKeyCount() ORDER2:" , OrdKeyCount() 
	? "FOR Condition ORDER2:" , DbOrderInfo(DBOI_CONDITION) 
	? "Descending ?  ORDER2:" , DbOrderInfo(DBOI_ISDESC ) 
	? "Unique ?      ORDER2:" , DbOrderInfo(DBOI_UNIQUE ) ,OrdIsUnique()	
	?  
	
	SET ORDER TO "ORDER3"
	? "OrdKeyCount() ORDER3:" , OrdKeyCount()
	? "FOR Condition ORDER3:" , DbOrderInfo(DBOI_CONDITION) 
	? "Descending ?  ORDER3:" , DbOrderInfo(DBOI_ISDESC ) 
	? "Unique ?      ORDER3:" , DbOrderInfo(DBOI_UNIQUE ) ,OrdIsUnique()
	?	
	
	SET ORDER TO "ORDER1"
	
	
	GO TOP 
	
	?   
	
	// ------
		
	c = "O"
	
 	? 	
	
	
	SEEK c
	        
	
	IF Found() 
		
        ? "Found" 

	ELSE 
		
		? "not found"	
	
	ENDIF

	CLOSE DATABASES  
	
	
RETURN 

FUNCTION TestEval()
	
	?  "TestEval()" 

	RETURN NIL 

Note:

- Instead of ORDCONDSET()/ORDCREATE() i´m using DbSetOrderCondition()/DbCreateOrder().
- Foxpro uses the word "OF" instead of "TO" to specify a cdx name.

A quick test shows that there´s still a problem if a ".AND." condition is written as "AND". The Preprocessor translates this to

Code: Select all

DbSetOrderCondition( "Upper(LAST)="O"ANDAGE>12" , {||Upper(LAST)="O"ANDAGE>12}

but the macro compiler can´t handle this.

regards
Karl-Heinz

[Zdeněk Krejčí]

Robert,
vfp cursor against sql server can be updatable. You have to set some properties of the cursor via use some cursorsetprop() functions.
CURSORSETPROP("Tables", ... )
CURSORSETPROP("UpdatableFieldList" , ...)
CURSORSETPROP("UpdateNameList" , ...)
CURSORSETPROP("KeyFieldList" , ...)
CURSORSETPROP("SendUpdates", .T. , "T1")
CURSORSETPROP("Buffering" , 5 , "T1")
CursorSetProp("WhereType", 2 , "T1")

Zdeněk

[robert]

Zdeněk ,

Ok thanks for the info. I guess we have more work to do then <g>.
Can you show (mail) me an example of this at work ?

Robert

[Zdeněk Krejčí]

MS SQL server

Code: Select all

	CREATE TABLE [dbo].[TEST](
		[idTest] [int] IDENTITY(1,1) NOT NULL,
		[n1] [int] NULL,
		[c1] [varchar](50) NULL,
	 CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
	(
		[idTest] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

VFP

Code: Select all

lnHandle  = Sqlstringconnect( "your connection string")
If m.lnHandle > 0

	SQLExec(m.lnHandle, "select * from test", "curTest")

	= CursorSetProp("KeyFieldList", "idTest", "curTest")
	= CursorSetProp("Tables", "test", "curTest")
	= CursorSetProp("UpdatableFieldList", "idTest,n1,c1", "curTest")
	= CursorSetProp("UpdateNameList", "idTest test.idTest,n1 test.n1,c1 test.c1", "curTest")
	= CursorSetProp("SendUpdates", .T., "curTest")

	Select curTest
	Browse
	Tableupdate(.T.)

	SQLDisconnect(m.lnHandle)

Endif

Zdeněk

[Zdeněk Krejčí]

sptsetupdatable.prg

Code: Select all

Lparameters lcAlias, lcSqlTable, lcKeyFields
*--------------------------------------------
*   Set SPT kurzor updatable, field names are the same as on the server
*--------------------------------------------
*   lcAlias     - local cursor
*   lcSqlTable  - table on the server
*   lcKeyFields - list of the fields of the primary key
*--------------------------------------------
*
Store "" To lcUpdatableFieldList, lcUpdateNameList

For lnField = 1 To Afields(laFields, m.lcAlias)
	lcUpdatableFieldList = m.lcUpdatableFieldList + "," + laFields(m.lnField, 1)
	lcUpdateNameList	 = m.lcUpdateNameList + "," + laFields(m.lnField, 1) + " " + lcSqlTable + "." + laFields(m.lnField, 1)
Endfor

lcUpdatableFieldList = Substr(m.lcUpdatableFieldList, 2)
lcUpdateNameList	 = Substr(m.lcUpdateNameList, 2)

= CursorSetProp("KeyFieldList"      , m.lcKeyFields, m.lcAlias)
= CursorSetProp("Tables"            , m.lcSqlTable, m.lcAlias)
= CursorSetProp("UpdatableFieldList", m.lcUpdatableFieldList, m.lcAlias)
= CursorSetProp("UpdateNameList"    , m.lcUpdateNameList, m.lcAlias)
= CursorSetProp("SendUpdates"       , .T., m.lcAlias)

Return

[FoxProMatt]

Wow.... I must say, I never knew this was possible to push from local cursor to Sql Server in this way; using the TableUpdate() function with some setup by CursorSetProp().

I assume it's the case that the source cursor doesn't have to have originated from Sql Server in the first place.... It could have come to life from any available manner, as long as all the CursorSetProp() settings are correct you can update Sql Server Table from the local cursor.

FoxPro never ceases to amaze me.

[FoxProMatt]

Well, my *assumption* was wrong that:
.

I assume it's the case that the source cursor doesn't have to have originated from Sql Server in the first place.... It could have come to life from any available manner, as long as all the CursorSetProp() settings are correct you can update Sql Server Table from the local cursor.

Turns our that is not true! It cannot be used to update from just any old local cursor...it only works on cursors that actually came from a SqlExec() call.

I did a test where I called SqlExec() to get a local SPT cursor (call it "CURSOR1") then I did a FoxPro SELECT SQL command to select from that SPT cursor to *another* local FoxPro cursor (call it "CURSOR2"). Then I tried to configure "CURSOR2" with CursorSetProp(), but it fails and give an error message on the CursorSetProp("Tables"...) call with error "Property is invalid for table cursors".

BTW the above test indeed worked perfectly as Zdeněk Krejčí said when I did the updates only from SPT "CURSOR1".

2020-06-12_12-53-11.png (153.87 KiB) Viewed 755 times

[robert]

Matt,
Of course this needs a cursor coming from SQLExec().
How else would FoxPro (or X#) know what ODBC connection to use ?

Robert