Avatar of ullenulle
ullenulle
Flag for United States of America asked on

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

Ulrich
MySQL Server

Avatar of undefined
Last Comment
ullenulle

8/22/2022 - Mon
Mukesh Yadav

Where you want to export pictures?
Kamal Khaleefa

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
        Try

            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)

                        bw.Write(arrByteData)
                        bw.Flush()
                        bw.Close()

                        fs.Close()
                        fs.Dispose()

                    End If

                    If Not (dsDoc Is Nothing) Then
                        dsDoc.Dispose()
                    End If
                Next
            End If

            If Not (dsDocuments Is Nothing) Then
                dsDocuments.Dispose()
            End If

        Catch ex As Exception


        End Try

    End Sub

Open in new window

ullenulle

ASKER
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

Ulrich
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Kamal Khaleefa

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
ullenulle

ASKER
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

Ulrich
Kamal Khaleefa

Hi

put your connection string inside this function

GetMyimagesFromDB()

where you will write your sql to get the images from DB
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ullenulle

ASKER
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

Ulrich
ullenulle

ASKER
I'm working on the issue these days and will close the question myself and reward points!
ullenulle

ASKER
Ops... missed the object button. :-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
ullenulle

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ullenulle

ASKER
Thank you.
ullenulle

ASKER
Case closed.