ZSQLProcessor problem

zippo · Post by **zippo** » 15.06.2008, 00:05

Hi!

After a long and happy living with Zeos I have a strange problem and I'm stuck here for several days, grrr.... Upon executing the Processor.Execute method I get an error. Delimiter is set to dtSetTerm. If I runt the script directly in MySQL Query browser, it works OK. I somehow can't understand what is wrong..

Ok, I get this error:

---------------------------
Import Error
---------------------------
SQL 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 '' at line 1

SQL:

CREATE DEFINER=`root`@`%` PROCEDURE folpo_d.`UseCardCredit`(IN_BOX_ID int, IN_SOCKET_ID Int, IN_CARD_ID Int, IN_OLD_COUNTER BigInt, IN_NEW_COUNTER Bigint)
COMMENT 'Poraba kredita na kartici glede na spremembe stevcov prikljucka'
BEGIN
DECLARE Val FLOAT;
DECLARE SocketType INT;

If IN_NEW_COUNTER IS NULL THEN
SET IN_NEW_COUNTER = 0;
END IF;

If IN_NEW_COUNTER IS NULL THEN
SET IN_NEW_COUNTER = 0;
END IF;

Call WriteLog(1, Concat('(UseCardCredit) IN_NEW_COUNTER: ', IN_NEW_COUNTER, ', IN_OLD_COUNTER: ', IN_OLD_COUNTER));

IF IN_NEW_COUNTER-IN_OLD_COUNTER > 0 THEN
SET @Val = GetMoneyFromUsage(IN_BOX_ID, IN_SOCKET_ID, IN_NEW_COUNTER-IN_OLD_COUNTER);
Call WriteLog(1, Concat('(UseCardCredit) Val= ', @Val));

IF @Val > 0 THEN
Call WriteLog(1, Concat('(UseCardCredit) Sprememba cene: ', @Val));
# Izracun odbitka cene
UPDATE CARD_ACTIVITY Set CREDIT = CREDIT - @Val Where CARD_ID=IN_CARD_ID;
# Logiranje za zgodovino - spremembe na stanju kartice
REPLACE INTO CARD_HISTORY Values (
NULL,
IN_CARD_ID,
NOW(),
@Val
);
END IF;

Call WriteLog(1, Concat('(UseCardCredit) Sprememba stevca: ', IN_NEW_COUNTER-IN_OLD_COUNTER));
# Logiranje za zgodovino - spremembe na stanju stevcev
REPLACE INTO SOCKET_HISTORY Values (
NULL,
IN_BOX_ID,
IN_SOCKET_ID,
IN_CARD_ID,
NOW(),
IN_OLD_COUNTER,
IN_NEW_COUNTER
);
END IF;
END
---------------------------
OK
---------------------------

The script is here (please replace [TARGET_DB] with a valid database name):

Code: Select all

--
-- Structure for procedure `AddCreditToCard`
--

DROP PROCEDURE IF EXISTS [TARGET_DB].`AddCreditToCard` $$

CREATE DEFINER=`root`@`%` PROCEDURE [TARGET_DB].`AddCreditToCard`(CARD Int, ADD_CREDIT float)
    COMMENT 'Procedura za dodajanje kredita na kartice'
BEGIN
  Update CARD_ACTIVITY Set CREDIT=CREDIT+ADD_CREDIT Where CARD_ID=CARD;
  # Logiranje za zgodovino - spremembe na stanju kartice
  REPLACE INTO CARD_HISTORY Values (
    NULL,
    CARD,
    NOW(),
    ADD_CREDIT
  );
END $$

--
-- Structure for procedure `UseCardCredit`
--

DROP PROCEDURE IF EXISTS [TARGET_DB].`UseCardCredit` $$

CREATE DEFINER=`root`@`%` PROCEDURE [TARGET_DB].`UseCardCredit`(IN_BOX_ID int, IN_SOCKET_ID Int, IN_CARD_ID Int, IN_OLD_COUNTER BigInt, IN_NEW_COUNTER Bigint)
    COMMENT 'Poraba kredita na kartici glede na spremembe stevcov prikljucka'
BEGIN
  DECLARE Val FLOAT;
  DECLARE SocketType INT;

  If IN_NEW_COUNTER IS NULL THEN
    SET IN_NEW_COUNTER = 0;
  END IF;

  If IN_NEW_COUNTER IS NULL THEN
    SET IN_NEW_COUNTER = 0;
  END IF;

  Call WriteLog(1, Concat('(UseCardCredit) IN_NEW_COUNTER: ', IN_NEW_COUNTER, ', IN_OLD_COUNTER: ', IN_OLD_COUNTER));

  IF IN_NEW_COUNTER-IN_OLD_COUNTER > 0 THEN
    SET @Val = GetMoneyFromUsage(IN_BOX_ID, IN_SOCKET_ID, IN_NEW_COUNTER-IN_OLD_COUNTER);
    Call WriteLog(1, Concat('(UseCardCredit) Val= ', @Val));

    IF @Val > 0 THEN
      Call WriteLog(1, Concat('(UseCardCredit) Sprememba cene: ', @Val));
      # Izracun odbitka cene
      UPDATE CARD_ACTIVITY Set CREDIT = CREDIT - @Val Where CARD_ID=IN_CARD_ID;
      # Logiranje za zgodovino - spremembe na stanju kartice
      REPLACE INTO CARD_HISTORY Values (
        NULL,
        IN_CARD_ID,
        NOW(),
        @Val
      );
    END IF;

    Call WriteLog(1, Concat('(UseCardCredit) Sprememba stevca: ', IN_NEW_COUNTER-IN_OLD_COUNTER));
    # Logiranje za zgodovino - spremembe na stanju stevcev
    REPLACE INTO SOCKET_HISTORY Values (
      NULL,
      IN_BOX_ID,
      IN_SOCKET_ID,
      IN_CARD_ID,
      NOW(),
      IN_OLD_COUNTER,
      IN_NEW_COUNTER
    );
  END IF;
