Change DataWindow SQL statement at runtime?

Is it possible to change the DW SQL statement at runtime?

I want to retrieve records according to a retrieval argument if the user supply
it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
from the SQL statement).

Thanks.
Michael
0
Michael
7/30/1999 5:17:42 PM
sybase.powerbuilder.general 62418 articles. 14 followers. Follow

16 Replies
1368 Views

Similar Articles

[PageSpeed] 7

Michael, you can do what your asking two ways:

1) If there is not much data to retrieve, then retrieve it all then use the
SetFilter ( ) and Filter ( ) methods of the DataWindow control to narrow down
what's visible.  The nice thing about this technique is that you don't have to
go back to the database if the user wants to change the filter.

2) Set up your DataWindow object with a SQL statement with no WHERE clause as
the default.  If you need to add a WHERE clause at runtime, use:

dw_1.Object.DataWindow.Table.Select += ' WHERE {rest of WHERE clause}'

then do your Retrieve ( )



On Fri, 30 Jul 1999 13:17:42 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Is it possible to change the DW SQL statement at runtime?
>
>I want to retrieve records according to a retrieval argument if the user supply
>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>from the SQL statement).
>
>Thanks.
>Michael
0
Brad
7/30/1999 5:48:56 PM
Thanks a lot. I`ll go for the 2nd solution.

I was trying with:
dw_1.Object.DataWindow.Table.SQLSelect (.SQLSelect instead of .Select)
even though it seems to display the same SQL statement when I put it in a
message box, I could not assign anything to it.

".Select" works fine. BTW do you know what is the difference between .SQLSelect
and .Select?

Regards,
Michael


On Fri, 30 Jul 1999 13:48:56 -0400,
 in powersoft.public.powerbuilder.general
Brad Ashton <bashton@centric-corp.com> wrote: 
>Michael, you can do what your asking two ways:
>
>1) If there is not much data to retrieve, then retrieve it all then use the
>SetFilter ( ) and Filter ( ) methods of the DataWindow control to narrow down
>what's visible.  The nice thing about this technique is that you don't have to
>go back to the database if the user wants to change the filter.
>
>2) Set up your DataWindow object with a SQL statement with no WHERE clause as
>the default.  If you need to add a WHERE clause at runtime, use:
>
>dw_1.Object.DataWindow.Table.Select += ' WHERE {rest of WHERE clause}'
>
>then do your Retrieve ( )
>
>
>
>On Fri, 30 Jul 1999 13:17:42 -0400,
> in powersoft.public.powerbuilder.general
>Michael <mfong@ultramar.ca> wrote: 
>>Is it possible to change the DW SQL statement at runtime?
>>
>>I want to retrieve records according to a retrieval argument if the user
supply
>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>from the SQL statement).
>>
>>Thanks.
>>Michael
0
Michael
7/30/1999 6:04:29 PM
You can setup your SQL something like this:
SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
           "CUSTOMER"."AREACODE" ,
           "CUSTOMER"."TELEPHONE" ,
           "CUSTOMER"."FAXAREACODE" ,
           "CUSTOMER"."FAXNUMBER" ,
           "CUSTOMER"."EMAILADDRESS" 			    
          FROM "CUSTOMER"      
          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )  and
          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or   (
:telephone = '*') ) and
          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
(:customer_name = '*') ) 
Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
an argument it will retrieve everything.

On Fri, 30 Jul 1999 13:17:42 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Is it possible to change the DW SQL statement at runtime?
>
>I want to retrieve records according to a retrieval argument if the user supply
>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>from the SQL statement).
>
>Thanks.
>Michael
0
Mike
7/30/1999 6:05:56 PM
Could not make your idea work.

1. When I send only 2 arguments instead of 3, I get a message like expecting 3
arguments but got only 2.

2. Even if I send 3 arguments, it does not retrieve anything at all. Even tried
sending a NULL value.

Using PB6.5 and MS SQL Server 6.5

Michael
P.S. I think you can only supply more retrieval arguments than what the DW is
expecting, but not the other way around.


On Fri, 30 Jul 1999 14:05:56 -0400,
 in powersoft.public.powerbuilder.general
Mike <mdalsant@domdir.com> wrote: 
>You can setup your SQL something like this:
>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>           "CUSTOMER"."AREACODE" ,
>           "CUSTOMER"."TELEPHONE" ,
>           "CUSTOMER"."FAXAREACODE" ,
>           "CUSTOMER"."FAXNUMBER" ,
>           "CUSTOMER"."EMAILADDRESS" 			    
>          FROM "CUSTOMER"      
>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )  and
>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
(
>:telephone = '*') ) and
>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>(:customer_name = '*') ) 
>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
>an argument it will retrieve everything.
>
>On Fri, 30 Jul 1999 13:17:42 -0400,
> in powersoft.public.powerbuilder.general
>Michael <mfong@ultramar.ca> wrote: 
>>Is it possible to change the DW SQL statement at runtime?
>>
>>I want to retrieve records according to a retrieval argument if the user
supply
>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>from the SQL statement).
>>
>>Thanks.
>>Michael
0
Michael
7/30/1999 6:57:17 PM
Mike,
That's a flexible approach, but the ORs will cause a sequential scan of the
customer table.  Probably OK for a small table, but this will kill
performance for a really large table, even if you provide all 3 arguments.

Paul Horan
VCI
Springfield, MA

Mike <mdalsant@domdir.com> wrote in message
news:fSk6rYr2#GA.111@forums.sybase.com...
> You can setup your SQL something like this:
> SELECT   "CUSTOMER"."CUSTOMERNAME" ,
>            "CUSTOMER"."AREACODE" ,
>            "CUSTOMER"."TELEPHONE" ,
>            "CUSTOMER"."FAXAREACODE" ,
>            "CUSTOMER"."FAXNUMBER" ,
>            "CUSTOMER"."EMAILADDRESS"
>           FROM "CUSTOMER"
>           WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
and
>           (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone
or   (
> :telephone = '*') ) and
>           (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
> (:customer_name = '*') )
> Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
supply
> an argument it will retrieve everything.
>
> On Fri, 30 Jul 1999 13:17:42 -0400,
>  in powersoft.public.powerbuilder.general
> Michael <mfong@ultramar.ca> wrote:
> >Is it possible to change the DW SQL statement at runtime?
> >
> >I want to retrieve records according to a retrieval argument if the user
supply
> >it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
> >from the SQL statement).
> >
> >Thanks.
> >Michael


0
Paul
7/30/1999 7:46:35 PM
The PB 6.5 help files state:

"SQLSelect - The most recently executed SELECT statement. Setting this has no
effect. See Select in this table."

The key word here is "executed".  Since you can change the SELECT on the fly (as
you want to do) you could execute a SELECT, then change it, but the SQLSelect
attribute will still hold the one that you last executed.  I assume that if no
SELECT has been executed yet that PowerBuilder simply defaults this attribute
the SELECT coded into the DataWindow, which is why it appears to provide you
with the same data.

One word of caution - by setting the Select attribute directly as in the example
below, PowerBuilder DOES NOT VALIDATE the SQL.  So, if anything is wrong with
the SELECT you code (i.e. bad column/table name, etc) then you will only find
out when you go to Retrieve ( ).  

If you want the SQL to be validated before doing a Retrieve ( ), then use the
DataWindow SetSQLSelect ( ) method.  It will perform slower as it has to go to
the database and validate the SQL.  How much slower will depend on your
database, network, server, client, etc.


On Fri, 30 Jul 1999 14:04:29 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Thanks a lot. I`ll go for the 2nd solution.
>
>I was trying with:
>dw_1.Object.DataWindow.Table.SQLSelect (.SQLSelect instead of .Select)
>even though it seems to display the same SQL statement when I put it in a
>message box, I could not assign anything to it.
>
>".Select" works fine. BTW do you know what is the difference between .SQLSelect
>and .Select?
>
>Regards,
>Michael
>
>
>On Fri, 30 Jul 1999 13:48:56 -0400,
> in powersoft.public.powerbuilder.general
>Brad Ashton <bashton@centric-corp.com> wrote: 
>>Michael, you can do what your asking two ways:
>>
>>1) If there is not much data to retrieve, then retrieve it all then use the
>>SetFilter ( ) and Filter ( ) methods of the DataWindow control to narrow down
>>what's visible.  The nice thing about this technique is that you don't have to
>>go back to the database if the user wants to change the filter.
>>
>>2) Set up your DataWindow object with a SQL statement with no WHERE clause as
>>the default.  If you need to add a WHERE clause at runtime, use:
>>
>>dw_1.Object.DataWindow.Table.Select += ' WHERE {rest of WHERE clause}'
>>
>>then do your Retrieve ( )
>>
>>
>>
>>On Fri, 30 Jul 1999 13:17:42 -0400,
>> in powersoft.public.powerbuilder.general
>>Michael <mfong@ultramar.ca> wrote: 
>>>Is it possible to change the DW SQL statement at runtime?
>>>
>>>I want to retrieve records according to a retrieval argument if the user
>supply
>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>from the SQL statement).
>>>
>>>Thanks.
>>>Michael
0
Brad
7/30/1999 8:00:41 PM
The datawindow expects the number of arguments that it was designed
with. The argument list is stored outside of the SQL statement, and I
don't *think* it is modifiable through a Modify() function. If you
want to change the number of arguments the datawindow requires, I'd
expect the only way to do this would be to get the syntax of the
entire datawindow with Describe(), modify the datawindow to the way
you want it (find the argument list, change it...) and Create() a
datawindow object with the new syntax. 

I think what the other Mike might have been suggesting was to create
your SQL in a way that you can pass it dummy values for arguments so
that the SQL ignores it. In his case, the dummy value for cust_id was
0, so that if 0 was passed, the DBMS wouldn't limit the data set based
on CUSTOMER.CUST_ID.

Good luck,

Terry [TeamSybase] and Sequel the techno-kitten

On Fri, 30 Jul 1999 14:57:17 -0400, "Michael" <mfong@ultramar.ca>
wrote:

>Could not make your idea work.
>
>1. When I send only 2 arguments instead of 3, I get a message like expecting 3
>arguments but got only 2.
>
>2. Even if I send 3 arguments, it does not retrieve anything at all. Even tried
>sending a NULL value.
>
>Using PB6.5 and MS SQL Server 6.5
>
>Michael
>P.S. I think you can only supply more retrieval arguments than what the DW is
>expecting, but not the other way around.
>
>
>On Fri, 30 Jul 1999 14:05:56 -0400,
> in powersoft.public.powerbuilder.general
>Mike <mdalsant@domdir.com> wrote: 
>>You can setup your SQL something like this:
>>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>>           "CUSTOMER"."AREACODE" ,
>>           "CUSTOMER"."TELEPHONE" ,
>>           "CUSTOMER"."FAXAREACODE" ,
>>           "CUSTOMER"."FAXNUMBER" ,
>>           "CUSTOMER"."EMAILADDRESS" 			    
>>          FROM "CUSTOMER"      
>>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )  and
>>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
>(
>>:telephone = '*') ) and
>>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>>(:customer_name = '*') ) 
>>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
>>an argument it will retrieve everything.
>>
>>On Fri, 30 Jul 1999 13:17:42 -0400,
>> in powersoft.public.powerbuilder.general
>>Michael <mfong@ultramar.ca> wrote: 
>>>Is it possible to change the DW SQL statement at runtime?
>>>
>>>I want to retrieve records according to a retrieval argument if the user
>supply
>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>from the SQL statement).
>>>
>>>Thanks.
>>>Michael

