TechMate


I am getting "Feature Not Available" for the following stored procedure when called from Win App using OLEDB driver for FoxPro. I checked the Supported/Unsupported commands but I dont find anything that is not supported in my procedure

Can someone tell me if you find any errors or alternatives

PROCEDURE PROCESS_LOCATION_VIEW_TABLES

PARAMETERS setpathdirectory

DO POPULATE_DATA_201 WITH setpathdirectory

ENDPROC

PROCEDURE POPULATE_DATA_201

PARAMETER pathname

CREATE cursor __Temp (loc C(60), bill_date C(8), Total N(11,2))

INSERT INTO __Temp (loc, bill_date, total) SELECT loc, bill_date, total FROM h33_CGN

INSERT INTO __Temp (loc, bill_date, total) SELECT loc, bill_date, total FROM h83_CGN

INSERT INTO __Temp (loc, bill_date, total) SELECT loc, bill_date, total FROM h93_CGN

INSERT INTO __Temp (loc, bill_date, total) SELECT loc, bill_date, total FROM h113_CGN

CREATE CURSOR CollectDataFor201 (loc C(60), bill_date C(6), Total Numeric(11,2))

INSERT INTO CollectDataFor201 (loc, bill_date, total) SELECT loc, bill_date, SUM(Total) FROM __Temp GROUP BY loc, bill_date

IF INDBC('H201_CGN', 'TABLE')

DELETE FROM H201_CGN

CLOSE TABLES

PACK H201_CGN

ELSE

CREATE TABLE (ADDBS(m.pathname)+"H201_CGN") (loc C(60), keep C(1) DEFAULT "", t200506 NUMERIC(10,2) DEFAULT 0, t200507 NUMERIC(10,2) DEFAULT 0, t200508 NUMERIC(10,2) DEFAULT 0, t200509 NUMERIC(10,2) DEFAULT 0, t200510 NUMERIC(10,2) DEFAULT 0, t200511 NUMERIC(10,2) DEFAULT 0, t200512 NUMERIC(10,2) DEFAULT 0, t200601 NUMERIC(10,2) DEFAULT 0, t200602 NUMERIC(10,2) DEFAULT 0, t200603 NUMERIC(10,2) DEFAULT 0, t200604 NUMERIC(10,2) DEFAULT 0, t200605 NUMERIC(10,2) DEFAULT 0, t200606 NUMERIC(10,2) DEFAULT 0, t200607 NUMERIC(10,2) DEFAULT 0, t200608 NUMERIC(10,2) DEFAULT 0, t200609 NUMERIC(10,2) DEFAULT 0, t200610 NUMERIC(10,2) DEFAULT 0, t200611 NUMERIC(10,2) DEFAULT 0, t200612 NUMERIC(10,2) DEFAULT 0)

USE H201_CGN

INDEX ON loc TAG Location

ENDIF

INSERT INTO H201_CGN (loc) SELECT DISTINCT ALLTRIM(loc) FROM CollectDataFor201

SELECT H201_CGN

gnFieldCount = AFIELDS(gaMyArray)

CLOSE TABLES

SELECT DISTINCT ALLTRIM(bill_date) AS BILL_Date FROM CollectDataFor201

SCAN

myColCaught = .F.

colName = "t"+ALLTRIM(bill_date)

FOR nCount = 1 TO gnFieldCount

IF UPPER(gaMyArray(nCount,1)) = UPPER(colName) THEN

myColCaught = .T.

EXIT

ENDIF

ENDFOR

IF myColCaught = .F. THEN

ALTER TABLE H201_CGN ADD COLUMN ("t"+ALLTRIM(bill_date))NUMERIC(11,2) DEFAULT 0

SELECT H201_CGN

gnFieldCount = AFIELDS(gaMyArray)

ENDIF

ENDSCAN

&& Procedure executes until this point...

&& I am using dynamic fieldname to update based on Bill Date - I am guessing this is the problem.

&& I originally had a Select Case with actual fieldname instead of &bdtCol and it worked without any issues.

gCounter = 0

locName = ""

bDate = ""

gTotal = 0

SELECT CollectDataFor201

SCAN

locName = loc

gTotal = total

bdtCol = "t" + ALLTRIM(bill_date)

UPDATE H201_CGN SET &bdtCol = gTotal WHERE H201_CGN.loc = locName

ENDSCAN

CLOSE TABLES

ENDPROC




Re: Feature Not Available - OleDb Provider Error

CetinBasoz


At first glance I see "index" there. It is not supported AFAIK. As I remember I already showed you that you were doing an unnecessary thing in one of your previous posts.




Re: Feature Not Available - OleDb Provider Error

TechMate

Like I mentioned in the comments in the attached code, the stored procedure runs fine until that point. I have the same version with hardcoded SELECT CASE in the last step. I went one step further and commented the LAST UPDATE statement and ran the exact same procedure from windows application (including the index command) and there are no errors.

The update statement "

UPDATE H201_CGN SET &bdtCol = gTotal WHERE H201_CGN.loc = locName

" is throwing this error. This table contains columns that are in the format "TYYYYMM" where T is a prefix and YYYY is year and MM is month. In an effort to ensure this procedure runs for months going forward, I replaced the Update statement in SELECT CASE to the construct above. This syntax works flawlessly in the MS Visual FoxPro program but not through the OLE DB provider.

I am dynamically setting bdtCol = "T200506" (for example) and trying to reference the ColumnName by using & symbol and update the data. I COMMENTED this single line in previous code snippet and I dont get the "Feature Not Available" message at all.

I understand there are some limitations in the driver, but there isnt much help I can find online or in the documentation. Is there an equivalent code snippet that will run for the same function

Thanks much






Re: Feature Not Available - OleDb Provider Error

TechMate

I have taken your suggestions and comments and removed all those temporary tables from previous post. I am using only cursors but the issue is not related to any of previous problems. I am not sure if you still notice something in my procedure that you feel as unnecessary. I appreciate your feedback



Re: Feature Not Available - OleDb Provider Error

AndyKr

I don't think the OLEDB Provider supports macro expansion in explicit commands - only in stored procedures (this is what the help file implies, anyway).






Re: Feature Not Available - OleDb Provider Error

TechMate

What other alternatives do we have Any clues or suggestions please...



Re: Feature Not Available - OleDb Provider Error

AndyKr

>> What other alternatives do we have

Don't do it this way! Create a stored procedure and you will, presumably, be fine.






Re: Feature Not Available - OleDb Provider Error

TechMate

What i attached in my original post IS a STORED PROCEDURE... What do you mean by "create a stored procedure"



Re: Feature Not Available - OleDb Provider Error

AndyKr

Sorry I did not realize that you had defined these in the DBC - I assumed that these were just procedure files defined in code.

Sorry I have no idea what else to suggest since, according to all the rules it should work.