Hey everyone,
I’m building a match engine using bubble and a mysql database on an external hosting.
On one side the user is looking for certains qualifications and attributes in a database, and the result comes back order by a match percentage.
The problem is that the SQL sentence runing from the plugin SQL Database Connector does not work but if I run the exactly same SQL sentence from phpmyadmin it works perfect.
This is my SQL sentence:
DROP PROCEDURE IF EXISTS matchsearch;
delimiter //
CREATE PROCEDURE matchsearch(lista_og varchar(200))
wholeblock:BEGIN
DECLARE x INT;
DECLARE y DECIMAL(5,3);
DECLARE z INT;
DECLARE w INT;
DECLARE v INT;
DECLARE str VARCHAR(255);
DECLARE largo_lista INT;
DECLARE lista_profesional VARCHAR(255);
DECLARE cant_certificaciones INT;
DECLARE cant_registros INT;
DECLARE n_registro INT;
DECLARE lista VARCHAR(200);
SET cant_registros = (SELECT COUNT(*) from match_cert_temp);
SET n_registro=1;
OUTERLOOP: LOOP
if n_registro > cant_registros THEN LEAVE OUTERLOOP;
END IF;
SET lista=lista_og;
SET largo_lista=LENGTH(lista);
SET lista_profesional=(SELECT certificaciones from match_cert_temp where id = n_registro);
SET cant_certificaciones=LENGTH(lista)-LENGTH(REPLACE((lista), ‘,’, ‘’))+1;
SET w=cant_certificaciones;
SET x=0;
SET y=0;
SET z=0;
SET v=0;
SET str=‘’;
INNERLOOP: LOOP
IF x > w THEN
LEAVE INNERLOOP;
END IF;
SET str=SUBSTRING_INDEX(lista,‘,’,1);
SET z=LENGTH(str);
IF LOCATE(str,lista_profesional) > 0 then SET y=y+1; end if;
SET v=largo_lista-z-2;
SET lista=RIGHT(lista,v);
SET largo_lista=LENGTH(lista);
SET x=x+1;
ITERATE INNERLOOP;
END LOOP INNERLOOP;
SET y=y-1;
SET y=y/w*25;
UPDATE match_cert_temp set match_certificaciones = y where id=n_registro;
SET n_registro = n_registro+1;
ITERATE OUTERLOOP;
end LOOP OUTERLOOP;
END;//
DELIMITER ;
CREATE TEMPORARY TABLE IF NOT EXISTS match_cert_temp(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, id_registro INTEGER not null, id_usuario varchar(200) not null, certificaciones varchar(200) not null, match_certificaciones INTEGER);
insert into match_cert_temp(id_registro, id_usuario, certificaciones) SELECT id_registro, id_usuario, certificaciones from profesionales;
call matchsearch(‘API, BBDD, IT’);
SELECT ,
CASE
WHEN titulo =‘ERROR’ then ‘Error’
else porcentajetitulo + porcentajeinstitucion + porcentajegrado+porcentajecalificacion+match_certificaciones
end as porcentajematch
from
(
SELECT profesionales.id_usuario, profesionales.titulo, profesionales.institucion, profesionales.grado, profesionales.certificaciones, usuarios.Username, grados.nivel, usuarios.calificacion, match_cert_temp.match_certificaciones,
CASE
WHEN titulo = ‘Ingeniero Civil Informático’ then ‘20’
else 0
end as porcentajetitulo,
CASE
WHEN institucion = ‘Universidad de Chile’ then ‘12’
else 0
end as porcentajeinstitucion,
CASE
WHEN nivel >= 5 then ‘18’
else 0
end as porcentajegrado,
CASE when calificacion between 3 and 5 then calificacion5
else 0
end as porcentajecalificacion
FROM profesionales INNER join usuarios on profesionales.id_usuario = usuarios.id_usuario inner join grados on profesionales.grado = grados.nombre_grado inner join match_cert_temp on profesionales.id_usuario=match_cert_temp.id_usuario)
as tablactm2 order by porcentajematch DESC LIMIT 5
This is the error Im getting from bubble:
Does anyone knows how to fix it or why is this happening? Thanks!