LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)

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

LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)

m i l e s
    - Hi and Welcome to -

++++++++++++++++++++++++++++++++
   LASSO TIPS FOR NEWBIES: 2.8
++++++++++++++++++++++++++++++++

I'm your host, M i l e s.

First and foremost, a good place for you to start with Lasso is the  
following 4 things:

The FIRST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=143312
The LAST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=148892
10 LASSO RESOURCES
-> http://www.listsearch.com/lassotalk.lasso?id=143018
THE LASSO RESOURCES ADDENDUM
-> http://www.listsearch.com/lassotalk.lasso?id=143417

            +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
              THIS PORTION OF LASSO TIPS FOR NEWBIES IS SPONSORED BY:
                               M A C M I N I C O L O
                            http://www.MacMiniColo.NET/
                  Lasso & MySQL pre-installed and ready to go.
              phone: (512) 853 - 9500 :: email: [hidden email]
            +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

     This week there is no news...sad to say....that said...on with  
the tips....

++++++++++++++++++++++++++++++++
            TODAYS TIP:
           Lasso AND SQL
++++++++++++++++++++++++++++++++

     PART ONE: A FEAR OF SQL.

     Patient - "Doctor, I have a problem."

     Doctor - "Yes, and that is ?".

     Patient - "I have a fear of SQL."

     Doctor - "Im sorry, SEEEEQWIL ?  Im not familar with that..."

     Patient - "No. Not SEEEQWIL, but ES QUE ELL, or Structure Query  
Language, its the lingua franca of the internet.  Its a database  
thing Doc."

     Doctor - "And your problem with this...what did you call...

     Patient - "SQL"

     Doctor - "Yes...yes....SQL, your problem with it iisssssssss ?"

     Patient - "Im afraid of SQL doctor....I have nightmares about it"

     Doctor - "Nightmares about SQL.  Sooooo tell me about these  
nightmares..."

     Patient - "I'm afraid that Im going to break it.  That the boss  
will come in one morning and find that I've deleted everything from  
the company databases, and there's no back up and I'll get fired..."

     Doctor - "and how do you feel about that...."

     Patient - "Feel ?  FEEL ?  Doc, this is SQL, not my wife Im  
talking about!"
     ...
     The mere moniker strikes fear into the hearts of the  
