RAlvarez579911


Been assigned to work on a new project using VFP9, yet am not used to the language yet.

At first, the SQLEXEC line was working fine, then suddenly it doesn't return the records its supposed to return. No changes in the database at all, it just stopped retrieving.

FUNCTION runClass(tnConnectionId, tcProjectName)

LOCAL loPrograms, ;

loRates, ;

lcFiscalCalendar, ;

lcBudgetElement, ;

lcRateFile, ;

lcName, ;

lnResult

* Retrieve Selected Project Record

lcName = "SELECT PROGRAM, PROG_DESC, FISC_FILE, ; CALC_FILE, RATE_FILE FROM PROGRAM WHERE PROGRAM = '" + tcProjectName + "'"

lnResult = SQLEXEC(tnConnectionId, lcName, "PROGRAM", laRecCtr)

* Validate if Retrieve was Successful

IF lnResult < 0 THEN

MESSAGEBOX(MB_ERR_ACCESS, MB_OK + MB_EXCLAMATION, MB_TITLE_ERR)

RETURN .F.

ENDIF

IF laRecCtr[2]=0 then

RETURN

ENDIF

To check, I replaced tcProjectName with the actual data I wanted to retrieve and it worked, but when I used a variable again, it stopped working again. I debugged this part of the code and the variable holds the right value during execution, so I'm at a lost as to why its acting this way now.

Help




Re: SQLEXEC

CetinBasoz


I think the typo in code is due to formatting here ( semicolon in select command ). Anyway I'd rewrite that part as:

lcName = "SELECT PROGRAM, PROG_DESC, FISC_FILE," +;

" CALC_FILE, RATE_FILE FROM PROGRAM WHERE PROGRAM = '" + tcProjectName + "'"

(This is actually irrelevant to problem but I'd write it like that).

Your problem might be related to 2 things:

1) Foxpro is a case insensitive language and field names take precendence. Here it 'sounds' you mean memory variable 'tcProjectName' but there is no guarantee. If a table is open in current workarea (likely in VFP) and have a field named 'tcProjectName' (say to mean 'Temp C Project' someone used a field named tCProject) then that field's value would be used. To prevent that always prefix your memory variables with m. (widely known as mDot). To see what I mean try this:

use (_samples+'data\customer')

Company = "My Company"

Company && not 'My Company but something like 'Alfred's Futterskie'

m.Company && 'My Company'

2) The trimming and casing of tcProject variable might be important depending on your backend. For example if backend is VFP then it works based on current set ansi setting and is case sensitive. These 2 expressions would return entirely different results:

where Title = 'Sales ' && anything that starts with Sales if ANSI is OFF.

where Title == 'Sales ' && only if title is exactly Sales no matter what the ANSI setting is

Both are case sensitive. In SQL server 'sales', 'Sales' might equally work well but not in VFP.

And lastly use parameters in SQLExec rather than hardcoding with concatenation (here it's not very important but it becomes important with other datatypes executed against different backends). So try like this (leaving casing possibility out):

FUNCTION runClass(tnConnectionId, tcProjectName)

LOCAL loPrograms, ;

loRates, ;

lcFiscalCalendar, ;

lcBudgetElement, ;

lcRateFile, ;

lcName, ;

lnResult

* Retrieve Selected Project Record

tcProjectName = trim(m.tcProjectName)

lcName = "SELECT PROGRAM, PROG_DESC, FISC_FILE,"+;

" CALC_FILE, RATE_FILE FROM PROGRAM WHERE PROGRAM = m.tcProjectName"

lnResult = SQLEXEC(m.tnConnectionId, m.lcName, "PROGRAM", laRecCtr)






Re: SQLEXEC

dni

Do you have SP1 installed

That solves "SQLEXEC() function allows reentrance for the same connection handle" :

http://download.microsoft.com/download/c/7/b/c7b400fa-ff4f-43d3-ada1-8d0ea0e6803e/VFP9SP1FixList.htm







Re: SQLEXEC

RAlvarez

Thanks, the mDot worked like a charm. However, the " " in m.tcProjectName you wrote kinda eludes me since its inside the quotation marks, am guessing that the before the variable signals VFP to treat the next word as a variable and not as a literal, right That kinda gets a bit confusing when reading it Smile



Re: SQLEXEC

Alex Feldstein

RAlvarez wrote:
Thanks, the mDot worked like a charm. However, the " " in m.tcProjectName you wrote kinda eludes me since its inside the quotation marks, am guessing that the before the variable signals VFP to treat the next word as a variable and not as a literal, right That kinda gets a bit confusing when reading it

You are basically correct. In that case it means to treat m.tcProjectName as a replaceable parameter in the query.