7 Replies - 638 Views - Last Post: 23 September 2009 - 12:29 AM Rate Topic: -----

#1 Guest_NIXZ*


Reputation:

_____~* Something like MySQL and FTP all in one hard to describe *~___

Post icon  Posted 22 September 2009 - 06:46 AM

MySQL databases exists for saving values as strings (text only). Is there any way that i could store files with the same speed as a mysql command ? I know that it depends on the file size but lets say that the file is "Avatar.jpg" 50KB... I ve tried FTP but this is not what im looking for.
e.g. You want your users been able to upload a file like the [Add This Attachment] button does in dreamincode.net and you dont have any background on PHP/HTML/Javascript/ASP.NET
just VB.NET and the basics of MySQL plus you dont want to use it for a website. How do you accomplish this?

Something silly that ive tried was opening an image as text then update the mysql server with that text but it didnt work backwards after i wrote the text on the file and oppened it ...it said that the image was corrupted. :P H.E.L.P.

This post has been edited by NIXZ: 22 September 2009 - 06:59 AM


Is This A Good Question/Topic? 0

Replies To: _____~* Something like MySQL and FTP all in one hard to describe *~___

#2 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 22 September 2009 - 07:21 AM

Hi,

I know you can to this with MSSQL, but you shoud be able to do this in MySQL as well.
My Type in MSSQL is varbinary(max) so you would have to check what you need in MySQL and i use table adapters to insert the file content and get it back again.

'Example only
Me.InsertTableAdapter.Insert(File.ReadAllBytes("C:\Test.txt"))



to get it back again

Dim MyBytes() As Byte = Me.InsertTableAdapter.InsertTable.Rows(0).Item("ServedFile")
			If My.Computer.FileSystem.FileExists("C:\Test.txt") Then
				My.Computer.FileSystem.DeleteFile("C:\Test.txt")
			End If
			Dim MyFileStream As System.IO.FileStream = System.IO.File.Create(("C:\Test.txt")
			MyFileStream.Write(MyBytes, 0, MyBytes.Length)



Hope this points to the right direction
Was This Post Helpful? 1
  • +
  • -

#3 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 22 September 2009 - 07:42 AM

First off do some research and see how to upload a file to your site.

Once you figure that part out, instead of storing it on the server break it up into Bytes and store it as a Byte series and then insert it into the database. Catch is you need to find a comfortable size for the file and then use that size accordingly as your Byte size. My first concern is that file sizes (excluding image files) can vary widely and remember even white space in a file is considered a byte reference so i would suggest uploading text files to the server and inserting a reference to the file on the server.

Some examples of uploading a file to the server include:

1) When you upload the file to the server take the first 2 to 4 characters of the file name and create folder directories accordingly. Make sure they fall into the lower Unicode spectrum (Latin Case-Incensitive) and store it that way. you will most likely need to translate UTF8 character sets to Unicode 128 for this to work properly.

2) Store file in a specified directory for the specified extension.

3) Throw it in a pile of uploaded documents without any filing ;)

I would suggest in uploading the file to the server and point to that location on the server in the SQL database. As when you upload it as Bytes, when you upgrade to the next version (regardless of MSSQL or MySQL) the byte reference information is lost.
Was This Post Helpful? 0
  • +
  • -

#4 Guest_NIXZ*


Reputation:

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 22 September 2009 - 07:45 AM

View Postmotcom, on 22 Sep, 2009 - 06:21 AM, said:

Hi,

I know you can to this with MSSQL, but you shoud be able to do this in MySQL as well.
My Type in MSSQL is varbinary(max) so you would have to check what you need in MySQL and i use table adapters to insert the file content and get it back again.

'Example only
Me.InsertTableAdapter.Insert(File.ReadAllBytes("C:\Test.txt"))



to get it back again

Dim MyBytes() As Byte = Me.InsertTableAdapter.InsertTable.Rows(0).Item("ServedFile")
			If My.Computer.FileSystem.FileExists("C:\Test.txt") Then
				My.Computer.FileSystem.DeleteFile("C:\Test.txt")
			End If
			Dim MyFileStream As System.IO.FileStream = System.IO.File.Create(("C:\Test.txt")
			MyFileStream.Write(MyBytes, 0, MyBytes.Length)



Hope this points to the right direction


Thanks motcom thats pretty much my idea but theres many problems:
1. What is this "InsertTableAdapter" ?
2. It works the same as a regular MySQL command? like UPDATE FROM MainTable ('FileBytes') VALUES ('ASDF1234') ???
3. Theres a lot of gaps to fill e.g. .commandtext = Text As String when FileBytes = Text As Bytes doesnt mix.
4. Do i have to download another 3rd party component except from mysql connector ?
5. I just have a MySQL Server, a Database called maindatabase and a table MainTable thats fine right ? what else should i have?

Can you post a really quick and simple tutorial that will take you no more than 5 minutes ??? :P

K.I.S.S. Thanks

This post has been edited by NIXZ: 22 September 2009 - 07:47 AM

Was This Post Helpful? 0

#5 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 22 September 2009 - 07:52 AM

View PostNIXZ, on 22 Sep, 2009 - 08:45 AM, said:

View Postmotcom, on 22 Sep, 2009 - 06:21 AM, said:

Hi,

I know you can to this with MSSQL, but you shoud be able to do this in MySQL as well.
My Type in MSSQL is varbinary(max) so you would have to check what you need in MySQL and i use table adapters to insert the file content and get it back again.

'Example only
Me.InsertTableAdapter.Insert(File.ReadAllBytes("C:\Test.txt"))



to get it back again

Dim MyBytes() As Byte = Me.InsertTableAdapter.InsertTable.Rows(0).Item("ServedFile")
			If My.Computer.FileSystem.FileExists("C:\Test.txt") Then
				My.Computer.FileSystem.DeleteFile("C:\Test.txt")
			End If
			Dim MyFileStream As System.IO.FileStream = System.IO.File.Create(("C:\Test.txt")
			MyFileStream.Write(MyBytes, 0, MyBytes.Length)



Hope this points to the right direction


Thanks motcom thats pretty much my idea but theres many problems:
1. What is this "InsertTableAdapter" ?
2. It works the same as a regular MySQL command? like UPDATE FROM MainTable ('FileBytes') VALUES ('ASDF1234') ???
3. Theres a lot of gaps to fill e.g. .commandtext = Text As String when FileBytes = Text As Bytes doesnt mix.
4. Do i have to download another 3rd party component except from mysql connector ?
5. I just have a MySQL Server, a Database called maindatabase and a table MainTable thats fine right ? what else should i have?

Can you post a really quick and simple tutorial that will take you no more than 5 minutes ??? :P

K.I.S.S. Thanks


5 minute tutorial doesnt exist. Dont believe the book shelves, nothing can be learned in 24 hrs or 6 months. It takes years of using and experimenting to properly learn the rules and guidelines of a programming language and its structure.
Was This Post Helpful? 0
  • +
  • -

#6 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 22 September 2009 - 08:05 AM

Hi NIXZ

I use datasets and thrugh that my tableadapters get created.

I guess you could use parameters in your case.

I will try to make a tutorial/example but will need to go now, i'll try tomorrow
Was This Post Helpful? 1
  • +
  • -

#7 Guest_NIXZ*


Reputation:

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 22 September 2009 - 01:14 PM

ill wait :)
Was This Post Helpful? 0

#8 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: _____~* Something like MySQL and FTP all in one hard to describe *~___

Posted 23 September 2009 - 12:29 AM

Hi NIXZ

I have put something together, very basic, but you should get the idea.

First of all this is my Table in MySQL

Table upload

`ID` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
`FileName` varchar(45) NOT NULL
`FileContent` longblob NOT NULL

You might have to investigate into "TINYBLOB", "MEDIUMBLOB" and "LONGBLOB" to see how much data each one
can hold. (I don't know if you want someone to upload very huge files.)

Next I have 2 buttons, btnDownload and btnUpload. (self explainatory)
This is the code i used to get and put the file.

Imports MySql.Data.MySqlClient

Public Class Form1

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

	End Sub

	Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
		Dim sqlConn As New MySqlConnection(My.Settings.test1234ConnectionString)
		Dim sqlCmd As New MySqlCommand
		Try
			sqlConn.Open()

			With sqlCmd
				.Connection = sqlConn
				.CommandText = "INSERT INTO Upload (FileName, FileContent) VALUES (@FileName,@FileContent)"
				.Parameters.AddWithValue("@FileName", "Test.jpg")
				.Parameters.AddWithValue("@FileContent", My.Computer.FileSystem.ReadAllBytes("C:\Test.jpg"))
				.ExecuteNonQuery()
				.Dispose()
			End With
		Catch ex As MySqlException
			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
		Finally
			sqlConn.Close()
			sqlConn.Dispose()
		End Try
	End Sub

	Private Sub btnDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDownload.Click
		Dim sqlConn As New MySqlConnection(My.Settings.test1234ConnectionString)
		Dim sqlCmd As New MySqlCommand
		Dim objReader As MySqlDataReader

		Try
			sqlConn.Open()

			With sqlCmd
				.Connection = sqlConn
				.CommandText = "SELECT * FROM upload WHERE FileName = 'Test.jpg'"
				objReader = .ExecuteReader
				.Dispose()
			End With
			If objReader.HasRows = True Then
				objReader.Read()
				Dim MyBytes() As Byte = objReader("FileContent")
				If My.Computer.FileSystem.FileExists("C:\NewTest.jpg") Then
					My.Computer.FileSystem.DeleteFile("C:\NewTest.jpg")
				End If
				Dim MyFileStream As System.IO.FileStream = System.IO.File.Create("C:\NewTest.jpg")
				MyFileStream.Write(MyBytes, 0, MyBytes.Length)
				MyFileStream.Dispose()
			Else
				MessageBox.Show("No Records found", "No Records found", MessageBoxButtons.OK, MessageBoxIcon.Information)
			End If
			objReader.Close()
		Catch ex As MySqlException
			MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
		Finally
			sqlConn.Close()
			sqlConn.Dispose()
		End Try
		
	End Sub
End Class



Hope this explains what you want to do...

motcom
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1