Solved

mySQL - Store files on mySQL databases

Posted on 2003-11-02
4
447 Views
Last Modified: 2011-10-03
Hello, I am trying to find some code that will allow me a way to have a small file manager on Visual Basic connected to a mySQL server.

In other words..  I want to store a file on a database table on line.  This database I want to be able to re-call that file.
Can someone show me some example code?
0
Comment
Question by:clear100-com
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 1

Accepted Solution

by:
eScapePro earned 30 total points
ID: 9678062
You can get similar code from Microsoft's website. You will need to create a binary type field in the database which will hold the file data.

Basically, you will need to create the table. I suggest having two tables. Many users will advise you not to store files straight into a database but I have done this many times and it is very useful.

Have a table called FileIndex, where you can store file.primary.key, filename, date/time, user, other information. Then have a table, like FileData, which has file.primary.key, filebinarydata as binary. This way you can search the index table quickly and then just jump to the second table using the samy primary key. This will speed stuff up!

This link shows how to read and write BLOB (binary large object ) to and from Access using DAO. The same code (nearly) will work with ADO and mySQL over OLEDB/ODBC:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q210/4/86.asp&NoWebContent=1
0
 

Author Comment

by:clear100-com
ID: 9684413
Im lost when it comes to the ADO stuff I use ADODB

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
----------------------------------------------------------------------

Is there a way you can convert it with my string?  When i try the following, it says the db is read only. .. And yes my right are ok.


Private Sub Form_Load()
CopyFile "scrapfile.ppt", "doublescrapfile.ppt"
End Sub

'**************************************************************
' SUB: CopyFile
'
' PURPOSE:
'   Demonstrates how to use ReadBLOB() and WriteBLOB().
'
' PREREQUISITES:
'   A table called BLOB that contains an OLE Object field called
'   Blob.
'
' ARGUMENTS:
'   Source - The path and filename of the information to copy.
'   Destination - The path and filename of the file to write
'                 the binary information to.
'
' EXAMPLE:
'   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
'**************************************************************
Sub CopyFile(Source As String, Destination As String)
    Dim BytesRead As Variant, BytesWritten As Variant
    Dim Msg As String
    Dim db As DAO.Database
    Dim T As DAO.Recordset

    ' Open the BLOB table.
   
    Set db = OpenDatabase("", dbDriverComplete, False, "DRIVER={MySQL ODBC 3.51 Driver}" & _
                        ";SERVER=" & "localhost" & _
                        ";DATABASE=" & "Test" & _
                        ";UID=" & "mysys" & _
                        ";PWD=" & "wd006451" & _
                        ";OPTION=2")
   
    Set T = db.OpenRecordset("MyTest", dbOpenTable)
    ' Create a new record and move to it.
    T.AddNew
    T.Update
    T.MoveLast

    BytesRead = ReadBLOB(Source, T, "MyTest")

    Msg = "Finished reading """ & Source & """"
    Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
    MsgBox Msg, 64, "Copy File"

    BytesWritten = WriteBLOB(T, "MyTest", Destination)

    Msg = "Finished writing """ & Destination & """"
    Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
    MsgBox Msg, 64, "Copy File"
End Sub





0
 

Author Comment

by:clear100-com
ID: 9686862
Well I did figure it out, I am giving the points however to eScapePro because he showed me that it was part of blob.  But i found a site on mySQL that shoed me how to do this and it worked great!!!

http://www.vbmysql.com/articles/blobaccessvb.html#sample1
0
 

Author Comment

by:clear100-com
ID: 9689270
Hey: eScapePro

I just wanted to say thank you again, and thanks for the idea of using a seprate table or database, I am designing a case system and once a case is moved from one techs bin to another i need that file kept in there, and i don't want to have the user download the file and upload it again just to move it.  So,  yes, great idea.

Bill.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 5 hours left to enroll

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question