Solved

mySQL - Store files on mySQL databases

Posted on 2003-11-02
4
433 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
  • 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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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