ADO sql joins with WinOle32

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" <lipkinrm29063@yahoo.com> ha scritto nel messaggio
news:81f28e87-3eaa-4abf-a6c6-63f6d009a25a@34g2000hsf.googlegroups.com...

> 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 ??

Did you try your query using another tool? Did it work?

EMG

--
EMAG Software Homepage: http://www.emagsoftware.it
The EMG's ZX-Spectrum Page: http://www.emagsoftware.it/spectrum
The Best of Spectrum Games: http://www.emagsoftware.it/tbosg
The EMG Music page: http://www.emagsoftware.it/emgmusic

Enrico

SQL Studio is what I user .. the origional query looked like this :

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 proj,
P_SPONSOR spon
WHERE (proj.PROJECTEID = spon.PROJECTEID)
AND (proj.PROJMGR = 'LIPKINRM' or spon.SPONSORID = 'LIPKINRM')


after I plugged it into Sql studio it looked like this :

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')


when I took it to my program .. I had to splice it up into a string
and it looks like my example above. It seems whenever I try a table(s)
join where I have to use aliases for the multiple tables ( e.g.
proj.Field, spon.FIELD ) .. winole32 just does not like the syntax
somehow .. I can not pin it down, however my initial thought are the
alias pointers with the period are causing come confusion of the SQL
command line.

I am at a loss here and this is a 'critical' sql statement for the
latest project I am working on and will be a REAL 'show stopper' if I
can not geth this to work.

Rick Lipkin






On Aug 6, 10:22*am, "Enrico Maria Giordano"
<e.m.giord...@emagsoftware.it> wrote:
> "Rick Lipkin" <lipkinrm29...@yahoo.com> ha scritto nel messaggionews:81f28e87-3eaa-4abf-a6c6-63f6d009a25a@34g2000hsf.googlegroups.com...
>
> > 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 ??
>
> Did you try your query using another tool? Did it work?
>
> EMG
>
> --
> EMAG Software Homepage: * *http://www.emagsoftware.it
> The EMG's ZX-Spectrum Page:http://www.emagsoftware.it/spectrum
> The Best of Spectrum Games:http://www.emagsoftware.it/tbosg
> The EMG Music page: * * * *http://www.emagsoftware.it/emgmusic

1) please describe "locks": you need an hard reset ? your application
exit or simply it doesn't give the result (but the hd light is on ? what
about cpu load ?)

2) remove the inner join and revert back to the WHERE clause

> I know the query works
3) I don't know ADO and how it needs to be called but did you try to
replace the query with a simple SELECT just to see that connection is
setup ?


To debug SQL query I use this method:
- start with a simple SELECT, one field, one table
- add one table and the join (repeat the step for every table)
- add the new table fields
- add the WHERE

Francesco

Francesco

When I run the query .. the application just locks up .. the two
tables are very small .. have less than 10 rows or so in each. I will
try to pare back the SQL query with 2 or three SELECT fields and try
the join .. again, my guess is that the SQL statement does not like
the alias pointer .. the


TRY
oRsProj:Open( cSQL, .....
CATCH oERR
...
END TRY


oRsProj:Open statement doesn't even get a chance to fail and return
control to the CATCH .. there is no fail error .. no run-time error ..
no nothing .. the application just locks up.

Rick Lipkin



On Aug 6, 11:03*am, FP <mc8647__nnoossppa...@mclink.it> wrote:
> 1) please describe "locks": you need an hard reset ? your application
> exit or simply it doesn't give the result (but the hd light is on ? what
> about cpu load ?)
>
> 2) remove the inner join and revert back to the WHERE clause
>
> *> I know the query works
> 3) I don't know ADO and how it needs to be called but did you try to
> replace the query with a simple SELECT just to see that connection is
> setup ?
>
> To debug SQL query I use this method:
> - start with a simple SELECT, one field, one table
> - add one table and the join (repeat the step for every table)
> - add the new table fields
> - add the WHERE
>
> Francesco

To All

I have pared down the SQL query to just a few fields with the same
UGLY results .. here is the query :

cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME,"
cSQL += "proj.PROJMGR, spon.PROJECTEID AS Expr1, spon.SPONSORID"
cSQL += " FROM PROJECT proj, P_SPONSOR spon"
cSQL += " WHERE (proj.PROJECTEID = spon.PROJECTEID)"
cSQL += " AND (proj.PROJMGR = 'LIPKINRM' or spon.SPONSORID =
'LIPKINRM')"

even tried this without the AS Expr1

cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME,"
cSQL += "proj.PROJMGR, spon.PROJECTEID, spon.SPONSORID"
cSQL += " FROM PROJECT proj, P_SPONSOR spon"
cSQL += " WHERE (proj.PROJECTEID = spon.PROJECTEID)"
cSQL += " AND (proj.PROJMGR = 'LIPKINRM' or spon.SPONSORID =
'LIPKINRM')"

