Server Side Program that handles Database

Question

Server Side Program that handles Database

svagelis (Programmer)

(OP)

4 Apr 09 14:55

Hello to everyone,
i want to build a platform for my database applictions (transactional), using Visual Basic (Client)and mySQL (server).
My problem is that i cant pass arrays with data (to and from ) a storedprocedure. You see i want to handle all my database issues like transactions, locking server side checks etc from code what resides on the server. I worked with lagre Oracle databases and demanding db issues in the past and the standards we used backthen prooved very trustworthy. Well with oracle we used plsql records and tables and as far as i know there is no serious way (split large strings into variables) to achieve that with sp in mySQL.
Question is can i write code in vb that has all the SQL commands what supposed to be in the sp (Looping Inserts Updates Delletes ) WHAT RESIDES ON SERVER AND CALL THEM FROM MY CLIENTS???
And if so how to i call them from my clients ???

hmckillop (Programmer) · Answer 1 · 2009-04-07 05:05:45

Create the following stored procedure in mySQL and it can be used to split strings (e.g. you send "3,4,6,8,9" and the delimiter e.g. "," into the function and it returns
3
4
6
8
9

CODE

DROP PROCEDURE IF EXISTS split_string
  CREATE PROCEDURE split_string (
     IN input TEXT,
     IN delimiter VARCHAR(10)
  )
      SQL SECURITY INVOKER
      BEGIN
      DECLARE cur_position INT DEFAULT 1 ;
      DECLARE remainder TEXT;
      DECLARE cur_string VARCHAR(1000);
      DECLARE delimiter_length TINYINT UNSIGNED;
      DROP TEMPORARY TABLE IF EXISTS SplitValues;
      CREATE TEMPORARY TABLE SplitValues (
        value VARCHAR(1000) NOT NULL PRIMARY KEY
        ) ENGINE=MEMORY;
      SET remainder = input;
      SET delimiter_length = CHAR_LENGTH(delimiter);

       WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
        SET cur_position = INSTR(remainder, delimiter);

      IF cur_position = 0 THEN
        SET cur_string = remainder;
     ELSE
        SET cur_string = LEFT(remainder, cur_position - 1);
        END IF;

      IF TRIM(cur_string) != '' THEN
        INSERT INTO SplitValues VALUES (cur_string);
     END IF;

      SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);

      END WHILE;
      END

Then create another stored procedure to call this function with the list you need and the action you require. E.g. insert new records based on this list

CODE

INSERT INTO mytable (field1) SELECT * from split_string ("1,2,3,4",",")
--not sure if this is the format in mysql but something similiar should be possible

"I'm living so far beyond my income that we may almost be said to be living apart"

Come Join Us!

Posting Guidelines

Jobs

Server Side Program that handles Database