uninitiated.  But once you get over that part, its NOT so daunting  
anymore....really.  Yeah I know what you're thinking..."easy for you  
to say Mister Miles...easy for you to say..."  Well yes young  
Skywalker, it is easy for me to say NOW but not so when I was going  
through it.  I just didn't get it.  I have to be honest here and  
state that for me SQL still gives me the willies...it really does  
seem daunting.  I always feel like Im gonna break something with it.  
Always.  Because I come from an FMP background, FMP always shielded  
me from the harsh realities of a more robust database platform (like  
MySQL or MS SQL or Oracle)...and it does do just that in many many  
different ways, every time I'd go and delete a record, there was no  
nice lil warning dialog asking if I was sure I wanted to do  
that...nope, SQL just up and deleted the records and moved onto the  
next thing...awaiting my command.  Whereas FMP was much more user  
friendly in that respect.  SQL seemed cold and distant, no bullshit,  
just delete the data and move on.  Which is where I got scared.  
Wondering...did I do it...did the records get deleted ?  Or, OH SHIT!  
DID I JUST DELETE THOSE RECORDS!!!!!!! Instant Panic.  That was one  
of the hard parts working with SQL at first.  No feedback whatsoever  
other than (depending on which client you were using) getting back a  
prompt (if you're running from the command line), or from any one of  
several clients, "no error".  Well GOSH that's helpful.  Not.

     All Im really saying here is that I sympathize with whatever  
fear you may have about working with SQL.  Be it from you have to  
compile and install the actual database server itself (if its MySQL)  
and there's no nice pretty UI to tell you that you've done it; To the  
command line interface which consists of a series of prompts just  
like you're running a CLI OS (BSD Unix being of those); Or to the  
myriads of client applications on the market, which are seemingly  
everywhere (once you start looking for them), and basically all do  
the same things, so its just a matter of choice.  All of it is a lil  
duanting.  I feelya dawg!

     Lasso.

     Lasso on the other hand, in a lot of ways is very much like FMP  
in that it shields you from the harsh realities of SQL, it makes  
friendly for that which isn't.  It simplifies and makes easy that  
which is seemingly over complicated and not very clear.  It seemingly  
creates clarity and consistancy where there was none before.  And it  
also creates a false sense of security that you're data is secure and  
safe because Lasso is sitting in front of the database.  I say false  
sense for a varity of reasons because you're only as safe as you  
really want to be.  (NOTE: True security on a network is to take the  
machine OFF THE NETWORK, and leave it in the box.  (giggle)  That  
way, no one is getting in, then again, neither are you.  So it really  
is secure.  Albiet not very helpful, but as saying goes..."dead men  
tell no tales".  Same is true with computers...computers that are  
still in the box and powered down, can't be hacked into.)

     Lasso in short, creates a very nice robust atmosphere for your  
fear of SQL to be vanquished, Lasso allows you to literally bypass  
all the gobblygook and frightfulness of a SQL backend.

    Think of Lasso as another SQL client, only this client is one  
that allows you to do a lot more than store your data!

    And now a word from my most gracious sponsor....

         +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
           THIS PORTION OF LASSO TIPS FOR NEWBIES IS SPONSORED BY:

                 P O I N T   I N   S P A C E   H O S T I N G
                        http://www.pointinspace.com/

               Professional Lasso / MySQL / FileMaker Hosting
         Mention this ad on sign-up and receive 5% off your account!
         +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

     PART TW0: MIXING METHODS

         There are two ways you can work with SQL and LASSO.  The  
first way, is one so blindingly simple that you hardly know that  
you're working with SQL at all and that's because you won't.  And the  
Second method is a mixture of both SQL and Lasso.  Its that second  
method that we're really after, which we'll get to in a bit.

         The first way is sort of redundant, but it does bear talking  
about in the brief so that you understand that yes you are working  
with SQL in every aspect of Lasso.  Lasso's internal databases are  
SQL based.  SQLite to be exact.  All of your permissions, connections  
to any other database server, usernames, passwords, tables  
values....those are all saved and stored in a SQL database.  Even if  
you're working with FMP you're still making SQL calls, you just never  
know it or see it.

         It should be pointed out that when you write an INLINE call,  
that's SQL right there.

         STATIC DATA REQUESTS: the second way....

         The second way to work with Lasso and SQL is to mix the  
two...and here's where things get interesting....its actually a three  
step process and its very simple.

         First things first, you're going to define your ENTIRE SQL  
statement as ONE very large VARIABLE.  Second, we'll pass that  
variable to an INLINE and thirdly we'll output the results of that  
call as a named inline.

         0.) Opening the variable/Initializing the variable.  
[var:'v_MySQL'=(string)] - Optional

         1.) Populating variable [$v_MySQL += 'SELECT * FROM  
mydb.mytb WHERE myfield =' + '"miles"']

         2.) Passing to an inline.
             [inline: -inlinename='iMySQL', ($db_connection), -sql=
($v_MySQL)][/inline]

         3.) [records: -inlinename='iMySQL']...some values...[/records]

         You can do this with LassoScript (which is easier and  
cleaner looking) or you can do this as LSQB [ ... ].  In either case  
the code is the same.

             <?LassoScript

                 include: 'db_values.inc';

                 var: 'v_MySQL' = (string);

                 $v_MySQL += 'SELECT * FROM mydb.mytb WHERE myfield =';
                 $v_MySQL += '"miles"';

                 inline: -inlinename='iMySQL_test', ($db_connection),  
-sql=($v_MySQWL), -search;/inline;

             ?>

         Hold on Mister Miles, you can do the same thing with an  
inline call and never have to touch SQL, so why bother ?  Ahhhh young  
padawan learner....so quick to judge are you!  SPEED and FLEXIBILITY  
my friends, speed and flexibility is the response to that question.  
In short, you'll get a much speedier response off the direct sql call  
than you would if lasso were doing the inline itself.  Secondly, the  
flexibility that you gain is worth its price you pay for writing out  
the sql calls, specifically if you're doing a ... come on, say it  
with me, a SQL JOIN or UNION!  Go back to last week's example of a  
join and you'll begin to see that you can't rightfully do what you're  
suggesting unless you run TWO inlines inside of one another and the  
sort the requests against one another.  Using SQL by itself, you  
bypass those two inline calls (whichi woudl have thereby slowed you  
down) into ONE request, already presorted and ready to pull the  
values that you're after!

         Here's a neat lil trick for the uninitiated...naming your  
own SQL fields!  Let's take a look at a SELECT statement...

         SELECT mytb.fieldone, mytb.fieldtwo, mytb.fieldthree FROM mydb

         Very simple SELECT statement.  However, what happens if we  
want to change the fieldname and use it as a variable on the page ?  
SQL allows for that by doing the following:

         SELECT mytb.fieldone AS 1field, mytb.fieldtwo AS 2field,  
mytb.fieldthree AS 3field FROM mydd

     PART THREE: DYNAMIC SQL REQUESTS - the lasso way....

         One question that comes up for a lot of people is how to  
pass a lasso variable into the SQL query ?

         MagicMiles to the rescue....

         Let's take that same script above....only we're going to  
make TWO changes to it...

             <?LassoScript

                 include: 'db_values.inc';

                 *var: 'v_PassedVariable' = (action_param:'e1');

                 var: 'v_MySQL' = (string);

                 $v_MySQL += 'SELECT * FROM mydb.mytb WHERE myfield =';
                 $v_MySQL += '"' + $v_PassedVariable + '"';*

                 inline: -inlinename='iMySQL_test', ($db_connection),  
-sql=($v_MySQWL), -search;/inline;

             ?>

         The two changes are that we added were, a variable to allow  
for passing in a user selected or passed value, and then we passed  
that variable into the select statement itself.

         As you can see, that literally changes EVERYTHING.  Because  
not only can you do that with search parameters, on the fly, you can  
do that with the type of query you're calling, the tables that its  
calling, everything can be dynamic based on a certain criteria....an  
example would be:


            <?LassoScript

                 include: 'db_values.inc';

                 var: 'v_PassVar' = (action_param:'e1');

                 if: $v_PassVar >> '1'; var: 'v_Table' = 'mydb.mytb';
                 else $v_PassVar >> '2'; var: 'v_Table' = 'mydb.mytb2';
                 /if;

                 var: 'v_MySQL' = (string);

                 $v_MySQL += 'SELECT * FROM' + $v_Table + 'WHERE  
myfield =';
                 $v_MySQL += '"' + $v_PassVar + '"';

                 inline: -inlinename='iMySQL_test', ($db_connection),  
-sql=($v_MySQWL), -search;/inline;

             ?>

      v_PassVar becomes the deciding value that drives WHICH table to  
search on.

CONCLUSION:

As you can see from just this small lil bit of an example Lasso and  
SQL can work very well together and actually create a rather  
wonderful symbiosis, or symbiotic relationship, each giving the other  
exactly what they need to create something entirely new and different.

Thats said....happy lassoing, "...see you on the other side" !

M i l e s.

---- LASSO DEVELOPER FOR HIRE: CHEAP RATES - NEEDS WORK ----
  *!- lasso tips for newbies - look fro them every monday -*
------------------------------------------------------------
M i l e s                                [hidden email]
MagicMiles Software                         (413) 374 - 5161
http://www.magicmiles.com         AIM/Yahoo/MSN:  magikmiles
------------------------------------------------------------
I create content management systems for the rest of humanity,
starting at just $25 a month, includes domain registration,
web hosting, email and webmail -- PLUS -- * TOOLBOXENGINE*!
------------------------------------------------------------




--
------------------------------
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: LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)

Bil Corry
Few tips for your tips, looking at this code:

        $v_MySQL += 'SELECT * FROM' + $v_Table + 'WHERE myfield =';
        $v_MySQL += '"' + $v_PassVar + '"';

