SQL Does and Don'ts 
Author Message
 SQL Does and Don'ts

Or Prop:filter with the SQL() function, ie:

table{PROP:Filter} = 'SQL(event.date between <39>2002-01-02<39> AND <39>
2002-12-31<39>)'

Joe



Quote:
>> That sounds encouraging.  Where do I put the Prop:filter statement
>> and
> when
>> do I need to use it?
> The Prop:Filter is generated by the browse template if you put a
> filter on the browse. In this case, it's beter to change it by
> Prop:SqlFilter in the embed point "before opening view"

>> I've been experimenting today and I've found that for hand coding, I
>> get a major improvement in speed if I use prop:filter instead of SET.
>>  For example:

>> SET(KEY)
>> LOOP
>>  NEXT(FILE)   !first occurence generates a query that gets the ENTIRE
>>  FILE
> !
>>  IF ERRORCODE() THEN BREAK.
>> END

>> gets changed to:

>> FILE{PROP:SQL}='SELECT * FROM FILE WHERE .......whatever I want for a
> query'
>> LOOP
>>  NEXT(FILE)
>>  IF ERRORCODE() THEN BREAK.
>> END

>> I'm not sure where the data is going with Prop:sql but it seems to
>> work well.  Any problems with the above code?

> That's a way I do. The only thing you have to take care with this code
> is that the fields in your database must be declared exatly in the
> same order than in your dictionary :
> With the "select * from file", the back end return you all the fields
> of your table, in the order they are declared. Then Clarion try to
> read them in the order they are declared in the dictionary.
> I personaly prefer to declare a view in Clarion, in which I just
> declare the fields that I need, then use the Prop:Sql on the view.

> Example :

> ViewSql  VIEW(FILE)
>                    PROJECT(FIL:Number)
>                    PROJECT(FIL:Desciption)
>                END

>   CODE
>   OPEN(ViewSql)
>   ViewSql{Prop:Sql} = 'select Number,Description from File where ...'
>   LOOP
>       NEXT(ViewSql)
>       IF ERRORCODE() = 33
>            BREAK
>       ELSIF ERRORCODE()
>            MESSAGE('Error is ' & ERROR())
>            BREAK
>       END
>       ! Your stuff
>   END

> Now, Michael Gould send me a message that I copy here (I don't know
> why he don't post it to the group) about a problem that I personaly
> never had, but maybe you can ask him some precision

>> There are some inherent problems with the Clarion chain which hasn't
>> had
> any
>> overhauls since CW2 days.  We were doing SQL back then when I worked
>> for TopSpeed and the biggest problem we saw is that for every Do
>> RefreshWindow it would loop through that 3 times.

>> This causes extra work on the backedn.

> HTH,

> Guy



Wed, 09 Feb 2005 00:05:53 GMT  
 SQL Does and Don'ts
What does the Cowboy SQL templates get you, and HandyTools, for that matter,
that ABC generated browses don't give (without handcoding).  Anyone want to
give a quick comparison?  I haven't done any SQL yet, so I can't compare
myself.

Thanks,

John Farmer



Fri, 25 Feb 2005 13:23:26 GMT  
 SQL Does and Don'ts
Hi John,

On Mon, 26 Aug 2002 21:31:50 -0500, "John Farmer"

Quote:

>What does the Cowboy SQL templates get you, and HandyTools, for that matter,
>that ABC generated browses don't give (without handcoding).  Anyone want to

I'm not quite impartial here<g> but the Icetips Cowboy SQL templates
retrieve the data from the sql server by using sql statement - like
'Select myf.field1 from myfile' etc. where as ABC will use the Clarion
language statements and leave it up to the driver (ODBC or native SQL
driver) to convert it into SQL statements.  This also opens up
possibilities for you for more direct queries as you are dealing
directly with the sql syntax.

Best regards,

Arnr Baldvinsson
Icetips Software        
San Antonio, Texas, USA
www.icetips.com

ICQ:  113314380

Subscribe to information from Icetips.com:
http://www.icetips.com/getnotificationinfo.htm



Fri, 25 Feb 2005 13:24:10 GMT  
 SQL Does and Don'ts
Hello again,

In experimenting with SQL I'm having a problem understanding something.
Does a browse with no range limit or filter send a select statement which
gets all of the records in the table?  If so, wouldn't that be very
inefficient?  On the other hand, if not, then the other alternative that I
can think of is that it gets one or a few records at a time and therefore
issues a bunch of smaller sql statements.  That doesn't seem too efficient
either.

There's still a bit of confusion for me to work out but I'm getting there.
Any more advice and discussion would be appreciated.

Thanks,

-Troy



Fri, 25 Feb 2005 13:24:14 GMT  
 
 [ 20 post ]  Go to page: [1] [2]

 Relevant Pages 
 

 
Powered by phpBB® Forum Software