and even like this

cSQL := "SELECT proj.PROJECTEID, proj.PROJNAME,"
cSQL += "proj.PROJMGR, spon.PROJECTEID AS Expr1, spon.SPONSORID"
cSQL += " FROM PROJECT proj, P_SPONSOR spon"
cSQL += " WHERE (proj.PROJECTEID = spon.PROJECTEID)"
cSQL += " AND (proj.PROJMGR = 'LIPKINRM' or Expr1 = 'LIPKINRM')"

The recordset is not even responding and the program never hist the
CATCH block .. you have to use the task mgr to kill the application.
Something more going on here and i think it is a Syntactical question
for one or the WinOle32 developers ?? ( ron ?? )

Rick Lipkin






On Aug 6, 11:30*am, Rick Lipkin <lipkinrm29...@yahoo.com> wrote:
> Francesco
>
> When I run the query .. the application just locks up .. the two
> tables are very small .. have less than 10 rows or so in each. I will
> try to pare back the SQL query with 2 or three SELECT fields and try
> the join .. again, my guess is that the SQL statement does not like
> the alias pointer .. the
>
> TRY
> * * oRsProj:Open( cSQL, .....
> CATCH oERR
> * *...
> END TRY
>
> oRsProj:Open statement doesn't even get a chance to fail and return
> control to the CATCH .. there is no fail error .. no run-time error ..
> no nothing .. the application just locks up.
>
> Rick Lipkin
>
> On Aug 6, 11:03*am, FP <mc8647__nnoossppa...@mclink.it> wrote:
>
>
>
> > 1) please describe "locks": you need an hard reset ? your application
> > exit or simply it doesn't give the result (but the hd light is on ? what
> > about cpu load ?)
>
> > 2) remove the inner join and revert back to the WHERE clause
>
> > *> I know the query works
> > 3) I don't know ADO and how it needs to be called but did you try to
> > replace the query with a simple SELECT just to see that connection is
> > setup ?
>
> > To debug SQL query I use this method:
> > - start with a simple SELECT, one field, one table
> > - add one table and the join (repeat the step for every table)
> > - add the new table fields
> > - add the WHERE
>
> > Francesco- Hide quoted text -
>
> - Show quoted text -

"Rick Lipkin" <lipkinrm29063@yahoo.com> ha scritto nel messaggio
news:1eb345df-36c2-4592-890f-8b30387e7c20@m73g2000hsh.googlegroups.com...

> It seems whenever I try a table(s) join where I have to
> use aliases for the multiple tables ( e.g. proj.Field,
> spon.FIELD ) .. winole32 just does not like the syntax
> somehow ..

No, ADO knows nothing about SQL. If the query (and the other parameters) is
correct then it should work with ADO too. Are you absolutely sure that the
query is correct?

EMG

--
EMAG Software Homepage: http://www.emagsoftware.it
The EMG's ZX-Spectrum Page: http://www.emagsoftware.it/spectrum
The Best of Spectrum Games: http://www.emagsoftware.it/tbosg
The EMG Music page: http://www.emagsoftware.it/emgmusic

"Rick Lipkin" <lipkinrm29063@yahoo.com> ha scritto nel messaggio
news:faf45b2b-12b6-433b-bf30-13871c922231@i76g2000hsf.googlegroups.com...

> oRsProj:Open statement doesn't even get a chance to fail and return
> control to the CATCH .. there is no fail error .. no run-time error ..
> no nothing .. the application just locks up.

Any chance to have a reduced and self-contained sample of the problem
(database included)?

EMG

--
EMAG Software Homepage: http://www.emagsoftware.it
The EMG's ZX-Spectrum Page: http://www.emagsoftware.it/spectrum
The Best of Spectrum Games: http://www.emagsoftware.it/tbosg
The EMG Music page: http://www.emagsoftware.it/emgmusic

Enrico

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()






On Aug 6, 2:00*pm, "Enrico Maria Giordano"
<e.m.giord...@emagsoftware.it> wrote:
> "Rick Lipkin" <lipkinrm29...@yahoo.com> ha scritto nel messaggionews:faf45b2b-12b6-433b-bf30-13871c922231@i76g2000hsf.googlegroups.com...
>
> > oRsProj:Open statement doesn't even get a chance to fail and return
> > control to the CATCH .. there is no fail error .. no run-time error ..
> > no nothing .. the application just locks up.
>
> Any chance to have a reduced and self-contained sample of the problem
> (database included)?
>
> EMG
>
> --
> EMAG Software Homepage: * *http://www.emagsoftware.it
> The EMG's ZX-Spectrum Page:http://www.emagsoftware.it/spectrum
> The Best of Spectrum Games:http://www.emagsoftware.it/tbosg
> The EMG Music page: * * * *http://www.emagsoftware.it/emgmusic