MySQL Variables, Modifying Microsoft SQL Sequence Insert For Use In Mysql
I have the following microsoft SQL statement that inserts 25 new
records in sequence. However I am trying to modify this to work in
MySQL.
I cant seem to get the variable decleration right. I tried using
DECLARE CurVal int but that generates a mysql error. How can I modify
the code below to work under mysql? Code:
View Complete Forum Thread with Replies
Related Forum Messages:
Modifying MySQL
I've been thinking alot in how to modify all my stored data, i have large database with many tables, each table has lots of data (around 40,000 rows) , each row has a url field, the url field datas are all similar and link to downloadable files. Now I need to change all these url stored data from (( http://www....../example1/filename.ext )) to (( /usr/example1/filename.ext ))
View Replies !
Bulk Insert Of New Records In Sequence
I am looking to create a querty that searches for the max value a field called listnum in a table called tbl_listing and then interests 25 new records with listnum's starting 1 higher then the max value. ie if the highest listnum is 1000 it should insert new records with a listnum of 1001-1024. All the other fields in the table can be blank. Is there a simple (or not so simple) query that I can run to do this?
View Replies !
Microsoft SQL Vs MySQL
I am having a program developed in Microsoft SQL. I am going forward with the idea that we do not need their sql or windows to get it all running. saving money makes the boss . i asked if we could use mysql instead. they said something like " because the application is built for microsoft sql it uses colons and mysql uses semicolons. " The queries that the program runs internally that is. whatever, they are different they say. does this have any validity?
View Replies !
MySql Vs Microsoft Sql
Is MySql and Microsoft Sql pretty much the same? I mean do they work the same? I have a friend that has his own server and wants to store the site on his server but since I have never used Microsoft Sql I dont know if I will be able to use it.
View Replies !
Microsoft SQL And MYSQL Together
I need to install MySQL in the same machine where is installed Microsoft SQL Server too. Could MySQL and SQL Server work fine together or I need to unistall Microsoft SQL Server first (option that I cannot consider because it has needed by any others programs)?
View Replies !
Microsoft SQL -> MySQL
I am not sure if this is the right forum to ask, but I have a Microsoft SQL statement which doesn't seem to work in mySQL: select ProjectID,BugID,BugTitle from Bug where Cast(BugID as Char) + Cast(ProjectID as Char) not in (select Cast(BugID as Char) + Cast(ProjectID as Char) from BugTracking) Anyone has any ideas how I can modify the above script to make it work in mySQL? If I run the above script, instead of giving me 4 records in MS SQL it is giving me over 1000 records in mySQL.
View Replies !
MySQL And Microsoft Access
I have a linux server running MySQL 4.0.12. I have a user accessing this database via ODBC (MyODBY), via Microsoft Access. The wierd thing is that he can write to the table(s) with Access, and he can extract data into Excel, but when he is viewing records in Access, he gets the correct number of rows, but all the cells say "#DELETED"...the data is still there, but Access apparently doesn't know what to do with it. I don't use Access when I write to the database...I use MySQL Front for writing and administering, and I am unfamiliar with Access. The only reason we are employing it with some of the users is their familiarity with Access, and MS Office. I am using the most recent MyODBC driver MySQL has on their site
View Replies !
How To Create A Sequence In MySQL
I am not able to create a sequence in MYSQL 5.0 Want help on creating the same. Basically, what I am looking for is, that I should get a number greater that the last one on every call to this sequence. In Oracle, one can simply write something like:- CREATE SEQUENCE seq_ordermaster increment by 1 start with 1 maxvalue 999999minvalue 1 nocache Nocycle; And while calling, one can use it by selecting seq_ordermaster.nextval from DUAL Considering the fact that there is no DUAL table in MySQL, how can One write such a sequence and also, how one can get the next value on each select statement?
View Replies !
Mysql Execution Sequence
I need to retrieve the last value from a column in a mysql table, perform some actions on it, then add 1 to the original value and store a new entry. I am worried that if multiple users access the database at the same time I will end up with duplicated 'new values'. Does mysql complete execution of a script before it allows access to another user or do I need to 'lock' the table in some way while the script executes?
View Replies !
Path Of MySQL Database In Microsoft LAN
All computers in the LAN are running windows OS. The MySQL database is installed on a server and a script is running on a client. I have problem to use the script to connect and query with the MySQL database. What is the path or address of the MySQL database?
View Replies !
Microsoft Access / MySQL Linked Tables
I've got a UTF-8 database table with Japanese text in. I thought I'd start a new post as its a seperate issue to my previous one. I'm trying to set up MS Access using linked tables to access the data. I'm using the MySQL Connector/ODBC 3.51 driver. Trouble is its displaying the data as ??? ??? ??? like this. I need to somehow find a way of making the odbc connection or access into UTF-8. Also, the other client I'm using ( Aqua Data Studio ) displays the text incorrectly. I know the data is ok in the actual table as if I retrieve it it displays on my web page ok. EDIT: It displays FINE in MySQL Query Browser, part of MySQLAdmin. This proves its the client.
View Replies !
Variables In MySQL
I am new to MySQL and am trying to acquire skills in MySQL without the benefit of any formal training although I have been in IT for more years than I care to admit (retired now). I have bought and read several SQL and MySQL books and frequently reference the on-line MySQL manual on http://www.mysql.com/doc/en/index.html. I have also browsed this and related newsgroups with considerable benefit. For all of my efforts I seem to have missed some basic points regarding the use of variables in MySQL scripts. I hope that the readers of this posting do not find the problem described in the example code below too silly or trivial to comment on. Please advise me if I have chosen the wring newsgroup for this posting. As the example code shows, all I a trying to do is set a variable (v) to the current contents of the cell in column i, row 1 of table t. DROP TABLE IF EXISTS t; CREATE TABLE t ( i INT(2), r int(2) NOT NULL auto_increment, PRIMARY KEY (r) ); INSERT t SET i = 1; # This is the line that fails. SET @v = SELECT i FROM t where r = 1; The version of MySQL that I am using is 4.0.6-gamma-win. It throws back the above code with a bland error message that does not indicate what the problem is. Am I wrong in thinking that variables can be used in this way in MySQL? Assuming that I eventually get variables to work I intend to set up a while loop for going through the rows of a table referencing the cells in several columns.
View Replies !
Variables In Mysql
I need to know how to define variables which will accept null values from the server . I am new and if anybody know about variables.
View Replies !
Using Variables From Php In Mysql?
im geting variables from flash using php.but i dont know how to use them variables in these statement... $placeEarnt = $_POST['placeEarnt']; $earnings = mysql_query("SELECT '$placeEarnt' FROM users WHERE username = '$username'")or die(mysql_error()); if(mysql_num_rows($earnings)==0) echo "There is no data in the table"; else for($i=0;$i<mysql_num_rows($earnings);$i++) { $row=mysql_fetch_assoc($earnings); } $updateEarnings = $row[earnings] + $_POST['tokens']; $result = mysql_query("UPDATE users SET $placeEarnt ='$updateEarnings' WHERE username = '$username'") or die(mysql_error()); this doesnt throw an error but only adds the value in $_POST['tokens'] to the database. does anyone know how i can make this work?
View Replies !
Mysql Variables.. Is This Possible?
I've got a table that stores positions of various elements along with other data, and when a user deletes one of these elements from their profile, I want to run an update query to reorganize the elements to fill the gaps left from the deleted rows.. If this sounds confusing, for example, say I have a table that looks like this after a few rows have been deleted: id | title | pos_left -------------------------- 1 | abc | 100 2 | bcd | 200 3 | cde | 300 6 | fgh | 600 8 | hij | 800 now I want to update the rows so they look like this: id | title | pos_left -------------------------- 1 | abc | 100 2 | bcd | 200 3 | cde | 300 6 | fgh | 400 8 | hij | 500 I know I can do this by running a query on each row with a for loop in PHP, but can I achieve this with a single UPDATE query, perhaps using a MySQL variable that increments on each updated row, and assign that incremented variable to the pos_left field in the next row? I've tried the query below which doesn't work: SET @num=0; UPDATE admin_desktop SET pos_left = (@num = @num+100) WHERE admin_id = 3 ORDER BY title ASC
View Replies !
Inserting Variables In MySQL Table!
Have you guys tried inserting variables in mySQL tables? Do I have to use 'quote' as we had been doing to insert strings? mysql> INSERT INTO occurrence (word_id,page_id) VALUES ($word_id,$page_id); ERROR 1054: Unknown column '$word_id' in 'field list'
View Replies !
Setting MySQL Variables At Start Using My.ini
I am battling to set the default datetime_format in my.ini, if i add the line i want to change the setting, the service refuses to start! The line i am adding is as follows: set-variable = datetime_format="%y/%m/%d %H:%i:%s" Am i doing something wrong here?
View Replies !
Using Variables In A MySQL Query - Problem PLEASE HELP
I have been having a problem sending a MySQL query with a php variable in it. This is my $sql = MYSQL_QUERY('SELECT `messages`.`msg_no`, `messages`.`name_for`, `messages`.`name_from`, `messages`.`date_left`, `messages`.`message`' . ' FROM messages' . ' WHERE (`messages`.`name_for` = $query_for)' . ' ORDER BY `messages`.`msg_no` DESC LIMIT 0, 30'); Furhter down the script I have: while ($row = mysql_fetch_array($sql, MYSQL_NUM)) { echo "<TR>"; echo "<TD width=ཉ%'>", $row[2], "</TD><TD width=ཌ%'>", $row[3], "</TD><TD width=ྃ%' >", $row[4], "</TD>"; echo "</TR>"; On the 5th line of the first piece of code I have the var $query_for, which has a string value. If I use this code in place of that line: . ' WHERE (`messages`.`name_for` = Ben)' It works perfectly but when using the variable there instead it says when I open the page: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
View Replies !
MySQL Values (for Variables) That Seem To Be To High
phpMyAdmin shows my some values for variables in the status are marked red. I assume, that these values might be to high. Section Handler: Handler_read_rnd 414 k Handler_read_rnd_next 316 M Temporary data Created_tmp_disk_tables 3 k Key cache Key_reads 78 k Joins Select_full_join 3 k Tables Opened_tables 6 k Table_locks_waited 5 Now I've got a lot of questions: 1) For Handler_read_rnd it says "You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly." How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this? 2) For Handler_read_rnd_next it says "Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have." Same questions as in 1): How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this? 3) For Created_tmp_disk_tables it says "If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based." The tmp_table_size value it 33,554,432 right now. How would I increase this? 4) Key_reads: "The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests." My key_buffer_size is 16,777,216, Key_read_requests is 3M. So the cache miss should be 78 k / 3072k??? What am I supposed to do here? 5) Select_full_join: "The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables." Is there an easy way to check the indexes of all tables? 6) Opened_tables: "The number of tables that have been opened. If opened tables is big, your table cache value is probably too small." table cache is 64. What size should it be? How would I increase this? 7) Table_locks_waited: "The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication." Is there a way to find out which queries / tables should be optimized?
View Replies !
Stored Proc In Mysql Varchar Variables
Heres a little test sp, I can't get it to use the @H variable in the select , what am i doing wrong?, been messing for days now :( The row caount always comes back as 0 , but if I just change the line "SELECT count(*) INTO s from test where url=@'H';" to use url='tony' for example or any other value it works as expected. Code:
View Replies !
Mysql Extended Server Status Variables
Here are 3 questions about extended-status and server parameters. I have a server with 1G of Ram Dual Cpu running mysql 3.23.41. 1/ to know if table_cache need to be increased, what is the maximum rate of opened_table that must be seen. I read somewhere that 1 or 2 per sec was acceptable. I have a server that most of the time is in the [0,2] opened table per sec but can go up to 100 / sec at peak hour. Current table cache is 64 planning to increase to 512. Is there a way to find the optimal table_cache value? 2/ In the extended status what represents exactly open_files and open_tables. They seem to be cummulative values but doing diffs at regular 5 min intervals shows that they can grow an decrease. 3/ Is there a way to find an optimal value for the number of needed file descriptors based on maximum number of connections, table_cache, number of tables in database etc... Any referrence to server tuning based on extended-status will be helpfull.
View Replies !
Storing Values Of Session Variables Into MySQL Database
I'm creating an online survey with about 100 questions. To make it more user-friendly, I have broken them down to "bite-size" pieces of 10 questions per page. On clicking the "next page" button, the responses get stored in session variables. At the end of the questionnaire, when they click the submit button, I'd like the session variables to be stored in the database and the session will be destroyed. Code:
View Replies !
Modifying Auto_Increment Value
I currently have a table which has a primary key that consists of the following: ID INT(5) AUTO_INCREMENT NOT NULL time DATE NOT NULL I want to reset the ID field value to number one again. I used: ALTER TABLE tbl_name AUTO_INCREMENT = 1 but it doesnt work. It only works if I put a value which has not been used (if i put 1234 it will begin from there). But it cant reset it back to one.
View Replies !
Modifying Row ID Values
I want to be able to delete a row in a table, and then have every row's ID from below that row to subtract one, in order to move up. Here's how I have it, and I'm sure I'm doing something wrong. $query = "DELETE FROM tbl_image WHERE id = '{$_GET['del']}'"; mysql_query($query) or die('Error : ' . mysql_error()); $query1 = "SELECT FROM tbl_image WHERE id > '{$_GET['del']}"; mysql_query($query1) or die('Error : ' . mysql_error()); $result2 = mysql_query($query1); while($row = mysql_fetch_array($result2)) { $id = $id - 1; $query2 = "UPDATE tbl_image SET id = '$id'" mysql_query($query2) or die('Error : ' . mysql_error()); }
View Replies !
Complicated Sql Using Variables - How To Throw In Exogenous Variables
The "avg" from the following SQL code gives me the number of weekdays between two dates. Now I need to throw in holidays to see if there is a holiday between the two dates. I have holidays in a serialized array in a database field. In php I would unserialize it into a session variable. How can I incorporate holidays into the calculation of result2? SELECT @days:=((UNIX_TIMESTAMP(dateEnded) - UNIX_TIMESTAMP(dateStarted)) / 86400) + 1, @d:= MOD(@days,7), @w:=DAYOFWEEK(dateEnded), @a:=IF(((@d + @w) >= 7), 1,0), @b:=IF(((@d + @w) >= 8), 1,0), @c:=IF((@w = 0), 1,0), @result2:=(FLOOR(@days / 7) * 5) + @d - @a - @b - @c, ROUND(SUM(@result2)/COUNT(id), 1)as avg ... GROUP BY month Here's how I do it in php: PHP function sdsd(startDate, endDate) { ... //$arrHolidays = serialize(array(��-07-04', ��-09-04', ��-11-10', ��-11-23', ��-11-24', ��-12-24', ��-12-25')); $arrHolidays = unserialize($arrHolidays); foreach ($arrHolidays as $a) { if ($startDate <= $a AND $endDate >= $a) { $result--; } }
View Replies !
Cleint Modifying Database
I have a clisnt who wants to be able to easily modify a database in his hosted MySQL. Is there a way to setup Access or OpenOffice base to link to these tables so the user can edit the table in a more user friendly interface?
View Replies !
Modifying Primary Key And Auto_increment In Pre-existing Table
Given the tables defined below, is there a sql statement that could make num_1 an auto_increment field without rebuilding the table (nevermind any potential numbering conflicts)? I don't see anything in the alter table documentation that will produce this effect. Also, say I wanted to change the primary key of test_1 (e.g. primary key(num_1,num_2)). I think because num_1 is a foreign key for the table test_2 MySQL grumbles about changing the primary key, in addition to dissalowing the drop and redefinition of the primary key. Could anyone tell me how to easily handle these situations? This is a simplified example of my actual situaion, but if it can be solved here then I think I would be OK. Code:
View Replies !
Modifying Access Rights With Grant Command
I already created a user with the following syntax : grant select,insert,update on trainings.* to trainings@'localhost' identified by 'password'; Now I want to be able to add this trainings user the delete rigth without having to specify its password. So far I would have used the following syntax: grant delete on trainings.* to trainings@'localhost' identified by 'password'; Do you know how I could add the delete right without specifying the password? Of course when I add the delete right, I don't want to change the password I first set...
View Replies !
Modifying Newly Inserted Row Instead Of Old Row On ON DUPLICATE KEY UPDATE
While doing an INSERT in a table with an auto_increment key ('id' in this simplified example): INSERT INTO mytable SELECT * FROM mytable WHERE id=3 This will result in an error because id is an auto_increment key which cannot be duplicated. If I try to do: INSERT INTO mytable SELECT * FROM mytable WHERE id=3 ON DUPLICATE KEY UPDATE id=<some unused id> this will update the 'old' row with the same id. Is there a way to update the row that was just inserted (or is about to be inserted) so it assumes an unused auto_increment key? I don't want to mess with old rows. Also, I could specify the columns explicitly in my query and include all columns except 'id' but I want to make my query future-proof in case the table structure in extended.
View Replies !
MySQL Insert
This is a sample SQL query that was generated for inserting new data into the table: INSERT INTO tblMembers (FirstName, LastName, Desc, Username, Password, Website, Email, mailinglist, Image) VALUES('Mike', 'Jones', '<p>test123</p>', 'MikeJones2', 'abcd1234', 'http://www.ceditmx.com', 'acmpsolutions@yahoo.com', '1', 'businessup(36).jpg'); It gives me this error: [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]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 'Desc, Username, Password, Website, Email, mailinglist, Image) VALUES('Mike', 'Jo' at line 1
View Replies !
Microsoft Access Database
i need some advice as I'm sure there will be a way to do this i just don't know it. I have an access database that i use, however i need to share this with my work college who is in another location. So i was wondering if there is some way i can make all the data online so it runs from something like an MySql database and we just use the software to execute it all.
View Replies !
Microsoft Word Interface
I am developing an online app that helps many editors review articles, check for errors, add new articles, etc. The editors are used to using Word to help them go through several articles at once. Because of this, I'm hoping to figure out a way to allow several persons access to their content on a MySQL database online via their local install of MS Word. I have been working some with the OBDC mysql provides at http://www.mysql.com/products/connector/odbc/ (ver 3.51) and have been able to connect and retrieve some data. My problem is how do I update the users' articles online after they have finished reviewing them in MS Word?
View Replies !
Odbc 3.51 And Microsoft Access
I have an access database using mysql tables, developed on one machine on which is mysql. When I put this DB onto a client, it works okay. We took this client pc to another location with a different machine hosting freshly installed mysql and the db is still looking for the original IP address. Even when this is changed to reflect the new machine, it still looks for the old one. I cannot update table links, yet if you go to odbc and test the connection, it says connection successful.
View Replies !
|
TRACKER