0
terryvoth
7/30/1999 8:39:39 PM
I should have given you a bit more info,sorry, in your pfc_retrieve event you'll
have to validate your arguments such as:

if isnull(il_custid) then
	       il_custid = -0
end if
		
if len(trim(is_telephone)) = 0 or isnull(is_telephone) then
	 is_telephone = '*'
end if

if len(trim(is_name)) > 0 or isnull(is_telephone) then
	is_name = is_name + '%'
else
	is_name = '*'
end if

return this.retrieve(il_custid,is_telephone,is_name)

If I don't pass any values in my arguments my arguments will still have values
(as per above) so the retrieve would look like : retrieve(0,'*','*'), this which
would retrieve everything in the customer table. If I only wanted to search by
name then I would pass a value to my name field then validation as above and the
retrieve would look like : retrieve(0,'*','Smith%'). Hope this clarifies things
a bit more.

On Fri, 30 Jul 1999 14:57:17 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Could not make your idea work.
>
>1. When I send only 2 arguments instead of 3, I get a message like expecting 3
>arguments but got only 2.
>
>2. Even if I send 3 arguments, it does not retrieve anything at all. Even tried
>sending a NULL value.
>
>Using PB6.5 and MS SQL Server 6.5
>
>Michael
>P.S. I think you can only supply more retrieval arguments than what the DW is
>expecting, but not the other way around.
>
>
>On Fri, 30 Jul 1999 14:05:56 -0400,
> in powersoft.public.powerbuilder.general
>Mike <mdalsant@domdir.com> wrote: 
>>You can setup your SQL something like this:
>>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>>           "CUSTOMER"."AREACODE" ,
>>           "CUSTOMER"."TELEPHONE" ,
>>           "CUSTOMER"."FAXAREACODE" ,
>>           "CUSTOMER"."FAXNUMBER" ,
>>           "CUSTOMER"."EMAILADDRESS" 			    
>>          FROM "CUSTOMER"      
>>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
and
>>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
>(
>>:telephone = '*') ) and
>>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>>(:customer_name = '*') ) 
>>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
>>an argument it will retrieve everything.
>>
>>On Fri, 30 Jul 1999 13:17:42 -0400,
>> in powersoft.public.powerbuilder.general
>>Michael <mfong@ultramar.ca> wrote: 
>>>Is it possible to change the DW SQL statement at runtime?
>>>
>>>I want to retrieve records according to a retrieval argument if the user
>supply
>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>from the SQL statement).
>>>
>>>Thanks.
>>>Michael
0
Mike
7/30/1999 9:03:53 PM
The response has actually been quite good. I use this routine on a small table
(couple of thousand records) and the retrieval is instantaneous. I also use it
on a table of almost 400,000 rows ,it takes about 5 secs, but that's acceptable
considering the users don't need to retrieve from that table constantly. But
you're right, it will do a table scan.
On Fri, 3
 1999 15:46:35 -0400,
 in powersoft.public.powerbuilder.general
Paul Horan <paulh@NOSPAMmindspring.com> wrote: 
>Mike,
>That's a flexible approach, but the ORs will cause a sequential scan of the
>customer table.  Probably OK for a small table, but this will kill
>performance for a really large table, even if you provide all 3 arguments.
>
>Paul Horan
>VCI
>Springfield, MA
>
>Mike <mdalsant@domdir.com> wrote in message
>news:fSk6rYr2#GA.111@forums.sybase.com...
>> You can setup your SQL something like this:
>> SELECT   "CUSTOMER"."CUSTOMERNAME" ,
>>            "CUSTOMER"."AREACODE" ,
>>            "CUSTOMER"."TELEPHONE" ,
>>            "CUSTOMER"."FAXAREACODE" ,
>>            "CUSTOMER"."FAXNUMBER" ,
>>            "CUSTOMER"."EMAILADDRESS"
>>           FROM "CUSTOMER"
>>           WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
>and
>>           (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone
>or   (
>> :telephone = '*') ) and
>>           (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>> (:customer_name = '*') )
>> Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
>supply
>> an argument it will retrieve everything.
>>
>> On Fri, 30 Jul 1999 13:17:42 -0400,
>>  in powersoft.public.powerbuilder.general
>> Michael <mfong@ultramar.ca> wrote:
>> >Is it possible to change the DW SQL statement at runtime?
>> >
>> >I want to retrieve records according to a retrieval argument if the user
>supply
>> >it, otherwise I want to retrieve all records (i.e. removing the WHERE
>clause
>> >from the SQL statement).
>> >
>> >Thanks.
>> >Michael
>
>
0
Mike
7/30/1999 9:25:15 PM
Mike, I guess that it seems like a lot work to simply filter a list, and isn't
very intuative or understandable.  

