MySQL Stored Procedures and Lasso 8.6.3

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

MySQL Stored Procedures and Lasso 8.6.3

Pier Kuipers
Hi All,

Having tried everything, I'm now completely at a loss when trying to get a
MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server are
version 5.6.

The stored procedure is quite complex, but the problem appears to be with
declaring any kind of procedure, e.g.

DELIMITER //
CREATE PROCEDURE GetAllDefinitions()
BEGIN
SELECT * FROM file_definitions;
END //
DELIMITER ;

If I run the above query in MySQL directly (or in Navicat) everything is
fine - same with the complex procedure I'm trying to use.
Runing the same query in Lasso results in an error, however - even when
using the SQL utility in Lasso Admin:

Error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
file_' at line 1 (1064)

I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's Mysql
error keeps talking about "line 1", seemingly ignoring carriage returns
completely.

Has anyone got any idea what I could be doing wrong?

Cheers

--
Pier Kuipers
Mobile: +353 87 294 3063
Blog: http://www.pierkuipers.com
Facebook: http://www.facebook.com/pier.kuipers
Twitter: @pkvisualid
AIM: pkvisualid

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

stevepiercy
Do you have a preceding statement that does not terminate with
';'?  Sometimes I forget to do that when I execute more than one
statement to get multiple result sets.

--steve


On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers) pronounced:

>Hi All,
>
>Having tried everything, I'm now completely at a loss when trying to get a
>MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server are
>version 5.6.
>
>The stored procedure is quite complex, but the problem appears to be with
>declaring any kind of procedure, e.g.
>
>DELIMITER //
>CREATE PROCEDURE GetAllDefinitions()
>BEGIN
>SELECT * FROM file_definitions;
>END //
>DELIMITER ;
>
>If I run the above query in MySQL directly (or in Navicat) everything is
>fine - same with the complex procedure I'm trying to use.
>Runing the same query in Lasso results in an error, however - even when
>using the SQL utility in Lasso Admin:
>
>Error: You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use near
>'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
>file_' at line 1 (1064)
>
>I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's Mysql
>error keeps talking about "line 1", seemingly ignoring carriage returns
>completely.
>
>Has anyone got any idea what I could be doing wrong?
>
>Cheers
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Pier Kuipers
Nope - just that really simple procedure in my example will throw an error
in Lasso Admin's sql utility thingamajig. It's as if stored procedures are
just not supported :-(

Thanks

Pier
On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <[hidden email]>
wrote:

> Do you have a preceding statement that does not terminate with ';'?
> Sometimes I forget to do that when I execute more than one statement to get
> multiple result sets.
>
> --steve
>
>
> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers) pronounced:
>
> Hi All,
>>
>> Having tried everything, I'm now completely at a loss when trying to get a
>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server are
>> version 5.6.
>>
>> The stored procedure is quite complex, but the problem appears to be with
>> declaring any kind of procedure, e.g.
>>
>> DELIMITER //
>> CREATE PROCEDURE GetAllDefinitions()
>> BEGIN
>> SELECT * FROM file_definitions;
>> END //
>> DELIMITER ;
>>
>> If I run the above query in MySQL directly (or in Navicat) everything is
>> fine - same with the complex procedure I'm trying to use.
>> Runing the same query in Lasso results in an error, however - even when
>> using the SQL utility in Lasso Admin:
>>
>> Error: You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use near
>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
>> file_' at line 1 (1064)
>>
>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's Mysql
>> error keeps talking about "line 1", seemingly ignoring carriage returns
>> completely.
>>
>> Has anyone got any idea what I could be doing wrong?
>>
>> Cheers
>>
>>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Soquel, CA
> <[hidden email]>               <http://www.StevePiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Steffan A. Cline
For the fun of it, put action_statement just after your inline and see what Lasso is sending to MySQL.



Thank you,
Steffan Cline
602-793-0014








On 3/18/16, 1:28 PM, "Pier Kuipers" <[hidden email] on behalf of [hidden email]> wrote:

>Nope - just that really simple procedure in my example will throw an error
>in Lasso Admin's sql utility thingamajig. It's as if stored procedures are
>just not supported :-(
>
>Thanks
>
>Pier
>On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <[hidden email]>
>wrote:
>
>> Do you have a preceding statement that does not terminate with ';'?
>> Sometimes I forget to do that when I execute more than one statement to get
>> multiple result sets.
>>
>> --steve
>>
>>
>> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers) pronounced:
>>
>> Hi All,
>>>
>>> Having tried everything, I'm now completely at a loss when trying to get a
>>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server are
>>> version 5.6.
>>>
>>> The stored procedure is quite complex, but the problem appears to be with
>>> declaring any kind of procedure, e.g.
>>>
>>> DELIMITER //
>>> CREATE PROCEDURE GetAllDefinitions()
>>> BEGIN
>>> SELECT * FROM file_definitions;
>>> END //
>>> DELIMITER ;
>>>
>>> If I run the above query in MySQL directly (or in Navicat) everything is
>>> fine - same with the complex procedure I'm trying to use.
>>> Runing the same query in Lasso results in an error, however - even when
>>> using the SQL utility in Lasso Admin:
>>>
>>> Error: You have an error in your SQL syntax; check the manual that
>>> corresponds to your MySQL server version for the right syntax to use near
>>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
>>> file_' at line 1 (1064)
>>>
>>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's Mysql
>>> error keeps talking about "line 1", seemingly ignoring carriage returns
>>> completely.
>>>
>>> Has anyone got any idea what I could be doing wrong?
>>>
>>> Cheers
>>>
>>>
>> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>> Steve Piercy              Website Builder              Soquel, CA
>> <[hidden email]>               <http://www.StevePiercy.com/>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>>  the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>>
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>
>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Pier Kuipers
Hi Steffan :-)

Lasso doesn't even get that far:

