Resetting User Variable Causes Variable To Not Hold Decimals
I am currently working on a query that makes use of user variables. I notice one small issue (bug?) depending on whether or not I reset a user variable with the "SET" command or don't before executing the query.
When I do reset the user variables, some of my variables will not hold decimal points (7.56734234 gets turned into and stored as 7). However when I run the query afterwards without resetting the user variables, the correct value (with the decimal) is stored.
Example with query: ..
View Complete Forum Thread with Replies
Related Forum Messages:
User Defined Variable
I'm creating a variable using the sql: select @birth := birth from president where last_name='jackson' and first_name='andrew'; I'm also getting a result with a date. But when I use this variable in any other queries, it does not give me anything. The variable does not hold any value inside it. It shows me the result as 'NULL' when I select the value for the variable in the SELECT statement.Am I missing anything here? Do I have to save the variable?
View Replies !
User Variable And DATE_ADD Function
I have a query that uses a user variable to get the latest date from a table using the MAX function. I then need to add 1 month onto this date using DATE_ADD but the query just returns a NULL for the second field. Here's my query: SELECT CheckId, #This displays correctly @lastchecked:=MAX(CheckedDate), #This just returns (NULL) DATE_ADD(@lastchecked,INTERVAL 1 MONTH ) as lastcheckedplus1month FROM checkingtable GROUP BY CheckId Has anyone managed to use DATE_ADD with a user variable? Can you tell me if I'm missing something?
View Replies !
Assign Resultset To A User Variable
I would like to know why am I getting an error when assigning the result of a query to a user variable? SET @newClientID = SELECT clientID FROM tblClients WHERE clientClave = @clientClave; The code above doesn't work on a Windows server running MySQL 4.0.12-nt. It does work on a workstation with Windows XP running MySQL 4.1.3b-beta-nt
View Replies !
User Defined Variable Overflow
I've got a query that i'm using to dynamically generate a new query and save in a user-defined variable. Then i'm using PREPARE and EXECUTE to run the query that is saved in the variable. The problem is the query string i want to save into the variable is pretty long, and seems to be getting truncated i assume because of maximum variable size. Using the query browser it looks like the variable is a Blob and the data is getting truncated after 1024 bytes, and yet everywhere i see the blob maximum size is 2^16-1 (65,535) bytes. Anyone know why the blob is getting truncated after 1024 or a way around it? ....
View Replies !
Accept Keyboard Input To Set User Variable?
We use the Navicat GUI tool for end-user report generation, data exports, etc, from MySQL databases. In this capacity, it works admirably. Unfortunately, one downside is the lack of detailed documentation. We would like to be able to prompt the user to enter a value and have that value stored in a SQL user variable. Given the existance of such a function (call it 'PROMPT_USER'), we'd like to do something like: @var = PROMPT_USER('Enter a customer name:'); select * from customer where name = @var; Does anyone know if Navicat provides such functionality?
View Replies !
Alter Auto_increment To User Defined Variable @var
I'm atmysqlting to build a statement that reads the max id of a table and resets the auto_increment value to the max id + 1. My approach has been to set a mysqlorary variable like: SELECT @mysql:=MAX(ID) FROM `accounting`; and then set the value of auto_increment to the variable value like: ALTER TABLE accounting AUTO_INCREMENT = (@mysql+1);
View Replies !
Access User Defined Variable In Mysql Query
using loop and getting string in user defined variable like uvariable. now executing the statement in java file resultset=statement.executeQuery("select abc from bcd where T=""+uvariable+"""); t and bcd are column.getting error message "check syntax".
View Replies !
Set Variable
On our Linux server we get this error: 030722 08:09:55 mysqld started /usr/local/mysql/bin/mysqld: ERROR: unknown variable 'ft_min_word_len = 2' 030722 08:09:55 mysqld ended [...]It's strange becouse I can set this variable through my.ini on my Windows.
View Replies !
Variable Instead
I have this working PHP MySQL script: PHP Code: $sql = mysql_query('SELECT `response` FROM `cspeak` WHERE `you` LIKE CONVERT(_utf8 '%Hello%' USING latin1) COLLATE latin1_swedish_ci ORDER BY `response` ASC LIMIT 0, 30 '); I want to instead of searching for "Hello" I want to serach for $variable. It is absolutely necessary that the wild cards are still there.
View Replies !
Variable
I try to use "user defined variable" in my query. I manage to get this working: SELECT @myvar:='100'; SELECT DATE_ADD(NOW(), INTERVAL @myvar HOUR); -> 2005-10-05 00:39:19 But I want to do this now: SELECT @myvar:='100 HOUR'; SELECT DATE_ADD(NOW(), INTERVAL @myvar); -> #1064 - 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 ')' at line 1 .
View Replies !
Using An AS Variable In WHERE
I have a query that pulls all users from a database for a phone listing. I have one table that is all of the users with UserID as the Primary Key(PK). Then I have a cell phones table that has CellID as the PK. Then a cellphone relationship table with a PK of UserID,CellID. *I am using PHP5 and MySQL5 The query: CREATE VIEW phonelisting as select users.UserID, concat_ws(' ', users.fname, users.minitial, users.lname) AS userName, users.positionTitle AS positionTitle, users.deskPhone, users.DID, users.homePhone, users.pager, group_concat(distinct concat(substr(cell.number, 1, 3), '-', substr(cell.number,4 , 4)) order by cell.number ASC separator '<br />') AS cellphones FROM users LEFT JOIN cellrelationship ON cellrelationship.userID = users.UserID LEFT JOIN cell ON cell.CellID = cellrelationship.cellID && cell.hideListing <> Ƈ' WHERE users.userStatus <> 'term' && users.hideListing <> Ƈ' && (users.termdate = '' OR users.termdate > UNIX_TIMESTAMP(CURRENT_DATE)) && (users.deskPhone <> '' OR users.pager <> '') GROUP BY users.UserID ORDER BY concat_ws(' ', users.fname, users.minitial, users.lname) What I would like to do is use the cellphones value in the SELECT part of the query in the WHERE clause. The query should pull all users that: 1. Are not terminated (Signified by users.userStatus <> 'term') 2. Users that do not have a termination date or that the termination date has yet to come (Signified by users.termdate) 3. Users that have at least a cell phone, a pager, or a desk phone 4. Then order the users by fname minitial lname The part that I have yet to get to work is if they have a cell phone or not. Any suggestions?
View Replies !
Order By With Variable
I have listings with coordinates, i would like to do ORDER BY and display listings with coords first. If they don't have coords the value is blank and the coords go both positive and negative values. So i tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like a WHERE statement removing all results with no coords. I tried GROUP BY as well but same results.So the question is how can i display all results with the ones that have coords first? It seems like it should be simple but I'm just not seeing it.
View Replies !
System Variable.
i am running mysql version 4.0.16-nt. now, i am connected to the server and i build some tables etc etc. everything is fine. i run the command "show variables". it executes fine and shows me the values of all the variables on the server. now if i try and change the value of the variable tmpdir using the set command, it tells me unknown "error 1193: system variable tmpdir". help needed here........ another point, the variable "tmpdir", points to the dir c:/windows/temp. i put a text file in that dir and try to load some data from that file into a table, and i get an error "file not found".
View Replies !
System Variable
i am connected to the server and i build some tables etc etc. everything is fine. i run the command "show variables". it executes fine and shows me the values of all the variables on the server. now if i try and change the value of the variable tmpdir using the set command, it tells me unknown "error 1193: system variable tmpdir". help needed here........ another point, the variable "tmpdir", points to the dir c:/windows/temp. i put a text file in that dir and try to load some data from that file into a table, and i get an error "file not found".
View Replies !
Date Variable
mysql_query("SELECT orderID,ordername,slots FROM users_orders WHERE userID='$userID' ORDER BY ordername"); but theres also a column called nextInvoiceDue and i only want to select where the current date isnt past the next invoice due date. ie if the nextInvoiceDue is july 19th (i forget what it looks like in mysql format) and today is july 20th it woudlnt select that row because its past the due date. (but if it today were the 19th or earlier it would show up)i dont know how to state this in a mysql query though
View Replies !
Mysql Variable
I have the following query: SELECT @test := 'hello', CONCAT('Message: @test', column_name) The problem is that I do not want the @test to be standalone, I do not want something like this: CONCAT('Message:', @test, column_name) Is there anyway to evaluate a variable enrapped inside quotes without seperating it from the rest of the string?
View Replies !
Variable Amount
i'm developping a php-application that needs to be highly customizable. On the one side, the user has a input-mask, where he can edit database records. on the other side, the user should be given the possibility to alter this database mask on his own, probably in a special "settings tab" - e.g. adding or deleting fields. My approach so far: I have to tables: one, which holds the names of the fields to be offered AND another one, which holds the entries for each "record". These two are linked together by a "right-join" statement.
View Replies !
Using A Variable For A Table Name
Take a look and tell me where I'm going wrong. The initial SELECT statement works, doing a "SELECT @TTN;" does yield the result I'm looking for. In this case - "test.SystemTable", which is the database.tablename I want for my other SELECT statement. It looks like the server is choking on EXECUTE. Code: SELECT @TTN:=CONCAT('test.', Name, 'Table') FROM Mulligan.DDT_TypeTable WHERE (TypeID='15'); PREPARE stmt1 FROM 'SELECT * FROM ?'; EXECUTE stmt1 USING @TTN; This example, substituted for the last two lines of the previous example, doesn't work either. The SELECT part does tield a "valid" SQL statement, but again, EXECUTE chokes for some reason. Code: SELECT @stmt:=CONCAT('SELECT * FROM ', @TTN); PREPARE stmt1 USING @stmt; EXECUTE stmt1;
View Replies !
Variable Within The Query
I have: SELECT userid, count(*) AS matches FROM userinfo_responses WHERE username = 'vikdhu' (have skinned the query to take out irrelevant...) Now I'd like to use 'matches' in the WHERE clause too - like SELECT userid, count(*) AS matches FROM userinfo_responses WHERE username = 'vikdhu' AND matches > 20 but this doesn't work.. is there a way out?
View Replies !
DELETE And Variable
I am rather new to MySql and have a question regarding using variables with the DELETE statement. I want to run the following: DELETE FROM X WHERE X.Id = number; number is a C# UInt32 variable. How should this be written?
View Replies !
Variable Defined By AS
I need to be able to reference a variable I define via the 'AS' keyword in a where clause. I read that this inst possible though. Here is my query: SELECT CASE invoices.associated_status_id WHEN 1 THEN invoices.date_quote_due WHEN 2 THEN invoices.date_follow_up WHEN 3 THEN invoices.date_job_complete END AS custom_due_date FROM invoices WHERE custom_due_date BETWEEN " . mysql_smart_quote(convert_date_2_mysql_date($_POST['first_date_filter'])) . " AND " . mysql_smart_quote(convert_date_2_mysql_date($_POST['second_date_filter'])) . "ORDER BY custom_due_date DESC;" See the problem is that I am trying to use the variable custom_due_date, which I declare on the fly, in the WHERE clause of the BETWEEN. Evidently, this inst possible, though I can reference custom_due_date in the ORDER BY clause just fine.So, what is a fix?
View Replies !
Variable Expansion
SELECT time,switch_id,port,packets FROM data WHERE packets = (SELECT MAX(packets) FROM data WHERE time > 0 AND time < 1229000000); (I don't know if it's the most effective way, but let's pretend it is ;)) The query will finish within 0.0x seconds. But now, when I want to set the time limits by variables, like this: SET @date_min := 0; SET @date_max := 1229000000; SELECT time,switch_id,port,packets FROM data WHERE packets = (SELECT MAX(packets) FROM data WHERE time > @date_min AND time < @date_max); The query keeps running and running (I don't know for how long. I interrupted it)... CPU load on top... and where's the result? The CPU is iP4, 2400MHz, 512MB RAM, OS is GNU/Linux Debian stable (Etch) with all updates, MySQL is in version 5.0.32-7 in default installation.
View Replies !
Set A Local Variable
I would like to create 3 variables, 1 I will set manually the other 2 will be based on a query from the first. So I have: set @fk_scenario_id =96, @startdate1 = (select start_day from scenario_execution where fk_scenario_id =@fk_scenario_id), @enddate1 = (select end_day from scenario_execution where fk_scenario_id =@fk_scenario_id) ; select @fk_scenario_id, @startdate1, @enddate1; Which returns: 96 (null) (null)
View Replies !
Type Of Variable To Use
i would like to store long articles into a mysql table; i've these fields in the table (title, body, summerize). The 'body' field should content the whole article (the article uses to be very long 1, or 2, or 3 full paper); please what should be the type of the field 'body' (varchar, text or blob)? Why please?
View Replies !
Variable LOCAL
I'm trying to load data to my mysql 4.11 db from txt files, using LOAD DATA INFILE. I received the following message: "The used command is not allowed with this MySQL version" In the manual that message means I have a LOCAL variable issue. I set the variable to off (set variable LOCAL-infile=0;) but it still doesn't work... I got the same error message. (The LOAD DATA command is been running inside a Visual Basic program in a client computer) The txt file is inside a folder of the server. If I remove the LOCAL, the error message is 'Cant get stat of 'vendedor.txt' ' What can I do?
View Replies !
SELECT INTO :variable
I'm not sure, but is it possible in MYSQL to perform a "select field INTO :variable" command (or equivalent). I used to use IBM DB2 a lot, and this was the common way of building "single" selects. However in MySQL (within a Java program) I have to create a resultset and then perform a while ResultSet.next() command which loops through all results (only one loop in this case). Which seems inefficient (executing multiple commands rather than one single command?) Can anyone advise if this is possible and if so what the correct syntax would be?
View Replies !
Variable Troubles
when i see a url like this: http://www.example.com/index.php?other=new does that mean the php coding on the page has a variable and that varable is set to other when you load that link, and my other question is im trying to make a table in mysql with a variable like such: $named=$_POST['username'] . "'s mail"; mysql_query(CREATE TABLE '$named'(id varchar(15), subject varchar(15))"; why doesnt that work.
View Replies !
Remove '@-prefixed' Variable
i input several commands into the command prompt such as SET @a = 'test'; I thought by deleting the database the variable will be destroyed but it turns out that doing a "SELECT @a", it lists a table consisting of 'test'. Does anyone know how to remove it and list several other variables that i forgot to remember the names? Also, what is such thing called so that I know what keyword to use to google about it.
View Replies !
Create Database Using Variable Name
I am using an SQL script to create a temporary database backup. While there may be "other" or "better" ways to do this - I am really only asking if the syntax (with tweaking) is possible - and if so what it is. Here is a very stripped down version of what I'm trying to attempt: set @backUpName = 'myBackup'; create database @backUpName;.
View Replies !
Boolean Variable Types
i guess this can account to more than just BOOL database variables. bassicaly if i have a boolean type variable which represents an on or off state of something. ie user_is_banned should i set it to null and change it to 1 when/if i ban this user. or is it equally as efficent to use 0 or null? just curious becasue in some of my tables i have 0 meaning off and other ones i have null meaning off. i want to change it to all one way or the other to create consistancy. i just want to know if theres a reson to go one way or the other. personally it feels like using null is better because doesnt null take up less space than 0? but i guess thats only in theory and null takes up the same amount of space, im not sure.
View Replies !
Buffer Size Variable
when data is inserted into a innodb table, the table size increases in sizes of 1 Mb. I think thats the default size of incremental block. Is there any varialble that can be set that so that the table increases in larger block sizes. what i mean is that you will not see increase in table size until the 1 Mb block is full. Can anyone provide the varialble that can be set or any other means to do so.
View Replies !
Changing Ft_min_word_len Variable
I've built a search engine for my website and made the mysql field FULLTEXT and wrote the queries and everything is fine, the problem is when users search for 3-letters words they get no results, the MySQL system variable (( ft_min_word_len )) is responsible for that as it's set to 4, i've tried alot of variations and googled to change this number to 3 but no luck, i own the server and has full access to it, please help in modifying this variable, Thanks in advance. P.S.: i tried to add [mysql] ft_min_word_len=3 in etc/my.cnf but it says unknown variable ft_min_word_len
View Replies !
Variable Function In MySQL
Is there a way to use a 'variable function' in MySQL, e.g.Code: SELECT @hash:= value FROM prefs WHERE key = 'hash_method'; -- @hash = SHA1 SELECT @hash('test string'); So the variable @hash stores the current hash method in the `prefs` table. This method does not work, is there a way to do this?
View Replies !
Bin Directoryh In PATH Variable
I've been using the command line tool as I do my first exercises in MySql, but I need to do some work with jdbc, and I'm wondering if I should add the bin directory into my PATH variable so I can run my sql from any command prompt window.
View Replies !
System Variable Tx_isolation
The System Variable tx_isolation is used for what? What this actually means, Development Server System Variable Value tx_isolation READ-COMMITTED Production Server trx_isolation REPEATABLE-READ What will be the difference between these two thing?
View Replies !
Trouble Using Variable In LIKE Clause
I'm trying to use a variable in a LIKE clause with wildcard characters, but I'm having some trouble. It works with just the variable name (a.ActivityName) and returns a valid set. SELECT n.Name n.Time from NameTable n JOIN Activity a ON (n.TimePeriod = a.TimePeriod) WHERE n.Name LIKE a.ActivityName; It doesn't work with quotes around the wildcard characters and variable name ('%a.ActivityName%') because it returns an empty set. SELECT n.Name n.Time from NameTable n JOIN Activity a ON (n.TimePeriod = a.TimePeriod) WHERE n.Name LIKE '%a.ActivityName%'; It doesn't work with only the wildcard characters and variable name (%a.ActivityName%) because it returns a syntax error. SELECT n.Name n.Time from NameTable n JOIN Activity a ON (n.TimePeriod = a.TimePeriod) WHERE n.Name LIKE %a.ActivityName%;
View Replies !
@variable From Command-line
I'm trying to figure out if it is possible for me to pass a variable to a SQL file. I'm on Linux The SQL file would look something like: charset utf8; select * from `my_table` where `a_field` = @var; And I would like to be able to change the value of @var on the command-line. Is there anyway to do it, without me having to make an extra program/script for this?
View Replies !
|
TRACKER
