Need helpsetting value to a mysql field using as name a reserved word (using RDO)

[dtv]

Hello, nice to see you all, i was a vb6 maniac a decate ago, the crisis placed me in another company's position and after 8 years i felt that something is missing from my life......... that was VB6 !

Due to company's needs, i am making a program that is sending automatically many posts everyday on our company's web page. That page made using Joomla 3.4.1.

Joomla uses MySQL and i have complete access to it, that is not the problem.

My app uses Microsoft's RDO component and i find it very easy to use, i think is almost like DAO; dont know the difference.

When entering new post inside Joomla's GUI, the main text saved in table "jos_k2_items" and field "introtext".

If the text contains a "readmore" tag, automatically Joomla saves the text above that tag to the field "introtext", but the text below the readmore tag is saved in another field named "fulltext".

"FULLTEXT" is a MySQL reserved word and causes some troubles.

I know that i can put " ` " and others symbols to access a field called with reserved word, but in RDO methods i dont know how to do that.

.AddNew

RS!field1=(value)
RS!field2=(value)
RS!introtext=(value)

.Update

That , works.

If before .Update i set the "fulltext" field (existable field alright, "MEDIUMTEXT" as introtext it is), it causes a runtime error saying that the MySQL syntax is incorrectly. That error produced by MYSQL ODBC driver 3.51 (which i am using).

.AddNew

RS!field1=(value)
RS!field2=(value)
RS!introtext=(value)

RS!fulltext=(value)

.Update

That , causes the runtime error. Some google and the cause is the "fulltext" name, as it declared to be a reserved work for MYSQL system (i dont even know why Joomla uses a reserved word as a field name!).

RS.rdoColumns("fulltext").Value= causes the same error

RS.rdoColumns(x).Value= where 'x' is the field index number , causes the same error

RS.rdoColumns("`fulltext`").Value= causes the same error!

Does anybody knows how to set a value to a table field with having a name a reserved mysql word, like "fulltext" is ?

Maybe i can do it through SQL query, but i really want to use the RDO methods, not a SQL statement, because that part of vb code is inside a big loop executing many many stuff.

"update (table) set `fulltext` = (value) where id=xxx" (something like this? i am not using SQL a lot).

Please be kind and help me ;-) I am some minutes before a brain stroke.

[DataMiser]

I would like to help but RDO? I have not saw RDO used in anything in many years now and rarely ever used it even when it was current.

Is there a reason you are not using ADO instead?

When using reserved words as field names you have to bracket them using []
Using the field index number should work as well, though I am not sure about RDO.

Is it possible to change the field name in the database? Using reserved words as field names is generally not a good idea.

[dtv]

The reason for me to start using RDO was ... Google. Trying to remember some basic stuff on connecting to a mysql server, Google presented to me some good examples from people using these methods. In some older project of mine i was using DAO (3.6 object library) to handle Access databases, but i dont recall much differences between them. Special adding a row in a table the methods was the same (.AddNew, objRS!(field)=(value), .Update , etc).

Its easy to change the field name, but then Joomla site will not work (i dont think it will be so smart to understand what happened to the database).

I am not on my main computer right now, but i still dont understand how to use brackets because we are not talking about SQL, its specific methods that needs specific parameters. If you mean to use objRS.rdoColumns("[fulltext]") then i will try it , thanks.

p/s I 've installed a desktop mysql client program (EMS MySQL Manager 3), freeware, it connects to the database and it shows all the data in the specific table. When i am entering new value to the "fulltext" field, it posts successfully the row. Hmmm, so the "reserved word" maybe its not the main problem.

I would like to help but RDO? I have not saw RDO used in anything in many years now and rarely ever used it even when it was current.

Is there a reason you are not using ADO instead?

When using reserved words as field names you have to bracket them using []
Using the field index number should work as well, though I am not sure about RDO.

Is it possible to change the field name in the database? Using reserved words as field names is generally not a good idea.

[dtv]

i 'm kind sticky here, all of my tries guides to runtime errors . Its impossible to pass the field name WITH special quoting as a parameter in a resultset method. The results then are "cant find object indicated by text" (for example using objRS("[fulltext]")=(value) ) either "wrong mysql syntax..." (for example using objRS![fulltext] = (value) ) , both produced at run-time.

When calling the .Update method it seems the RDO library synthesizes an "UPDATE ........ " SQL statement and sends it to MYSQL ODBC 3.51 driver.

My final question is, can we tell the driver it self (as i said is MySQL ODBC 3.51) to use by default backticks ` in each-ALL column names passed to it ? Some search and i could'nt find much on how to do that.

[dtv]

Thank you for your time, project "closed" (until next version...).

I couldnt make the rdo library to accept the field named "fulltext" cause its a mysql reserved word, so... SQL took place to complete the process and the loop finishes with correct results. Its a bit ugly, but it has to do with not too much rows and it stays fast.

.AddNew
objRS!(field1)=(value1)
objRS!(field2)=(value2)
... 'objRS![fulltext]=(value) fails...
.Update
objConnection.Execute "update jos_k2_items set `fulltext` = 'some value' where (condition to be sure that it corresponds to the previously added row)"

[DataMiser]

You should use ADO instead, both RDO and DAO are very outdated.

As of the release of VB6 ADO was the recommended method for data access.
It works well with MySQL, SQL Server, Access and many others with no or very little differences in code beyond the initial connection string.