ADO Sql Query limitations ??

[Rick Lipkin]

To All

I have put together a simple sql query that joins two tables that I have cut and pasted out of MS SQL Studio.

I know the query works .. every time I try to execute this query, my computer locks up .. any ideas here ??

Rick Lipkin

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

cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME, proj.DESCRIP, proj.START_DATE, proj.END_DATE, "+;
"proj.EST_BUDGET, proj.FUNDED, proj.USEREID, proj.PROJMGR, "+;
"proj.REPORTING, proj.FREQUENCYEID, proj.FREQUENCY, proj.ROW_COLOR, proj.EMAIL, "+;
"proj.ACTIVE, proj.CONTRACT, proj.ENTRYBY, proj.ENTRYDATE, "+;
"proj.CREATEDBY, proj.CREATEDATE, proj.UPDATED, spon.PROJECTEID AS Expr1, spon.SPONSORID "+;
"FROM PROJECT AS proj INNER JOIN P_SPONSOR AS spon ON proj.PROJECTEID = spon.PROJECTEID "+;
"WHERE (proj.PROJMGR = 'LIPKINRM') OR (spon.SPONSORID = 'LIPKINRM')"

oRsProj := TOleAuto():New( "ADODB.Recordset" )
oRsProj:CursorType := 1 // opendkeyset
oRsProj:CursorLocation := 3 // local cache
oRsProj:LockType := 3 // lockoportunistic

TRY
oRsProj:Open( cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error in Opening PROJECT table" )
oDlg:End()
RETURN(.F.)
END TRY

[Rick Lipkin]

To All

I have this problem FIXED .. as it turned out .. it had nothing directly to do with the query, but I was displaying cSQL on a title bar for a wait dialog box .. see code :

It was the length of the variable cSQL that was being passed on the title line that 'hosed' everything up .. Until I placed a few msgbox's before and after did I realize that the Openup() function was not even being executed .. The program stopped at the wrapper dialog ..

I just could not believe it ..Thanks, the query ran as expected and returned all the rows it was supposed to !!!

Rick Lipkin

//-------

cLEVEL2 := "Y"

DO CASE
CASE xSUPER = 'Y'
cSQL := "SELECT * from PROJECT order by Projname"
CASE xPROJ = 'Y' .and. xSUPER <> 'Y'
IF cLEVEL2 = "N"
cSQL := "SELECT * from PROJECT order by Projname"
ELSE

cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME, proj.DESCRIP, proj.START_DATE, proj.END_DATE, "+;
"proj.EST_BUDGET, proj.FUNDED, proj.USEREID, proj.PROJMGR, "+;
"proj.REPORTING, proj.FREQUENCYEID, proj.FREQUENCY, proj.ROW_COLOR, proj.EMAIL, "+;
"proj.ACTIVE, proj.CONTRACT, proj.ENTRYBY, proj.ENTRYDATE, "+;
"proj.CREATEDBY, proj.CREATEDATE, proj.UPDATED, spon.PROJECTEID AS Expr1, spon.SPONSORID "+;
"FROM PROJECT AS proj INNER JOIN P_SPONSOR AS spon ON proj.PROJECTEID = spon.PROJECTEID "+;
"WHERE (proj.PROJMGR = 'LIPKINRM') OR (spon.SPONSORID = 'LIPKINRM')"

ENDIF



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

SysReFresh()

lOK := .F.

DEFINE DIALOG oDlg ;
FROM 5, 8 to 10, 75 ;
TITLE "Please be patient " ; //+cSQL ; <--- here
COLOR "N/W" ;
STYLE nOr( WS_POPUP,WS_CAPTION,WS_THICKFRAME )

cSAY := "Opening Initial PROJECT Data Recordset"

@ 1,13 SAY oSay var cSAY of oDLG COLOR "N/W"
oDLG:bStart := { | | lOK1 := _OpenUm(oDlg, cSQL, oWNDMDI, lFROMLINK, xPROJECTEID ) }



ACTIVATE DIALOG oDLG CENTERED

IF lOK1 = .F.
SysReFresh()
RETURN(NIL)
ENDIF

SysReFresh()

_PBrow(oWndMdi, cSQL, lFROMLINK, xPROJECTEID, cLEVEL2 )

SysReFresh()
Return(nil)

//----------------
Static Func _OpenUm(oDlg, cSQL, oWNDMDI, lFROMLINK, xPROJECTEID )

LOCAL oErr, SAYING, cSTRING, cFIND


IF cSQL = " "
cSQL := "SELECT * FROM PROJECT order by projname"
ENDIF

SysReFresh()

oRsProj := TOleAuto():New( "ADODB.Recordset" )
oRsProj:CursorType := 1 // opendkeyset
oRsProj:CursorLocation := 3 // local cache
oRsProj:LockType := 3 // lockoportunistic

TRY
oRsProj:Open( cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error in Opening PROJECT table" )
oDlg:End()
RETURN(.F.)
END TRY

SysReFresh()
oDLG:END()