Search Results

Search found 49789 results on 1992 pages for 'mysql insert id'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 28/1992 | < Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >

  • Honing Performance Tuning Skills on MySQL

    - by Antoinette O'Sullivan
    Get hands-on experience with techniques for tuning a MySQL Server with the Authorized MySQL Performance Tuning course.  This course is designed for database administrators, database developers and system administrators who are responsible for managing, optimizing, and tuning a MySQL Server. You can follow this live instructor led training: From your desk. Choose from among the 800+ events on the live-virtual training schedule. In a classroom. A selection of events/locations listed below  Location  Date  Delivery Language  Prague, Czech Republic  1 October 2012  Czech  Warsaw, Poland  9 July 2012  Polish  London, UK  19 November 2012  English  Rome, Italy  23 October 2012  Italian  Lisbon, Portugal  17 September 2012  European Portugese  Aix-en-Provence, France  4 September 2012  French  Strasbourg, France  16 October 2012  French  Nieuwegein, Netherlands  3 September 2012  Dutch  Madrid, Spain  6 August 2012  Spanish  Mechelen, Belgium  1 October 2012  English  Riga, Latvia  10 December 2012  Latvian  Petaling Jaya, Malaysia  10 September 2012  English  Edmonton, Canada  27 August 2012  English  Vancouver, Canada  27 August 2012  English  Ottawa, Canada  26 November 2012  English  Toronto, Canada  26 November 2012  English  Montreal, Canada  26 November 2012  English  Mexico City, Mexico  9 July 2012  Spanish  Sao Paulo, Brazil  2 July 2012  Brazilian Portugese To find a virtual or in-class event that suits you, go or http://oracle.com/education and choose a course and delivery type in your location.  

    Read the article

  • SELECT INTO or Stored Procedure?

    - by Kerry
    Would this be better as a stored procedure or leave it as is? INSERT INTO `user_permissions` ( `user_id`, `object_id`, `type`, `view`, `add`, `edit`, `delete`, `admin`, `updated_by_user_id` ) SELECT `user_id`, $object_id, '$type', 1, 1, 1, 1, 1, $user_id FROM `user_permissions` WHERE `object_id` = $object_id_2 AND `type` = '$type_2' AND `admin` = 1 You can think of this with different objects, lets say you have groups and subgroups. If someone creates a subgroup, it is making everyone who had access to the parent group now also have access to the subgroup. I've never made a stored procedure before, but this looks like it might be time. This call be probably be called very often. Should I be creating a procedure or will the performance be insignificant?

    Read the article

  • Mysql 'On Duplicate Key INSERT .... SELECT...'

    - by calumbrodie
    Hi, I'm looking for a way to do the following in mysql. INSERT INTO category (cat_id,sku,description,color) VALUES ('$cat_id','$sku','$description','$color') ON DUPLICATE KEY UPDATE description=$description" this works fine... but I want something further to happen if there is a duplicate key. I want to copy a row from the 'product' table to the 'categorized_products'... AND INSERT categorized_products (a,b,c) SELECT a,b,c FROM products WHERE products.cat = '$cat_id' is there a way to do the INSERT...SELECT as part of the first query or do I need to evaluate the above with mysql_affected_rows ( 1 is updated and == 1 is inserted) and then run my second query. Obviously the above will work but means another query and more LOC. Thanks!

    Read the article

  • Several Small, Specific, MySQL Query Cache Questions

    - by Robbie
    I've look all over the web and in the questions asked here about MySQL caching and most of them seem very non-specific about a couple of questions that I have about performance and MySQL query caching. Specifically I want answers to these questions, assume for all questions that I have the query cache enabled and it is of type 2, or "DEMAND": Is the query cache per table, per database, or per server? Meaning if I have the cache size set to X and have T tables and D databases will I be caching TX, DX, or X amount of data? If I have table T1 which I regularly use the SQL_CACHE hint on for SELECT queries and table T2 which I never do, when I query T2 with a SELECT query will it check through the cache first before performing the query? *Note: I don't want to use the SQL_NO_CACHE for all T2 queries.* Assume the same situation as in question 2. If I alter (INSERT, DELETE) table T2 will any processing be done on the cache? For answers to 2 and 3, is this processing time negligible if T2 is constantly being altered and is the target of a majority of my SELECT queries?

    Read the article

  • Optimizing MySQL update query

    - by Jernej Jerin
    This is currently my MySQL UPDATE query, which is called from program written in Java: String query = "UPDATE maxday SET DatePressureREL = (SELECT Date FROM ws3600 WHERE PressureREL = (SELECT MAX" + "(PressureREL) FROM ws3600 WHERE Date >= '" + Date + "') AND Date >= '" + Date + "' ORDER BY Date DESC LIMIT 1), " + "PressureREL = (SELECT PressureREL FROM ws3600 WHERE PressureREL = (SELECT MAX(PressureREL) FROM ws3600 " + "WHERE Date >= '" + Date + "') AND Date >= '" + Date + "' ORDER BY Date DESC LIMIT 1), ..."; try { s.execute(query); } catch (SQLException e) { System.out.println("SQL error"); } catch(Exception e) { e.printStackTrace(); } Let me explain first, what does it do. I have two tables, first is ws3600, which holds columns (Date, PressureREL, TemperatureOUT, Dewpoint, ...). Then I have second table, called maxday, which holds columns like DatePressureREL, PressureREL, DateTemperatureOUT, TemperatureOUT,... Now as you can see from an example, I update each column, the question is, is there a faster way? I am asking this, because I am calling MAX twice, first to find the Date for that value and secondly to find the actual value. Now I know that I could write like that: SELECT Date, PressureREL FROM ws3600 WHERE PressureREL = (SELECT MAX(PressureREL) FROM ws3600 WHERE Date >= '" + Date + "') AND Date >= '" + Date + "' ORDER BY Date DESC LIMIT 1 That way I get the Date of the max and the max value at the same time and then update with those values the data in maxday table. But the problem of this solution is, that I have to execute many queries, which as I understand takes alot more time compared to executing one long mysql query because of overhead in sending each query to the server. If there is no better way, which solution beetwen this two should I choose. The first, which only takes one query but is very unoptimized or the second which is beter in terms of optimization, but needs alot more queries which probably means that the preformance gain is lost because of overhead in sending each query to the server?

    Read the article

  • Syntax Error in MySql StoredProc

    - by karthik
    I am using the below stored proc in mysql to generate the insert statements. I am getting the following error : Script line: 4 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 '\’”\’,',’ifnull(‘,column_name,’,””)’,',\’”\’)')) INTO @S' at line 12 What would be the syntax problem in that ? DELIMITER $$ DROP PROCEDURE IF EXISTS `InsGen` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`(in_db varchar(20),in_table varchar(20),in_file varchar(100)) BEGIN declare Whrs varchar(500); declare Sels varchar(500); declare Inserts varchar(2000); declare tablename varchar(20); set tablename=in_table; select tablename; # Comma separated column names – used for Select select group_concat(concat(‘concat(\’”\’,',’ifnull(‘,column_name,’,””)’,',\’”\’)')) INTO @Sels from information_schema.columns where table_schema=’test’ and table_name=tablename; # Comma separated column names – used for Group By select group_concat(‘`’,column_name,’`') INTO @Whrs from information_schema.columns where table_schema=’test’ and table_name=tablename; #Main Select Statement for fetching comma separated table values set @Inserts=concat(“select concat(‘insert into “, in_db,”.”,tablename,” values(‘,concat_ws(‘,’,”,@Sels,”),’);’) from “, in_db,”.”,tablename,” group by “,@Whrs, ” INTO OUTFILE ‘”, in_file ,”‘”); PREPARE Inserts FROM @Inserts; EXECUTE Inserts; END $$ DELIMITER ;

    Read the article

  • Mysql - Help me alter this search query to get desired results

    - by sandeepan-nath
    Following is a dump of the tables and data needed to answer understand the system:- The system consists of tutors and classes. The data in the table All_Tag_Relations stores tag relations for each tutor registered and each class created by a tutor. The tag relations are used for searching classes. CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`), KEY `tag_4` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Tags` (`id_tag`, `tag`) VALUES (1, 'Sandeepan'), (2, 'Nath'), (3, 'first'), (4, 'class'), (5, 'new'), (6, 'Bob'), (7, 'Cratchit'); CREATE TABLE IF NOT EXISTS `All_Tag_Relations` ( `id_tag` int(10) unsigned NOT NULL default '0', `id_tutor` int(10) default NULL, `id_wc` int(10) unsigned default NULL, KEY `All_Tag_Relations_FKIndex1` (`id_tag`), KEY `id_wc` (`id_wc`), KEY `id_tag` (`id_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `All_Tag_Relations` (`id_tag`, `id_tutor`, `id_wc`) VALUES (1, 1, NULL), (2, 1, NULL), (3, 1, 1), (4, 1, 1), (6, 2, NULL), (7, 2, NULL), (5, 2, 2), (4, 2, 2); Following is my query:- This query searches for "first class" (tag for first = 3 and for class = 4, in Tags table) and returns all those classes such that both the terms first and class are present in the class name. SELECT wtagrels.id_wc,SUM(DISTINCT( wtagrels.id_tag =3)) AS key_1_total_matches, SUM(DISTINCT( wtagrels.id_tag =4)) AS key_2_total_matches FROM all_tag_relations AS wtagrels WHERE ( wtagrels.id_tag =3 OR wtagrels.id_tag =4 ) GROUP BY wtagrels.id_wc HAVING key_1_total_matches = 1 AND key_2_total_matches = 1 LIMIT 0, 20 And it returns the class with id_wc = 1. But, I want the search to show all those classes such that all the search terms are present in the class name or its tutor name So that searching "Sandeepan class" (wtagrels.id_tag = 1,4) or "Sandeepan Nath" also returns the class with id_wc=1. And Searching. Searching "Bob First" should not return any classes. Please modify the above query or suggest a new query, if possible using MyIsam - fulltext search, but somehow help me get the result.

    Read the article

  • convert MsSql StoredPorcedure to MySql

    - by karthik
    I need to covert the following SP of MsSql To MySql. I am new to MySql.. Help needed. CREATE PROC InsertGenerator (@tableName varchar(100)) as --Declare a cursor to retrieve column specific information --for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000) --for storing the first half --of INSERT statement DECLARE @stringData nvarchar(3000) --for storing the data --(VALUES) related statement DECLARE @dataType nvarchar(1000) --data types returned --for respective columns SET @string='INSERT '+@tableName+'(' SET @stringData='' DECLARE @colName nvarchar(50) FETCH NEXT FROM cursCol INTO @colName,@dataType IF @@fetch_status<0 begin print 'Table '+@tableName+' not found, processing skipped.' close curscol deallocate curscol return END WHILE @@FETCH_STATUS=0 BEGIN IF @dataType in ('varchar','char','nchar','nvarchar') BEGIN SET @stringData=@stringData+'''''''''+ isnull('+@colName+','''')+'''''',''+' END ELSE if @dataType in ('text','ntext') --if the datatype --is text or something else BEGIN SET @stringData=@stringData+'''''''''+ isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' END ELSE IF @dataType = 'money' --because money doesn't get converted --from varchar implicitly BEGIN SET @stringData=@stringData+'''convert(money,''''''+ isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' END ELSE IF @dataType='datetime' BEGIN SET @stringData=@stringData+'''convert(datetime,''''''+ isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+' END ELSE IF @dataType='image' BEGIN SET @stringData=@stringData+'''''''''+ isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+' END ELSE --presuming the data type is int,bit,numeric,decimal BEGIN SET @stringData=@stringData+'''''''''+ isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+' END SET @string=@string+@colName+',' FETCH NEXT FROM cursCol INTO @colName,@dataType END

    Read the article

  • MySQL Connector: parameters not being added

    - by LookitsPuck
    Hey all! Looking at my query log for MySQL, I see my parameters aren't being added. Here's my code: MySqlConnection conn = new MySqlConnection(ApplicationVariables.ConnectionString()); MySqlCommand com = new MySqlCommand(); try { conn.Open(); com.Connection = conn; com.CommandText = String.Format(@"SELECT COUNT(*) AS totalViews FROM pr_postreleaseviewslog AS prvl WHERE prvl.dateCreated BETWEEN (@startDate) AND (@endDate) AND prvl.postreleaseID IN ({0})" , ids); com.CommandType = CommandType.Text; com.Parameters.Add(new MySqlParameter("@startDate", thisCampaign.Startdate)); com.Parameters.Add(new MySqlParameter("@endDate", endDate)); numViews = Convert.ToInt32(com.ExecuteScalar()); } catch (Exception ex) { } finally { conn.Dispose(); com.Dispose(); } Looking at the query log, I see this: SELECT COUNT(*) AS totalViews FROM pr_postreleaseviewslog AS prvl WHERE prvl.dateCreated BETWEEN (@startDate) AND (@endDate) AND prvl.postreleaseID IN (1,2) I've used the MySQL .NET connector on countless projects (I actually have a base class that takes care of opening these connections, and closing them with transactions, etc.). However, I took over this application, and here I am now. Thanks for the help!

    Read the article

  • Stored procedure using cursor in mySql.

    - by RAVI
    I wrote a stored procedure using cursor in mysql but that procedure is taking 10 second to fetch the result while that result set have only 450 records so, I want to know that why that proedure is taking that much time to fetch tha record. procedure as below: DELIMITER // DROP PROCEDURE IF EXISTS curdemo123// CREATE PROCEDURE curdemo123(IN Branchcode int,IN vYear int,IN vMonth int) BEGIN DECLARE EndOfData,tempamount INT DEFAULT 0; DECLARE tempagent_code,tempplantype,tempsaledate CHAR(12); DECLARE tempspot_rate DOUBLE; DECLARE var1,totalrow INT DEFAULT 1; DECLARE cur1 CURSOR FOR select SQL_CALC_FOUND_ROWS ad.agentCode,ad.planType,ad.amount,ad.date from adplan_detailstbl ad where ad.branchCode=Branchcode and (ad.date between '2009-12-1' and '2009-12-31')order by ad.NUM_ID asc; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EndOfData = 1; DROP TEMPORARY TABLE IF EXISTS temptable; CREATE TEMPORARY TABLE temptable (agent_code varchar(15), plan_type char(12),sale double,spot_rate double default '0.0', dATE DATE); OPEN cur1; SET totalrow=FOUND_ROWS(); while var1 <= totalrow DO fetch cur1 into tempagent_code,tempplantype,tempamount,tempsaledate; IF((tempplantype='Unit Plan' OR tempplantype='MIP') OR tempplantype='STUP') then select spotRate into tempspot_rate from spot_amount where ((monthCode=vMonth and year=vYear) and ((agentCode=tempagent_code and branchCode=Branchcode) and (planType=tempplantype))); INSERT INTO temptable VALUES(tempagent_code,tempplantype,tempamount,tempspot_rate,tempsaledate); else INSERT INTO temptable(agent_code,plan_type,sale,dATE) VALUES(tempagent_code,tempplantype,tempamount,tempsaledate); END IF; SET var1=var1+1; END WHILE; CLOSE cur1; select * from temptable; DROP TABLE temptable; END // DELIMITER ;

    Read the article

  • MySQL DDL error creating tables

    - by Alexandstein
    I am attempting to create tables for a MySQL database, but I am having some syntactical issues. It would seem that syntax checking is behaving differently between tables for some reason. While I've gotten all the other tables to go through, the table, 'stock' doesn't seem to be working, despite seeming to use the same syntax patterns. CREATE TABLE users ( user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(30) NOT NULL, password CHAR(41) NOT NULL, date_joined DATETIME NOT NULL, funds DOUBLE UNSIGNED NOT NULL, PRIMARY KEY(user_id), UNIQUE KEY(username) ); CREATE TABLE owned_stocks ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id SMALLINT UNSIGNED NOT NULL, paid_price DOUBLE UNSIGNED NOT NULL, quantity MEDIUMINT UNSIGNED NOT NULL, purchase_date DATETIME NOT NULL, PRIMARY KEY(id) ); CREATE TABLE tracking_stocks ( ticker VARCHAR(5) NOT NULL, user_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(ticker) ); CREATE TABLE stocks ( ticker VARCHAR(5) NOT NULL, last DOUBLE UNSIGNED NOT NULL, high DOUBLE UNSIGNED NOT NULL, low DOUBLE UNSIGNED NOT NULL, company_name VARCHAR(30) NOT NULL, last_updated INT UNSIGNED NOT NULL, change DOUBLE NOT NULL, percent_change DOUBLE NOT NULL, PRIMARY KEY(ticker) ); Am I just missing a really obvious syntactical issue? ERROR: #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 'change DOUBLE NOT NULL, percent_change DOUBLE NOT NULL, last DOUBLE' at line 4

    Read the article

  • How to get MySQL database to appear on index.php

    - by Teddy Truong
    Hi, I have a submission form on my website (index.php) and I have the data(user submissions) being stored into a MySQL database. Right now, I have the user submitting a post and then the page directs them to an update.php which shows what they inputed. However, I want all of the data in the database in MySQL to be shown on the index.php. It's a lot like a comment system. User submits a post... and sees their post above the other submitted posts all on the same page. I think I'm missing AJAX... ? Here is the code for index.php <div align="center"> <p>&nbsp;</p> <h2 align="center" class="Title"><em><strong>REDACTED</strong></em></h2> <form id="form1" name="form1" method="post" action="update.php"> <hr /> <label><br /> <form action="update.php" method="post"> REDACTED: <input type="text" name="text" /> <input type="submit" /> </form> </label> </form> </div> On update.php I have this: ?php $text = $_POST['text']; $myString = "REDACTED"; mysql_connect ("db----.net", "-----3", "------------") or die ('Error: ' . mysql_error()); mysql_select_db ("-----------"); $query="INSERT INTO TextArea (ID, text) VALUES ('NULL', '".$text."')"; mysql_query($query) or die ('Error updating database'); echo " $myString "," $text "; ?> Thanks a lot!

    Read the article

  • MySQL "OR MATCH" hangs (very slow) on multiple tables

    - by Kerry
    After learning how to do MySQL Full-Text search, the recommended solution for multiple tables was OR MATCH and then do the other database call. You can see that in my query below. When I do this, it just gets stuck in a "busy" state, and I can't access the MySQL database. SELECT a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, b.`price`, c.`image`, c.`swatch`, e.`name` AS industry, MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) AS relevance FROM `products` AS a LEFT JOIN `website_products` AS b ON (a.`product_id` = b.`product_id`) LEFT JOIN ( SELECT `product_id`, `image`, `swatch` FROM `product_images` WHERE `sequence` = 0) AS c ON (a.`product_id` = c.`product_id`) LEFT JOIN `brands` AS d ON (a.`brand_id` = d.`brand_id`) INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`) WHERE b.`website_id` = %d AND b.`status` = %d AND b.`active` = %d AND MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) OR MATCH ( d.`name` ) AGAINST ( '%s' IN BOOLEAN MODE ) GROUP BY a.`product_id` ORDER BY relevance DESC LIMIT 0, 9 Any help would be greatly appreciated. EDIT All the tables involved are MyISAM, utf8_general_ci. Here's the EXPLAIN SELECT statement: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 16076 Using temporary; Using filesort 1 PRIMARY b ref product_id product_id 4 database.a.product_id 2 1 PRIMARY e eq_ref PRIMARY PRIMARY 4 database.a.industry_id 1 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 23261 1 PRIMARY d eq_ref PRIMARY PRIMARY 4 database.a.brand_id 1 Using where 2 DERIVED product_images ALL NULL NULL NULL NULL 25933 Using where I don't know how to make that look neater -- sorry about that UPDATE it returns the query after 196 seconds (I think correctly). The query without multiple tables takes about .56 seconds (which I know is really slow, we plan on changing to solr or sphinx soon), but 196 seconds?? If we could add a number to the relevance if it was in the brand name ( d.name ), that would also work

    Read the article

  • Optimizing an embedded SELECT query in mySQL

    - by Crazy Serb
    Ok, here's a query that I am running right now on a table that has 45,000 records and is 65MB in size... and is just about to get bigger and bigger (so I gotta think of the future performance as well here): SELECT count(payment_id) as signup_count, sum(amount) as signup_amount FROM payments p WHERE tm_completed BETWEEN '2009-05-01' AND '2009-05-30' AND completed > 0 AND tm_completed IS NOT NULL AND member_id NOT IN (SELECT p2.member_id FROM payments p2 WHERE p2.completed=1 AND p2.tm_completed < '2009-05-01' AND p2.tm_completed IS NOT NULL GROUP BY p2.member_id) And as you might or might not imagine - it chokes the mysql server to a standstill... What it does is - it simply pulls the number of new users who signed up, have at least one "completed" payment, tm_completed is not empty (as it is only populated for completed payments), and (the embedded Select) that member has never had a "completed" payment before - meaning he's a new member (just because the system does rebills and whatnot, and this is the only way to sort of differentiate between an existing member who just got rebilled and a new member who got billed for the first time). Now, is there any possible way to optimize this query to use less resources or something, and to stop taking my mysql resources down on their knees...? Am I missing any info to clarify this any further? Let me know... EDIT: Here are the indexes already on that table: PRIMARY PRIMARY 46757 payment_id member_id INDEX 23378 member_id payer_id INDEX 11689 payer_id coupon_id INDEX 1 coupon_id tm_added INDEX 46757 tm_added, product_id tm_completed INDEX 46757 tm_completed, product_id

    Read the article

  • Creating a Mysql view to SELECT coloumns from different tables

    - by user330429
    I need help in constructing a VIEW on 4 tables. The view should contain coloumns: ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME ALIASES EMP_REPORT ER , EMP_BIO EB, CUST_RECORD CR, CUST_LOC CL, DEPT_ID DI THE DATA MODELS ARE: describe EMP_REPORT; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | empid | int(11) | NO | | NULL | | | custid | int(11) | NO | | NULL | | | status | varchar(32) | NO | | NULL | | | datereported | bigint(20) | NO | | NULL | | | report | text | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ describe EMP_BIO; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | empid | int(11) | NO | PRI | NULL | | | name | varchar(56) | NO | | NULL | | | sex | char(1) | NO | | NULL | | | deptid | int(11) | NO | | NULL | | | email | varchar(32) | NO | | NULL | | | mobile | bigint(20) | YES | | NULL | | | gtlk | varchar(32) | YES | | NULL | | | skype | varchar(32) | YES | | NULL | | | cvid | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ describe CUST_RECORD; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | custid | int(11) | NO | PRI | NULL | auto_increment | | custname | varchar(32) | NO | | NULL | | | address | varchar(255) | YES | | NULL | | | contactp | varchar(32) | YES | | NULL | | | mobile | bigint(20) | YES | | NULL | | | locid | int(11) | NO | | NULL | | | remarks | text | YES | | NULL | | | date | int(11) | YES | | NULL | | | addedby | int(11) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ describe CUST_LOC; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | locid | int(11) | NO | PRI | 0 | | | locname | varchar(32) | NO | | NULL | | +---------+-------------+------+-----+---------+-------+ describe DEPT_ID; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | deptid | int(11) | NO | | NULL | | | deptname | varchar(32) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ The table EMP_REPORT contains reports submitted by employees, all the coloumns in it needs to be fetched. The empid in this table should be used to fetch corresponding name in EMP_BIO (employee biodata) table. The custid in EMP_REPORT should be used to fetch corresponding locid in CUST_RECORD(customer record) which is used to fetch locname in CUST_LOC(customer location) table. The empid in EMP_REPORT is used to fetch corresponding deptid in EMP_BIO table which is then used to fetch corresponding deptname from DEPT_ID(department id) table. I tried constructing view using union of different select queries, but dint get proper results. Please help me. Thanks in advance. PS: sorry for my poor english

    Read the article

  • DataReader is not returning results from a MySQL Stored Proc

    - by Glenn Slaven
    I have a stored proc in MySQL (5.5) that I'm calling from a C# application (using MySQL.Data v6.4.4.0). I have a bunch of other procs that work fine, but this is not returning any results, the data reader says the result set is empty. The proc does a couple of inserts & an update inside a transaction then selects 2 local variables to return. The inserts & update are happening, but the select is not returning. When I run the proc manually it works, gives a single row with the two fields, but the data reader is empty. This is the proc: CREATE DEFINER=`root`@`localhost` PROCEDURE `File_UpdateFile`(IN siteId INT, IN fileId INT, IN description VARCHAR(100), IN folderId INT, IN fileSize INT, IN filePath VARCHAR(100), IN userId INT) BEGIN START TRANSACTION; SELECT MAX(v.versionNumber) + 1 INTO @versionNumber FROM `file_version` v JOIN `file` f ON (v.fileId = f.fileId) WHERE v.fileId = fileId AND f.siteId = siteId; INSERT INTO `file_version` (fileId, versionNumber, description, fileSize, filePath, uploadedOn, uploadedBy, fileVersionState) VALUES (fileId, @versionNumber, description, fileSize, filePath, NOW(), userId, 0); INSERT INTO filehistory (fileId, `action`, userId, createdOn) VALUES (fileId, 'UPDATE', userId, NOW()); UPDATE `file` f SET f.checkedOutBy = NULL WHERE f.fileId = fileId; COMMIT; SELECT fileId, @versionNumber `versionNumber`; END$$ I'm calling the proc using Dapper, but I've debugged into the SqlMapper class and I can see that the reader is not returning anything.

    Read the article

  • convert SQL Server StoredPorcedure to MySql

    - by karthik
    I need to covert the following SP of SQL Server To MySql. I am new to MySql.. Help needed. CREATE PROC InsertGenerator (@tableName varchar(100)) as --Declare a cursor to retrieve column specific information --for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000) --for storing the first half --of INSERT statement DECLARE @stringData nvarchar(3000) --for storing the data --(VALUES) related statement DECLARE @dataType nvarchar(1000) --data types returned --for respective columns SET @string='INSERT '+@tableName+'(' SET @stringData='' DECLARE @colName nvarchar(50) FETCH NEXT FROM cursCol INTO @colName,@dataType IF @@fetch_status<>0 begin print 'Table '+@tableName+' not found, processing skipped.' close curscol deallocate curscol return END WHILE @@FETCH_STATUS=0 BEGIN IF @dataType in ('varchar','char','nchar','nvarchar') BEGIN SET @stringData=@stringData+'''''''''+ isnull('+@colName+','''')+'''''',''+' END ELSE if @dataType in ('text','ntext') --if the datatype --is text or something else BEGIN SET @stringData=@stringData+'''''''''+ isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' END ELSE IF @dataType = 'money' --because money doesn't get converted --from varchar implicitly BEGIN SET @stringData=@stringData+'''convert(money,''''''+ isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' END ELSE IF @dataType='datetime' BEGIN SET @stringData=@stringData+'''convert(datetime,''''''+ isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+' END ELSE IF @dataType='image' BEGIN SET @stringData=@stringData+'''''''''+ isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+' END ELSE --presuming the data type is int,bit,numeric,decimal BEGIN SET @stringData=@stringData+'''''''''+ isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+' END SET @string=@string+@colName+',' FETCH NEXT FROM cursCol INTO @colName,@dataType END

    Read the article

  • Need help with 2 MySql Queries. Join vs Subqueries.

    - by BugBusterX
    I have 2 tables: user: id, name message: sender_id, receiver_id, message, read_at, created_at There are 2 results I need to retrieve and I'm trying to find the best solution. I have included queries that I'm using in the very end. I need to retrieve a list of users, and also with each user have information available whether there are any unread messages from each user (them as sender, me as receiver) and whether or not there are any read messages between us ( they send I'm receiver or I send they are receivers) I need Same as above, but only those members where there has been any messaging between us, sorted by unread first, then by last message received. Can you advise please? Should this be done with joins or subqueries? In first case I do not need the count, I just need to know whether or not there is at least one unread message. I'm posting code and my current queries, please have a look when you get a chance: BTW, everything is the way I want in fist query. My concern is: In second query I would like to order by messages.created_at, but I dont think I can do that with grouping? And also I dont know if this approach is the most optimized and fast. CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) INSERT INTO `user` VALUES (1,'User 1'),(2,'User 2'),(3,'User 3'),(4,'User 4'),(5,'User 5'); CREATE TABLE `message` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `sender_id` bigint(20) DEFAULT NULL, `receiver_id` bigint(20) DEFAULT NULL, `message` text, `read_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`) ) INSERT INTO `message` VALUES (1,3,1,'Messge',NULL,'2010-10-10 10:10:10'),(2,1,4,'Hey','2010-10-10 10:10:12','2010-10-10 10:10:11'),(3,4,1,'Hello','2010-10-10 10:10:19','2010-10-10 10:10:15'),(4,1,4,'Again','2010-10-10 10:10:25','2010-10-10 10:10:21'),(5,3,1,'Hiii',NULL,'2010-10-10 10:10:21'); SELECT u.*, m_new.id as have_new, m.id as have_any FROM user u LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL) LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1)) GROUP BY u.id SELECT u.*, m_new.id as have_new, m.id as have_any FROM user u LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL) LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1)) where m.id IS NOT NULL GROUP BY u.id

    Read the article

  • Vim: I don't want to insert!!!

    - by bhh1988
    I could not find anything online addressing this problem, which is surprising. The problem is that I find it very easy to accidentally insert stuff in Vim. I know I can undo by with 'u', but it still is quite annoying and frequent. Often, I enter a command like 'sp file.txt' without realizing that I haven't entered the ':' character yet (so I'm not yet on the command line). Unfortunately, there are several characters that take you to insert mode, including 's', 'a', 'i', 'o'. I'd rather have insert mode mapped to just one keybinding which is very deliberate, like shift-space. Can anyone point me to something that might have what I'm looking for? Thanks.

    Read the article

  • MYSQL: Limit Word Length for MySql Insert

    - by elmaso
    Hi, every search query is saved in my database, but I want to Limit the Chracterlength for one single word: odisafuoiwerjsdkle -- length too much -- dont write in the database my actually code is: $search = $_GET['q']; if (!($sql = mysql_query ('' . 'SELECT * FROM `history` WHERE `Query`=\'' . $search . '\''))) { exit ('<b>SQL ERROR:</b> 102, Cannot write history.'); ; } while ($row = mysql_fetch_array ($sql)) { $ID = '' . $row['ID']; } if ($ID == '') { mysql_query ('' . 'INSERT INTO history (Query) values (\'' . $search . '\')'); } if (!($sql = mysql_query ('SELECT * FROM `history` ORDER BY `ID` ASC LIMIT 1'))) { exit ('<b>SQL ERROR:</b> 102, Cannot write history.'); ; } while ($row = mysql_fetch_array ($sql)) { $first_id = '' . $row['ID']; } if (!($sql = mysql_query ('SELECT * FROM `history`'))) { exit ('<b>SQL ERROR:</b> 102, Cannot write history.'); ; }

    Read the article

  • Searching phpbb's 'topic_title' via MYSQL php, but exact match doesn't work

    - by Mint
    $sql = sprintf("SELECT topic_title FROM `phpbb_topics` WHERE `topic_title` LIKE '%%%s%%' LIMIT 20", mysql_real_escape_string('match this title')); Which I run this query in phpMyAdmin the results are: (correct) match this title match this title 002 But when I run that same MYSQL query in PHP I get: (incorrect) match this title 002 I have also tried MATCH AGAINST with the same result with both php and phpMyAdmin: $sql = "SELECT topic_title FROM phpbb_topics WHERE MATCH (topic_title) AGAINST('match this title' IN BOOLEAN MODE)"; Whats going on? I'v been searching all over the place and have found next to no help :(

    Read the article

  • Relay Access Denied (State 13) Postfix + Dovecot + Mysql

    - by Pierre Jeptha
    So we have been scratching our heads for quite some time over this relay issue that has presented itself since we re-built our mail-server after a failed Webmin update. We are running Debian Karmic with postfix 2.6.5 and Dovecot 1.1.11, sourcing from a Mysql database and authenticating with SASL2 and PAM. Here are the symptoms of our problem: 1) When users are on our local network they can send and receive 100% perfectly fine. 2) When users are off our local network and try to send to domains not of this mail server (ie. gmail) they get the "Relay Access Denied" error. However users can send to domains of this mail server when off the local network fine. 3) We host several virtual domains on this mailserver, the primary domain being airnet.ca. The rest of our virtual domains (ex. jeptha.ca) cannot receive email from domains not hosted by this mailserver (ie. gmail and such cannot send to them). They receive bounce backs of "Relay Access Denied (State 13)". This is regardless of whether they are on our local network or not, which is why it is so urgent for us to get this solved. Here is our main.cf from postfix: myhostname = mail.airnet.ca mydomain = airnet.ca smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu) biff = no smtpd_sasl_type = dovecot queue_directory = /var/spool/postfix smtpd_sasl_path = private/auth smtpd_sender_restrictions = permit_mynetworks permit_sasl_authenticated smtp_sasl_auth_enable = yes smtpd_sasl_auth_enable = yes append_dot_mydomain = no readme_directory = no smtp_tls_security_level = may smtpd_tls_security_level = may smtp_tls_note_starttls_offer = yes smtpd_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key smtpd_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem smtpd_tls_loglevel = 1 smtpd_tls_received_header = yes smtpd_tls_auth_only = no alias_maps = proxy:mysql:/etc/postfix/mysql/alias.cf hash:/etc/aliases alias_database = hash:/etc/aliases mydestination = mail.airnet.ca, airnet.ca, localhost.$mydomain mailbox_command = procmail -a "$EXTENSION" mailbox_size_limit = 0 recipient_delimiter = + local_recipient_maps = $alias_maps $virtual_mailbox_maps proxy:unix:passwd.byname home_mailbox = /var/virtual/ mail_spool_directory = /var/spool/mail mailbox_transport = maildrop smtpd_helo_required = yes disable_vrfy_command = yes smtpd_etrn_restrictions = reject smtpd_data_restrictions = reject_unauth_pipelining, permit show_user_unknown_table_name = no proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps $virtual_uid_maps $virtual_gid_maps virtual_alias_domains = message_size_limit = 20971520 transport_maps = proxy:mysql:/etc/postfix/mysql/vdomain.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql/vmailbox.cf virtual_alias_maps = proxy:mysql:/etc/postfix/mysql/alias.cf hash:/etc/mailman/aliases virtual_uid_maps = proxy:mysql:/etc/postfix/mysql/vuid.cf virtual_gid_maps = proxy:mysql:/etc/postfix/mysql/vgid.cf virtual_mailbox_base = / virtual_mailbox_limit = 209715200 virtual_mailbox_extended = yes virtual_create_maildirsize = yes virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql/vmlimit.cf virtual_mailbox_limit_override = yes virtual_mailbox_limit_inbox = no virtual_overquote_bounce = yes virtual_minimum_uid = 1 maximal_queue_lifetime = 1d bounce_queue_lifetime = 4h delay_warning_time = 1h append_dot_mydomain = no qmgr_message_active_limit = 500 broken_sasl_auth_clients = yes smtpd_sasl_path = private/auth smtpd_sasl_local_domain = $myhostname smtpd_sasl_security_options = noanonymous smtpd_sasl_authenticated_header = yes smtp_bind_address = 142.46.193.6 relay_domains = $mydestination mynetworks = 127.0.0.0, 142.46.193.0/25 inet_interfaces = all inet_protocols = all And here is the master.cf from postfix: # ========================================================================== # service type private unpriv chroot wakeup maxproc command + args # (yes) (yes) (yes) (never) (100) # ========================================================================== smtp inet n - - - - smtpd #submission inet n - - - - smtpd # -o smtpd_tls_security_level=encrypt # -o smtpd_sasl_auth_enable=yes # -o smtpd_client_restrictions=permit_sasl_authenticated,reject # -o milter_macro_daemon_name=ORIGINATING #smtps inet n - - - - smtpd # -o smtpd_tls_wrappermode=yes # -o smtpd_sasl_auth_enable=yes # -o smtpd_client_restrictions=permit_sasl_authenticated,reject # -o milter_macro_daemon_name=ORIGINATING #628 inet n - - - - qmqpd pickup fifo n - - 60 1 pickup cleanup unix n - - - 0 cleanup qmgr fifo n - n 300 1 qmgr #qmgr fifo n - - 300 1 oqmgr tlsmgr unix - - - 1000? 1 tlsmgr rewrite unix - - - - - trivial-rewrite bounce unix - - - - 0 bounce defer unix - - - - 0 bounce trace unix - - - - 0 bounce verify unix - - - - 1 verify flush unix n - - 1000? 0 flush proxymap unix - - n - - proxymap proxywrite unix - - n - 1 proxymap smtp unix - - - - - smtp # When relaying mail as backup MX, disable fallback_relay to avoid MX loops relay unix - - - - - smtp -o smtp_fallback_relay= # -o smtp_helo_timeout=5 -o smtp_connect_timeout=5 showq unix n - - - - showq error unix - - - - - error retry unix - - - - - error discard unix - - - - - discard local unix - n n - - local virtual unix - n n - - virtual lmtp unix - - - - - lmtp anvil unix - - - - 1 anvil scache unix - - - - 1 scache maildrop unix - n n - - pipe flags=DRhu user=vmail argv=/usr/bin/maildrop -d ${recipient} # # See the Postfix UUCP_README file for configuration details. # uucp unix - n n - - pipe flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient) # # Other external delivery methods. # ifmail unix - n n - - pipe flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient) bsmtp unix - n n - - pipe flags=Fq. user=bsmtp argv=/usr/lib/bsmtp/bsmtp -t$nexthop -f$sender $recipient scalemail-backend unix - n n - 2 pipe flags=R user=scalemail argv=/usr/lib/scalemail/bin/scalemail-store ${nexthop} ${user} ${extension} mailman unix - n n - - pipe flags=FR user=list argv=/usr/lib/mailman/bin/postfix-to-mailman.py ${nexthop} ${user} spfpolicy unix - n n - - spawn user=nobody argv=/usr/bin/perl /usr/sbin/postfix-policyd-spf-perl smtp-amavis unix - - n - 4 smtp -o smtp_data_done_timeout=1200 -o smtp_send_xforward_command=yes -o disable_dns_lookups=yes #127.0.0.1:10025 inet n - n - - smtpd dovecot unix - n n - - pipe flags=DRhu user=dovecot:21pever1lcha0s argv=/usr/lib/dovecot/deliver -d ${recipient Here is Dovecot.conf protocols = imap imaps pop3 pop3s disable_plaintext_auth = no log_path = /etc/dovecot/logs/err info_log_path = /etc/dovecot/logs/info log_timestamp = "%Y-%m-%d %H:%M:%S ". syslog_facility = mail ssl_listen = 142.46.193.6 ssl_disable = no ssl_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem ssl_key_file = /etc/ssl/private/ssl-cert-snakeoil.key mail_location = mbox:~/mail:INBOX=/var/virtual/%d/mail/%u mail_privileged_group = mail mail_debug = yes protocol imap { login_executable = /usr/lib/dovecot/imap-login mail_executable = /usr/lib/dovecot/rawlog /usr/lib/dovecot/imap mail_executable = /usr/lib/dovecot/gdbhelper /usr/lib/dovecot/imap mail_executable = /usr/lib/dovecot/imap imap_max_line_length = 65536 mail_max_userip_connections = 20 mail_plugin_dir = /usr/lib/dovecot/modules/imap login_greeting_capability = yes } protocol pop3 { login_executable = /usr/lib/dovecot/pop3-login mail_executable = /usr/lib/dovecot/pop3 pop3_enable_last = no pop3_uidl_format = %08Xu%08Xv mail_max_userip_connections = 10 mail_plugin_dir = /usr/lib/dovecot/modules/pop3 } protocol managesieve { sieve=~/.dovecot.sieve sieve_storage=~/sieve } mail_plugin_dir = /usr/lib/dovecot/modules/lda auth_executable = /usr/lib/dovecot/dovecot-auth auth_process_size = 256 auth_cache_ttl = 3600 auth_cache_negative_ttl = 3600 auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@ auth_verbose = yes auth_debug = yes auth_debug_passwords = yes auth_worker_max_count = 60 auth_failure_delay = 2 auth default { mechanisms = plain login passdb sql { args = /etc/dovecot/dovecot-sql.conf } userdb sql { args = /etc/dovecot/dovecot-sql.conf } socket listen { client { path = /var/spool/postfix/private/auth mode = 0660 user = postfix group = postfix } master { path = /var/run/dovecot/auth-master mode = 0600 } } } Please, if you require anything do not hesistate, I will post it ASAP. Any help or suggestions are greatly appreciated! Thanks, Pierre

    Read the article

  • Select random row from MySQL (with probability)

    - by James Simpson
    I have a MySQL table that has a row called cur_odds which is a percent number with the percent probability that that row will get selected. How do I make a query that will actually select the rows in approximately that frequency when you run through 100 queries for example? I tried the following, but a row that has a probability of 0.35 ends up getting selected around 60-70% of the time. SELECT * FROM table ORDER BY RAND()*cur_odds DESC

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >