Solved

Writing to BLOBs in mySQL and ASP

Posted on 2004-04-28
15
1,189 Views
Last Modified: 2012-08-13
I have transferred to a mySQL database and have run up against a problem.

I use the below code to create a new record but it cannot create the record due to an error caused by incorrect code when writing to a BLOB.

Response.Buffer = true
set rst = Server.CreateObject("ADODB.RecordSet")
sqltext = "SELECT * FROM TableName"

rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name")' varchar in DB
rst("email") = Request.form("email") ' varchar in DB
rst("information") = Request.form("information") 'blob in DB
rst.update
rst.close
0
Comment
Question by:BeakerTrail
  • 8
  • 6
15 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10939588
Change the code to

rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name")' varchar in DB
rst("email") = Request.form("email") ' varchar in DB
rst("information").AppendChunk = Request.form("information") 'blob in DB
rst.update
rst.close
0
 

Author Comment

by:BeakerTrail
ID: 10939660
That returns an error:

ADODB.Field error '800a0c93'

Operation is not allowed in this context.

line 95 <-- line with appendchunk on.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10940926
Not sure about this: ($)

Response.Buffer = true
sqltext = "INSERT INTOP TableName (name, email, information) VALUES " & _
    "('" & request("email") & "', '" & request("email") & "', '$" & request("information") & "')"

objaConn.execute sqltext
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 31

Expert Comment

by:alorentz
ID: 10940943
Whoops...think that was PHP, didn't read enough ;-)
0
 

Author Comment

by:BeakerTrail
ID: 10940989
Yeah. What I am trying to do is in ASP - i've zero knowledge of PHP
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10941000
However, I would like to recommend not storing the image in the DB, rather store the path to the uploaded fileonthe server, then use this to display:

<img src="<%=rs("information")%>">
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10941026
Try this:

rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name")' varchar in DB
rst("email") = Request.form("email") ' varchar in DB
rst("information").AppendChunk Request.form("information") 'no = sign
rst.update
rst.close
0
 

Author Comment

by:BeakerTrail
ID: 10941112
It isn't an image, it is a very big chunk of text.

The error i get occurs when writing to a Blob or a Text field. This is ASP and mySQL

I will post all the code below with the mySQL field types, maybe it will help:

Dim rst, objaConn, objaRS
Set objaConn = Server.CreateObject("ADODB.Connection")
objaConn.Open "Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"
Response.Buffer = true
set rst = Server.CreateObject("ADODB.RecordSet")
sqltext = "SELECT * FROM TableName"
rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name") 'varchar
rst("email") = Request.form("email") 'varchar
rst("datejoined") = Year(Date) & "-" & Month(Date) & "-" & Day(Date) & " " & time 'datetime
rst("information") = Request.form("information") 'text
rst("interests") = Request.form("interests") 'text
rst("occupation") = Request.form("occupation") 'text
rst.update 'line 114
rst.close

which gives the error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Invalid string or buffer length

line 114
0
 

Author Comment

by:BeakerTrail
ID: 10941140
For the line:
rst("information").AppendChunk Request.form("information")

It gives:

ADODB.Field error '800a0c93'

Operation is not allowed in this context.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10941237
May need to put this in there:


Set objaConn = Server.CreateObject("ADODB.Connection")
objaConn.Open "Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"

objaConn.CursorLocation = adUseClient  '<---------ADD THIS

0
 

Author Comment

by:BeakerTrail
ID: 10941299
That returns:

ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
0
 
LVL 31

Accepted Solution

by:
alorentz earned 250 total points
ID: 10941344
This then:

Set objaConn = Server.CreateObject("ADODB.Connection")
objaConn.CursorLocation = 3                           '<-----move line here and change to 3
objaConn.Open "Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"

Also, make sure you put back to this:

rst("information") = Request("information") '<-----should be fine for TEXT field


0
 
LVL 31

Expert Comment

by:alorentz
ID: 10941357
Also, check the request object for data at top of page:

Response.write "TEXT: " & request("information")
Response.end
0
 

Author Comment

by:BeakerTrail
ID: 10941514
Excellent! that did the trick, i think.... mySQL server keeps going down - argh!

Thanks :)
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10941524
MySQL is sketchy like that....good luck!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use a variable for Class Name in Classic ASP 4 61
Summernote required 3 183
CDO.Message not able to send attachement 5 32
Time Conversions...both ways 2 16
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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