vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   Adding TABLE_PREFIX in a manual query (https://vborg.vbsupport.ru/showthread.php?t=91919)

Mr Blunt 07-10-2005 12:59 PM
Adding TABLE_PREFIX in a manual query
 
I don't know if it's a vbulletin bug or maybe me telling people to do something that's incorrect and I seek advise here as I don't wish to report it as a bug until someone with more knowledge tells me they think the same.

In my "whodownloaded this attachment" hack....
https://vborg.vbsupport.ru/showthread.php?t=91390
.... I require adding one new field to vb's existing "user" table.

Post#13 shows C.Birch with a 1064 error when running my query.
Quote:
Query
ALTER TABLE " . TABLE_PREFIX . " user ADD COLUMN downloads TEXT NOT NULL

vBulletin Message
An error occurred while attempting to execute your query. The following information was returned.
error number: 1064
error desc: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '" . TABLE_PREFIX . " user ADD COLUMN downloads TEXT NOT NULL' a
Now please note his error shows a space between the quote mark and the word "user". My query does not have a space there, and since that user IS a coder here, I suspect he did not cut n paste my query incorrectly (but possible since I didn't ask him to confirm).


1) Is this a vbulletin bug and it's adding a space when it shouldn't?
2) Is the query I ask people to run incorrect?

Why am I now thinking that the " marks should be '
Hmmm
HELP?

For now I asked my installers to manually edit the query with their prefix if they use one but I'd rather not leave it like that if possible.

Crap, I'm sorry I asked a question in here.
I didn't mean that.

Can someone move this please?
(or answer it and it will then be a "how-to", LOL)
Again, I am truely sorry.

Cloudrunner 07-10-2005 01:09 PM
Quote:
Originally Posted by Mr Blunt
I don't know if it's a vbulletin bug or maybe me telling people to do something that's incorrect and I seek advise here as I don't wish to report it as a bug until someone with more knowledge tells me they think the same.

In my "whodownloaded this attachment" hack....
https://vborg.vbsupport.ru/showthread.php?t=91390
.... I require adding one new field to vb's existing "user" table.

Post#13 shows C.Birch with a 1064 error when running my query.


Now please note his error shows a space between the quote mark and the word "user". My query does not have a space there, and since that user IS a coder here, I suspect he did not cut n paste my query incorrectly (but possible since I didn't ask him to confirm).


1) Is this a vbulletin bug and it's adding a space when it shouldn't?
2) Is the query I ask people to run incorrect?

Why am I now thinking that the " marks should be '
Hmmm
HELP?

For now I asked my installers to manually edit the query with their prefix if they use one but I'd rather not leave it like that if possible.

Crap, I'm sorry I asked a question in here.
I didn't mean that.

Can someone move this please?
(or answer it and it will then be a "how-to", LOL)
Again, I am truely sorry.
if you are telling them to do a manual query and then tell them to cut and paste exactly the following query
PHP Code:
ALTER TABLE ".TABLE_PREFIX."user ADD COLUMN downloads TEXT NOT NULL 
this will not work if they do a true cut and paste. The term 'TABLE_PREFIX' is a php definition from within vBulletin's resident filesystem. Unless they are using that query from within a php script that has called vBulletin's global.php, this term will not be defined, and MySQL does not know what that means.

I would tell the users to do the manual query
PHP Code:
ALTER TABLE *YourTablePrefix*user ADD COLUMN downloads TEXT NOT NULL 
and to make sure that they replace the *YourTablePrefix* with what they have assigned the table prefix as, if any.

To explain better with your query, if the user who is running the query has a table prefix of "vb3_", then the query should be
PHP Code:
ALTER TABLE vb3_user ADD COLUMN downloads TEXT NOT NULL 
for a manual run. Whereas a user that is not using a table prefix the query would look like this
PHP Code:
ALTER TABLE user ADD COLUMN downloads TEXT NOT NULL 
This is most likely where your problem has occured. The user will be required to replace the TABLE_PREFIX with what their system is using, otherwise MySQL will be looking for a table that is nonexistant.