(1) I'd recommend not using "SELECT *" as it degrades performance, especially
with tables with many columns or joins that produce many columns.  The other
problem is if you have the same column name in two joined tables, MySQL will
return two, but Lasso will only reference the first one via
(column:'column_name').

(2) I'd recommend using double-quotes as the string delimiter for Lasso, and the
single-quote for the SQL query.  While MySQL will accept either, Oracle will
only accept the single quote.  It may save you time down the road if you ever
wish to switch.

(3) You must validate the user input $v_PassVar!  The code as provided allows
for SQL injection[1].  At a minimum, use [encode_sql] for strings.

(4) Slight bug, there must be a space after FROM, otherwise the SQL query will
read "FROMtable_name" which will fail.  Same with WHERE, must be a space before
it (unless $v_Table contains a leading and trailing space, but I wouldn't code
assuming that).

So the above code becomes (notice the quotes are now reversed):

        $v_MySQL += "SELECT column_A, column_B FROM " + $v_Table + " WHERE
myfield = ";
        $v_MySQL += "'" + (encode_sql: $v_PassVar) + "'";


Looking at this code:

        if: $v_PassVar >> '1'; var: 'v_Table' = 'mydb.mytb';
        else $v_PassVar >> '2'; var: 'v_Table' = 'mydb.mytb2';
        /if;

If $v_PassVar contains neither '1' nor '2', then the variable $v_Table is never
defined and the page will generate a Lasso error.  For my projects, I generally
will define the variable up front and assign it to either a default value, or a
dummy value that I know will cause the SQL inline to fail (where I then handle
the problem):

        var:'v_Table' = 'default value or dummy value';
        if: $v_PassVar >> '1'; $v_Table = 'mydb.mytb';
        else $v_PassVar >> '2'; $v_Table = 'mydb.mytb2';
        /if;


Footnotes:
[1] For more on SQL injection and Lasso SQL inlines, see:
http://www.ldml.org/articles/SQL_Injection.lasso



- Bil

------

Bil Corry
[hidden email]

Enterprise internet application development and security consulting
  http://www.fivegeeks.com/

Tools for Rapid Lasso Development
  http://www.lassoware.com/
 
-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
m i l e s
Sent: Monday, May 23, 2005 3:55 PM
To: [hidden email]
Subject: LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)

    - Hi and Welcome to -

++++++++++++++++++++++++++++++++
   LASSO TIPS FOR NEWBIES: 2.8
++++++++++++++++++++++++++++++++

I'm your host, M i l e s.

First and foremost, a good place for you to start with Lasso is the  
following 4 things:

The FIRST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=143312
The LAST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=148892
10 LASSO RESOURCES
-> http://www.listsearch.com/lassotalk.lasso?id=143018
THE LASSO RESOURCES ADDENDUM
-> http://www.listsearch.com/lassotalk.lasso?id=143417

            +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
              THIS PORTION OF LASSO TIPS FOR NEWBIES IS SPONSORED BY:
                               M A C M I N I C O L O
                            http://www.MacMiniColo.NET/
                  Lasso & MySQL pre-installed and ready to go.
              phone: (512) 853 - 9500 :: email: [hidden email]
            +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

     This week there is no news...sad to say....that said...on with  
the tips....

++++++++++++++++++++++++++++++++
            TODAYS TIP:
           Lasso AND SQL
++++++++++++++++++++++++++++++++

     PART ONE: A FEAR OF SQL.

     Patient - "Doctor, I have a problem."

     Doctor - "Yes, and that is ?".

     Patient - "I have a fear of SQL."

     Doctor - "Im sorry, SEEEEQWIL ?  Im not familar with that..."

     Patient - "No. Not SEEEQWIL, but ES QUE ELL, or Structure Query  
Language, its the lingua franca of the internet.  Its a database  
thing Doc."

     Doctor - "And your problem with this...what did you call...

     Patient - "SQL"

     Doctor - "Yes...yes....SQL, your problem with it iisssssssss ?"

     Patient - "Im afraid of SQL doctor....I have nightmares about it"

     Doctor - "Nightmares about SQL.  Sooooo tell me about these  
nightmares..."

     Patient - "I'm afraid that Im going to break it.  That the boss  
will come in one morning and find that I've deleted everything from  
the company databases, and there's no back up and I'll get fired..."

     Doctor - "and how do you feel about that...."

     Patient - "Feel ?  FEEL ?  Doc, this is SQL, not my wife Im  
talking about!"
     ...
     The mere moniker strikes fear into the hearts of the  
uninitiated.  But once you get over that part, its NOT so daunting  
anymore....really.  Yeah I know what you're thinking..."easy for you  
to say Mister Miles...easy for you to say..."  Well yes young  
Skywalker, it is easy for me to say NOW but not so when I was going  
through it.  I just didn't get it.  I have to be honest here and  
state that for me SQL still gives me the willies...it really does  
seem daunting.  I always feel like Im gonna break something with it.  
Always.  Because I come from an FMP background, FMP always shielded  
me from the harsh realities of a more robust database platform (like  
MySQL or MS SQL or Oracle)...and it does do just that in many many  
different ways, every time I'd go and delete a record, there was no  
nice lil warning dialog asking if I was sure I wanted to do  
that...nope, SQL just up and deleted the records and moved onto the  
next thing...awaiting my command.  Whereas FMP was much more user  
friendly in that respect.  SQL seemed cold and distant, no bullshit,  
just delete the data and move on.  Which is where I got scared.  
Wondering...did I do it...did the records get deleted ?  Or, OH SHIT!  
DID I JUST DELETE THOSE RECORDS!!!!!!! Instant Panic.  That was one  
of the hard parts working with SQL at first.  No feedback whatsoever  
other than (depending on which client you were using) getting back a  
prompt (if you're running from the command line), or from any one of  
several clients, "no error".  Well GOSH that's helpful.  Not.

     All Im really saying here is that I sympathize with whatever  
fear you may have about working with SQL.  Be it from you have to  
compile and install the actual database server itself (if its MySQL)  
and there's no nice pretty UI to tell you that you've done it; To the  
command line interface which consists of a series of prompts just  
like you're running a CLI OS (BSD Unix being of those); Or to the  
myriads of client applications on the market, which are seemingly  
everywhere (once you start looking for them), and basically all do  
the same things, so its just a matter of choice.  All of it is a lil  
duanting.  I feelya dawg!

     Lasso.

     Lasso on the other hand, in a lot of ways is very much like FMP  
in that it shields you from the harsh realities of SQL, it makes  
friendly for that which isn't.  It simplifies and makes easy that  
which is seemingly over complicated and not very clear.  It seemingly  
creates clarity and consistancy where there was none before.  And it  
also creates a false sense of security that you're data is secure and  
safe because Lasso is sitting in front of the database.  I say false  
sense for a varity of reasons because you're only as safe as you  
really want to be.  (NOTE: True security on a network is to take the  
machine OFF THE NETWORK, and leave it in the box.  (giggle)  That  
way, no one is getting in, then again, neither are you.  So it really  
is secure.  Albiet not very helpful, but as saying goes..."dead men  
tell no tales".  Same is true with computers...computers that are  
still in the box and powered down, can't be hacked into.)

     Lasso in short, creates a very nice robust atmosphere for your  
fear of SQL to be vanquished, Lasso allows you to literally bypass  
all the gobblygook and frightfulness of a SQL backend.

    Think of Lasso as another SQL client, only this client is one  
that allows you to do a lot more than store your data!

    And now a word from my most gracious sponsor....

         +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
           THIS PORTION OF LASSO TIPS FOR NEWBIES IS SPONSORED BY:

                 P O I N T   I N   S P A C E   H O S T I N G
                        http://www.pointinspace.com/

               Professional Lasso / MySQL / FileMaker Hosting
         Mention this ad on sign-up and receive 5% off your account!
         +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

     PART TW0: MIXING METHODS

         There are two ways you can work with SQL and LASSO.  The  
first way, is one so blindingly simple that you hardly know that  
you're working with SQL at all and that's because you won't.  And the  
Second method is a mixture of both SQL and Lasso.  Its that second  
method that we're really after, which we'll get to in a bit.

         The first way is sort of redundant, but it does bear talking  
about in the brief so that you understand that yes you are working  
with SQL in every aspect of Lasso.  Lasso's internal databases are  
SQL based.  SQLite to be exact.  All of your permissions, connections  
to any other database server, usernames, passwords, tables  
values....those are all saved and stored in a SQL database.  Even if  
you're working with FMP you're still making SQL calls, you just never  
know it or see it.

         It should be pointed out that when you write an INLINE call,  
that's SQL right there.

         STATIC DATA REQUESTS: the second way....

         The second way to work with Lasso and SQL is to mix the  
two...and here's where things get interesting....its actually a three  
step process and its very simple.

         First things first, you're going to define your ENTIRE SQL  
statement as ONE very large VARIABLE.  Second, we'll pass that  
variable to an INLINE and thirdly we'll output the results of that  
call as a named inline.

         0.) Opening the variable/Initializing the variable.  
[var:'v_MySQL'=(string)] - Optional

         1.) Populating variable [$v_MySQL += 'SELECT * FROM  
mydb.mytb WHERE myfield =' + '"miles"']

         2.) Passing to an inline.
             [inline: -inlinename='iMySQL', ($db_connection), -sql=
($v_MySQL)][/inline]

         3.) [records: -inlinename='iMySQL']...some values...[/records]

         You can do this with LassoScript (which is easier and  
cleaner looking) or you can do this as LSQB [ ... ].  In either case  
the code is the same.

             <?LassoScript

                 include: 'db_values.inc';

                 var: 'v_MySQL' = (string);

                 $v_MySQL += 'SELECT * FROM mydb.mytb WHERE myfield =';
                 $v_MySQL += '"miles"';

                 inline: -inlinename='iMySQL_test', ($db_connection),  
-sql=($v_MySQWL), -search;/inline;

             ?>

         Hold on Mister Miles, you can do the same thing with an  
inline call and never have to touch SQL, so why bother ?  Ahhhh young  
padawan learner....so quick to judge are you!  SPEED and FLEXIBILITY  
my friends, speed and flexibility is the response to that question.  
In short, you'll get a much speedier response off the direct sql call  
than you would if lasso were doing the inline itself.  Secondly, the  
flexibility that you gain is worth its price you pay for writing out  
the sql calls, specifically if you're doing a ... come on, say it  
with me, a SQL JOIN or UNION!  Go back to last week's example of a  
join and you'll begin to see that you can't rightfully do what you're  
suggesting unless you run TWO inlines inside of one another and the  
sort the requests against one another.  Using SQL by itself, you  
bypass those two inline calls (whichi woudl have thereby slowed you  
down) into ONE request, already presorted and ready to pull the  
values that you're after!

         Here's a neat lil trick for the uninitiated...naming your  
own SQL fields!  Let's take a look at a SELECT statement...

         SELECT mytb.fieldone, mytb.fieldtwo, mytb.fieldthree FROM mydb

         Very simple SELECT statement.  However, what happens if we  
want to change the fieldname and use it as a variable on the page ?  
SQL allows for that by doing the following:

         SELECT mytb.fieldone AS 1field, mytb.fieldtwo AS 2field,  
mytb.fieldthree AS 3field FROM mydd

     PART THREE: DYNAMIC SQL REQUESTS - the lasso way....

         One question that comes up for a lot of people is how to  
pass a lasso variable into the SQL query ?

         MagicMiles to the rescue....

         Let's take that same script above....only we're going to  
make TWO changes to it...

             <?LassoScript

                 include: 'db_values.inc';

                 *var: 'v_PassedVariable' = (action_param:'e1');

                 var: 'v_MySQL' = (string);

                 $v_MySQL += 'SELECT * FROM mydb.mytb WHERE myfield =';
                 $v_MySQL += '"' + $v_PassedVariable + '"';*

                 inline: -inlinename='iMySQL_test', ($db_connection),  
-sql=($v_MySQWL), -search;/inline;

             ?>

         The two changes are that we added were, a variable to allow  
for passing in a user selected or passed value, and then we passed  
that variable into the select statement itself.

         As you can see, that literally changes EVERYTHING.  Because  
not only can you do that with search parameters, on the fly, you can  
do that with the type of query you're calling, the tables that its  
calling, everything can be dynamic based on a certain criteria....an  
example would be:


            <?LassoScript

                 include: 'db_values.inc';

                 var: 'v_PassVar' = (action_param:'e1');

                 if: $v_PassVar >> '1'; var: 'v_Table' = 'mydb.mytb';
                 else $v_PassVar >> '2'; var: 'v_Table' = 'mydb.mytb2';
                 /if;

                 var: 'v_MySQL' = (string);

                 $v_MySQL += 'SELECT * FROM' + $v_Table + 'WHERE  
myfield =';
                 $v_MySQL += '"' + $v_PassVar + '"';

                 inline: -inlinename='iMySQL_test', ($db_connection),  
-sql=($v_MySQWL), -search;/inline;

             ?>

      v_PassVar becomes the deciding value that drives WHICH table to  
search on.

CONCLUSION:

As you can see from just this small lil bit of an example Lasso and  
SQL can work very well together and actually create a rather  
wonderful symbiosis, or symbiotic relationship, each giving the other  
exactly what they need to create something entirely new and different.

Thats said....happy lassoing, "...see you on the other side" !

M i l e s.

---- LASSO DEVELOPER FOR HIRE: CHEAP RATES - NEEDS WORK ----
  *!- lasso tips for newbies - look fro them every monday -*
------------------------------------------------------------
M i l e s                                [hidden email]
MagicMiles Software                         (413) 374 - 5161
http://www.magicmiles.com         AIM/Yahoo/MSN:  magikmiles
------------------------------------------------------------
I create content management systems for the rest of humanity,
starting at just $25 a month, includes domain registration,
web hosting, email and webmail -- PLUS -- * TOOLBOXENGINE*!
------------------------------------------------------------




--
------------------------------
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 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: LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)

Erlend Garåsen
In reply to this post by m i l e s

Bil Corry wrote:

> So the above code becomes (notice the quotes are now reversed):
>
> $v_MySQL += "SELECT column_A, column_B FROM " + $v_Table + " WHERE
> myfield = ";
> $v_MySQL += "'" + (encode_sql: $v_PassVar) + "'";

Maybe I have been drinking too little coffee this morning, but does
Lasso actually accept double-quotes like this? I thought it only
accepted single-quotes for variables. If this is not the case, I will
follow your suggestions from now on (yes, I have been struggling a lot
with Oracle and Lasso).

Erlend


--
Erlend Gar?sen
Center for Information Technology Services
University of Oslo
P.O. Box 1086 Blindern, N-0317 OSLO, Norway
Ph: (+47) 22840193, Fax: (+47) 22852730

--
------------------------------
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: LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)

Bil Corry
In reply to this post by m i l e s
> does Lasso actually accept double-quotes like this?

Yes.  Lasso will accept either single- or double-quotes as a string delimiter.
That's why when one of them is in the string, I use the other to delimit it.
Otherwise, you have to escape the quote in the string, which is harder to
read/debug.

Example:

        var:'test' = "That's great!";

        vs.

        var:'test' = 'That\'s great!';


- Bil

------

Bil Corry
[hidden email]

Enterprise internet application development and security consulting
  http://www.fivegeeks.com/

Tools for Rapid Lasso Development
  http://www.lassoware.com/
 
-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
Erlend Garåsen
Sent: Wednesday, May 25, 2005 1:33 AM
To: [hidden email]
Subject: Re: LASSO TIPS FOR NEWBIES: 2.8 SQL and Lasso (pt2)


Bil Corry wrote:

> So the above code becomes (notice the quotes are now reversed):
>
> $v_MySQL += "SELECT column_A, column_B FROM " + $v_Table + " WHERE
> myfield = ";
> $v_MySQL += "'" + (encode_sql: $v_PassVar) + "'";

Maybe I have been drinking too little coffee this morning, but does
Lasso actually accept double-quotes like this? I thought it only
accepted single-quotes for variables. If this is not the case, I will
follow your suggestions from now on (yes, I have been struggling a lot
with Oracle and Lasso).

Erlend




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