Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Martina Jindrová
  Where is Martina Jindrová?
 Trutnov
 Czech Republic
 Martina Jindrová
 To: juancarlos jimenez
  Where is juancarlos jimenez?
 guayaquil
 Ecuador
 juancarlos jimenez
 Tags
Subject: RE: group concat max len code from mysql
Thread ID: 395570 Message ID: 395598 # Views: 42 # Ratings: 0
Version: Visual FoxPro 6 Category: General VFP Topics
Date: Thursday, January 16, 2014 7:39:31 AM         
   


> hi i have this code in mysql
>
> SET @@group_concat_max_len = 20024;
> SET @sqlprueba=NULL,@sqlprueba0=NULL,@sqlprueba1=NULL,@sqlprueba2=NULL, @sqlprueba3=NULL, @sqlprueba4=NULL;
> SELECT
> GROUP_CONCAT(DISTINCT CONCAT(' COUNT(CASE WHEN (monthname(fecha)="',MES,'" AND day(fecha)="',DIA,'" ) THEN autorizacion ELSE NULL END) AS ',dia,mes) )
> INTO @sqlprueba
> FROM
> (SELECT DAY(FECHA) DIA, MONTHNAME(FECHA) MES FROM servicio WHERE FECHA BETWEEN '2014-01-01' AND '2014-01-31' GROUP BY MONTHNAME(FECHA),DAY(FECHA)) T2;
> SET @sqlprueba2=CONCAT('select hour(hora) hora,',@sqlprueba,' from servicio WHERE valido!=3 and fecha BETWEEN "2014-01-01" AND "2014-01-31" GROUP BY HOUR(hora)');
> PREPARE stmt FROM @sqlprueba2;
> EXECUTE stmt;
>
> i use it without problems in my sql browser etc, but when i try to use it in the vf6 i cant make it run
>
> i use it in this way
>
> reporte = [ SET @@group_concat_max_len = 20024; ]+;
> [ SET @sqlprueba=NULL, @sqlprueba0=NULL, @sqlprueba1=NULL, @sqlprueba2=NULL, @sqlprueba3=NULL, @sqlprueba4=NULL; ]+;
> [ SELECT ]+;
> [ GROUP_CONCAT(DISTINCT CONCAT(' COUNT(CASE WHEN (monthname(fecha)="',MES,'" AND day(fecha)="',DIA,'" ) THEN autorizacion ELSE NULL END) AS ',dia,mes) ) ]+;
> [ INTO @sqlprueba ]+;
> [ FROM ]+;
> [(SELECT DAY(FECHA) DIA, MONTHNAME(FECHA) MES FROM servicio WHERE FECHA BETWEEN ('2014-01-01') and ('2014-01-31') ]+;
> [ GROUP BY MONTHNAME(FECHA),DAY(FECHA)) T2; ]+;
> [ SET @sqlprueba2=CONCAT('select hour(hora) hora,',@sqlprueba,' from servicio WHERE valido!=3 and fecha BETWEEN "2014-01-01" AND "2014-01-31" ]+;
> [ GROUP BY HOUR(hora)'); ]+;
> [ PREPARE stmt FROM @sqlprueba2; ]+;
> [ EXECUTE stmt; ]+;
> =SQLEXEC(enlace,reporte,"listareporte")
>
> what im making wrong??? please a lil help


Because Mysql has disabled batch processing from ODBC/OLEDB.

MartinaJ

JID: gorila@dione.zcu.cz

ENTIRE THREAD

group concat max len code from mysql Posted by juancarlos jimenez @ 1/15/2014 10:08:48 PM
RE: group concat max len code from mysql Posted by Martina Jindrová @ 1/16/2014 7:39:31 AM