What if you want to provide a window with a number of SLEs, one for each field
in your table, then have the code dynamically check what was entered and filter
the DataWindow?

With your method you have a heavy database hit each and every query because of
the size of your WHERE clause.  That may not be all that bad in a small
database, but once you get several hundred thousand records in the table most
databases will simply give up any pretense of using an index and do a table scan
anyway.

The goal should be to pass to the database only what it needs to return to you
the correct data.  Anything else will slow it down.


On Fri, 30 Jul 1999 17:03:53 -0400,
 in powersoft.public.powerbuilder.general
Mike <mdalsant@domdir.com> wrote: 
>I should have given you a bit more info,sorry, in your pfc_retrieve event
you'll
>have to validate your arguments such as:
>
>if isnull(il_custid) then
>	       il_custid = -0
>end if
>		
>if len(trim(is_telephone)) = 0 or isnull(is_telephone) then
>	 is_telephone = '*'
>end if
>
>if len(trim(is_name)) > 0 or isnull(is_telephone) then
>	is_name = is_name + '%'
>else
>	is_name = '*'
>end if
>
>return this.retrieve(il_custid,is_telephone,is_name)
>
>If I don't pass any values in my arguments my arguments will still have values
>(as per above) so the retrieve would look like : retrieve(0,'*','*'), this
which
>would retrieve everything in the customer table. If I only wanted to search by
>name then I would pass a value to my name field then validation as above and
the
>retrieve would look like : retrieve(0,'*','Smith%'). Hope this clarifies things
>a bit more.
>
>On Fri, 30 Jul 1999 14:57:17 -0400,
> in powersoft.public.powerbuilder.general
>Michael <mfong@ultramar.ca> wrote: 
>>Could not make your idea work.
>>
>>1. When I send only 2 arguments instead of 3, I get a message like expecting 3
>>arguments but got only 2.
>>
>>2. Even if I send 3 arguments, it does not retrieve anything at all. Even
tried
>>sending a NULL value.
>>
>>Using PB6.5 and MS SQL Server 6.5
>>
>>Michael
>>P.S. I think you can only supply more retrieval arguments than what the DW is
>>expecting, but not the other way around.
>>
>>
>>On Fri, 30 Jul 1999 14:05:56 -0400,
>> in powersoft.public.powerbuilder.general
>>Mike <mdalsant@domdir.com> wrote: 
>>>You can setup your SQL something like this:
>>>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>>>           "CUSTOMER"."AREACODE" ,
>>>           "CUSTOMER"."TELEPHONE" ,
>>>           "CUSTOMER"."FAXAREACODE" ,
>>>           "CUSTOMER"."FAXNUMBER" ,
>>>           "CUSTOMER"."EMAILADDRESS" 			    
>>>          FROM "CUSTOMER"      
>>>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
>and
>>>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
>>(
>>>:telephone = '*') ) and
>>>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>>>(:customer_name = '*') ) 
>>>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
supply
>>>an argument it will retrieve everything.
>>>
>>>On Fri, 30 Jul 1999 13:17:42 -0400,
>>> in powersoft.public.powerbuilder.general
>>>Michael <mfong@ultramar.ca> wrote: 
>>>>Is it possible to change the DW SQL statement at runtime?
>>>>
>>>>I want to retrieve records according to a retrieval argument if the user
>>supply
>>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>>from the SQL statement).
>>>>
>>>>Thanks.
>>>>Michael
0
Brad
7/30/1999 9:47:39 PM
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
A simple and worked out way goes like this - what I tried.
<p>Create the dw with the basic SQL (typically without any where clause)
which you need as the SQL to build all the where clause at any time. Now
before the first retrieval store the dw sql in an instance variable. typically
you could use either dw.Descibe("DataWindow.Table.Select") and get the
sql stored in LS_ORIGINAL_SQL.
<p>Now you have the base SQL available at any time will not bother you.
At any instance where you need to retrieve with any number or specific
SQL create the string variable - only the WHERE clause, typically " WHERE
CUST_ID = " + String(li_no) + " AND ..." so on... in&nbsp; ls_where_clause
variable. At any time where you need to&nbsp; change the where criteria,
just change this variable and do a dw.Modify("DataWindow.Table.Select=
' " + LS_ORIGINAL_SQL + ls_where_clause + " '") and do the Retrieve().
No need to pass the args!
<p>Note that if your base dw sql uses the '~' characters&nbsp; in table
names and column names, then you have to take care of that while building
the where clause.
<p>All the best.
<p>Suresh
<p>Michael wrote:
<blockquote TYPE=CITE>Is it possible to change the DW SQL statement at
runtime?
<p>I want to retrieve records according to a retrieval argument if the
user supply
<br>it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
<br>from the SQL statement).
<p>Thanks.
<br>Michael</blockquote>
</html>

0
Suresh
7/30/1999 10:13:35 PM
Brad Ashton <bashton@centric-corp.com> wrote in message
news:ZXp3kUt2#GA.302@forums.sybase.com...
> What if you want to provide a window with a number of SLEs, one for each
field
> in your table,

No, no, not SLEs again :-))

