SQL command can't find any records

classic Classic list List threaded Threaded
46 messages Options
123
Reply | Threaded
Open this post in threaded view
|

SQL command can't find any records

Bobb Menk
I'm new to SQL as well as to Lasso and can't seem to
make this do what I want.

I've been asked to take a SQL search statement and
create a web form that will let me put in a user ID
number and get back the data associated with that
number from an Oracle db.

Our Oracle expert constructed the SQL query which I've
put into the vSQL_Search variable. I can see it work
from within the SQL tab on the Lasso control panel, so
I have to assume the SQL syntax is correct.

So far I've turned on SQL Query rights in Lasso
security, enabled the database and all its tables, and
created a page that contains the following code,
(adapted from one of Miles' Lasso Tips).

The result is a page that returns nothing but a zero
from the found count statement.

I'm not sure where to go with this. Do I need an
explicit search command? What about a records tag in
the inline - if so, how do I format such a thing using
Lasso script - all the examples I've seen use the
square brackets notation. (BTW to test that theory I
converted the whole thing to square brackets and
included a records tag pair, but I still got zero
records).

Help greatly appreciated.

Bobb Menk
MIT Lincoln Laboratory Library

Here's my code:


<?LassoScript
var: 'vSQL_Search'=(string);

$vSQL_Search ='select a.user_key,
to_char(b.date_time_charged,\'mm/dd/yyyy hh:mi AM\')
date_time_charged, b.catalog_key, c.marc,
d.item_number, e.tag author, e1.tag title, f.id
barcode, g.policy_name from users a, charge b, catalog
c , callnum d, marc0 e, marc0 e1, item f, policy g
where a.user_key = b.user_key and a.id = \'17480\' and
b.catalog_key = c.catalog_key and b.catalog_key =
d.catalog_key and b.call_sequence = d.call_sequence
and c.marc = e.marc (+) and e.tag_number (+)  =
\'100\' and c.marc = e1.marc (+) and e1.tag_number (+)
= \'245\' and b.catalog_key = f.catalog_key and
b.call_sequence = f.call_sequence and b.copy_number =
f.copy_number and b.circulation_rule = g.policy_number
and g.policy_type = \'CIRC\'';

inline: -inlinename='iSql_Search', -Database='SIRSI*',
-Username='user', -password='pw', -sql=$vSQL_Search;
/inline;
Found_Count;
?>






Bobb
[hidden email]

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

Eric Landmann
Bobb Menk <[hidden email]> wrote on Wednesday, July 26, 2006:

>I'm new to SQL as well as to Lasso and can't seem to
>make this do what I want.
>
>I've been asked to take a SQL search statement and
>create a web form that will let me put in a user ID
>number and get back the data associated with that
>number from an Oracle db.
>
>Our Oracle expert constructed the SQL query which I've
>put into the vSQL_Search variable. I can see it work
>from within the SQL tab on the Lasso control panel, so
>I have to assume the SQL syntax is correct.
>
>So far I've turned on SQL Query rights in Lasso
>security, enabled the database and all its tables, and
>created a page that contains the following code,
>(adapted from one of Miles' Lasso Tips).
>
>The result is a page that returns nothing but a zero
>from the found count statement.
>
>I'm not sure where to go with this. Do I need an
>explicit search command? What about a records tag in
>the inline - if so, how do I format such a thing using
>Lasso script - all the examples I've seen use the
>square brackets notation. (BTW to test that theory I
>converted the whole thing to square brackets and
>included a records tag pair, but I still got zero
>records).
>
>Help greatly appreciated.
>
>Bobb Menk
>MIT Lincoln Laboratory Library
>
>Here's my code:
>
>
><?LassoScript
>var: 'vSQL_Search'=(string);
>
>$vSQL_Search ='select a.user_key,
>to_char(b.date_time_charged,\'mm/dd/yyyy hh:mi AM\')
>date_time_charged, b.catalog_key, c.marc,
>d.item_number, e.tag author, e1.tag title, f.id
>barcode, g.policy_name from users a, charge b, catalog
>c , callnum d, marc0 e, marc0 e1, item f, policy g
>where a.user_key = b.user_key and a.id = \'17480\' and
>b.catalog_key = c.catalog_key and b.catalog_key =
>d.catalog_key and b.call_sequence = d.call_sequence
>and c.marc = e.marc (+) and e.tag_number (+)  =
>\'100\' and c.marc = e1.marc (+) and e1.tag_number (+)
>= \'245\' and b.catalog_key = f.catalog_key and
>b.call_sequence = f.call_sequence and b.copy_number =
>f.copy_number and b.circulation_rule = g.policy_number
>and g.policy_type = \'CIRC\'';
>
>inline: -inlinename='iSql_Search', -Database='SIRSI*',
>-Username='user', -password='pw', -sql=$vSQL_Search;
>/inline;
>Found_Count;
>?>

Bobb:

Two things to try:

1. Run the same command in an SQL client. Any results or feeback?

2. Run the SQL command from Lasso Site Admin (Utility->SQL->Browser). Does this return any records or give any feedback?

--Eric

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

m i l e s-5
In reply to this post by Bobb Menk
Bobb,

You rang ?

Try this instead.  Minor modifications.

Ive cleaned it up a bit solely to show what was happening....you  
don't need to do this but at least it will show you where and why  
you're doing what you're doing.

If what you say is true that your security settings are allowing a  
sql statement against the datasource, then this statement as written  
should work.  If its not, then we'll need to tweak a bit....and see  
if something else isn't set right....

<?LassoScript

        var: 'vSQL_Search'=(string);

        $vSQL_Search = 'select a.user_key, to_char(b.date_time_charged,\"mm/
dd/yyyy hh:mi AM\") date_time_charged, ';
        $vSQL_Search += 'b.catalog_key, c.marc, d.item_number, e.tag  
author, e1.tag title, f.id barcode, ';
        $vSQL_Search += 'g.policy_name from users a, charge b,  
catalog c , callnum d, marc0 e, marc0 e1, item f, policy g ';
        $vSQL_Search += 'where a.user_key = b.user_key and a.id =  
\"17480\" and ';
        $vSQL_Search += 'b.catalog_key = c.catalog_key and ';
        $vSQL_Search += 'b.catalog_key = d.catalog_key and ';
        $vSQL_Search += 'b.call_sequence = d.call_sequence and ';
        $vSQL_Search += 'c.marc = e.marc (+) and ';
        $vSQL_Search += 'e.tag_number (+)  = \"100\" and ';
        $vSQL_Search += 'c.marc = e1.marc (+) and ';
        $vSQL_Search += 'e1.tag_number (+) = \"245\" and ';
        $vSQL_Search += 'b.catalog_key = f.catalog_key and ';
        $vSQL_Search += 'b.call_sequence = f.call_sequence and ';
        $vSQL_Search += 'b.copy_number = f.copy_number and ';
        $vSQL_Search += 'b.circulation_rule = g.policy_number and ';
        $vSQL_Search += 'g.policy_type = \"CIRC\"';

        inline: -inlinename='iSql_Search', -Database='SIRSI*', -
Username='user', -password='pw', -sql=$vSQL_Search;

        Found_Count;
       
        /inline;
?>






Bobb
[hidden email]

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker  
data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:
http://www.listsearch.com/lassotalk.lasso?manage


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

Fletcher Sandbeck
In reply to this post by Bobb Menk
On 2006-07-26 at 12:07 by [hidden email] (Bobb Menk):

>inline: -inlinename='iSql_Search', -Database='SIRSI*',
>-Username='user', -password='pw', -sql=$vSQL_Search;
>/inline;
>Found_Count;

The [Inline] ... [/Inline] tags create an environment in which you can access the search results.  You need to place the [Found_Count] tag before the closing [/Inline] tag in order for it to return the results you expect.  

Code like this will let you see if the SQL statement generated an error, what the found count was, and what fields and records Lasso is returning.

inline: -inlinename='iSql_Search', -Database='SIRSI*',
-Username='user', -password='pw', -sql=$vSQL_Search;
  'Error '; Error_Msg; '<hr />';
  'Count '; Found_Count; '<hr />';
  'Fields '; Field_Names; '<hr />';
  'Records '; Records_Array;
/inline;

[fletcher]
--
Fletcher Sandbeck                         [hidden email]
Director of Product Development       http://www.lassostudio.com
OmniPilot Software, Inc.                http://www.omnipilot.com

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

stevepiercy
In reply to this post by Bobb Menk
found_count does not work outside of inlines, named or not.  So make the following change, and one other to display any error message to help debug what is going on with the connection to the database:

inline: -inlinename='iSql_Search', -Database='SIRSI*', -Username='XXX', -password='XXX', -sql=$vSQL_Search;
    Found_Count;
    error_currenterror;
/inline;

I often set a variable to the value of found_count, and use that var elsewhere in the page.

--steve


On Wednesday, July 26, 2006, [hidden email] (Bobb Menk) pronounced:

>I'm new to SQL as well as to Lasso and can't seem to
>make this do what I want.
>
>I've been asked to take a SQL search statement and
>create a web form that will let me put in a user ID
>number and get back the data associated with that
>number from an Oracle db.
>
>Our Oracle expert constructed the SQL query which I've
>put into the vSQL_Search variable. I can see it work
>from within the SQL tab on the Lasso control panel, so
>I have to assume the SQL syntax is correct.
>
>So far I've turned on SQL Query rights in Lasso
>security, enabled the database and all its tables, and
>created a page that contains the following code,
>(adapted from one of Miles' Lasso Tips).
>
>The result is a page that returns nothing but a zero
>from the found count statement.
>
>I'm not sure where to go with this. Do I need an
>explicit search command? What about a records tag in
>the inline - if so, how do I format such a thing using
>Lasso script - all the examples I've seen use the
>square brackets notation. (BTW to test that theory I
>converted the whole thing to square brackets and
>included a records tag pair, but I still got zero
>records).
>
>Help greatly appreciated.
>
>Bobb Menk
>MIT Lincoln Laboratory Library
>
>Here's my code:
>
>
><?LassoScript
>var: 'vSQL_Search'=(string);
>
>$vSQL_Search ='select a.user_key,
>to_char(b.date_time_charged,\'mm/dd/yyyy hh:mi AM\')
>date_time_charged, b.catalog_key, c.marc,
>d.item_number, e.tag author, e1.tag title, f.id
>barcode, g.policy_name from users a, charge b, catalog
>c , callnum d, marc0 e, marc0 e1, item f, policy g
>where a.user_key = b.user_key and a.id = \'17480\' and
>b.catalog_key = c.catalog_key and b.catalog_key =
>d.catalog_key and b.call_sequence = d.call_sequence
>and c.marc = e.marc (+) and e.tag_number (+)  =
>\'100\' and c.marc = e1.marc (+) and e1.tag_number (+)
>= \'245\' and b.catalog_key = f.catalog_key and
>b.call_sequence = f.call_sequence and b.copy_number =
>f.copy_number and b.circulation_rule = g.policy_number
>and g.policy_type = \'CIRC\'';
>
>inline: -inlinename='iSql_Search', -Database='SIRSI*',
>-Username='user', -password='pw', -sql=$vSQL_Search;
>/inline;
>Found_Count;
>?>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

m i l e s-5
In reply to this post by Fletcher Sandbeck

Bobb,

To add to what Fletcher Suggested....its a good idea when using a SQL  
statement to add the following line:

inline: -inlinename='iSql_Search', -Database='SIRSI*',
-Username='user', -password='pw', -sql=$vSQL_Search;
   'Error '; Error_Msg; '<hr />';
   'Count '; Found_Count; '<hr />';
   'Fields '; Field_Names; '<hr />';
   'Records '; Records_Array; '<hr />';
   'SQL Statement: '; action_statement;  <-- added line|
/inline;


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

Bobb Menk
In reply to this post by Fletcher Sandbeck
Ok

I used Miles technique of breaking the variable into
multiple lines and concatenating them. That broke as
well until I used all escaped single quotes within the
SQL query instead of escaped double quotes.

Then I added Fletcher's tags for seeing the error
messages, and it works. Thanks to all!

Seeing my results leads to perhaps an inevitable
further question: any pointers to how to tackle
formatting the results array for the browser?

Thanks.

Bobb Menk


The working code now reads:

<?LassoScript
        var: 'vSQL_Search'=(string);

        $vSQL_Search = 'select a.user_key,
to_char(b.date_time_charged,\'mm/ dd/yyyy hh:mi AM\')
date_time_charged, ';
        $vSQL_Search += 'b.catalog_key, c.marc,
d.item_number, e.tag author, e1.tag title, f.id
barcode, ';
        $vSQL_Search += 'g.policy_name from users a,
charge b, catalog c , callnum d, marc0 e, marc0 e1,
item f, policy g ';
        $vSQL_Search += 'where a.user_key = b.user_key
and a.id =  \'17480\' and ';
        $vSQL_Search += 'b.catalog_key = c.catalog_key
and ';
        $vSQL_Search += 'b.catalog_key = d.catalog_key
and ';
        $vSQL_Search += 'b.call_sequence =
d.call_sequence and ';
        $vSQL_Search += 'c.marc = e.marc (+) and ';
        $vSQL_Search += 'e.tag_number (+)  = \'100\'
and ';
        $vSQL_Search += 'c.marc = e1.marc (+) and ';
        $vSQL_Search += 'e1.tag_number (+) = \'245\'
and ';
        $vSQL_Search += 'b.catalog_key = f.catalog_key
and ';
        $vSQL_Search += 'b.call_sequence =
f.call_sequence and ';
        $vSQL_Search += 'b.copy_number = f.copy_number
and ';
        $vSQL_Search += 'b.circulation_rule =
g.policy_number and ';
        $vSQL_Search += 'g.policy_type = \'CIRC\'';

       inline: -inlinename='iSql_Search',
-Database='SIRSI*', -Username='user', -password='pw',
-sql=$vSQL_Search;
  'Error '; Error_Msg; '<hr />';
  'Count '; Found_Count; '<hr />';
  'Fields '; Field_Names; '<hr />';
  'Records '; Records_Array;
/inline;
?>


--- Fletcher Sandbeck <[hidden email]> wrote:

> On 2006-07-26 at 12:07 by [hidden email] (Bobb
> Menk):
>
> >inline: -inlinename='iSql_Search',
> -Database='SIRSI*',
> >-Username='user', -password='pw',
> -sql=$vSQL_Search;
> >/inline;
> >Found_Count;
>
> The [Inline] ... [/Inline] tags create an
> environment in which you can access the search
> results.  You need to place the [Found_Count] tag
> before the closing [/Inline] tag in order for it to
> return the results you expect.  
>
> Code like this will let you see if the SQL statement
> generated an error, what the found count was, and
> what fields and records Lasso is returning.
>
> inline: -inlinename='iSql_Search',
> -Database='SIRSI*',
> -Username='user', -password='pw', -sql=$vSQL_Search;
>   'Error '; Error_Msg; '<hr />';
>   'Count '; Found_Count; '<hr />';
>   'Fields '; Field_Names; '<hr />';
>   'Records '; Records_Array;
> /inline;
>
> [fletcher]
> --
> Fletcher Sandbeck                        
> [hidden email]
> Director of Product Development      
> http://www.lassostudio.com
> OmniPilot Software, Inc.              
> http://www.omnipilot.com
>
> ------------------------------
> LASSO MASTER CLASS - Orlando, FL
> Following the FileMaker Developer Conference
> 2 Day intensive seminar: Learn the best way to get
> your FileMaker data on the web.
> http://www.briandunning.com/lasso-training/
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives:
> http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


Bobb
[hidden email]

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

stevepiercy
The Lasso Professional Language Guide has some simple examples under "Searching and Displaying Data".  You can modify it with HTML tables, rows and cells.

<table>
[records]
<tr>
    <td>[field:'foo']</td>
...
    <td>[field:'bar']</td>
</tr>
[/records]
<table>

--steve


On Wednesday, July 26, 2006, [hidden email] (Bobb Menk) pronounced:

>Seeing my results leads to perhaps an inevitable
>further question: any pointers to how to tackle
>formatting the results array for the browser?
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

Bobb Menk
Steve

Thanks for the pointer!

Quick question: am I doing this table with the square
brackets tagging inside my Lassoscript inline? Or
after it?

Bobb Menk



--- Steve Piercy - Web Site Builder
<[hidden email]> wrote:

> The Lasso Professional Language Guide has some
> simple examples under "Searching and Displaying
> Data".  You can modify it with HTML tables, rows and
> cells.
>
> <table>
> [records]
> <tr>
>     <td>[field:'foo']</td>
> ...
>     <td>[field:'bar']</td>
> </tr>
> [/records]
> <table>
>
> --steve
>
>
> On Wednesday, July 26, 2006, [hidden email]
> (Bobb Menk) pronounced:
>
> >Seeing my results leads to perhaps an inevitable
> >further question: any pointers to how to tackle
> >formatting the results array for the browser?
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> -- -- -- -- -- --
> Steve Piercy               Web Site Builder        
>      Soquel, CA
> <[hidden email]>                  
> <http://www.StevePiercy.com>
>
> ------------------------------
> LASSO MASTER CLASS - Orlando, FL
> Following the FileMaker Developer Conference
> 2 Day intensive seminar: Learn the best way to get
> your FileMaker data on the web.
> http://www.briandunning.com/lasso-training/
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives:
> http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


Bobb
[hidden email]

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

m i l e s-5
In reply to this post by Bobb Menk

Bobb,

inline: -Database='SIRSI*', -Username='user', -password='pw', -sql=
$vSQL_Search;
  var: 'v_sqlfc' = (found_count) \\ for later use further on down  
the page
        var: 'a_myarray' = (records_array); \\ stuffing the array into a  
variable.
/inline;

... further down in your logic ...

iterate: $a_myarray; var:'iArray';
        $iArray + '<BR>';
/iterate;

That will display the array values.  Also note that I removed the  
named inline statement, if you're going to pull values from the  
array, you're making lasso work twice for the same records that it  
would have produced.  Don't...its not necessary.  Moving right  
along...Im gathering that you'll actually want to USE those values  
from the array...in which case, you'll have to change that logic to  
something resembling:

iterate: $a_myarray; var:'iArray';
        $iArray->(get: x);
/iterate;

Where X is the array item of a particular line in the array.

Might I suggest reading up on arrays and their usage in the LP8.5  
Guide.  Well written.  Take a gander.

Im going to go out on a limb here and guess that records_array is not  
what you'll want to do here, because it grabs EVERYTHING from sql  
statement that you handed it.  Which is good and bad.  Because the  
array in this case will be packed with useless data in the order in  
which is was requested.  So look at the sequence of fields requested  
in the sql statement and X is the value of the field that you want.  
There is a situation where you'll want to 'roll your own array', and  
that's the "However" here.

If that's the case:

inline: -Database='SIRSI*', -Username='user', -password='pw', -sql=
$vSQL_Search;
        var: 'v_sqlfc' = (found_count) \\ for later use further on down  
the page
        var: 'a_myarray' = (array);        \\ stuffing the array into a  
variable.

        loop: ($v_sqlfc);
           var: 'a_passing'= (array);
           $a_passing->(insert:(field:'somefieldfromthesqlstatement'));
           $a_myarray->insert($a_passing);
        /loop;
/inline;

Now you pull the values as you see fit....

iterate: $a_myarray; var:'iArray';
        $iArray->(get: x);
/iterate;

M i l e s.

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

Bobb Menk
In reply to this post by m i l e s-5
One further follow up question now that I can see the
thing actually working:

I'm going to want to take one parameter out of the
variable and put it in a form so users can enter it
interactively.

Any suggestions as to how to remove this parameter:

$vSQL_Search += 'where a.user_key = b.user_key and
a.id =  \'17480\' and ';

into a form without breaking the query? The users will
be inputting a 5 digit string to replace the string
"17480" in my original example.

Thanks in advance.

Bobb Menk


--- m i l e s <[hidden email]> wrote:

> Bobb,
>
> You rang ?
>
> Try this instead.  Minor modifications.
>
> Ive cleaned it up a bit solely to show what was
> happening....you  
> don't need to do this but at least it will show you
> where and why  
> you're doing what you're doing.
>
> If what you say is true that your security settings
> are allowing a  
> sql statement against the datasource, then this
> statement as written  
> should work.  If its not, then we'll need to tweak a
> bit....and see  
> if something else isn't set right....
>
> <?LassoScript
>
> var: 'vSQL_Search'=(string);
>
> $vSQL_Search = 'select a.user_key,
> to_char(b.date_time_charged,\"mm/
> dd/yyyy hh:mi AM\") date_time_charged, ';
>         $vSQL_Search += 'b.catalog_key, c.marc,
> d.item_number, e.tag  
> author, e1.tag title, f.id barcode, ';
>         $vSQL_Search += 'g.policy_name from users a,
> charge b,  
> catalog c , callnum d, marc0 e, marc0 e1, item f,
> policy g ';
>         $vSQL_Search += 'where a.user_key =
> b.user_key and a.id =  
> \"17480\" and ';
>         $vSQL_Search += 'b.catalog_key =
> c.catalog_key and ';
>         $vSQL_Search += 'b.catalog_key =
> d.catalog_key and ';
>         $vSQL_Search += 'b.call_sequence =
> d.call_sequence and ';
>         $vSQL_Search += 'c.marc = e.marc (+) and ';
>         $vSQL_Search += 'e.tag_number (+)  = \"100\"
> and ';
>         $vSQL_Search += 'c.marc = e1.marc (+) and ';
>         $vSQL_Search += 'e1.tag_number (+) = \"245\"
> and ';
>         $vSQL_Search += 'b.catalog_key =
> f.catalog_key and ';
>         $vSQL_Search += 'b.call_sequence =
> f.call_sequence and ';
>         $vSQL_Search += 'b.copy_number =
> f.copy_number and ';
>         $vSQL_Search += 'b.circulation_rule =
> g.policy_number and ';
>         $vSQL_Search += 'g.policy_type = \"CIRC\"';
>
>         inline: -inlinename='iSql_Search',
> -Database='SIRSI*', -
> Username='user', -password='pw', -sql=$vSQL_Search;
>
> Found_Count;
>
>         /inline;
> ?>
>
>
>
>
>
>
> Bobb
> [hidden email]
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
> ------------------------------
> LASSO MASTER CLASS - Orlando, FL
> Following the FileMaker Developer Conference
> 2 Day intensive seminar: Learn the best way to get
> your FileMaker  
> data on the web.
> http://www.briandunning.com/lasso-training/
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives:
> http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage
>
>
> ------------------------------
> LASSO MASTER CLASS - Orlando, FL
> Following the FileMaker Developer Conference
> 2 Day intensive seminar: Learn the best way to get
> your FileMaker data on the web.
> http://www.briandunning.com/lasso-training/
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives:
> http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


Bobb
[hidden email]

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

m i l e s-5
In reply to this post by stevepiercy
Bobb,

Extending Steve's statements a bit...and my own....

And that's OUTSIDE the lassoscript tags...this is referred to as  
DISPLAY CODE

<table>
[records: -inlinename='iSql_Search']
<tr>
     <td>[field:'foo']</td>
...
     <td>[field:'bar']</td>
</tr>
[/records]
<table>

However if you are using a iterate statement to display some data as  
I implied earlier...

<table>
[iterate: $a_myarray, var: 'iArray']
<tr>
     <td>[$iArray->(get: '1')]</td>
</tr>
[/iterate]
<table>

M i l e s.



------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

m i l e s-5
In reply to this post by Bobb Menk
Bobb,

 >Any suggestions as to how to remove this parameter:
 >
 >$vSQL_Search += 'where a.user_key = b.user_key and a.id =  \'17480
\' and ';

Set a variable BEFORE the SQL statement that looks like this:

var: 'v_myvariable' = (encode_sql: (action_param:'e1'));

Where 'e1' is the form parameter that you are sending.  It is  
important that you encode the variable as I do above or you will  
invite a possible sql injection attack.

Then replace the variable in your sql statement like so:

$vSQL_Search += 'where a.user_key = b.user_key and a.id =  \' +  
($v_myvariable) + ' and ';

M i l e s.

------------------------------------------------------------
                 LASSO CONSULTANT FOR HIRE
------------------------------------------------------------
M i l e s                          [hidden email]
MagicMiles Software                         (415) 686 - 6164
http://www.lassoconsultant.com/   AIM/Yahoo/MSN:  magikmiles
------------------------------------------------------------



------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

-SQL double quotes or escaped single quotes

Jim Van Heule-2
MySQL accepts either " or ' for its starting and ending input and was  
wondering why it would be so bad to use double quotes instead of  
escaped single quotes so as not to interfere with Lasso's single  
quote requirements?

Something like this...

Inline:
        -Database='MyDatabase',
        -SQL='
                SELECT
                        *
                FROM
                        my_table
                WHERE
                        first_name = "' + (Encode_SQL:$Firstname) + '"
                AND
                        name_last = "' + (Encode_SQL:($LastName) + '"';
/Inline;

It just seems so much easier to read and write. Oh... and it works  
just fine.

Just curious what the big nasties are doing it this way?

--
Jim Van Heule
Heunox Corporation
(616) 844-0066

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: -SQL double quotes or escaped single quotes

Bil Corry
Jim Van Heule wrote:
> MySQL accepts either " or ' for its starting and ending input and was
> wondering why it would be so bad to use double quotes instead of escaped
> single quotes so as not to interfere with Lasso's single quote
> requirements?

There's nothing "bad" about using double-quotes to delimit the Lasso string, I've been doing it for years.  Here's what my sql queries typically look like:

 var:'sql' = "
        SELECT
                myColumn
        FROM
                myTable
        WHERE
                myColumn = 'abc'
        ";


> It just seems so much easier to read and write. Oh... and it works just
> fine.

It *is* easier to read/write.  Plus, it's easier to copy/paste queries into Navicat for testing.

 
> Just curious what the big nasties are doing it this way?

Nothing more/less than doing it any other way.  Just make sure you protect against sql injection.


- Bil


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: -SQL double quotes or escaped single quotes

Doug Gentry
Bil -

With this approach how would you format the use of a variable - such  
as $myvar - in place of 'abc'  ?

....Doug

On Jul 26, 2006, at 3:38 PM, Bil Corry wrote:

>
> There's nothing "bad" about using double-quotes to delimit the  
> Lasso string, I've been doing it for years.  Here's what my sql  
> queries typically look like:
>
> var:'sql' = "
> SELECT
> myColumn
> FROM
> myTable
> WHERE
> myColumn = 'abc'
> ";
>

---
Doug Gentry
Dynapolis & Southern Oregon University
p:  541-261-8501 / Toll Free: 888-490-0644
[hidden email]


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL command can't find any records

stevepiercy
In reply to this post by Bobb Menk
There are many ways to skin this cat, each of which is "better" in a given situation.

Generally I like to use an include statement within my logic to pull in a page that has mostly HTML peppered with Lasso code in [] square bracket syntax.

inline: -blah blah blah;
    if: found_count > 0;
        include: 'display_results.lasso';
    else;
        'no records found';
    /if;
/inline;

In the display_results.lasso page, you will find a complete HTML page, within which would be a snippet like this:

<table>
[records]
<tr>
    <td>[field:'foo']</td>
...
    <td>[field:'bar']</td>
</tr>
[/records]
<table>

You can use named inlines, too, if you like.

--steve


On Wednesday, July 26, 2006, [hidden email] (Bobb Menk) pronounced:

>Steve
>
>Thanks for the pointer!
>
>Quick question: am I doing this table with the square
>brackets tagging inside my Lassoscript inline? Or
>after it?
>
>Bobb Menk
>
>
>
>--- Steve Piercy - Web Site Builder
><[hidden email]> wrote:
>
>> The Lasso Professional Language Guide has some
>> simple examples under "Searching and Displaying
>> Data".  You can modify it with HTML tables, rows and
>> cells.
>>
>> <table>
>> [records]
>> <tr>
>>     <td>[field:'foo']</td>
>> ...
>>     <td>[field:'bar']</td>
>> </tr>
>> [/records]
>> <table>
>>
>> --steve
>>
>>
>> On Wednesday, July 26, 2006, [hidden email]
>> (Bobb Menk) pronounced:
>>
>> >Seeing my results leads to perhaps an inevitable
>> >further question: any pointers to how to tackle
>> >formatting the results array for the browser?
>> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>> -- -- -- -- -- --
>> Steve Piercy               Web Site Builder        
>>      Soquel, CA
>> <[hidden email]>                  
>> <http://www.StevePiercy.com>
>>
>> ------------------------------
>> LASSO MASTER CLASS - Orlando, FL
>> Following the FileMaker Developer Conference
>> 2 Day intensive seminar: Learn the best way to get
>> your FileMaker data on the web.
>> http://www.briandunning.com/lasso-training/
>>
>>
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives:
>> http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription:  
>> http://www.listsearch.com/lassotalk.lasso?manage
>>
>
>
>Bobb
>[hidden email]
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com 
>
>------------------------------
>LASSO MASTER CLASS - Orlando, FL
>Following the FileMaker Developer Conference
>2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
>http://www.briandunning.com/lasso-training/
>
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:  
>http://www.listsearch.com/lassotalk.lasso?manage
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: -SQL double quotes or escaped single quotes

Jim Van Heule-2
In reply to this post by Doug Gentry
> With this approach how would you format the use of a variable -  
> such as $myvar - in place of 'abc'  ?


It looks like Bil and I do the exact opposite with the same results.  
To answer Doug's question Bill would do it this way I suppose....

var:'sql' = "
        SELECT
                myColumn
        FROM
                myTable
        WHERE
                myColumn = '" + $myvar + "'
        ";

My way looks like this...

var:'sql' = '
        SELECT
                myColumn
        FROM
                myTable
        WHERE
                myColumn = "' + $myvar + '"
        ';

Basically we inverted quote types. I have heard from both OP and  
MySQL that there is a chance that double quotes may not be supported  
at some future date. That basically is the only reason I can see  
using the escaped single quote.

Either way works fine. I've been doing it my way for years as well. I  
was simply asking because I see all these posts with escape  
characters all over the place which I think can be very confusing for  
new people. Heck, it gets confusing to me when reading the code. :-)

--
Jim Van Heule
Heunox Corporation
(616) 844-0066


On Jul 26, 2006, at 6:48 PM, Doug Gentry wrote:

> Bil -
>
> With this approach how would you format the use of a variable -  
> such as $myvar - in place of 'abc'  ?
>
> ....Doug
>
> On Jul 26, 2006, at 3:38 PM, Bil Corry wrote:
>
>>
>> There's nothing "bad" about using double-quotes to delimit the  
>> Lasso string, I've been doing it for years.  Here's what my sql  
>> queries typically look like:
>>
>> var:'sql' = "
>> SELECT
>> myColumn
>> FROM
>> myTable
>> WHERE
>> myColumn = 'abc'
>> ";
>>
>
> ---
> Doug Gentry
> Dynapolis & Southern Oregon University
> p:  541-261-8501 / Toll Free: 888-490-0644
> [hidden email]
>
>
> ------------------------------
> LASSO MASTER CLASS - Orlando, FL
> Following the FileMaker Developer Conference
> 2 Day intensive seminar: Learn the best way to get your FileMaker  
> data on the web.
> http://www.briandunning.com/lasso-training/
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: -SQL double quotes or escaped single quotes

Bil Corry
In reply to this post by Doug Gentry
Doug Gentry wrote:
> Bil -
>
> With this approach how would you format the use of a variable - such as
> $myvar - in place of 'abc'  ?

It looks like this:

var:'sql' = "
        SELECT
                myColumn
        FROM
                myTable
        WHERE
                myColumn = '" (encode_sql: $myVar) "' and
                myID = " (integer: $myID) "
        ";

You'll notice I have trailing space, even though it's not really needed.  I find I make less mistakes that way -- vs. doing this:


var:'sql' = "
        SELECT
                myColumn
        FROM
                myTable
        WHERE
                myColumn = '" (encode_sql: $myVar) "' and
                myID = " (integer: $myID)
        ;




- Bil


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: -SQL double quotes or escaped single quotes

Bil Corry
In reply to this post by Jim Van Heule-2
Jim Van Heule wrote:
> I have heard from both OP and MySQL
> that there is a chance that double quotes may not be supported at some
> future date. That basically is the only reason I can see using the
> escaped single quote.

Using single-quotes within the SQL query to delimit strings is the SQL92 standard, so many RDBMS only allow single-quotes (such as Oracle).  That's how I started using double-quotes to delimit the Lasso string; I was creating queries for MySQL and Oracle and needed to standardize.


> Either way works fine. I've been doing it my way for years as well. I
> was simply asking because I see all these posts with escape characters
> all over the place which I think can be very confusing for new people.
> Heck, it gets confusing to me when reading the code. :-)

I agree, escaping quotes is more work than needed.  Far easier to use the opposite delimiter for Lasso strings vs -sql strings.


- Bil


------------------------------
LASSO MASTER CLASS - Orlando, FL
Following the FileMaker Developer Conference
2 Day intensive seminar: Learn the best way to get your FileMaker data on the web.
http://www.briandunning.com/lasso-training/


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
123