Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

mySQL - Store files on mySQL databases

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
clear100-com
Asked:
clear100-com
  • 3
1 Solution
 
eScapeProCommented:
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
 
clear100-comAuthor Commented:
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
 
clear100-comAuthor Commented:
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
 
clear100-comAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now