Mr Blunt 07-10-2005 01:21 PM
Your fix is basically what I told my other thread (meaning I concurr with you about solution).

But if they run the query from WITHIN the admincp, wouldn't globals be loaded?
I suppose it's possible he ran it from phpmyadmin, but why would he have?

Paul M 07-10-2005 01:26 PM
It won't work from within the admincp either, the query is just passed straight to mysql (as is) - mysql will trip over the table prefix just the same.

Mr Blunt 07-10-2005 01:42 PM
Thanks guys!!

On a related note, I notice Cloudrunner has no semicolon at the end.

Should there be a semicolon at end of query?
Or not?
Or does it not matter either way?

Christine 07-10-2005 03:39 PM
Only if you are running multiple queries, which you can't do from the AdminCP.

akanevsky 07-10-2005 08:33 PM
Since when do you add columns by ADD COLUMN? When you add columns in phpmyadmin, it will show the column addition as just "ADD" without any "COLUMN" afterwards.

EDIT: This tutorial confirms what I just said. So you might wanna replace ADD COLUMN with ADD.

Marco van Herwaarden 07-10-2005 08:49 PM
'ADD COLUMN' is the official syntax. You can however leave the 'COLUMN' out. Just the 'ADD' will be enough for MySQL to understand what you want.

akanevsky 07-10-2005 08:52 PM
Quote:
'ADD COLUMN' is the official syntax. You can however leave the 'COLUMN' out. Just the 'ADD' will be enough for MySQL to understand what you want.
I see, thanks. However, if there is a short way to write something, and that way is compatible with every system regardless of its version and/or configuration, then I suppose you should use the shorter way to preserve space.

Marco van Herwaarden 07-10-2005 09:08 PM
If you want to be compatible with every system/configuration, you should stick to ANSI SQL, and use ADD COLUMN. There are no garantuees that i would work in future versions, and if vB would sometime support other database system (Oracle for example), you would be in more problems with only a ADD.

Mr Blunt 07-10-2005 10:00 PM
Thanks for moving thread, and again I apolize for that .... rookie move, LOL.
And interesting sidenotes guys!!
Learn sumptin new everyday.

So Christine answered my last question and I'll remove the semicolon from future readme's, but unfortunately since I've written a public file that had one, and since I'm still learning, can I get further clarification as I could have been more specific.

Can the use of a semicolon at line end of a manual query cause a problem?
..... like for people who previously ran it that way.

akanevsky 07-10-2005 10:05 PM
Quote:
If you want to be compatible with every system/configuration, you should stick to ANSI SQL, and use ADD COLUMN. There are no garantuees that i would work in future versions, and if vB would sometime support other database system (Oracle for example), you would be in more problems with only a ADD.
Oh... I understand, thanks for the explanation :)

Marco van Herwaarden 07-11-2005 03:16 AM
Quote:
Originally Posted by Mr Blunt
Thanks for moving thread, and again I apolize for that .... rookie move, LOL.
And interesting sidenotes guys!!
Learn sumptin new everyday.

So Christine answered my last question and I'll remove the semicolon from future readme's, but unfortunately since I've written a public file that had one, and since I'm still learning, can I get further clarification as I could have been more specific.

Can the use of a semicolon at line end of a manual query cause a problem?
..... like for people who previously ran it that way.
About the semi-colon, it is the same story. According to the SQL-standards a command is ended by a semi-colon. In my opinion you should always add it. The AdminCP will automatic add (actually the SQL-routines) one of you ommit it.

Mr Blunt 07-11-2005 07:45 AM
Thanks again!!
I love definative answers!!


All times are GMT. The time now is 05:03 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01135 seconds
  • Memory Usage 1,761KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (4)bbcode_php_printable
  • (5)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (14)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete