LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Create an SQL procedure with SQL toolkit

Solved!
Go to solution

Hi,

 

I'm using the SQL toolkit to get data from a MySQL DB (using ODBC connectors).

I'd like to automatically create a procedure on my DB when lauching my software application, so that I'm sure it exists when I will use it.

But I can't figure out how to do so with the SQL Toolkit.

Indeed, trying to run this script (which works when run on MySQL workbench) returns the following error :

 

DELIMITER $$

CREATE DEFINER=`indus`@`%` PROCEDURE `FPY`(IN START_DATETIME datetime, IN END_DATETIME datetime)
BEGIN
CREATE temporary table ListingUUT
(
UUT_SERIAL_NUMBER VARCHAR (127),
TEST_COUNTER INT,
TEST_TIME datetime,
UUT_STATUS VARCHAR (32)
);

CREATE temporary table Stat_FPY
(
TESTED INT,
TESTED_ONCE INT,
RATIO FLOAT
);

INSERT INTO Stat_FPY VALUES (0,0,0);
INSERT INTO ListingUUT
(
SELECT
UUT_SERIAL_NUMBER, SUM(1), START_DATE_TIME, UUT_STATUS
FROM booster_results.uut_result
WHERE START_DATE_TIME BETWEEN START_DATETIME AND END_DATETIME
GROUP BY UUT_SERIAL_NUMBER
ORDER BY START_DATE_TIME DESC
)
;

END

 

=> Error -2147217900 occurred at NI_Database_API.lvlib:Conn Execute.vi->Untitled 1

Possible reason(s):
ADO Error: 0x80040E14
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.10-log]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 DEFINER=`indus`@`%` PROCEDURE `FPY`(IN START_DATETIME datet' at line 1 in NI_Database_API.lvlib:Conn Execute.vi->Untitled 1

 

Any idea so that I can create the procedure ?

CLA, CTA

View Cyril Gambini's profile on LinkedIn
0 Kudos
Message 1 of 3
(2,035 Views)
Solution
Accepted by topic author CyGa

Finally give up on creating the procedure with SQL Toolkit functions.

Made it using system exec.vi to directly adress mysql server with command line...

CLA, CTA

View Cyril Gambini's profile on LinkedIn
0 Kudos
Message 2 of 3
(1,823 Views)

Could you shared to solve photo for this issue?

0 Kudos
Message 3 of 3
(1,486 Views)