Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL insert of TEXT data type from VB6 OLEDB/ADO

Posted on 2007-04-01
6
Medium Priority
?
3,013 Views
Last Modified: 2013-12-25
I'm creating a dictionary in mySQL and populating it through VB6.  When I try to insert a TEXT definition, I'm getting an error.  

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MySqlProv;Data Source=dictionary;Password=password;User ID=root;Location=localhost;"
conn.CursorLocation = adUseClient
conn.Open

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
mystream.Type = adTypeText


rs.Open "SELECT * FROM words WHERE 1=0", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
mystream.Open
mystream.LoadFromFile "C:\test.txt"

rs!word = "test"
rs!definition = mystream.Read
rs.Update
mystream.Close
rs.Close
conn.Close

When I execute this, I get a Visual Basic "Run-time error '3219': Operation is not allowed in this context.
If I comment out "rs!definition = mystream.Read", then I get another error:
Run-time error '-2147467259 (80004005)': Insufficient base table information for updating or refreshing.

I'm new to VB databases, so any help on this would be much appreciated.
0
Comment
Question by:meerkat2040
[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
  • 4
6 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 18833285
Hi,

I'm not sure why you are using this particular connection string, and why you are using streams - not just normal recordset objects,

what version of mysql are you using?

if you look at

http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html

and follow the directions to increase the buffer length, and just cast the text value as a string it should work.
0
 

Author Comment

by:meerkat2040
ID: 18833478
Thanks.

I had copied the code from the BLOB binary data insert example on the mySQL page.  That doesn't work either.

 I thought that since the TEXT data is very long and contains special characters, that it would be better to input it as a stream.  Anyway, I removed the special characters and inserted it as a string in an conn.Execute statement.

For some reason, the definition is ending up with a bunch of extra spaces.

The inserted string looks like this:
 "<p><hw>Free</hw> <pr>(fr<emac/)</pr>, <pos>a.</pos> <amorph>[<pos>Compar.</pos> <adjf>Freer</adjf> <pr>(-<etil/r)</pr>; <pos>superl.</pos> <adjf>Freest</adjf> <pr>"

But when I go back and fetch it from the table it has a bunch of extra spaces, like this:

" < p > < hw > Free < /hw > < pr > (fr < emac/) < /pr > , < pos > a. < /pos > < amorph > [ < pos > Compar. < /pos > < adjf > Freer < /adjf > < pr > (- < etil/r) < /pr > ; < pos > superl. "

Any idea what's going on here?
0
 

Author Comment

by:meerkat2040
ID: 18833759
I'm using mySQL 4.1.8.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 22

Accepted Solution

by:
NovaDenizen earned 1000 total points
ID: 18836167
I've never used visual basic.  It looks to me like you're:

1. Running a select query that will return no records.
2. Creating a recordset object to iterate over the empty result of the select query.
3. Using the recordset object to create and submit new records into the database.

This may be common practice in VB database scripts, but to my eye it's shockingly horrid. :)

When you run a select query in mysql you get a list of records back.  You can't use these records to somehow go back upstream and modify the table they came from.  SELECT is a read-only operation.

If you want to add records then you have to use an INSERT query.

INSERT INTO WORDS (word, definition) VALUES ('wordgoeshere', 'definitiongoeshere');

You should use a parameterized query here, but I don't know how to do one of those with an ADODB.
0
 

Author Comment

by:meerkat2040
ID: 18840735
Yeah, I should have said that.  I removed that SELECT that brings up the empty recordset and just did it through an INSERT like you suggested.

It's wierd, when I use SQL Server, the definition inserts properly without any excess spaces, but when I use MySQL, the defintion has excessive spaces as I stated above.

Obviously, I'm new to using VB to work with databases.  I'm just using the VB string functions to parse through the tagset that's used in the public domain 1913 Webster dictionary.
0
 

Author Comment

by:meerkat2040
ID: 18840934
Looks like the spaces was a bug with the mySQL OLEDB driver.  I switched to ODBC v5 and the text comes across fine without the spaces surround the > and < signs.  Thanks for your help.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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