Export pictures from MySQL database...

Hi there.

I have some Pictures stored in a MySQL database. Yeah yeah... I know it's not the best solution, but I didn't have a safe option to put them on a fileserver. Anyway... What is the best way to export the Pictures? I usually use phpMyAdmin to Work with my databases, but I also use MySQL Workbench 6.2 once in a while.
Any suggestion how to export all Pictures in one step or alternatively a few steps? The variables in the table is like this:
Picture_id (int(11) primary key, auto increment)
Picture1 (longblob)
Picture_filename (varchar(50)
Picture_date (date)
Picture_filesize (int(11)
Picture_description (varchar(1000))

Best regards

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mukesh YadavFull Stack DeveloperCommented:
Where you want to export pictures?
Kamal KhaleefaInformation Security SpecialistCommented:
you can use this code to connect to ur data base and export all images
Private Sub lpSaveDocuments()

        Dim dsDocuments As DataSet
        Dim dsDoc As DataSet
        Dim intRowCnt As Integer

        Dim strSql As String = ""
        Dim strsqlDoc As String = ""

        Dim strFileName As String = ""
        Dim strDirPath As String = ""
        Dim strFilePath As String = ""

        strSql = <Outer SQL if needed>

        dsDocuments = GetMyimagesFromDB()
'GetMyimagesFromDB is a function that returns a dataset of images,image name ...etc

            If Not (dsDocuments Is Nothing OrElse dsDocuments.Tables.Count = 0 OrElse dsDocuments.Tables(0).Rows.Count < 1) Then

                'Open & Save Doc
                For intRowCnt = 0 To dsDocuments.Tables(0).Rows.Count - 1

                    strFileName = <File Name, It could be from database if present>

                    strFilePath = ""

                    Dim arrByteData() As Byte

                    strDirPath = <Path to the Directory where extracted file is to be saved>

                    strsqlDoc = <SQL for selecting the binary field>

                    dsDoc =  <Open the dataset>

                    If Not (dsDoc Is Nothing OrElse dsDoc.Tables.Count = 0 OrElse _
                        dsDoc.Tables(0).Rows.Count < 1) Then

                        arrByteData = dsDoc.Tables(0).Rows(0)("<Binary field name>")

                        strFilePath = String.Concat(strDirPath, strFileName)

                        Dim fs As System.IO.FileStream = _
                                New System.IO.FileStream(strFilePath, IO.FileMode.CreateNew, IO.FileAccess.ReadWrite)

                        Dim bw As System.IO.BinaryWriter = New System.IO.BinaryWriter(fs)



                    End If

                    If Not (dsDoc Is Nothing) Then
                    End If
            End If

            If Not (dsDocuments Is Nothing) Then
            End If

        Catch ex As Exception

        End Try

    End Sub

Open in new window

ullenulleAuthor Commented:
Hi Again.

I want to save the Pictures to a drive, MS SharePoint etc..

king2002: Forgive me if my question is stupid, but is that code VB, asp or aspx?
And what does this one mean: strSql = <Outer SQL if needed>

Best regards

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Kamal KhaleefaInformation Security SpecialistCommented:
this is VB.Net

<Outer SQL if needed> this mean you can put here another sql if needed to get ur data

you can ignore it if you don't needed
ullenulleAuthor Commented:
Hi king2002.

Thank you for your info. Forgive me if I'm a bit slow. Where do I put my connectionstring? And all the:

Dim xxxxxx As String = ""

Shall I add something in the "" ??

Best regards

Kamal KhaleefaInformation Security SpecialistCommented:

put your connection string inside this function


where you will write your sql to get the images from DB
ullenulleAuthor Commented:
Hi King2002.

Thank you for your reponse. Must I trigger the funktion with a button, or will it launch at page load?
And what about the other ""  in your code snippet (all the Dim as Sting .... = "")? Are they supposed to be empty?

Best regards

ullenulleAuthor Commented:
I'm working on the issue these days and will close the question myself and reward points!
ullenulleAuthor Commented:
Ops... missed the object button. :-)
ullenulleAuthor Commented:
Hey everyone.

My apology for no response. I had to use a quick solution and use a software to extract the pictures from the MySQL database. The software is MyLobEditor and worked very fine.
I will award the points to King2002 for his efford, and I'm sure it would work. I just didn't have time to test it for real. Sorry. But thank you King2002. :-)

Best regards


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ullenulleAuthor Commented:
Thank you.
ullenulleAuthor Commented:
Case closed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.