MySQL database for video clip info

[SpriggsySpriggs]

A post by @lawsonm1 earlier was talking about how he wanted to be able to store info about video clips in a file or something to be read back later so he can open them in VLC. This is just a test to show that it would work but here is my contribution using MySQL. I'm not posting the whole code here because I would need to use Source Merger and there would be a lot of cutting. I'll just post the main module that does the work :)

Code: QB64: [Select]

1. '$INCLUDE:'MYSQL.BI'
2. TYPE Video
3.     vid_id AS LONG
4.     video_name AS STRING
5.     coloration AS STRING
6.     clip_length AS STRING
7.     clip_type AS STRING
8. END TYPE
9.  
10. mydb = DB_Open("Spriggsy", "videouser", "pass@word", "")
11.  
12. DIM Video AS Video
13. Video.video_name = "Superman Returns"
14. Video.coloration = "color"
15. Video.clip_length = "2h49m"
16. Video.clip_type = "movie"
17.  
18. Video.vid_id = AddVideo(Video)
19. IF Video.vid_id <> 0 THEN
20.     PRINT "Successfully added "; Video.video_name; " to database"
21. END IF
22. PRINT Video.vid_id
23.  
24. FUNCTION AddVideo (Video AS Video)
25.     DIM ReturnVideo AS Video
26.     Video.vid_id = 0
27.     DB_QUERY "INSERT INTO `videos`.`video` (video_name, coloration, clip_length, clip_type) VALUES ('" + Video.video_name + "','" + Video.coloration + "','" + Video.clip_length + "','" + Video.clip_type + "');"
28.     DB_QUERY "SELECT vid_id FROM `videos`.`video` WHERE video_name = '" + Video.video_name + "' AND coloration = '" + Video.coloration + "' AND clip_length = '" + Video.clip_length + "' AND clip_type = '" + Video.clip_type + "';"
29.     FOR y = 1 TO UBOUND(db_result, 2)
30.         ReturnVideo.vid_id = VAL(DB_RESULT(1, y))
31.     NEXT
32.     IF ReturnVideo.vid_id <> 0 THEN
33.         AddVideo = ReturnVideo.vid_id
34.     ELSE
35.         AddVideo = 0
36.     END IF
37. END FUNCTION
38.  
39. '$INCLUDE:'MYSQL.BM'

Here are screenshots showing the program working:
 

 

I think it would be a great idea to use MySQL as it doesn't require any kind of parsing of a file or knowing any kind of insane sorting algorithms. I'll end up showing more for this later on when I finally do a post all about how to use MySQL effectively in QB64. This is just a simple usage. I figured using a user defined type would be best so you can keep it straight in your mind the column names and the variables that they pair with.