>Hello Everybody !
>
>I'm working with FOXPRO 9 against a maxdb database !
>ODBC with SQL-Passthrough
>
>L_cmd="Select .... from ...."
>sqlexec(connection,l_cmd,"mycursor)
>
>Now i have a problem with temporary resultcursors in the maxdb
>
>I create a temporary cursor with a complex recursive select and want to use this cursor for another sql-command
>
>l_cmd="create complexcursor as select ..... FOR REUSE"
>sqlexec(connection,l_cmd)
>
>l_cmd="update table xxx set yyy = 'A' where tablekey in (select tablekey from complexcursor)"
>sqlexec(connection,l_cmd)
>
>error: unkown table complexcursor
>
>The MAxDB-Folks told me that the syntax for creating the complexcursor is ok and normaly it has to be there, but
>some programming languages will do some "clearing" before each task ???!!!
>
>Can someone explain me, what foxpro does with a sequence of sqlexecutes
>Is there any kind of "clearing" befor passing a new command to the database ??????
>
>Any help welcomed
>Best regards
>Albert
Albert,
VFP doesn't do such clearing on itself. I don't know maxdb but with SQL server you can create local or global temporary tables and use them as long as they are in scope. ie:
Text to m.lcSQL noshow
CREATE TABLE #myTemp (myVal varchar(10))
INSERT INTO #myTemp VALUES ('hello')
INSERT INTO #myTemp VALUES ('there')
INSERT INTO #myTemp VALUES ('from fox')
EndText
lnHandle = SQLStringConnect('Driver=SQL server;Trusted_Connection=Yes;Server=(local)')
SQLExec(m.lnHandle,m.lcSQL)
SQLExec(m.lnHandle,"SELECT * FROM #myTemp where myVal like '%fox%'",'myResult')
SQLDisconnect(m.lnHandle)
Select myResult
browse
It should be something related to MaxDb syntax or driver.
Cetin