local('teststatement' = 'DELIMITER //
   CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT * FROM file_definitions;
   END //
   DELIMITER ;');
inline($gv_sql,-sql=#teststatement);
/inline;
action_statement;

Before Lasso gets to report the action_statement, it reports:

1064: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM
file_defini' at line 1

I'm totally baffled - would really like to get this to work... :-(

Cheers

Pier

On 18 March 2016 at 20:49, Steffan Cline <[hidden email]> wrote:

> For the fun of it, put action_statement just after your inline and see
> what Lasso is sending to MySQL.
>
>
>
> Thank you,
> Steffan Cline
> 602-793-0014
>
>
>
>
>
>
>
>
> On 3/18/16, 1:28 PM, "Pier Kuipers" <[hidden email] on
> behalf of [hidden email]> wrote:
>
> >Nope - just that really simple procedure in my example will throw an error
> >in Lasso Admin's sql utility thingamajig. It's as if stored procedures are
> >just not supported :-(
> >
> >Thanks
> >
> >Pier
> >On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <
> [hidden email]>
> >wrote:
> >
> >> Do you have a preceding statement that does not terminate with ';'?
> >> Sometimes I forget to do that when I execute more than one statement to
> get
> >> multiple result sets.
> >>
> >> --steve
> >>
> >>
> >> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers) pronounced:
> >>
> >> Hi All,
> >>>
> >>> Having tried everything, I'm now completely at a loss when trying to
> get a
> >>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server
> are
> >>> version 5.6.
> >>>
> >>> The stored procedure is quite complex, but the problem appears to be
> with
> >>> declaring any kind of procedure, e.g.
> >>>
> >>> DELIMITER //
> >>> CREATE PROCEDURE GetAllDefinitions()
> >>> BEGIN
> >>> SELECT * FROM file_definitions;
> >>> END //
> >>> DELIMITER ;
> >>>
> >>> If I run the above query in MySQL directly (or in Navicat) everything
> is
> >>> fine - same with the complex procedure I'm trying to use.
> >>> Runing the same query in Lasso results in an error, however - even when
> >>> using the SQL utility in Lasso Admin:
> >>>
> >>> Error: You have an error in your SQL syntax; check the manual that
> >>> corresponds to your MySQL server version for the right syntax to use
> near
> >>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
> >>> file_' at line 1 (1064)
> >>>
> >>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's
> Mysql
> >>> error keeps talking about "line 1", seemingly ignoring carriage returns
> >>> completely.
> >>>
> >>> Has anyone got any idea what I could be doing wrong?
> >>>
> >>> Cheers
> >>>
> >>>
> >> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> >> Steve Piercy              Website Builder              Soquel, CA
> >> <[hidden email]>               <http://www.StevePiercy.com/>
> >>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to
> >>  the mailing list Lasso [hidden email]
> >> Official list archives available at http://www.lassotalk.com
> >> To unsubscribe, E-mail to: <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >>
> >
> >#############################################################
> >
> >This message is sent to you because you are subscribed to
> >  the mailing list Lasso [hidden email]
> >Official list archives available at http://www.lassotalk.com
> >To unsubscribe, E-mail to: <[hidden email]>
> >Send administrative queries to  <[hidden email]>
> >
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>



--
Pier Kuipers
Mobile: +353 87 294 3063
Blog: http://www.pierkuipers.com
Facebook: http://www.facebook.com/pier.kuipers
Twitter: @pkvisualid
AIM: pkvisualid

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

stevepiercy
In reply to this post by Pier Kuipers
FWIW, I can replicate the issue as you described.

This worked in Lasso:

CREATE PROCEDURE GetAllDefinitions()
SELECT * FROM t;

--steve


On 3/18/16 at 8:28 PM, [hidden email] (Pier Kuipers) pronounced:

> Nope - just that really simple procedure in my example will throw an error
> in Lasso Admin's sql utility thingamajig. It's as if stored procedures are
> just not supported :-(
>
> Thanks
>
> Pier
> On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <[hidden email]>
> wrote:
>
> > Do you have a preceding statement that does not terminate with ';'?
> > Sometimes I forget to do that when I execute more than one statement to get
> > multiple result sets.
> >
> > --steve
> >
> >
> > On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers) pronounced:
> >
> > Hi All,
> >>
> >> Having tried everything, I'm now completely at a loss when trying to get a
> >> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server are
> >> version 5.6.
> >>
> >> The stored procedure is quite complex, but the problem appears to be with
> >> declaring any kind of procedure, e.g.
> >>
> >> DELIMITER //
> >> CREATE PROCEDURE GetAllDefinitions()
> >> BEGIN
> >> SELECT * FROM file_definitions;
> >> END //
> >> DELIMITER ;
> >>
> >> If I run the above query in MySQL directly (or in Navicat) everything is
> >> fine - same with the complex procedure I'm trying to use.
> >> Runing the same query in Lasso results in an error, however - even when
> >> using the SQL utility in Lasso Admin:
> >>
> >> Error: You have an error in your SQL syntax; check the manual that
> >> corresponds to your MySQL server version for the right syntax to use near
> >> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
> >> file_' at line 1 (1064)
> >>
> >> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's Mysql
> >> error keeps talking about "line 1", seemingly ignoring carriage returns
> >> completely.
> >>
> >> Has anyone got any idea what I could be doing wrong?
> >>
> >> Cheers
> >>
> >>
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > Steve Piercy              Website Builder              Soquel, CA
> > <[hidden email]>               <http://www.StevePiercy.com/>
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> >  the mailing list Lasso [hidden email]
> > Official list archives available at http://www.lassotalk.com
> > To unsubscribe, E-mail to: <[hidden email]>
> > Send administrative queries to  <[hidden email]>
> >
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.StevePiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Wade Maxfield
In reply to this post by Pier Kuipers
> On 19/03/2016, at 8:56 AM, Pier Kuipers <[hidden email]> wrote:
>
> Has anyone got any idea what I could be doing wrong?

Just coming out of left field:

Why are you getting Lasso to create the stored procedure?  I thought the normal process was for a developer to create stored procedures using a database management app (like Navicat), then use the stored procedure in other places to simplify or secure further code. I thought they were a write once, use many type of item.

Getting Lasso to create the stored procedure seems similar to getting Lasso to change the table definition, and not the type of thing I’d expect to be doing in Lasso rather than Navicat or similar.

Have you tried creating the stored procedure in Navicat, then granting access to the stored procedure to your Lasso user, then calling the stored procedure via Lasso, either SQL utility or via a normal page?


 - Wade



#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Steffan A. Cline
In reply to this post by Pier Kuipers
Pier,

action_statement should be inside the inline statement.

Why are you creating the stored procedure from Lasso?

Shouldn’t that just be created in MySQL and then called from Lasso?


Thank you,
Steffan Cline
602-793-0014








On 3/18/16, 2:07 PM, "Pier Kuipers" <[hidden email] on behalf of [hidden email]> wrote:

>Hi Steffan :-)
>
>Lasso doesn't even get that far:
>
>local('teststatement' = 'DELIMITER //
>   CREATE PROCEDURE GetAllProducts()
>   BEGIN
>   SELECT * FROM file_definitions;
>   END //
>   DELIMITER ;');
>inline($gv_sql,-sql=#teststatement);
>/inline;
>action_statement;
>
>Before Lasso gets to report the action_statement, it reports:
>
>1064: You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use near
>'DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM
>file_defini' at line 1
>
>I'm totally baffled - would really like to get this to work... :-(
>
>Cheers
>
>Pier
>
>On 18 March 2016 at 20:49, Steffan Cline <[hidden email]> wrote:
>
>> For the fun of it, put action_statement just after your inline and see
>> what Lasso is sending to MySQL.
>>
>>
>>
>> Thank you,
>> Steffan Cline
>> 602-793-0014
>>
>>
>>
>>
>>
>>
>>
>>
>> On 3/18/16, 1:28 PM, "Pier Kuipers" <[hidden email] on
>> behalf of [hidden email]> wrote:
>>
>> >Nope - just that really simple procedure in my example will throw an error
>> >in Lasso Admin's sql utility thingamajig. It's as if stored procedures are
>> >just not supported :-(
>> >
>> >Thanks
>> >
>> >Pier
>> >On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <
>> [hidden email]>
>> >wrote:
>> >
>> >> Do you have a preceding statement that does not terminate with ';'?
>> >> Sometimes I forget to do that when I execute more than one statement to
>> get
>> >> multiple result sets.
>> >>
>> >> --steve
>> >>
>> >>
>> >> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers) pronounced:
>> >>
>> >> Hi All,
>> >>>
>> >>> Having tried everything, I'm now completely at a loss when trying to
>> get a
>> >>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and server
>> are
>> >>> version 5.6.
>> >>>
>> >>> The stored procedure is quite complex, but the problem appears to be
>> with
>> >>> declaring any kind of procedure, e.g.
>> >>>
>> >>> DELIMITER //
>> >>> CREATE PROCEDURE GetAllDefinitions()
>> >>> BEGIN
>> >>> SELECT * FROM file_definitions;
>> >>> END //
>> >>> DELIMITER ;
>> >>>
>> >>> If I run the above query in MySQL directly (or in Navicat) everything
>> is
>> >>> fine - same with the complex procedure I'm trying to use.
>> >>> Runing the same query in Lasso results in an error, however - even when
>> >>> using the SQL utility in Lasso Admin:
>> >>>
>> >>> Error: You have an error in your SQL syntax; check the manual that
>> >>> corresponds to your MySQL server version for the right syntax to use
>> near
>> >>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT * FROM
>> >>> file_' at line 1 (1064)
>> >>>
>> >>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's
>> Mysql
>> >>> error keeps talking about "line 1", seemingly ignoring carriage returns
>> >>> completely.
>> >>>
>> >>> Has anyone got any idea what I could be doing wrong?
>> >>>
>> >>> Cheers
>> >>>
>> >>>
>> >> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>> >> Steve Piercy              Website Builder              Soquel, CA
>> >> <[hidden email]>               <http://www.StevePiercy.com/>
>> >>
>> >>
>> >> #############################################################
>> >>
>> >> This message is sent to you because you are subscribed to
>> >>  the mailing list Lasso [hidden email]
>> >> Official list archives available at http://www.lassotalk.com
>> >> To unsubscribe, E-mail to: <[hidden email]>
>> >> Send administrative queries to  <[hidden email]>
>> >>
>> >
>> >#############################################################
>> >
>> >This message is sent to you because you are subscribed to
>> >  the mailing list Lasso [hidden email]
>> >Official list archives available at http://www.lassotalk.com
>> >To unsubscribe, E-mail to: <[hidden email]>
>> >Send administrative queries to  <[hidden email]>
>> >
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>>   the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>>
>
>
>
>--
>Pier Kuipers
>Mobile: +353 87 294 3063
>Blog: http://www.pierkuipers.com
>Facebook: http://www.facebook.com/pier.kuipers
>Twitter: @pkvisualid
>AIM: pkvisualid
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>
>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Pier Kuipers
Excellent pointers guys, thanks for the feedback.

Creating the stored procedure in Lasso is probably a bit daft in the
context of DRY, I agree: why create the procedure every time you call the
page that uses it? To be honest, I hadn't put much thought into that part
yet - I was just annoyed that I couldn;t get the code to run to begin with.

Steve, you're right - the code will run if you take out the part that deals
with delimiters, and you have to call the procedure in a separate trip to
the database (connection), it seems.

My idea behind the whole exercise was to run a number of statements
entirely in MySQL, including an IF statement that check if a certain value
already exists. That typeof  IF statement is only supported within stored
procedures, so off I went on my little project.

As you can probably tell, I'm new to the concept of stored procedures, and
they do seem useful. I'll keep playing with my latest discoveries. Watch
this space.

Cheers

Pier

On 18 March 2016 at 21:38, Steffan Cline <[hidden email]> wrote:

> Pier,
>
> action_statement should be inside the inline statement.
>
> Why are you creating the stored procedure from Lasso?
>
> Shouldn’t that just be created in MySQL and then called from Lasso?
>
>
> Thank you,
> Steffan Cline
> 602-793-0014
>
>
>
>
>
>
>
>
> On 3/18/16, 2:07 PM, "Pier Kuipers" <[hidden email] on
> behalf of [hidden email]> wrote:
>
> >Hi Steffan :-)
> >
> >Lasso doesn't even get that far:
> >
> >local('teststatement' = 'DELIMITER //
> >   CREATE PROCEDURE GetAllProducts()
> >   BEGIN
> >   SELECT * FROM file_definitions;
> >   END //
> >   DELIMITER ;');
> >inline($gv_sql,-sql=#teststatement);
> >/inline;
> >action_statement;
> >
> >Before Lasso gets to report the action_statement, it reports:
> >
> >1064: You have an error in your SQL syntax; check the manual that
> >corresponds to your MySQL server version for the right syntax to use near
> >'DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM
> >file_defini' at line 1
> >
> >I'm totally baffled - would really like to get this to work... :-(
> >
> >Cheers
> >
> >Pier
> >
> >On 18 March 2016 at 20:49, Steffan Cline <[hidden email]> wrote:
> >
> >> For the fun of it, put action_statement just after your inline and see
> >> what Lasso is sending to MySQL.
> >>
> >>
> >>
> >> Thank you,
> >> Steffan Cline
> >> 602-793-0014
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> On 3/18/16, 1:28 PM, "Pier Kuipers" <[hidden email]
> on
> >> behalf of [hidden email]> wrote:
> >>
> >> >Nope - just that really simple procedure in my example will throw an
> error
> >> >in Lasso Admin's sql utility thingamajig. It's as if stored procedures
> are
> >> >just not supported :-(
> >> >
> >> >Thanks
> >> >
> >> >Pier
> >> >On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <
> >> [hidden email]>
> >> >wrote:
> >> >
> >> >> Do you have a preceding statement that does not terminate with ';'?
> >> >> Sometimes I forget to do that when I execute more than one statement
> to
> >> get
> >> >> multiple result sets.
> >> >>
> >> >> --steve
> >> >>
> >> >>
> >> >> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers)
> pronounced:
> >> >>
> >> >> Hi All,
> >> >>>
> >> >>> Having tried everything, I'm now completely at a loss when trying to
> >> get a
> >> >>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and
> server
> >> are
> >> >>> version 5.6.
> >> >>>
> >> >>> The stored procedure is quite complex, but the problem appears to be
> >> with
> >> >>> declaring any kind of procedure, e.g.
> >> >>>
> >> >>> DELIMITER //
> >> >>> CREATE PROCEDURE GetAllDefinitions()
> >> >>> BEGIN
> >> >>> SELECT * FROM file_definitions;
> >> >>> END //
> >> >>> DELIMITER ;
> >> >>>
> >> >>> If I run the above query in MySQL directly (or in Navicat)
> everything
> >> is
> >> >>> fine - same with the complex procedure I'm trying to use.
> >> >>> Runing the same query in Lasso results in an error, however - even
> when
> >> >>> using the SQL utility in Lasso Admin:
> >> >>>
> >> >>> Error: You have an error in your SQL syntax; check the manual that
> >> >>> corresponds to your MySQL server version for the right syntax to use
> >> near
> >> >>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT *
> FROM
> >> >>> file_' at line 1 (1064)
> >> >>>
> >> >>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's
> >> Mysql
> >> >>> error keeps talking about "line 1", seemingly ignoring carriage
> returns
> >> >>> completely.
> >> >>>
> >> >>> Has anyone got any idea what I could be doing wrong?
> >> >>>
> >> >>> Cheers
> >> >>>
> >> >>>
> >> >> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> >> >> Steve Piercy              Website Builder              Soquel, CA
> >> >> <[hidden email]>               <http://www.StevePiercy.com/>
> >> >>
> >> >>
> >> >> #############################################################
> >> >>
> >> >> This message is sent to you because you are subscribed to
> >> >>  the mailing list Lasso [hidden email]
> >> >> Official list archives available at http://www.lassotalk.com
> >> >> To unsubscribe, E-mail to: <[hidden email]>
> >> >> Send administrative queries to  <[hidden email]>
> >> >>
> >> >
> >> >#############################################################
> >> >
> >> >This message is sent to you because you are subscribed to
> >> >  the mailing list Lasso [hidden email]
> >> >Official list archives available at http://www.lassotalk.com
> >> >To unsubscribe, E-mail to: <[hidden email]>
> >> >Send administrative queries to  <[hidden email]>
> >> >
> >>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to
> >>   the mailing list Lasso [hidden email]
> >> Official list archives available at http://www.lassotalk.com
> >> To unsubscribe, E-mail to: <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >>
> >
> >
> >
> >--
> >Pier Kuipers
> >Mobile: +353 87 294 3063
> >Blog: http://www.pierkuipers.com
> >Facebook: http://www.facebook.com/pier.kuipers
> >Twitter: @pkvisualid
> >AIM: pkvisualid
> >
> >#############################################################
> >
> >This message is sent to you because you are subscribed to
> >  the mailing list Lasso [hidden email]
> >Official list archives available at http://www.lassotalk.com
> >To unsubscribe, E-mail to: <[hidden email]>
> >Send administrative queries to  <[hidden email]>
> >
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>



--
Pier Kuipers
Mobile: +353 87 294 3063
Blog: http://www.pierkuipers.com
Facebook: http://www.facebook.com/pier.kuipers
Twitter: @pkvisualid
AIM: pkvisualid

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Steffan A. Cline
Share your query in pseudo code. We can probably help you.

Thanks,
Steffan Cline
[hidden email]
602-793-0014

> On Mar 18, 2016, at 4:45 PM, Pier Kuipers <[hidden email]> wrote:
>
> Excellent pointers guys, thanks for the feedback.
>
> Creating the stored procedure in Lasso is probably a bit daft in the
> context of DRY, I agree: why create the procedure every time you call the
> page that uses it? To be honest, I hadn't put much thought into that part
> yet - I was just annoyed that I couldn;t get the code to run to begin with.
>
> Steve, you're right - the code will run if you take out the part that deals
> with delimiters, and you have to call the procedure in a separate trip to
> the database (connection), it seems.
>
> My idea behind the whole exercise was to run a number of statements
> entirely in MySQL, including an IF statement that check if a certain value
> already exists. That typeof  IF statement is only supported within stored
> procedures, so off I went on my little project.
>
> As you can probably tell, I'm new to the concept of stored procedures, and
> they do seem useful. I'll keep playing with my latest discoveries. Watch
> this space.
>
> Cheers
>
> Pier
>
>> On 18 March 2016 at 21:38, Steffan Cline <[hidden email]> wrote:
>>
>> Pier,
>>
>> action_statement should be inside the inline statement.
>>
>> Why are you creating the stored procedure from Lasso?
>>
>> Shouldn’t that just be created in MySQL and then called from Lasso?
>>
>>
>> Thank you,
>> Steffan Cline
>> 602-793-0014
>>
>>
>>
>>
>>
>>
>>
>>
>> On 3/18/16, 2:07 PM, "Pier Kuipers" <[hidden email] on
>> behalf of [hidden email]> wrote:
>>
>>> Hi Steffan :-)
>>>
>>> Lasso doesn't even get that far:
>>>
>>> local('teststatement' = 'DELIMITER //
>>>  CREATE PROCEDURE GetAllProducts()
>>>  BEGIN
>>>  SELECT * FROM file_definitions;
>>>  END //
>>>  DELIMITER ;');
>>> inline($gv_sql,-sql=#teststatement);
>>> /inline;
>>> action_statement;
>>>
>>> Before Lasso gets to report the action_statement, it reports:
>>>
>>> 1064: You have an error in your SQL syntax; check the manual that
>>> corresponds to your MySQL server version for the right syntax to use near
>>> 'DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM
>>> file_defini' at line 1
>>>
>>> I'm totally baffled - would really like to get this to work... :-(
>>>
>>> Cheers
>>>
>>> Pier
>>>
>>>> On 18 March 2016 at 20:49, Steffan Cline <[hidden email]> wrote:
>>>>
>>>> For the fun of it, put action_statement just after your inline and see
>>>> what Lasso is sending to MySQL.
>>>>
>>>>
>>>>
>>>> Thank you,
>>>> Steffan Cline
>>>> 602-793-0014
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On 3/18/16, 1:28 PM, "Pier Kuipers" <[hidden email]
>> on
>>>> behalf of [hidden email]> wrote:
>>>>
>>>>> Nope - just that really simple procedure in my example will throw an
>> error
>>>>> in Lasso Admin's sql utility thingamajig. It's as if stored procedures
>> are
>>>>> just not supported :-(
>>>>>
>>>>> Thanks
>>>>>
>>>>> Pier
>>>>> On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <
>>>> [hidden email]>
>>>>> wrote:
>>>>>
>>>>>> Do you have a preceding statement that does not terminate with ';'?
>>>>>> Sometimes I forget to do that when I execute more than one statement
>> to
>>>> get
>>>>>> multiple result sets.
>>>>>>
>>>>>> --steve
>>>>>>
>>>>>>
>>>>>> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers)
>> pronounced:
>>>>>>
>>>>>> Hi All,
>>>>>>>
>>>>>>> Having tried everything, I'm now completely at a loss when trying to
>>>> get a
>>>>>>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and
>> server
>>>> are
>>>>>>> version 5.6.
>>>>>>>
>>>>>>> The stored procedure is quite complex, but the problem appears to be
>>>> with
>>>>>>> declaring any kind of procedure, e.g.
>>>>>>>
>>>>>>> DELIMITER //
>>>>>>> CREATE PROCEDURE GetAllDefinitions()
>>>>>>> BEGIN
>>>>>>> SELECT * FROM file_definitions;
>>>>>>> END //
>>>>>>> DELIMITER ;
>>>>>>>
>>>>>>> If I run the above query in MySQL directly (or in Navicat)
>> everything
>>>> is
>>>>>>> fine - same with the complex procedure I'm trying to use.
>>>>>>> Runing the same query in Lasso results in an error, however - even
>> when
>>>>>>> using the SQL utility in Lasso Admin:
>>>>>>>
>>>>>>> Error: You have an error in your SQL syntax; check the manual that
>>>>>>> corresponds to your MySQL server version for the right syntax to use
>>>> near
>>>>>>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT *
>> FROM
>>>>>>> file_' at line 1 (1064)
>>>>>>>
>>>>>>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's
>>>> Mysql
>>>>>>> error keeps talking about "line 1", seemingly ignoring carriage
>> returns
>>>>>>> completely.
>>>>>>>
>>>>>>> Has anyone got any idea what I could be doing wrong?
>>>>>>>
>>>>>>> Cheers
>>>>>> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>>>>> Steve Piercy              Website Builder              Soquel, CA
>>>>>> <[hidden email]>               <http://www.StevePiercy.com/>
>>>>>>
>>>>>>
>>>>>> #############################################################
>>>>>>
>>>>>> This message is sent to you because you are subscribed to
>>>>>> the mailing list Lasso [hidden email]
>>>>>> Official list archives available at http://www.lassotalk.com
>>>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>>>> Send administrative queries to  <[hidden email]>
>>>>>
>>>>> #############################################################
>>>>>
>>>>> This message is sent to you because you are subscribed to
>>>>> the mailing list Lasso [hidden email]
>>>>> Official list archives available at http://www.lassotalk.com
>>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>>> Send administrative queries to  <[hidden email]>
>>>>
>>>>
>>>> #############################################################
>>>>
>>>> This message is sent to you because you are subscribed to
>>>>  the mailing list Lasso [hidden email]
>>>> Official list archives available at http://www.lassotalk.com
>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>>
>>>
>>> --
>>> Pier Kuipers
>>> Mobile: +353 87 294 3063
>>> Blog: http://www.pierkuipers.com
>>> Facebook: http://www.facebook.com/pier.kuipers
>>> Twitter: @pkvisualid
>>> AIM: pkvisualid
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>>  the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
>
> --
> Pier Kuipers
> Mobile: +353 87 294 3063
> Blog: http://www.pierkuipers.com
> Facebook: http://www.facebook.com/pier.kuipers
> Twitter: @pkvisualid
> AIM: pkvisualid
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Pier Kuipers
OK, here goes...
The table ("pagefiles") contains records of multipage documents, each row
is a page, and columns relate the relationship (page number x of total,
child of master page y):
+-------------------------------------------------+
|  id   |  page_no  |  page_total  |  parent_id   |
+-------------------------------------------------+
|  12   |  1        |  3           |  NULL        |
+-------------------------------------------------+
|  13   |  2        |  3           |  12          |
+-------------------------------------------------+
|  14   |  3        |  3           |  12          |
+-------------------------------------------------+

Eventually got my Lassoified stored procedure to work by leaving out the
DELIMITER part.
The idea is, that if you remove a row, the total number of pages is
recalculated for all related rows, and all pages are renumbered in sequence:

<?LassoScript

    local('fileToRemove' = (action_param('fileRemove')));
    local('multipageUpdateSQL' = 'DROP PROCEDURE IF EXISTS updateMultipage;
        CREATE PROCEDURE updateMultipage(IN fileToRemove INT(11))
        BEGIN
            SELECT @currentPageNo:=
                (SELECT page_no
                FROM pagefiles
                WHERE id=@fileToRemove);
            SELECT @parentID:=
                (SELECT parent_id
                FROM pagefiles
                WHERE id=@fileToRemove);
                IF (@currentPageNo > 1 AND @parentID > 0) THEN
                    UPDATE pagefiles
                        SET parent_id=0,
                        page_no=1,
                        page_total=1
                        WHERE id=@fileToRemove;
                    UPDATE pagefiles
                        SET page_no=(page_no - 1)
                        WHERE
                        parent_id=@parentID
                        AND page_no > @currentPageNo;
                    UPDATE pagefiles
                        SET page_total=
                        (SELECT IFNULL(
                            (SELECT * FROM
                                (SELECT MAX(page_no)
                                FROM pagefiles
                                WHERE parent_id=@parentID) AS pgno), 1))
                        WHERE id=@parentID
                            OR parent_id=@parentID;
                END IF;
        END');

    inline($gv_sql, -sql=#multipageUpdateSQL);
    /inline;

    inline($gv_sql, -sql='SET @fileToRemove:='+#fileToRemove+'; CALL
updateMultipage(@fileToRemove);');
    /inline;

?>

Bit of a mouthful, I know. Pity it takes two queries. Haven't tried running
the second one without the first one, once the stored procedure is
(supposedly) in place :-/

Cheers

Pier



On 18 March 2016 at 23:48, Steffan Cline <[hidden email]> wrote:

> Share your query in pseudo code. We can probably help you.
>
> Thanks,
> Steffan Cline
> [hidden email]
> 602-793-0014
>
> > On Mar 18, 2016, at 4:45 PM, Pier Kuipers <[hidden email]> wrote:
> >
> > Excellent pointers guys, thanks for the feedback.
> >
> > Creating the stored procedure in Lasso is probably a bit daft in the
> > context of DRY, I agree: why create the procedure every time you call the
> > page that uses it? To be honest, I hadn't put much thought into that part
> > yet - I was just annoyed that I couldn;t get the code to run to begin
> with.
> >
> > Steve, you're right - the code will run if you take out the part that
> deals
> > with delimiters, and you have to call the procedure in a separate trip to
> > the database (connection), it seems.
> >
> > My idea behind the whole exercise was to run a number of statements
> > entirely in MySQL, including an IF statement that check if a certain
> value
> > already exists. That typeof  IF statement is only supported within stored
> > procedures, so off I went on my little project.
> >
> > As you can probably tell, I'm new to the concept of stored procedures,
> and
> > they do seem useful. I'll keep playing with my latest discoveries. Watch
> > this space.
> >
> > Cheers
> >
> > Pier
> >
> >> On 18 March 2016 at 21:38, Steffan Cline <[hidden email]> wrote:
> >>
> >> Pier,
> >>
> >> action_statement should be inside the inline statement.
> >>
> >> Why are you creating the stored procedure from Lasso?
> >>
> >> Shouldn’t that just be created in MySQL and then called from Lasso?
> >>
> >>
> >> Thank you,
> >> Steffan Cline
> >> 602-793-0014
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> On 3/18/16, 2:07 PM, "Pier Kuipers" <[hidden email]
> on
> >> behalf of [hidden email]> wrote:
> >>
> >>> Hi Steffan :-)
> >>>
> >>> Lasso doesn't even get that far:
> >>>
> >>> local('teststatement' = 'DELIMITER //
> >>>  CREATE PROCEDURE GetAllProducts()
> >>>  BEGIN
> >>>  SELECT * FROM file_definitions;
> >>>  END //
> >>>  DELIMITER ;');
> >>> inline($gv_sql,-sql=#teststatement);
> >>> /inline;
> >>> action_statement;
> >>>
> >>> Before Lasso gets to report the action_statement, it reports:
> >>>
> >>> 1064: You have an error in your SQL syntax; check the manual that
> >>> corresponds to your MySQL server version for the right syntax to use
> near
> >>> 'DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM
> >>> file_defini' at line 1
> >>>
> >>> I'm totally baffled - would really like to get this to work... :-(
> >>>
> >>> Cheers
> >>>
> >>> Pier
> >>>
> >>>> On 18 March 2016 at 20:49, Steffan Cline <[hidden email]> wrote:
> >>>>
> >>>> For the fun of it, put action_statement just after your inline and see
> >>>> what Lasso is sending to MySQL.
> >>>>
> >>>>
> >>>>
> >>>> Thank you,
> >>>> Steffan Cline
> >>>> 602-793-0014
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> On 3/18/16, 1:28 PM, "Pier Kuipers" <
> [hidden email]
> >> on
> >>>> behalf of [hidden email]> wrote:
> >>>>
> >>>>> Nope - just that really simple procedure in my example will throw an
> >> error
> >>>>> in Lasso Admin's sql utility thingamajig. It's as if stored
> procedures
> >> are
> >>>>> just not supported :-(
> >>>>>
> >>>>> Thanks
> >>>>>
> >>>>> Pier
> >>>>> On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <
> >>>> [hidden email]>
> >>>>> wrote:
> >>>>>
> >>>>>> Do you have a preceding statement that does not terminate with ';'?
> >>>>>> Sometimes I forget to do that when I execute more than one statement
> >> to
> >>>> get
> >>>>>> multiple result sets.
> >>>>>>
> >>>>>> --steve
> >>>>>>
> >>>>>>
> >>>>>> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers)
> >> pronounced:
> >>>>>>
> >>>>>> Hi All,
> >>>>>>>
> >>>>>>> Having tried everything, I'm now completely at a loss when trying
> to
> >>>> get a
> >>>>>>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and
> >> server
> >>>> are
> >>>>>>> version 5.6.
> >>>>>>>
> >>>>>>> The stored procedure is quite complex, but the problem appears to
> be
> >>>> with
> >>>>>>> declaring any kind of procedure, e.g.
> >>>>>>>
> >>>>>>> DELIMITER //
> >>>>>>> CREATE PROCEDURE GetAllDefinitions()
> >>>>>>> BEGIN
> >>>>>>> SELECT * FROM file_definitions;
> >>>>>>> END //
> >>>>>>> DELIMITER ;
> >>>>>>>
> >>>>>>> If I run the above query in MySQL directly (or in Navicat)
> >> everything
> >>>> is
> >>>>>>> fine - same with the complex procedure I'm trying to use.
> >>>>>>> Runing the same query in Lasso results in an error, however - even
> >> when
> >>>>>>> using the SQL utility in Lasso Admin:
> >>>>>>>
> >>>>>>> Error: You have an error in your SQL syntax; check the manual that
> >>>>>>> corresponds to your MySQL server version for the right syntax to
> use
> >>>> near
> >>>>>>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT *
> >> FROM
> >>>>>>> file_' at line 1 (1064)
> >>>>>>>
> >>>>>>> I have tried all sorts of line endings (\r, \n, \r\n), but Lasso's
> >>>> Mysql
> >>>>>>> error keeps talking about "line 1", seemingly ignoring carriage
> >> returns
> >>>>>>> completely.
> >>>>>>>
> >>>>>>> Has anyone got any idea what I could be doing wrong?
> >>>>>>>
> >>>>>>> Cheers
> >>>>>> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> >>>>>> Steve Piercy              Website Builder              Soquel, CA
> >>>>>> <[hidden email]>               <http://www.StevePiercy.com/>
> >>>>>>
> >>>>>>
> >>>>>> #############################################################
> >>>>>>
> >>>>>> This message is sent to you because you are subscribed to
> >>>>>> the mailing list Lasso [hidden email]
> >>>>>> Official list archives available at http://www.lassotalk.com
> >>>>>> To unsubscribe, E-mail to: <[hidden email]>
> >>>>>> Send administrative queries to  <[hidden email]>
> >>>>>
> >>>>> #############################################################
> >>>>>
> >>>>> This message is sent to you because you are subscribed to
> >>>>> the mailing list Lasso [hidden email]
> >>>>> Official list archives available at http://www.lassotalk.com
> >>>>> To unsubscribe, E-mail to: <[hidden email]>
> >>>>> Send administrative queries to  <[hidden email]>
> >>>>
> >>>>
> >>>> #############################################################
> >>>>
> >>>> This message is sent to you because you are subscribed to
> >>>>  the mailing list Lasso [hidden email]
> >>>> Official list archives available at http://www.lassotalk.com
> >>>> To unsubscribe, E-mail to: <[hidden email]>
> >>>> Send administrative queries to  <[hidden email]>
> >>>
> >>>
> >>>
> >>> --
> >>> Pier Kuipers
> >>> Mobile: +353 87 294 3063
> >>> Blog: http://www.pierkuipers.com
> >>> Facebook: http://www.facebook.com/pier.kuipers
> >>> Twitter: @pkvisualid
> >>> AIM: pkvisualid
> >>>
> >>> #############################################################
> >>>
> >>> This message is sent to you because you are subscribed to
> >>> the mailing list Lasso [hidden email]
> >>> Official list archives available at http://www.lassotalk.com
> >>> To unsubscribe, E-mail to: <[hidden email]>
> >>> Send administrative queries to  <[hidden email]>
> >>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to
> >>  the mailing list Lasso [hidden email]
> >> Official list archives available at http://www.lassotalk.com
> >> To unsubscribe, E-mail to: <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >
> >
> >
> > --
> > Pier Kuipers
> > Mobile: +353 87 294 3063
> > Blog: http://www.pierkuipers.com
> > Facebook: http://www.facebook.com/pier.kuipers
> > Twitter: @pkvisualid
> > AIM: pkvisualid
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> >  the mailing list Lasso [hidden email]
> > Official list archives available at http://www.lassotalk.com
> > To unsubscribe, E-mail to: <[hidden email]>
> > Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>



--
Pier Kuipers
Mobile: +353 87 294 3063
Blog: http://www.pierkuipers.com
Facebook: http://www.facebook.com/pier.kuipers
Twitter: @pkvisualid
AIM: pkvisualid

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Stored Procedures and Lasso 8.6.3

Cory Robin
try adding a -statementonly and output the statement to the screen.  It
won't error because it won't send it to MySQL.



- Cory

On Fri, Mar 18, 2016 at 7:02 PM, Pier Kuipers <[hidden email]> wrote:

> OK, here goes...
> The table ("pagefiles") contains records of multipage documents, each row
> is a page, and columns relate the relationship (page number x of total,
> child of master page y):
> +-------------------------------------------------+
> |  id   |  page_no  |  page_total  |  parent_id   |
> +-------------------------------------------------+
> |  12   |  1        |  3           |  NULL        |
> +-------------------------------------------------+
> |  13   |  2        |  3           |  12          |
> +-------------------------------------------------+
> |  14   |  3        |  3           |  12          |
> +-------------------------------------------------+
>
> Eventually got my Lassoified stored procedure to work by leaving out the
> DELIMITER part.
> The idea is, that if you remove a row, the total number of pages is
> recalculated for all related rows, and all pages are renumbered in
> sequence:
>
> <?LassoScript
>
>     local('fileToRemove' = (action_param('fileRemove')));
>     local('multipageUpdateSQL' = 'DROP PROCEDURE IF EXISTS updateMultipage;
>         CREATE PROCEDURE updateMultipage(IN fileToRemove INT(11))
>         BEGIN
>             SELECT @currentPageNo:=
>                 (SELECT page_no
>                 FROM pagefiles
>                 WHERE id=@fileToRemove);
>             SELECT @parentID:=
>                 (SELECT parent_id
>                 FROM pagefiles
>                 WHERE id=@fileToRemove);
>                 IF (@currentPageNo > 1 AND @parentID > 0) THEN
>                     UPDATE pagefiles
>                         SET parent_id=0,
>                         page_no=1,
>                         page_total=1
>                         WHERE id=@fileToRemove;
>                     UPDATE pagefiles
>                         SET page_no=(page_no - 1)
>                         WHERE
>                         parent_id=@parentID
>                         AND page_no > @currentPageNo;
>                     UPDATE pagefiles
>                         SET page_total=
>                         (SELECT IFNULL(
>                             (SELECT * FROM
>                                 (SELECT MAX(page_no)
>                                 FROM pagefiles
>                                 WHERE parent_id=@parentID) AS pgno), 1))
>                         WHERE id=@parentID
>                             OR parent_id=@parentID;
>                 END IF;
>         END');
>
>     inline($gv_sql, -sql=#multipageUpdateSQL);
>     /inline;
>
>     inline($gv_sql, -sql='SET @fileToRemove:='+#fileToRemove+'; CALL
> updateMultipage(@fileToRemove);');
>     /inline;
>
> ?>
>
> Bit of a mouthful, I know. Pity it takes two queries. Haven't tried running
> the second one without the first one, once the stored procedure is
> (supposedly) in place :-/
>
> Cheers
>
> Pier
>
>
>
> On 18 March 2016 at 23:48, Steffan Cline <[hidden email]> wrote:
>
> > Share your query in pseudo code. We can probably help you.
> >
> > Thanks,
> > Steffan Cline
> > [hidden email]
> > 602-793-0014
> >
> > > On Mar 18, 2016, at 4:45 PM, Pier Kuipers <[hidden email]>
> wrote:
> > >
> > > Excellent pointers guys, thanks for the feedback.
> > >
> > > Creating the stored procedure in Lasso is probably a bit daft in the
> > > context of DRY, I agree: why create the procedure every time you call
> the
> > > page that uses it? To be honest, I hadn't put much thought into that
> part
> > > yet - I was just annoyed that I couldn;t get the code to run to begin
> > with.
> > >
> > > Steve, you're right - the code will run if you take out the part that
> > deals
> > > with delimiters, and you have to call the procedure in a separate trip
> to
> > > the database (connection), it seems.
> > >
> > > My idea behind the whole exercise was to run a number of statements
> > > entirely in MySQL, including an IF statement that check if a certain
> > value
> > > already exists. That typeof  IF statement is only supported within
> stored
> > > procedures, so off I went on my little project.
> > >
> > > As you can probably tell, I'm new to the concept of stored procedures,
> > and
> > > they do seem useful. I'll keep playing with my latest discoveries.
> Watch
> > > this space.
> > >
> > > Cheers
> > >
> > > Pier
> > >
> > >> On 18 March 2016 at 21:38, Steffan Cline <[hidden email]> wrote:
> > >>
> > >> Pier,
> > >>
> > >> action_statement should be inside the inline statement.
> > >>
> > >> Why are you creating the stored procedure from Lasso?
> > >>
> > >> Shouldn’t that just be created in MySQL and then called from Lasso?
> > >>
> > >>
> > >> Thank you,
> > >> Steffan Cline
> > >> 602-793-0014
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> On 3/18/16, 2:07 PM, "Pier Kuipers" <
> [hidden email]
> > on
> > >> behalf of [hidden email]> wrote:
> > >>
> > >>> Hi Steffan :-)
> > >>>
> > >>> Lasso doesn't even get that far:
> > >>>
> > >>> local('teststatement' = 'DELIMITER //
> > >>>  CREATE PROCEDURE GetAllProducts()
> > >>>  BEGIN
> > >>>  SELECT * FROM file_definitions;
> > >>>  END //
> > >>>  DELIMITER ;');
> > >>> inline($gv_sql,-sql=#teststatement);
> > >>> /inline;
> > >>> action_statement;
> > >>>
> > >>> Before Lasso gets to report the action_statement, it reports:
> > >>>
> > >>> 1064: You have an error in your SQL syntax; check the manual that
> > >>> corresponds to your MySQL server version for the right syntax to use
> > near
> > >>> 'DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM
> > >>> file_defini' at line 1
> > >>>
> > >>> I'm totally baffled - would really like to get this to work... :-(
> > >>>
> > >>> Cheers
> > >>>
> > >>> Pier
> > >>>
> > >>>> On 18 March 2016 at 20:49, Steffan Cline <[hidden email]> wrote:
> > >>>>
> > >>>> For the fun of it, put action_statement just after your inline and
> see
> > >>>> what Lasso is sending to MySQL.
> > >>>>
> > >>>>
> > >>>>
> > >>>> Thank you,
> > >>>> Steffan Cline
> > >>>> 602-793-0014
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>> On 3/18/16, 1:28 PM, "Pier Kuipers" <
> > [hidden email]
> > >> on
> > >>>> behalf of [hidden email]> wrote:
> > >>>>
> > >>>>> Nope - just that really simple procedure in my example will throw
> an
> > >> error
> > >>>>> in Lasso Admin's sql utility thingamajig. It's as if stored
> > procedures
> > >> are
> > >>>>> just not supported :-(
> > >>>>>
> > >>>>> Thanks
> > >>>>>
> > >>>>> Pier
> > >>>>> On 18 Mar 2016 20:23, "Steve Piercy - Website Builder" <
> > >>>> [hidden email]>
> > >>>>> wrote:
> > >>>>>
> > >>>>>> Do you have a preceding statement that does not terminate with
> ';'?
> > >>>>>> Sometimes I forget to do that when I execute more than one
> statement
> > >> to
> > >>>> get
> > >>>>>> multiple result sets.
> > >>>>>>
> > >>>>>> --steve
> > >>>>>>
> > >>>>>>
> > >>>>>> On 3/18/16 at 7:56 PM, [hidden email] (Pier Kuipers)
> > >> pronounced:
> > >>>>>>
> > >>>>>> Hi All,
> > >>>>>>>
> > >>>>>>> Having tried everything, I'm now completely at a loss when trying
> > to
> > >>>> get a
> > >>>>>>> MySQL stored procedure to run in Lasso 8.6.3 - MySQL client and
> > >> server
> > >>>> are
> > >>>>>>> version 5.6.
> > >>>>>>>
> > >>>>>>> The stored procedure is quite complex, but the problem appears to
> > be
> > >>>> with
> > >>>>>>> declaring any kind of procedure, e.g.
> > >>>>>>>
> > >>>>>>> DELIMITER //
> > >>>>>>> CREATE PROCEDURE GetAllDefinitions()
> > >>>>>>> BEGIN
> > >>>>>>> SELECT * FROM file_definitions;
> > >>>>>>> END //
> > >>>>>>> DELIMITER ;
> > >>>>>>>
> > >>>>>>> If I run the above query in MySQL directly (or in Navicat)
> > >> everything
> > >>>> is
> > >>>>>>> fine - same with the complex procedure I'm trying to use.
> > >>>>>>> Runing the same query in Lasso results in an error, however -
> even
> > >> when
> > >>>>>>> using the SQL utility in Lasso Admin:
> > >>>>>>>
> > >>>>>>> Error: You have an error in your SQL syntax; check the manual
> that
> > >>>>>>> corresponds to your MySQL server version for the right syntax to
> > use
> > >>>> near
> > >>>>>>> 'DELIMITER // CREATE PROCEDURE GetAllDefinitions() BEGIN SELECT *
> > >> FROM
> > >>>>>>> file_' at line 1 (1064)
> > >>>>>>>
> > >>>>>>> I have tried all sorts of line endings (\r, \n, \r\n), but
> Lasso's
> > >>>> Mysql
> > >>>>>>> error keeps talking about "line 1", seemingly ignoring carriage
> > >> returns
> > >>>>>>> completely.
> > >>>>>>>
> > >>>>>>> Has anyone got any idea what I could be doing wrong?
> > >>>>>>>
> > >>>>>>> Cheers
> > >>>>>> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > >>>>>> Steve Piercy              Website Builder              Soquel, CA
> > >>>>>> <[hidden email]>               <http://www.StevePiercy.com/>
> > >>>>>>
> > >>>>>>
> > >>>>>> #############################################################
> > >>>>>>
> > >>>>>> This message is sent to you because you are subscribed to
> > >>>>>> the mailing list Lasso [hidden email]
> > >>>>>> Official list archives available at http://www.lassotalk.com
> > >>>>>> To unsubscribe, E-mail to: <[hidden email]
> >
> > >>>>>> Send administrative queries to  <
> [hidden email]>
> > >>>>>
> > >>>>> #############################################################
> > >>>>>
> > >>>>> This message is sent to you because you are subscribed to
> > >>>>> the mailing list Lasso [hidden email]
> > >>>>> Official list archives available at http://www.lassotalk.com
> > >>>>> To unsubscribe, E-mail to: <[hidden email]>
> > >>>>> Send administrative queries to  <[hidden email]
> >
> > >>>>
> > >>>>
> > >>>> #############################################################
> > >>>>
> > >>>> This message is sent to you because you are subscribed to
> > >>>>  the mailing list Lasso [hidden email]
> > >>>> Official list archives available at http://www.lassotalk.com
> > >>>> To unsubscribe, E-mail to: <[hidden email]>
> > >>>> Send administrative queries to  <[hidden email]>
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> Pier Kuipers
> > >>> Mobile: +353 87 294 3063
> > >>> Blog: http://www.pierkuipers.com
> > >>> Facebook: http://www.facebook.com/pier.kuipers
> > >>> Twitter: @pkvisualid
> > >>> AIM: pkvisualid
> > >>>
> > >>> #############################################################
> > >>>
> > >>> This message is sent to you because you are subscribed to
> > >>> the mailing list Lasso [hidden email]
> > >>> Official list archives available at http://www.lassotalk.com
> > >>> To unsubscribe, E-mail to: <[hidden email]>
> > >>> Send administrative queries to  <[hidden email]>
> > >>
> > >>
> > >> #############################################################
> > >>
> > >> This message is sent to you because you are subscribed to
> > >>  the mailing list Lasso [hidden email]
> > >> Official list archives available at http://www.lassotalk.com
> > >> To unsubscribe, E-mail to: <[hidden email]>
> > >> Send administrative queries to  <[hidden email]>
> > >
> > >
> > >
> > > --
> > > Pier Kuipers
> > > Mobile: +353 87 294 3063
> > > Blog: http://www.pierkuipers.com
> > > Facebook: http://www.facebook.com/pier.kuipers
> > > Twitter: @pkvisualid
> > > AIM: pkvisualid
> > >
> > > #############################################################
> > >
> > > This message is sent to you because you are subscribed to
> > >  the mailing list Lasso [hidden email]
> > > Official list archives available at http://www.lassotalk.com
> > > To unsubscribe, E-mail to: <[hidden email]>
> > > Send administrative queries to  <[hidden email]>
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to
> >   the mailing list Lasso [hidden email]
> > Official list archives available at http://www.lassotalk.com
> > To unsubscribe, E-mail to: <[hidden email]>
> > Send administrative queries to  <[hidden email]>
> >
>
>
>
> --
> Pier Kuipers
> Mobile: +353 87 294 3063
> Blog: http://www.pierkuipers.com
> Facebook: http://www.facebook.com/pier.kuipers
> Twitter: @pkvisualid
> AIM: pkvisualid
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>