0
Philip
7/30/1999 10:18:52 PM
Michael wrote:
> 
> Is it possible to change the DW SQL statement at runtime?
> 
> I want to retrieve records according to a retrieval argument if the user supply
> it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
> from the SQL statement).
> 
> Thanks.
> Michael

Michael,

If I understand it correctly, what you need is 

1) a datawindow in query mode that accepts user's input of the retrieval
argument

and 2) another datawindow, shared data with the first one, that display
the retrieved data.

HTH,

-- 
Lijun

=======================================================
Lijun Yang  mailto:Lijun.Yang@bigfoot.com
=======================================================
0
Lijun
7/31/1999 4:37:59 PM
Thanks Mike.

I misunderstood your initial posting. I was concentrating on the "or
(:telephone = '*')" and "or (:customer_name = '*')" and thought that "or
 :cust_id =  -0)" was a mistype. I was wondering how that thing would solve
my problem :)

BTW is that a minus zero (-0) or a zero (0)? I would think it is a zero, but
then in a later posting by you, you put minus zero again.

puzzled :)
Michael


Mike <mdalsant@domdir.com> wrote in message
news:mFAcH8s2#GA.339@forums.sybase.com...
> I should have given you a bit more info,sorry, in your pfc_retrieve event
you'll
> have to validate your arguments such as:
>
> if isnull(il_custid) then
>        il_custid = -0
> end if
>
> if len(trim(is_telephone)) = 0 or isnull(is_telephone) then
> is_telephone = '*'
> end if
>
> if len(trim(is_name)) > 0 or isnull(is_telephone) then
> is_name = is_name + '%'
> else
> is_name = '*'
> end if
>
> return this.retrieve(il_custid,is_telephone,is_name)
>
> If I don't pass any values in my arguments my arguments will still have
values
> (as per above) so the retrieve would look like : retrieve(0,'*','*'), this
which
> would retrieve everything in the customer table. If I only wanted to
search by
> name then I would pass a value to my name field then validation as above
and the
> retrieve would look like : retrieve(0,'*','Smith%'). Hope this clarifies
things
> a bit more.
>
> On Fri, 30 Jul 1999 14:57:17 -0400,
>  in powersoft.public.powerbuilder.general
> Michael <mfong@ultramar.ca> wrote:
> >Could not make your idea work.
> >
> >1. When I send only 2 arguments instead of 3, I get a message like
expecting 3
> >arguments but got only 2.
> >
> >2. Even if I send 3 arguments, it does not retrieve anything at all. Even
tried
> >sending a NULL value.
> >
> >Using PB6.5 and MS SQL Server 6.5
> >
> >Michael
> >P.S. I think you can only supply more retrieval arguments than what the
DW is
> >expecting, but not the other way around.
> >
> >
> >On Fri, 30 Jul 1999 14:05:56 -0400,
> > in powersoft.public.powerbuilder.general
> >Mike <mdalsant@domdir.com> wrote:
> >>You can setup your SQL something like this:
> >>SELECT   "CUSTOMER"."CUSTOMERNAME" ,
> >>           "CUSTOMER"."AREACODE" ,
> >>           "CUSTOMER"."TELEPHONE" ,
> >>           "CUSTOMER"."FAXAREACODE" ,
> >>           "CUSTOMER"."FAXNUMBER" ,
> >>           "CUSTOMER"."EMAILADDRESS"
> >>          FROM "CUSTOMER"
> >>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id
=  -0) )
> and
> >>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") =
:telephone or
> >(
> >>:telephone = '*') ) and
> >>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name)
or
> >>(:customer_name = '*') )
> >>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
supply
> >>an argument it will retrieve everything.
> >>
> >>On Fri, 30 Jul 1999 13:17:42 -0400,
> >> in powersoft.public.powerbuilder.general
> >>Michael <mfong@ultramar.ca> wrote:
> >>>Is it possible to change the DW SQL statement at runtime?
> >>>
> >>>I want to retrieve records according to a retrieval argument if the
user
> >supply
> >>>it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
> >>>from the SQL statement).
> >>>
> >>>Thanks.
> >>>Michael


0
Michael
7/31/1999 7:47:57 PM
What if instead of
("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
we have
( ( :cust_id =  -0) or "CUSTOMER"."CUST_ID" = :cust_id )

Any difference in table scan?

Michael




Paul Horan <paulh@NOSPAMmindspring.com> wrote in message
news:71ydzMs2#GA.302@forums.sybase.com...
> Mike,
> That's a flexible approach, but the ORs will cause a sequential scan of
the
> customer table.  Probably OK for a small table, but this will kill
> performance for a really large table, even if you provide all 3 arguments.
>
> Paul Horan
> VCI
> Springfield, MA
>
> Mike <mdalsant@domdir.com> wrote in message
> news:fSk6rYr2#GA.111@forums.sybase.com...
> > You can setup your SQL something like this:
> > SELECT   "CUSTOMER"."CUSTOMERNAME" ,
> >            "CUSTOMER"."AREACODE" ,
> >            "CUSTOMER"."TELEPHONE" ,
> >            "CUSTOMER"."FAXAREACODE" ,
> >            "CUSTOMER"."FAXNUMBER" ,
> >            "CUSTOMER"."EMAILADDRESS"
> >           FROM "CUSTOMER"
> >           WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id
=  -0) )
> and
> >           (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") =
:telephone
> or   (
> > :telephone = '*') ) and
> >           (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name)
or
> > (:customer_name = '*') )
> > Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
> supply
> > an argument it will retrieve everything.
> >
> > On Fri, 30 Jul 1999 13:17:42 -0400,
> >  in powersoft.public.powerbuilder.general
> > Michael <mfong@ultramar.ca> wrote:
> > >Is it possible to change the DW SQL statement at runtime?
> > >
> > >I want to retrieve records according to a retrieval argument if the
user
> supply
> > >it, otherwise I want to retrieve all records (i.e. removing the WHERE
> clause
> > >from the SQL statement).
> > >
> > >Thanks.
> > >Michael
>
>