END $$

--
-- Structure for procedure `WriteLog`
--

DROP PROCEDURE IF EXISTS [TARGET_DB].`WriteLog` $$

CREATE DEFINER=`root`@`%` PROCEDURE [TARGET_DB].`WriteLog`(MsgLevel int, MsgTxt varchar(255))
BEGIN
  Insert Into LOG Values (NULL, NOW(), MsgLevel, MsgTxt, 0);
  if MsgTxt = "CLEAR" THEN
    Delete From LOG;
  End If;
END $$

--
-- Structure for function `GetMoneyFromUsage`
--

DROP FUNCTION IF EXISTS [TARGET_DB].`GetMoneyFromUsage` $$

CREATE DEFINER=`root`@`%` FUNCTION [TARGET_DB].`GetMoneyFromUsage`(BoxID int, SocketID int, UsageDelta float) RETURNS float
BEGIN
  Declare NoDefinedType int;

  Call WriteLog(1, Concat('(GetMoneyFromUsage) BoxID: ', BoxID, ', SocketID: ', SocketID,', UsageDelta: ', UsageDelta));

  Select IfNull(SOCKET_TYPE.ID,-1) As HasType From SOCKETS Left Outer Join SOCKET_TYPE On TYPE_ID=SOCKET_TYPE.ID
  Where SOCKETS.BOX_ID=BoxID And SOCKETS.SOCKET_ID=SocketID Into @NoDefinedType;

  IF @NoDefinedType = -1 THEN
    call WriteLog(4, Concat('(GetMoneyFromUsage) Socket ', BoxID,'-',SocketID,' (BOX_ID-SOCKET_ID) has no defined type! No Money calculation will be done!'));
  END IF;

  RETURN
  (
  Select If(PRICE Is NULL, 0, PRICE) * UsageDelta As PRICE
  From SOCKETS Left Outer Join SOCKET_TYPE On TYPE_ID=SOCKET_TYPE.ID
               Left Outer Join PRICELIST On SOURCE_TYPE=PRICELIST.SOURCE
  Where SOCKETS.BOX_ID=BoxID And SOCKETS.SOCKET_ID=SocketID
  );
END $$

--
-- Structure for function `GetSocketID`
--

DROP FUNCTION IF EXISTS [TARGET_DB].`GetSocketID`$$

CREATE DEFINER=`root`@`%` FUNCTION [TARGET_DB].`GetSocketID`(IN_BOX_ID Int, IN_DISPLAY_ID Int, IN_BOX_GROUP char(3)) RETURNS int(11)
BEGIN
  RETURN
  (
  Select SOCKET_ID
  From VIEW_SOCKETS
  Where IN_BOX_ID=BOX_ID And IN_DISPLAY_ID=DISPLAY And IN_BOX_GROUP=GROUP_FROM_BOX
  );
END $$

DROP TRIGGER IF EXISTS [TARGET_DB].CalcCena $$

CREATE TRIGGER [TARGET_DB].CalcCena BEFORE UPDATE ON [TARGET_DB].socket_activity
FOR EACH ROW
BEGIN
  DECLARE Val FLOAT;
  DECLARE SocketType INT;

  IF NEW.COUNTER-OLD.COUNTER > 0 THEN

    SET @Val = GetMoneyFromUsage(NEW.BOX_ID, NEW.SOCKET_ID, NEW.COUNTER-OLD.COUNTER);

    IF @Val > 0 THEN
#      Call WriteLog(Concat('Sprememba cene: ', @Val));
      # Izracun odbitka cene
      UPDATE CARD_ACTIVITY Set CREDIT = CREDIT - @Val Where CARD_ID=NEW.CARD_BINDED;
      # Logiranje za zgodovino - spremembe na stanju kartice
      REPLACE INTO CARD_HISTORY Values (
        NULL,
        NEW.CARD_BINDED,
        NOW(),
        @Val
      );
    END IF;

#    Call WriteLog(Concat('Sprememba stevca: ', NEW.COUNTER-OLD.COUNTER));
    # Logiranje za zgodovino - spremembe na stanju stevcev
    REPLACE INTO SOCKET_HISTORY Values (
      NULL,
      NEW.BOX_ID,
      NEW.SOCKET_ID,
      NEW.CARD_BINDED,
      NOW(),
      OLD.COUNTER,
      NEW.COUNTER
    );
#    Call WriteLog('Sprememba stanja box-a na ACTIVE (3)');
    # Sprememba stanja box-a na ACTIVE (3)
    UPDATE SOCKET_ACTIVITY Set STATUS=3 Where BOX_ID=NEW.BOX_ID And SOCKET_ID=NEW.SOCKET_ID;
  END IF;
END $$

technobot · Post by **technobot** » 15.06.2008, 11:25

I suspect it might have something to do with that `%` on the first line. Maybe the processor omits the % for some reason, or something like that... Try putting a TZSQLMonitor to see what's the actual SQL that gets passed to the server.

zippo · Post by **zippo** » 15.06.2008, 13:26

Good idea.. I'll try

zippo · Post by **zippo** » 15.06.2008, 13:49

I found the error - character # is not allowed.. Thanx - your suggestion helped me a lot!