List:General Discussion« Previous MessageNext Message »
From:Kailash R Date:October 16 2011 5:07pm
Subject:Re: User defined Variables
View as plain text  
Nice input Dan. Let me run some checks. My query is as follows:

select group_concat(Field1), field2, field3 from table1 group by field2,
field3 into str;
@sql = concat("select blah ... where field1 in ' ,str);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

So when the count(*) for the group by fields list exceeds a threshold (I
don't know what that value is) then @sql gets truncated because the
group_concat returns a big string. But I just ran your set
@a=repeat('a',1024*1024*10); query series to the heidiSQL connection to the
same environment but I it doesn't error out with the same problem. I suspect
server variables or what not .. not sure what.

Thanks for the feedback Dan I will post with more information.

Kailash.






On Sat, Oct 15, 2011 at 9:38 PM, Dan Nelson <dnelson@stripped> wrote:

> In the last episode (Oct 15), Kailash R said:
> > I was wondering if there is a way to increase the size of user defined
> > variables ?  I am not able to attribute a type to them and when I assign
> > @sql = 'a_text _field' , 'a_text_field' gets truncated if it exceeds the
> > size of @sql.  This is not wholesome as I am using @sql in a subsequent
> > prepare statement and my procedure errors out with a sql syntax error.
> > Any advice for this would help.
>
> Do you have example code that fails?  Mysql variables are probably limited
> to your server's max_packet value.  I am able to create variables
> containing
> long strings with no problems, at least:
>
> mysql> set @a=repeat('a',1024*1024*10);
> Query OK, 0 rows affected (0.95 sec)
>
> mysql> select length(@a);
> +------------+
> | length(@a) |
> +------------+
> |   10485760 |
> +------------+
> 1 row in set (0.05 sec)
>
> mysql>
>
> --
>        Dan Nelson
>        dnelson@stripped
>

Thread
User defined VariablesKailash R16 Oct
  • Re: User defined VariablesDan Nelson16 Oct
    • Re: User defined VariablesKailash R16 Oct
      • Re: User defined VariablesDan Nelson17 Oct
        • Re: User defined VariablesKailash R17 Oct