How To Write A Trigger
we have an invoice table with fields ( invoice_id,date, item_id,item_name, price_per_item,quantity,cust_id)
here (item_id ,item_name and price_per_item) are from table item_details.
now i have to write a trigger to track the users who have changed the price_per_item column before insertion in invoice table.
View Complete Forum Thread with Replies
Related Forum Messages:
How To Write Trigger
want to copy the table data from one host to another host why because its live data . i want to update one host to another host. ie i have one host 192.168.1.1 (db name is EXample table is live) i want to copy the live table values into another table which is located in different host ie(192.168.5.5 db name is Datadummy and table is livedummy). in triggers is it possible or not?
View Replies !
How Do I Write This Trigger?
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot! Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated and inserted to “user” (with all the same field info). How do I write this trigger? I know, it’s anti-normalization, but it will really help me out with testing one of our site's authentication.
View Replies !
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
View Replies !
Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this: May 19 20:42:09 2002 7 Services 3.23 4.34 0 None 2.31 Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?
View Replies !
Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line. Can't successfully use "INTO OUTFILE". Getting the following: mysql> select * -> into outfile '/users/steves/desktop/test.txt' -> fields terminated by ',' -> from industry; ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13) At the shell, I get this: steves2ndmac:~ steves$ perror 13 OS error code 13: Permission denied Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?
View Replies !
Cannot Write To Database
The result I am getting is: request "Could not execute SQL query" SELECT dt FROM webcalendar_events WHERE dt in ('2009-03-13') AND calendar_id=cum_wetheralhalls FOR UPDATE Unknown column 'cum_wetheralhalls' in 'where clause' The strange thing is that when the calendar_id is a numeric value, it works perfectly. Is there anything that I can do in MySQL to get this to work with text-characters also, or do I have to use numeric values for this? Is this something in the PHP script that is preventing it from saving to the database? In case you need to know the table row calendar_id is varchar(30).
View Replies !
Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter. What is the best way to do this in the most efficient query possible. I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators. any ideas? here is the query fyi: PHP $sql ="SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, categories_id as cat_id, " .boolean_sql_select( boolean_inclusive_atoms($search_string), $fulltext_key)." as relevance " ."FROM $table_name " ."WHERE " .boolean_sql_where($search_string,$fulltext_key)." " ."HAVING relevance>0 "; if (isset($HTTP_GET_VARS['sort'])) { if ($_GET['sort']=="brand") { $sql.="ORDER BY products_name "; } elseif ($_GET['sort']=="size") { $sql.="ORDER BY products_weight "; } elseif ($_GET['sort']=="price_low") { $sql.="ORDER BY products_price ASC "; } elseif ($_GET['sort']=="price_high") { $sql.="ORDER BY products_price DESC "; } elseif ($_GET['sort']=="fav") { $sql.="ORDER BY products_name "; } } else { $sql.="ORDER BY relevance DESC "; }
View Replies !
Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query: $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC"); And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are: rel_id post_id category_id category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio. So pseudocode would be something like: SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).
View Replies !
How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in my table as a condition. "select id from employees inner join translog on employees.emp_id=translog.empid inner join outofofficedays on employees.emp_id=outofofficedays.emp_id inner join holidays where (here is my question: ��-04-02' doesnt exist in the column translog.vtransdate) and (��-04-02' not between holidays.H_START and H_END) order by translog.vTransDate,employees.emp_id limit 1"
View Replies !
How To Write This Select?
I'm having some troubles writing a select. I have the following tables: theater ------- - id - name movie ----- - id - name showtime --------- - id - theater - movie - from - to - schedule I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?
View Replies !
C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I am using Stata that is written in C API, and I want to write the data out to MYSQL database. Using ODBC and loading the data in MYSQL from STATA takes forever. Can anyone please tell me if I can create the myd files by using the data in memory thru C programming.
View Replies !
Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to use the MySQL C API to be able to use a normal sql insert statement that will send the data or file to a directory and NOT the database. I'm not sure if there is anything out there that will allow this, but I've searched everywhere, and I have found no solution.
View Replies !
Write Conflict Error
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Code:
View Replies !
Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
MySql Write Vs Read
Essentially I am faced with a design choice; have an oft run (modifiable) query run every time any user wants to run it, comparing criteria againt each and every user in the table and delviering list OR saving that list (user/user/) so the next time all that needs to be queried is the existence of a record in the table. The basic tradeoff; version 1 there are many simultaneous queries of the user db on complex search criteria; version 2 there are fewer but their are as many far more simple queries to a much larger table, not to mention many writes to same to keep the table current. I know this is pretty vague, but I was wondering if in general there is an area that mysql excels in vis a vis complex query smaller tables simple query larger tables. Question 2 is there anyway to guestiate the search/write times of a user log in to both query and update? #Records, fields, etc? I'd like to keep log in to < 10 seconds max.
View Replies !
How To Write Group By Query
i create table item-info. i want to view my data in the following way. i write query use group by but i want name show only one time no repeatation name next line. item-info table: id name item price 1 A XX 15 2 B XX 16 3 A YY 17 4 C YY 20 5 B ZZ 50 6 A ZZ 40 View my data in the following way: name item price A XX 15 YY 17 ZZ 40 B XX 16 ZZ 50 C ZZ 20
View Replies !
Select .. Into Outfile Write
global scope: trying to execute on a schedule a .sql file which writes out a .csv file. windows(dont ask)/mysql in the batch file i have mysql -u myUser --password=somePass < myFile.sql the sql query in the file Code: use mydb; select field1,field2,field3 union select `actField1`,`actField2`,`actField3` into outfile 'F:folder est.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM `detailsTable`;
View Replies !
Create/write To The File
I am working on Solaris and seem to get the following error Cant create/write to the file '/home/dslab456/temp/tempoutput.lst' As in windows i cannot specify which drive it is or so ? could anyone help me out with this ? SELECT * INTO OUTFILE '/home/dslab456/temp/tempoutput.lst' FROM stocklist_vokus s,p_tmpr p WHERE s.STOCKLIST =p.stocklist_value ORDER BY p.rid Edited 1 time(s). Last edit at 02/12/2007 05:38AM by summer queen.
View Replies !
Read & Write Locks
I need to lock some of my tables for a while with lock tables. However I find how the READ and WRITE locks is implemented in the MyISAM storage engine a little odd. When obtaining a READ lock all updates are prevented, including writes from the thread obtaining it. WRITE locks gives exclusive access to the issuing thread. However what I want is to give read and write priveleges to one thread and read priveleges to all other threads, and I can't figure out how to obtain this effect with lock tables... Is it even possibel?
View Replies !
Vita And Write In MySQL
My web site use a odbc to a MySQL database. I create it on XP and it's worked but I'm now on vita and the data base on a 2003 server. All the reading access on the database are ok but all the writing access are denied. I haven't error message just error 500 on ie7.
View Replies !
Re-write Text File
i'm creating a log file regarding database operations. My problem is i want to append a new record into the log file each time i update my database. Previously I've used the following method to create a file: SELECT * INTO OUTFILE 'c:/"+fileName[i]+" ' FIELDS TERMINATED BY '|' LINES TERMINATED BY ' ' FROM "+tableName[i]+"; MY problem is I cannot re-use the file created. Is there any way to call the same file for the purpose of re-use. Or maybe the OUTFILE do not allow file created to be re-use.
View Replies !
Write Once Read Many Field
I am designing a database and one of the requirements my client has is that once data is entered into certain fields it cannot, under any circumstances, be changed. Is it possible to create a write once read many (WORM) field in MySQL directly?
View Replies !
Write Text Files
I'm wondering if it's possible to write text files from a trigger/procedure. For instance, if I want to write a log file, for debugging, that could trace my procedure. I'm a Oracle developer, and in that environment I can use UTL_FILE to write txt files. Is there something similar in MySQL ?
View Replies !
Can't Create/Write To File
#1 - Can't create/write to file 'AC Web Ultimate RepackServer mp#sql_dac_0.MYI' (Errcode: 2) I am using Wampserver and I am creating the database using myphpadmin. I installed the AC Web Ultimate Repack for World of Warcraft, but since then it has been uninstalled.
View Replies !
Slow Write Speeds
Here is what i have IBM x226 - 8GB ram - 2x 3.4 Ghz Xeon (single core) processors - RAID 5E (6x300GB disks) Suse Linux 9.0; MySQL 5.0.13 So, I am migrating from SQL server 2000 on a much smaller Dell PowerEdge (2gb ram, 2 Xeon 3.0 Ghz, Raid 5 over 5x74GB). I am exeriencing very slow write times. For instance, there is one table on both servers that has approx 9mil rows...here is a basic test: Update loan set test_col = 1; Platform Time IBM/Linux/MySQL 5 9:45 Dell/Win/SQL server 1:37 The tests were run during very light server activity and I tried them multiple times. Our IT department is fairly insistant that the IBM server is running per spec...obviously something is not right. I have changed some of the server variables with no improvement.
View Replies !
How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username. delimiter $$ create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int ) deterministic begin if length(m_username) = 0 then set m_username = 'jason' #set temporary name if empty end if set m_username_matched = 0; #finds number of matches for the given username select count(*) into m_username_matched #store into the variable to be returned from sampleDB.sampleTable where userName like '%m_username%' #this is the statement that does not work# end$$
View Replies !
Write Vs. Read Performance
I'm guessing this is just as much a file system question as a MySQL question, but I haven't been able to find any information touching this particular subject. Not considering data sent through network, what would be faster, fetching a row from a database, updating those columns that have been changed, let's say one of five, or simply don't fetch and update all the columns in the row (not including the id of course)?
View Replies !
No Read Or Write Between INSERT And UPDATE
I am using mysql with the InnoDB engine. I wrote a perl script that first selects something from a table, and then updates a second table based on the select from the first table. I need to make sure that there is no read or write to the tables while my script performs the insert and update. I looked at http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html and it says this: --------------------------------------------------------- If you are using a storage engine in MySQL that does not support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely: LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; UNLOCK TABLES; Without LOCK TABLES, it is possible that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements. --------------------------------------------------------- However, I am using InnoDB and it DOES support transaction. So, does that mean that even if I don't lock my tables, it will still work? If not, what do I need to do?
View Replies !
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
View Replies !
Read/write Table Efficiency
I've got a bit of an efficiency question, and just trying to figure out how best to manage this. For my site HearWhere.com, I have a table which holds artists shows. When i am refreshing the collection of shows and geocoding the locations, the database response for user queries slows significantly - which is understandable. One option I'm considering is to have one database which the users connect to for queries, and a second separate database for writing the updates, and then I can just move the updated database to become the live database on a daily basis. The other option I think is just to have a separate table which I write the updates to, and then just replace the table the users interact with from the table which was updated Either way I go, at the moment everything would be done on the same sql server - I don't currently have enough traffic to justify multiple servers.
View Replies !
How To Write A SELECT/COUNT + Other Fields
I have a table like so: NAME | AGE Joey | 21 Mary | 18 Lass | 43 Moch | 33 Joey | 23 Mary | 25 Mary | 65 Lass | 90 I what to write a single query that retrieves the total number of times a single name is found in the the table (Joey appears two times, Mary appears 3 times, and Lass appears two times) AND also retrieves each record. SELECT COUNT(name) as name_total, name, age FROM persons_table WHERE 1 RESULT: row#: name_total, name, age: row1: 2, Joey, 21 row2: 3, Mary, 18 row1: 2, Lass, 43 row1: 1, Moch, 33 row1: 2, Joey, 23 row1: 3, Mary, 25 row1: 3, Mary, 65 row1: 2, Lass, 90
View Replies !
Write A Script With Looping Constructs
I want to write a script to insert some rows in a database. The user of the script will login to mysql and paste the script at this point. Is there a way to use looping constructs, variables, etc. Iam thinking of a PL/SQL equivalent.
View Replies !
Can't Create/write To File In C:windows
I installed the 3.51 ODBC driver on a new test machine that is re-imaged each night to a clean OS (Win XP Pro SP2). We place our developer daily builds on this machine and run some automation. In the automation process, MYSQL statements are ran. The issue I get is that sometimes it stops with this error: "Can't create/write to file 'C:WINDOWSTEMP#sql_1808_0.MYD' (Errcode: 13), #-2147217871" or it will run right away, or sometimes it takes 3 tries before it finally gets past whatever it is choking on. I do not have MYSQL installed on that machine, only the driver. I have given the "Everyone" account full permissions, but still no luck. Any suggestions? The environment variable on the machine is set to C:WindowsTemp and it appears to execute some statements before, just is stops on some.
View Replies !
How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!". I have 2 tables, same DB. Table 1 is named Policies Table 2 is named Assignments Both contain a PolicyID field. I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments. So if "Select PolicyID from Assignments" returned the following: 1, 2, 3, 4, 5, 6 And "Select PolicyID from Policies" returned: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 What do I use to return this? 7, 8, 9, 10 Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
View Replies !
Trying To Write A Two-part Fast Query
The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?" The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3. And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.
View Replies !
Can Seqouia/c-jdbc Segregate Read & Write DBs ?
I haven't started using Seqouia/C-jdbc but I am probing the ability to do something like this: Controller CW has 2 controllers under it - C1 & C2. C1 is local and has a host HA with X.db's tables t1 & t2. C2 is remote and has a host HB also with X.db's tables t1 & t2. Controller CR has 2 controllers under it - C3 & C4. C3 is local and has a host HC with X.db's tables t1 & t2. C4 is remote and has a host HD also with X.db's tables t1 & t2. X.db is MySql type, from experience I've found that MySQL is better off doing either only writes or only reads. So I want to segregate my read and write stores. Hence, HA & HB are meant for writing into, while HC & HD are meant for reading. Now writes coming to CW should be treated like transactions which land up on local and remote X.db. These are my WRITE-ONLY-DBs. Likewise I want reads to goto CR for load balancing with higher priority to read from HC than HD. Questions: (a) Can c-jdbc help in replication of data between HA-HC and HB-HD ? Which component would help to set this up ? Or do I need to go for out-of-band MySQL replication ? (b) Is this configuration optimal for high scalability or availability ? Should I have added contollers horizontally instead of vertically ? (c) Also, looks like I'd have to write an app to direct writes to CW and read to CR. Or is there a way to obviate that with the controller settings itself ?
View Replies !
Obtaining Read/Write Ratio -Replication
I am looking into a replication strategies to improve performance and redundancy for a MySQL 4.1 database running on Linux/Apache2/mod_python. I am curious as to the standard way of obtaining a Read/Write Ratio. What patterns of usage suit One-Way or Two-Way replication? In my situation I have a database that intermittent bursts of inserts through the day, a lot of batched deletes/inserts/updates over night and fairly constant selects. I'd expect the RW Ratio would fluctuate dramatically in a 24 hour period.
View Replies !
Access ODBC 'write Conflict' SOLVED!
I had this problem where a user would attempt to change a record, and always get the 'write conflict', and that another user had changed the record... even though no other users were on the database. I searched high and low for a solution, to no avail. Yes, I had a primary key; I had a TIMESTAMP field. I played around with Access' settings for record locking, no record locking, etc.; I set the MyODBC setting to "return matching rows". None of these worked. Here is what I found: I had a TIMESTAMP field in the MySQL table, NULL = OK. I imported data from a text file into this table. This text file did not have the TIMESTAMP field, so all of the records in the MySQL table had a default timestamp of '00000000000000'. I wrote a SQL statement that changed this TIMESTAMP '00000000000000' value to an arbitrary value (for example, '20030910134340'). Everything worked fine.
View Replies !
How To Write :send Email And Clear Form In Php
I am a student and I am building a webpage. I need to build a formular to send emails. The fields to fill are the name and email of the sender and also the message to send. I would like to test it locally and also later on a remote server. Code:
View Replies !
Can't Create/write To Database And Temp Directories
I'm quite new to mySQL, and am having problems being able to create database tables. I'm on my [5th] setup of mySQL/apache/php, just after formatting windows, and everything else seems to be working, and working together, so I'm assuming the installation is ok. I can create databases, but when I try to create a table, I get this error message: ERROR 1 (HY000): Can't create/write to file '.customermidian.frm' (Errcode: 2) I'm assuming I haven't set up my temp folder correctly, but I don't understand how it works properly. After reading the mySQL maual repeatedly, I tried using --tmp=c: emp to set it, and it didn't seem to work, and I created a my.ini file with this in it: [mysqld] tmpdir=C:/temp is there anything else I can try? I am logging into mySQL with the root account, and a password.
View Replies !
How To Optimize Frequency Of Disc Write Operations
I need to optimize frequency of disc write operations for inserted or updated data on MyISAM tables. How can I tune MySQL for large records block disc flushing instead of record-by-record flushing by default? Some DBMS parameters have effect on disc flushing ("flush", "flush_time", "delay_key_write"), but any of these parameters does not allow to reduce intensity of disc write operations. MySQL 5.0 (Win32) is used.
View Replies !
Database Permissions (Unable To Write To .CSV File)
My managed server is running 4.1.21-standard. We have been able to connect and read a .csv file into the tables, but can't write or append data from tables to a .csv file for import into another application even thought all permissions have been granted. Is this because File is not in the list of permissions to be granted? Also, first user gets kicked out when second user access the MySQL Account Maintenance page. Second user does not even have to log in to cause problems. Are these MySQL issues or cPanel issues? How can I fix them?
View Replies !
|
TRACKER
