Solved

mySQL - Store files on mySQL databases

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

730 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