INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Server Side Program that handles Database

Server Side Program that handles Database

(OP)
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 ???
 

RE: Server Side Program that handles Database

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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close