NetTalk Central

NetTalk Web Server => Web Server - Ask For Help => Topic started by: broche on July 30, 2014, 03:56:27 PM

Title: PROP:SQL
Post by: broche on July 30, 2014, 03:56:27 PM

NT 8.21
CL 9.0

Starting to use PROP:SQL a lot more for speed. For the GURU's out there.
\I can contruct and run the SUM statement I need and get the deired results.  I can't figure out how to get the result out and into a varible that I can then use?

In Postgres This works
SELECT SUM(docvalue) as "totalvalue" FROM custvf where readyforbilling='N';

SQLFile{PROP:SQL}='SELECT SUM(filecount) FROM custvf where readyforbilling=<39>Y<39> AND invoice=0 AND cussysid=' & cus:cussysid;

How do I get the result out?  I have a file called SQLFile in the dictionay acting as the 'Dummy' SQL file with a first field as type real.

Brian.



 

Title: Re: PROP:SQL
Post by: peterH on July 30, 2014, 08:16:51 PM

Brian,

This is really not a NT question and you may get better answers in the Clarion ng's.

But since you asked <g>:
After issuing the prop:sql stmt you must actually read the result. So something like this:

SQLFile{PROP:SQL}='SELECT SUM(filecount) FROM custvf where readyforbilling=<39>Y<39> AND invoice=0 AND cussysid=' & cus:cussysid
if error()
   ! Always do some error checking here
else
   loop   ! Read one row at a time from the result set (= the structure of your sqlfile)
      next(SQLFile)
      if error() then break.
      ! Now you've got some data so use it:
      YourFileCountVar = sqlfile.NameOfTheFirstField
   end
end

Each next() in the loop retrieves the next row from the result set. In this case there's only one row but now you've got a generic way of doing it. Also, inside the loop do an error check as shown (it may be more advanced but in the example above it catches EOF).

HTH
Peter

Title: Re: PROP:SQL
Post by: Bruce on July 31, 2014, 12:34:33 AM

You may also want to check out UltimateSQL from ClarionLive - it's free - and encapsulates a lot of this sort of code.

cheers
Bruce

Title: Re: PROP:SQL
Post by: Rene Simons on July 31, 2014, 02:50:36 AM

@Bruce,
Is this thread safe?
And how do i treat the resultset when there are more than one ocurrences in the resultset?
Or do I have to use a memory file with the sessionid in every record to "park the result?

Rene

Title: Re: PROP:SQL
Post by: Bruce on July 31, 2014, 04:55:41 AM

Sorry Rene - is _what_ ThreadSafe? UltimateSQL? Or Peter's suggestion?

>> And how do i treat the resultset when there are more than one ocurrences in the resultset?  Or do I have to use a memory file with the sessionid in every record to "park the result?

It all depends on the context, but typicallyyes, if you have a multi-record reply you want to cache then that's done via a Mem table with a session ID.

cheers
Bruce

Title: Re: PROP:SQL
Post by: peterH on July 31, 2014, 05:26:11 AM

FWIW: My SQLFile is threaded.

Peter

Title: Re: PROP:SQL
Post by: broche on July 31, 2014, 07:01:07 AM

Thanks Peter - you arfe correct, sorry will post there in future on non- NT.

Title: Re: PROP:SQL
Post by: Rene Simons on July 31, 2014, 01:07:28 PM

@Bruce,

Yes I ment UltimateSQL. Sorry if I wasn't clear on that.

René

Title: Re: PROP:SQL
Post by: Bruce on July 31, 2014, 10:13:17 PM

I haven't looked at the code Rene

cheers
Bruce

Title: Re: PROP:SQL
Post by: Rene Simons on August 01, 2014, 10:06:41 AM

Ok,
I'll ask the Clarionlive crew then.
René