0
Michael
7/31/1999 7:50:28 PM
Errr... I think you misunderstood my initial posting

Say I have a DropDownListBox with department names. (the retrival argument)
If the user select a deparment name, then I'll retrieve data for that
particular department.
If the user does not select any department, then I'll retrieve data for ALL
departments.

The problem was the "WHERE" clause.

Thanks for your posting.
Michael


Lijun Yang <Lijun.Yang@bigfoot.com> wrote in message
news:37A32667.80F3DD2E@bigfoot.com...
> Michael wrote:
> >
> > Is it possible to change the DW SQL statement at runtime?
> >
> > I want to retrieve records according to a retrieval argument if the user
supply
> > it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
> > from the SQL statement).
> >
> > Thanks.
> > Michael
>
> Michael,
>
> If I understand it correctly, what you need is
>
> 1) a datawindow in query mode that accepts user's input of the retrieval
> argument
>
> and 2) another datawindow, shared data with the first one, that display
> the retrieved data.
>
> HTH,
>
> --
> Lijun
>
> =======================================================
> Lijun Yang  mailto:Lijun.Yang@bigfoot.com
> =======================================================


0
Michael
7/31/1999 7:55:26 PM
Reply:

Similar Artilces:

changing datawindow connection is breaking my datawindow SQL
Hi I have had to move a number of datawindows from Oracle 9i running on a windows 2003 to Oracle 9i on AIX 4.3.3 (which is also on a private network, and requires a VPN to connect to). In the datawindow designer, I have also had to change the database interface from O90 Oracle 9i to an ODBC datasource (because I was not able to connect). In doing so, I have had to graphically re-create all the DW SQL again because I get this error: Table "XYZ" has no columns, possible invalid table format. Do you want to continue in graphic mode? If I select "NO", the SQL is...

Top Statement and INDEX= Statement in Datawindow SQL Painter
Hi, Is there any way to put TOP and INDEX statement into Sql definition in Datawindow painter in graphical view. I know that I can go to "Convert to Sytax" but I want to learn if there is an option like "DISTINCT" in painter. SQL Example: Select TOP 100 Name,Surname FROM Names (INDEX = INX_name) Thanks for all Ali 'Top 100' you can do by making the first item in the select list a computed column TOP 100 Name I can't think of a way of doing Index. I never use graphical mode for anything other than very simple queries (and usually no...

Sorting Gridview by changing SQL statement
Hi, I need to sort the datagrid by the selected heading - Sub SortCommand is called and I want an SQL ORDER BY variable:1 Sub SortCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) 2 comm = New SqlCommand("SELECT Artist, Album, [My Rating], [Listened Amount], Art FROM Music ORDER BY e.SortExpression", conn) 3 4 open_connection() 5 End Sub   Try this: Sub SortCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)2        &n...

General SQL statement?
Once I have a table in SQLAnywhere 5.5.04, is there any way to generate a SQL statement that would have created it? Someone else needs to recreate the structure of my table and I'd like to send them a SQL statement that they could use to do so. One way to do it is to (for example, you need foo_table TABLE): 1) unload database (can be done from Sybase Central) into foo.sql 2) copy "CREATE TABLE foo_table" command where you need it... Petar Rick Charnes wrote: > > Once I have a table in SQLAnywhere 5.5.04, is there any way to generate > a SQL stateme...

SQL statement in datawindow
In my Datawindow painter, I would like to use "LIKE" in the where clause of my SQL SELECT STATEMENT, but it doesn't seem to show the correct results. I want to show the results from the database where the input (a retireval argument) matches anything that's entered close to that entry. I tried something like this: ie. SELECT name, address, city, province, postal_code WHERE name like '%(:name)%'; How can I use LIKE in the WHERE clause where string I am trying to match is an input retrieval argument? where name like '%' + :name + '%&#...

Help! How to modify the sql select statement for a nested datawindow inside a composite datawindow?
Help! How to modify the sql select statement for a nested datawindow inside a composite datawindow? Would the following work? dw_nest.Object.DataWindow.Table.Select="select employee_id from ..." Please help. jungan@aboex.com GetChild () ??? "Jun Gan" <jungan@aboex.com> wrote in message news:40e48313$1@forums-2-dub... > Help! > > How to modify the sql select statement for a nested datawindow inside a > composite datawindow? > > Would the following work? > > dw_nest.Object.DataWindow.Table.Select="select e...

Change datawindow SQL
Hello all, Is is possible to change SQL of datawindow in run time even if the result set would be different? In another word, could I change the table from which I retrieve data in run time? Thanks Yes you can. Take a look at the table.select property for the DataWindow object and the modify function. You can also use the SetSQLSelect function. The DWSyntax utility that comes with PowerBuilder lets you easily see what DataWindow object properties you can access and modify. Regards, Dave Fish [Team Powersoft] On Sun, 03 Jan 1999 08:45:49 -0800, Yuko <Miracle_mop@msn.com...

SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help  it's in selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'" if i delete the condition the " ELSE " appear. otherwise it's not appear whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...

Getting SQL statement from Datawindow
Hi! Is there a way to get the exact SQL statement that will be generated when calling dw.Update()? dw.GetSQLSelect() returns the statment in this form ... SELECT xx, yy FROM ... I am trying to get the statement in this form. UPDATE xx SET .... INSERT INTO XXX(xxx) VALUES(xx).... Thanks, Geoff Have a look at sqlPreview event. On Wed, 16 Jul 2003 15:52:54 +0900, in powersoft.public.powerbuilder.datawindow Geoffrey Sy <gsy@alliance.com.ph> wrote: >Hi! > >Is there a way to get the exact SQL statement that will be generated when >calling d...

SQL Statement of DDDW not changing
Peace be with you!!! I have a module that requires a DDDW sql statement to changed depending on the type of document selected. I was using SetSQLStatement to modify the DDDW statement, however it is not changing as I set it. The DDDW statement contains only one column and one reference table. Below is the sample codes I did to do this: Original DDDW Statement: ls_SQL = "Select CADV_NO As REF_DOCNO From CASH_ADVANCE_VOUCHER" Document A ls_SQL = "Select RBV_NO As REF_DOCNO From REBATE" Document B ls_SQL = "Select PFCS_NO As REF_DOCNO Fr...

Datawindow from arbitrary SQL statement
Hi - I have an SQL statement which executes fine on my host, the results of which I would like displayed (read-only) in a datawindow. I can't seem to define a datawindow that has as its syntax arbitrary SQL, since the DW painter seems to want to be able to "understand" it ... that is, my statement uses "WITH" clauses, and UNIONs, etc to the extent that is not graphically representable. Can I just tell the dw painter "never mind, just run the statement as is and help me layout the results " ? Thanks Choose Convert to Syntax, then try to ...

nested SQL statements in datawindows
We are developing a N-Tier application for the internet using EA Studio 3.0 and are having a SQL conflict. I am curious if there is a problem with using a nested sql statement, like th following, as data source SQL for a datawindow. Now the reason why this is distressing, is due to the fact that as a sql query I get the desired results, as well as during a HTML datawindow preview. What seems to happen is when the SQL statement is nested the retrieval arguments are no longer picked up and read properly. SELECT table1.name, table1.fname, ...

Changed DataWindow in SQL syntax!
I have problem with data window. When I create new DW and save this, it seems OK. Then if I open it DW, and click on SQL (data source) change something in syntax and then save this DW it seems ok too. Problem: if I have to open this DW and click on SQL the system goes into a loop and I cannot get out of the field either! The only way out is to KILL the application with the Task Manager. Has anyone seen this problem? I need help!! PB 7.0 build 5031, MS NT 4.0 and ASE 11.5.1. Thanks in advance, Ljiljana Tomic! Start by upgrading to release C3 or C4. The origi...

How do I pass an SQL statement to a datawindow?
Hello, I am creating a custom query module for my application. In that module I build the SQL statement (SELECT column1 FROM table1...), in my application, based on the criteria the user selects. I now need to display the records and I figured I could do that by passing the SQL to the datawindow and have the datawindow do the retrieval and formatting of the columns but I couldn't find any way to do that. Anyone have any ideas or suggestions? Thanks!! Take a look at SyntaxFromSQL function. Look at example 2 specifically Hope this helps Anil Lou wrote: >...

How do I pass an SQL statement to a datawindow?
Hello, I am creating a custom query module for my application. In that module I build the SQL statement (SELECT column1 FROM table1...), in my application, based on the criteria the user selects. I now need to display the records and I figured I could do that by passing the SQL to the datawindow and have the datawindow do the retrieval and formatting of the columns but I couldn't find any way to do that. Anyone have any ideas or suggestions? Thanks!! Take a look at SyntaxFromSQL function. Look at example 2 specifically Hope this helps Anil Lou wrote: >...

Dynamic changing of sql statement
Hi all, I have a window with a sle and a dw on it. When our users enter search criteria in the sle, we change dynamically the sql statement with getsqlselect and setsql statements. But when our users enter a ' (apostrophe) in the search criteria we end up having a dw error : sqlstate 42000 syntax error or access violation. I understand the sql uses ' itself, but how do i build a workaround to this problem ? TIA John Hi John, this depends on the RDBMS used. You have to escape the apostrophe with an appropriate escape character. I believe the apostrophe itself...

add sql statement on datawindow
a datawindow is very close to a database table. so i think it's useful to use sql statement to get data from a datawindow, instead of select data to a datawindow then save data to a database temp table and then use sql to get data from it. especially for n tie program,it will be more usefull hobby I don't really understand this request. I can't think of any reason to retrieve into a datawindow, then save the retrieved data back into a database temp table, and then use embedded SQL to re-retrieve the data back into the application. That's three trips across the ...

How do I pass an SQL statement to a datawindow?
Hello, I am creating a custom query module for my application. In that module I build the SQL statement (SELECT column1 FROM table1...), in my application, based on the criteria the user selects. I now need to display the records and I figured I could do that by passing the SQL to the datawindow and have the datawindow do the retrieval and formatting of the columns but I couldn't find any way to do that. Anyone have any ideas or suggestions? Thanks!! Take a look at SyntaxFromSQL function. Look at example 2 specifically Hope this helps Anil Lou wrote: >...

Changing sql in child datawindow
I have a child datawindow that has some retrieval arguments that I would like to change the sql dynamically. The SetSQLSelect does not work with that. I did set the transobject prior to the SetSQLSelect, but it doesn't work and the help text also says that it will not with retrieval args. Is there a property or another way to do this? thanks in advance, Jim Use the GetChild function to get a reference to the dropdown. Then you can use the describe and modify functions datawindowchild ldwc string ls_sql GetChild(<column_name>, ldwc) ls_sql = ldwc.Describe("...

Dynamic datawindow sql change
Whats the best way to change the syntax of a datawindow's sql if the datawindow has retreival args? -- Jim Doughty No easy way, other than parsing through the code. ls_criteria = dw_generic.Describe("DataWindow.Syntax") and then look for 'arguments'. -- Terry Dykstra (TeamSybase) Canadian Forest Oil Ltd. Sybase Developer's Network: http://www.sybase.com/sdn Techwave 2000: http://www.sybase.com/techwave2000/ "James Doughty, Jr." <jdoughty@pyramid-sys.com> wrote in message news:8O94uDS3$GA.285@forums.sybase.com... > Whats t...

Problem while changing datawindow color at runtime
Hello I am using Pb6.5 Build 444 . In my application I am changing the window color and datawindow color at runtime according to some user settings in the ini file.All the editable columns in my datawindow have background color as transparent . When this column getfocus I see the the datawindow color which was at the runtime in that column. This color appears only at the bottom of the column area i.e the space left between the data and the column border. I tried using autosize height feature but during changing focus this introduces some amount of flicker where that column appear...

Changing DataWindow appearance shut down PowerBuilder
Hi All I use PB 8.0.1 Build 8004 on NT 4.0 SP 6 I have DataWindow in which I'd like change properties for some columns. When I click on it PowerBuilder shut down. Clicking on text or computed field works OK. Thanks in advance Przemek Delete the registery key located at HKEY_CURRENT_USER/Software/Sybase/powerbuilder Then try. If problem continues try to save the dw with another name & try. Let me know. Good Luck Arun "Przemyslaw Kruczek" <przemyslaw_kruczek@rzesa.rzeszow.pl> wrote in message news:hFxCd36hBHA.375@forums.sybase.com... > Hi ...

How to change SQL select for nested datawindow?
How to change SQL select for nested datawindow? If I were attempting this, my first two attempts would be: <DW Control Name>.Object.<Reportname>.DataObject='<dataobject name>' ....or... <DW Control Name>.Object.<Reportname>.Object.DataWindow.Table.Select='<string containing the SQL select source of the DW>' In article <ndXD$iIy#GA.155@forums.sybase.com>, yuri_d@asainfo.online.ee says... > How to change SQL select for nested datawindow? > > > > -- John Strano [TeamSybase] Yuri, I post...

newbie: change the datawindow style at runtime?
I want to allow the user to click a button and change the datawindow style. So, if data window is showing as freeform, then the user clicks the button and the datawindow changes to grid style. This would allow the user to see more than one record at a time using the grid style and edit a single record using the freeform style. How can i do this? -- Thanks, Stephane Viau Ottawa, Canada A possible solution is to build all the datawindow objects you need and then change the DataObject proporty for the datawindow control to whatever datawindow you want, for example: Clic...

Web resources about - Change DataWindow SQL statement at runtime? - sybase.powerbuilder.general

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Intelligent Versus Surrogate Keys
Should I use business columns as primary key fields for tables in thedatabase, or generate artificial primary key values? When business-related ...

chevy volt
ProEXR File Description =Attributes= channels (chlist) compression (compression): Zip dataWindow (box2i): [0, 0, 5119, 2475] displayWindow (box2i): ...

Enable - A comprehensive solution for the localization of PowerBuilder applications Untitled Document ...
Enable is localization/ translation tool used by PowerBuilder developers to make their applications multilingual.Enable is a powerful tool that ...

comp.lang.clipper.visual-objects - Google Groups
comp.lang.clipper.visual-objects 1-20 von 33006 Themen werden angezeigt Integrity of a file marcosm...@gmail.com 09:35 Combobox value Lu 08:12 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
Developers can choose Win32 or newer style .Net development

PowerToTheBuilder - PowerBuilder UI Controls
HOME - COMPONENTS - PB UI DESIGNER - DOWNLOAD\PURCHASE - SUPPORT - FAQ - ABOUT Take Control of your UI with PB Ultimate Suite PowerToTheBuilder.com ...

Beautiful iPad Mini 3 Concept Reimagines Appleā€™s Tablet With iPhone 6 Design, Gold Color [Images]
The iPhone 6 may be grabbing all of the recent headlines due to its impending announcement, but spare a thought for those who prefer to stay ...

Java Networking - Real's HowTo
Real's HowTo Custom Search Java Language String and Number Applet AWT Swing Environment IO Javascript interaction JDBC Thread Networking JSP ...

Business Intelligence Products - Database Management, Data Warehousing & Mobility Software - Sybase Inc ...
Sybase offers a variety of Business Intelligence (BI) software like products for database management, data warehousing and mining, data integration ...

Resources last updated: 3/8/2016 7:28:23 AM