Search Results

Search found 23901 results on 957 pages for 'mysql stored procedure'.

Page 238/957 | < Previous Page | 234 235 236 237 238 239 240 241 242 243 244 245  | Next Page >

• Hibernate : Foreign key constraint violation problem

  - by Vinze

  I have a com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException in my code (using Hibernate and Spring) and I can't figure why. My entities are Corpus and Semspace and there's a many-to-one relation from Semspace to Corpus as defined in my hibernate mapping configuration : <class name="xxx.entities.Semspace" table="Semspace" lazy="false" batch-size="30"> <id name="id" column="idSemspace" type="java.lang.Integer" unsaved-value="null"> <generator class="identity"/> </id> <property name="name" column="name" type="java.lang.String" not-null="true" unique="true" /> <many-to-one name="corpus" class="xxx.entities.Corpus" column="idCorpus" insert="false" update="false" /> [...] </class> <class name="xxx.entities.Corpus" table="Corpus" lazy="false" batch-size="30"> <id name="id" column="idCorpus" type="java.lang.Integer" unsaved-value="null"> <generator class="identity"/> </id> <property name="name" column="name" type="java.lang.String" not-null="true" unique="true" /> </class> And the Java code generating the exception is : Corpus corpus = Spring.getCorpusDAO().getCorpusById(corpusId); Semspace semspace = new Semspace(); semspace.setCorpus(corpus); semspace.setName(name); Spring.getSemspaceDAO().save(semspace); I checked and the corpus variable is not null (so it is in database as retrieved with the DAO) The full exception is : com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`xxx/Semspace`, CONSTRAINT `FK4D6019AB6556109` FOREIGN KEY (`idCorpus`) REFERENCES `Corpus` (`idCorpus`)) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715) at com.mysql.jdbc.Connection.execSQL(Connection.java:3249) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102) at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:73) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2158) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2638) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70) at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519) at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:642) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373) at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:639) at xxx.dao.impl.AbstractDAO.save(AbstractDAO.java:26) at org.apache.jsp.functions.semspaceManagement_jsp._jspService(semspaceManagement_jsp.java:218) [...] The foreign key constraint has been created (and added to database) by hibernate and I don't see where the constraint can be violated. The table are innodb and I tried to drop all tables and recreate it the problem remains... EDIT : Well I think I have a start of answer... I change the log level of hibernate to DEBUG and before it crash I have the following log insert into Semspace (name, [...]) values (?, [...]) So it looks like it does not try to insert the idCorpus and as it is not null it uses the default value "0" which does not refers to an existing entry in Corpus table...

  Read the article

• Can I indicate where my MySQL parameter should go more meaningfully than just having a ? to mark the

  - by Paul H

  I've got a chunk of code where I can pass info into a MySQL command using parameters through an ODBC connection. Example code showing surname passed in using string surnameToLookFor: using (OdbcConnection DbConn = new OdbcConnection( connectToDB )) { OdbcDataAdapter cmd = new OdbcDataAdapter( "SELECT Firstname, Address, Postcode FROM customers WHERE Surname = ?", DbConn); OdbcParameter odbcParam = new OdbcParameter("surname", surnameToLookFor); cmd.SelectCommand.Parameters.Add(odbcParam); cmd.Fill(dsCustomers, "customers"); } What I'd like to know is whether I can indicate where my parameter should go more meaningfully than just having a ? to mark the position - as I could see this getting quite hard to debug if there are multiple parameters being replaced. I'd like to provide a name to the parameter in a manner something like this: SELECT Firstname, Address, Postcode FROM customers WHERE Surname = ?surname ", When I try this it just chokes. The following code public System.Data.DataSet Customer_Open(string sConnString, long ld) { using (MySqlConnection oConn = new MySqlConnection(sConnString)) { oConn.Open(); MySqlCommand oCommand = oConn.CreateCommand(); oCommand.CommandText = "select * from cust_customer where id=?id"; MySqlParameter oParam = oCommand.Parameters.Add("?id", MySqlDbType.Int32); oParam.Value = ld; oCommand.Connection = oConn; DataSet oDataSet = new DataSet(); MySqlDataAdapter oAdapter = new MySqlDataAdapter(); oAdapter.SelectCommand = oCommand; oAdapter.Fill(oDataSet); oConn.Close(); return oDataSet; } } is from http://www.programmingado.net/a-389/MySQL-NET-parameters-in-query.aspx and includes the fragment where id=?id Which would be ideal. Is this only available through the .Net connector rather than the ODBC? If it is possible to do using ODBC how would I need to change my code fragment to enable this?

  Read the article

• How to delete duplicate records in MySQL by retaining those fields with data in the duplicate item b

  - by NJTechGuy

  I have few thousands of records with few 100 fields in a MySQL Table. Some records are duplicates and are marked as such. Now while I can simply delete the dupes, I want to retain any other possible valuable non-null data which is not present in the original version of the record. Hope I made sense. For instance : a b c d e f key dupe -------------------- 1 d c f k l 1 x 2 g h j 1 3 i h u u 2 4 u r t 2 x From the above sample table, the desired output is : a b c d e f key dupe -------------------- 2 g c h k j 1 3 i r h u u 2 If you look at it closely, the duplicate is determined by using the key (it is the same for 2 records, so the one that has an 'x' for dupe field is the one to be deleted by retaining some of the fields from the dupe (like c, e values for key 1). Please let me know if you need more info about this puzzling problem. Thanks a tonne! p.s : If it is not possible using MySQL, a PERL/Python script sample would be awesome! Thanks!

  Read the article

• Why is MySQL with InnoDB doing a table scan when key exists and choosing to examine 70 times more ro

  - by andysk

  Hello, I'm troubleshooting a query performance problem. Here's an expected query plan from explain: mysql> explain select * from table1 where tdcol between '2010-04-13:00:00' and '2010-04-14 03:16'; +----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+ | 1 | SIMPLE | table1 | range | tdcol | tdcol | 8 | NULL | 5437848 | Using where | +----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+ 1 row in set (0.00 sec) That makes sense, since the index named tdcol (KEY tdcol (tdcol)) is used, and about 5M rows should be selected from this query. However, if I query for just one more minute of data, we get this query plan: mysql> explain select * from table1 where tdcol between '2010-04-13 00:00' and '2010-04-14 03:17'; +----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+ | 1 | SIMPLE | table1 | ALL | tdcol | NULL | NULL | NULL | 381601300 | Using where | +----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+ 1 row in set (0.00 sec) The optimizer believes that the scan will be better, but it's over 70x more rows to examine, so I have a hard time believing that the table scan is better. Also, the 'USE KEY tdcol' syntax does not change the query plan. Thanks in advance for any help, and I'm more than happy to provide more info/answer questions.

  Read the article

• Play! Framework 1.2.4 --- C3P0 settings to avoid Communications link failure do to idle time

  - by HelpMeStackOverflowMyOnlyHope

  I'm trying to customize my C3P0 settings to avoid the error shown at the bottom of this post. It was suggested at this url --- http://make-it-open.blogspot.com/2008/12/sql-error-0-sqlstate-08s01.html --- to adjust the settings as follows: In hibernate.cfg.xml, write <property name="c3p0.min_size">5</property> <property name="c3p0.max_size">20</property> <property name="c3p0.timeout">1800</property> <property name="c3p0.max_statements">50</property> Then create "c3p0.properties" in your root classpath folder and write c3p0.testConnectionOnCheckout=true c3p0.acquireRetryDelay=1000 c3p0.acquireRetryAttempts=1 I've tried to make those adjustments following the direction of the Play! Framework documentation, where they say use "db.pool..." as follows: db.pool.timeout=1800 db.pool.maxSize=15 db.pool.minSize=5 db.pool.initialSize=5 db.pool.acquireRetryAttempts=1 db.pool.preferredTestQuery=SELECT 1 db.pool.testConnectionOnCheckout=true db.pool.acquireRetryDelay=1000 db.pool.maxStatements=50 Are those settings not going to work? Should I be trying to set them in a different way? With those settings I still get the error shown below, that is due to to long of a idle time. Complete Stack Trace of Error: 23:00:44,932 WARN ~ SQL Error: 0, SQLState: 08S01 2012-04-13T23:00:44+00:00 app[web.1]: 23:00:44,932 ERROR ~ Communications link failure 2012-04-13T23:00:44+00:00 app[web.1]: 2012-04-13T23:00:44+00:00 app[web.1]: The last packet successfully received from the server was 274,847 milliseconds ago. The last packet sent successfully to the server was 7 milliseconds ago. 2012-04-13T23:00:44+00:00 app[web.1]: 23:00:44,934 ERROR ~ Why the driver complains here? 2012-04-13T23:00:44+00:00 app[web.1]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver. 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.Util.getInstance(Util.java:382) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1213) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.ConnectionImpl.getMutex(ConnectionImpl.java:3101) 2012-04-13T23:00:44+00:00 app[web.1]: at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4975) 2012-04-13T23:00:44+00:00 app[web.1]: at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74) 2012-04-13T23:00:44+00:00 app[web.1]: at $Proxy49.setAutoCommit(Unknown Source) 2012-04-13T23:00:44+00:00 app[web.1]: at play.db.jpa.JPAPlugin.closeTx(JPAPlugin.java:368) 2012-04-13T23:00:44+00:00 app[web.1]: at play.db.jpa.JPAPlugin.onInvocationException(JPAPlugin.java:328) 2012-04-13T23:00:44+00:00 app[web.1]: at play.plugins.PluginCollection.onInvocationException(PluginCollection.java:447) 2012-04-13T23:00:44+00:00 app[web.1]: at play.Invoker$Invocation.onException(Invoker.java:240) 2012-04-13T23:00:44+00:00 app[web.1]: at play.jobs.Job.onException(Job.java:124) 2012-04-13T23:00:44+00:00 app[web.1]: at play.jobs.Job.call(Job.java:163) 2012-04-13T23:00:44+00:00 app[web.1]: at play.jobs.Job$1.call(Job.java:66) 2012-04-13T23:00:44+00:00 app[web.1]: at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) 2012-04-13T23:00:44+00:00 app[web.1]: at java.util.concurrent.FutureTask.run(FutureTask.java:166) 2012-04-13T23:00:44+00:00 app[web.1]: at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:165) 2012-04-13T23:00:44+00:00 app[web.1]: at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:266) 2012-04-13T23:00:44+00:00 app[web.1]: at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) 2012-04-13T23:00:44+00:00 app[web.1]: at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) 2012-04-13T23:00:44+00:00 app[web.1]: at java.lang.Thread.run(Thread.java:636) 2012-04-13T23:00:44+00:00 app[web.1]: Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

  Read the article

• How to do this Python / MySQL manipulation (match) more efficiently?

  - by NJTechie

  Following is my data : Company Table : ID Company Address City State Zip Phone 1 ABC 123 Oak St Philly PA 17542 7329878901 2 CDE 111 Joe St Newark NJ 08654 3 GHI 211 Foe St Brick NJ 07740 7321178901 4 JAK 777 Wall Ocean NJ 07764 7322278901 5 KLE 87 Ilk St Plains NY 07654 7376578901 6 AB 1 W.House SField PA 87656 7329878901 Branch Office Table : ID Address City State Zip Phone 1 323 Alk St Philly PA 17542 7329832221 1 171 Joe St Newark NJ 08654 3 287 Foe St Brick NJ 07740 7321178901 3 700 Wall Ocean NJ 07764 7322278901 1 89 Blk St Surrey NY 07154 7376222901 File to be Matched (In MySQL): ID Company Address City State Zip Phone 1 ABC 123 Oak St Philly PA 17542 7329878901 2 AB 171 Joe St Newark NJ 08654 3 GHI 211 Foe St Brick NJ 07740 7321178901 4 JAK 777 Wall Ocean NJ 07764 7322278901 5 K 87 Ilk St Plains NY 07654 7376578901 Resulting File : ID Company Address City State Zip Phone appendedID 1 ABC 123 Oak St Philly PA 17542 7329878901 [Original record, field always empty] 1 ABC 171 Joe St Newark NJ 08654 1 [Company Table] 1 ABC 323 Alk St Philly PA 17542 7329832221 1 [Branch Office Table] 1 AB 1 W.House SField PA 87656 7329878901 6 [Partial firm and State, Zip match] 2 CDE 111 Joe St Newark NJ 08654 3 GHI 211 Foe St Brick NJ 07740 7321178901 3 GHI 700 Wall Ocean NJ 07764 7322278901 3 3 GHI 287 Foe St Brick NJ 07740 7321178901 3 4 JAK 777 Wall Ocean NJ 07764 7322278901 5 KLE 87 Ilk St Surrey NY 07654 7376578901 5 KLE 89 Blk St Surrey NY 07154 7376222901 5 Requirement : 1) I have to match each firm on the 'File to be Matched' to that of Company and Branch Office tables (MySQL). 2) If there are multiple exact/partial matches, then the ID from Company, Branch Office table is inserted as a new row in the resulting file. 3) Not all the firms will be matched perfectly, in that case I have to match on partial Company names (like 5/8th of the company name) and any of the address fields and insert them in the resulting file. Please help me out in the most efficient solution for this problem.

  Read the article

• Why would a script not like using MySQLi but is fine with MySQL?

  - by Taylor

  I'm having some issues using mysqli to execute a script with SELECT,DELETE,INSERT and UPDATE querys. They work when using norm mysql such as mysql_connect but im getting strange results when using mysqli. It works fine with a lot of the SELECT querys in other scripts but when it comes to some admin stuff it messes up. Its difficult to explain without attaching the whole script. This is the function for modifying... function database_queryModify($sql,&$insertId) { global $databaseServer; global $databaseName; global $databaseUsername; global $databasePassword; global $databaseDebugMode; $link = @mysql_connect($databaseServer,$databaseUsername,$databasePassword); @mysql_select_db($databaseName,$link); $result = mysql_query($sql,$link); if (!$result && $databaseDebugMode) { print "[".$sql."][".mysql_error()."]"; } $insertId = mysql_insert_id(); return mysql_affected_rows(); } and heres what I changed it to for mysqli function database_queryModify($sql,&$insertId) { global $databaseServer; global $databaseName; global $dbUser_feedadmin; global $dbUser_feedadmin_pw; global $databaseDebugMode; $link = @mysqli_connect($databaseServer,$dbUser_feedadmin,$dbUser_feedadmin_pw,$databaseName); $result = mysqli_query($link, $sql); if (!$result && $databaseDebugMode) { print "[".$sql."][".mysqli_error()."]"; } $insertId = mysqli_insert_id(); return mysqli_affected_rows(); } Does that look right? It isn't actually producing an error but its not functioning in the same way as when using mysql. any ideas?

  Read the article

• How do I bring forward the SELECTED option in PHP from MySQL?

  - by Derek

  Hi all, In my update form, I want the fields to recall the values that are already stored. This is very simple in a text field, but for my drop down () I'm having trouble with PHP reading the already stored name of user. Here is my query and code: $sql = "SELECT users.user_id, users.name FROM users"; $result = mysql_query($sql, $connection) or die ("Couldn't perform query $sql <br />".mysql_error()); $row = mysql_fetch_array($result);?> <label>Designated Person:</label> <select name="name" id="name"> <option value="<?php echo $row['user_id']?>" SELECTED><?php echo $row['name']?> - Current</option> <?php while($row = mysql_fetch_array($result)) { ?> <option value="<?php echo $row['user_id']; if (isset($_POST['user_id']));?>"><?php echo $row['fullname']?></option> <?php } ?> The result of this displays all of the users (as required) and lets me select a user then perform the change successfully...however the 'SELECTED' is always the first one in my database and never the user that was selected when my activity was added :( !!!

  Read the article

• Grabbing rows from MySql where current date is in between start date and end date (Check if current date lies between start date and end date)

  - by Jordan Parker

  I'm trying to select from the database to get the "campaigns" that dates fall into the month. So far i've been successful in grabbing rows that starts or ends inside the current month. What I need to do now is select rows that start in one month and ends a few months down the line ( EG: It's the 3rd month in the year, and there's a "campaign" that runs from the 1st month until the 5th. other example There is a "campaign" that runs from 2012 until 2013 ) I'm hoping there is some way to select via MySql all rows in which a capaign may run. If not should I grab all data in the database and only show the ones that run via the current month. I have already made a function that displays all the days inbetween each date inside an array, which is called "dateRange". I've also created another which shows how many days the campaign runs for called "runTime". Select all (Obviously) $result = mysql_query("SELECT * FROM campaign"); Select Starting This Month $result = mysql_query("SELECT * FROM campaign WHERE YEAR( START ) = YEAR( CURDATE( ) ) AND MONTH( START ) = MONTH( CURDATE( ) )"); Select Ending This Month $result = mysql_query("SELECT * FROM campaign WHERE YEAR( END ) = YEAR( CURDATE( ) ) AND MONTH( END ) = MONTH( CURDATE( ) ) LIMIT 0 , 30"); Code sample while($row = mysql_fetch_array($result)) { $dateArray = dateRange($row['start'], $row['end']); echo "<h3>" . $row['campname'] . "</h3> Start " . $row['start'] . "<br /> End " . $row['end']; echo runTime($row['start'], $row['end']); print_r($dateArray); } In regards to the dates, MySql database only holds start date and end date of the campaign.

  Read the article

• mysql query help, take total sum from a table, and based on discount value on another table calcula

  - by vegatron

  hi I have a table called invoices: CREATE TABLE IF NOT EXISTS `si_invoices` ( `id` int(10) NOT NULL AUTO_INCREMENT, `biller_id` int(10) NOT NULL DEFAULT '0', `customer_id` int(10) NOT NULL DEFAULT '0', `type_id` int(10) NOT NULL DEFAULT '0', `inv_tax_id` int(10) NOT NULL, `date` date NOT NULL DEFAULT '0000-00-00', `unreg_customer` tinyint(1) NOT NULL DEFAULT '0', `discount` decimal(10,2) NOT NULL DEFAULT '0.00', `discount_type` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=20 ; each invoice has items that are stored in invoice_items table : CREATE TABLE IF NOT EXISTS `si_invoice_items` ( `id` int(10) NOT NULL AUTO_INCREMENT, `invoice_id` int(10) NOT NULL DEFAULT '0', `quantity` int(10) unsigned NOT NULL DEFAULT '0', `product_id` int(10) DEFAULT '0', `warehouse_id` int(10) NOT NULL, `unit_price` decimal(25,2) DEFAULT '0.00', `total` decimal(25,2) DEFAULT '0.00', `description` text, PRIMARY KEY (`id`), KEY `invoice_id` (`invoice_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ; and tax table CREATE TABLE IF NOT EXISTS `si_tax` ( `tax_id` int(11) NOT NULL AUTO_INCREMENT, `tax_description` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `tax_percentage` decimal(25,6) DEFAULT '0.000000', `type` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, `tax_enabled` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1', PRIMARY KEY (`tax_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ; here is what I want to do step 1: get the sum_total of the invoice Items for a speciefic invoice step 2: calculate the discount, in the invoice table I have a discount_type field : if its equal to 0 , then there will be no discount if its equal to 1 , the discount value will be stored in the discount field if its equal to 2 , the discount is a percentage of sum_total step 3: calculate the taxes based on inv_tax_id based on the tax id , I will look in the tax table , get the tax_percentage and multiply it by the (sum_total - discount) in short here is the equation $gross_total = $sum_total - $disount + taxes

  Read the article

• Why am I returning empty records when querying in mysql with php?

  - by Brian Bolton

  I created the following script to query a table and return the first 30 results. The query returns 30 results, but they do not have any text or information. Why would this be? The table stores Vietnamese characters. The database is mysql4. Here's the page: http://saomaidanang.com/recentposts.php Here's the code: <?php header( 'Content-Type: text/html; charset=utf-8' ); //CONNECTION INFO $dbms = 'mysql'; $dbhost = 'xxxxx'; $dbname = 'xxxxxxx'; $dbuser = 'xxxxxxx'; $dbpasswd = 'xxxxxxxxxxxx'; $conn = mysql_connect($dbhost, $dbuser, $dbpasswd ) or die('Error connecting to mysql'); mysql_select_db($dbname , $conn); //QUERY $result = mysql_query("SET NAMES utf8"); $cmd = 'SELECT * FROM `phpbb_posts_text` ORDER BY `phpbb_posts_text`.`post_subject` DESC LIMIT 0, 30 '; $result = mysql_query($cmd); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html dir="ltr"> <head> <title>recent posts</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body> <p> <?php //DISPLAY while ($myrow = mysql_fetch_row($result)) { echo 'post subject:'; echo(utf8_encode($myrow ['post_subject'])); echo 'post text:'; echo(utf8_encode($myrow ['post_text'])); } ?> </p> </body>

  Read the article

• What is Wordpress doing for content encoding in it's mysql database?

  - by qbxk

  For some convoluted reasons best left behind us, I require direct access the contents of a wordpress database. I'm using mysql 5.0.70-r1 on gentoo with wordpress 2.6, and perl 5.8.8 ftr. So, sometimes we get high-order characters in the blog, we have quite a few authors contributing too, for the most part these characters end up in wp's database in wp_posts.post_content or wp_postmeta.meta_value, Wordpress is displaying these correctly on it's site, but the database stores it using single byte encoding that I can't figure out how to convert to the correct string. Today's example: the blog shows this, and doesn't even seem to escape any chars in the html, Hãhãhães but the database, when viewed via the mysql prompt, has, Hãhãhães So clearly this is some kind of double-byte encoding issue, but I don't know how I can correct it. I need to be able to pull that second string from the database (b/c that's what it gives me) and convert it to the first one, and i need to do so using perl. also, just to help unmuddy any waters, I took these strings and printed out the ascii codes for each character using perl's ord() function. Here is the output of the "wrong" string H = 72 à = 195 £ = 163 h = 104 à = 195 £ = 163 h = 104 à = 195 £ = 163 e = 101 s = 115 This is the correct string, that I need to produce in my script H = 72 ã = 227 h = 104 ã = 227 h = 104 ã = 227 e = 101 s = 115

  Read the article

• MySql ODBC connection in VB6 on WinXP VERY slow. Other machines on same network are fast.

  - by Matthew

  Hi All, I have a VB6 application that has been performing very well. Recently, we upgraded our server to a Windows 2003 server. Migration of the databases and shares went well and we experienced no problems. Except one. And it has happened at multiple sites. I use the MySQL ODBC 5.1 connector to point to my MySQL database. On identical machines (as far as I can tell, they are client machines not ours), access to the DB is lightning fast on all but one computer. They use the same software and have the same connection strings. And I'm sure it's not the program, but the ODBC connection. When I press the 'Test Connection' button in the ODBC connection string window, it can take up to 10 seconds on the poorly performing machine to respond with a success. All the other computers are instantaneous. I have tried using ip address versus the machine name in the UDL, no change. I enabled option 256, which sped it up initially, but it's slow again. Most of the time on a restart the program will be fast for an hour or so then go slow again with the option 256 enabled. Frankly, I am out of ideas and willing to entertain any and all ideas or suggestions. This is getting pretty frustrating. Anyone ever experience anything like this?

  Read the article

• MySQL running on an EC2 m1.small instance has high load but low memory usage, possible resolutions?

  - by Tosh

  I have a MySQL server 5.0.75 Ubuntu, on an m1.small instance running on Amazon's EC2 as part of an application. During peak usage the server load will rise very high, while the memory usage stays low and the application server is no longer responsive since it's waiting for query results. The application server has only 5-8 apache processes running (mod_perl processes). The data directory uses only 140MB of data so the MyIsam tables aren't very big. The queries are pretty complicated with some big joins being performed, and the application makes a lot of queries. mysqltuner reports everything OK except "Maximum possible memory usage: 1.7G (99% of installed RAM)" but I'm nowhere close to using that. My question is, where should I be looking to fix this? Is this something that can be tuned away, or do I just need a larger instance/server? Googling indicates either or also upgrading MySQL server. Any pointers in the right direction would be greatly appreciated, thanks! EDIT: I just discovered this in my slow queries log: # Time: 101116 11:17:00 # User@Host: user[pass] @ [host] # Query_time: 4063 Lock_time: 1035 Rows_sent: 0 Rows_examined: 19960174 SELECT * FROM contacts WHERE contacts.contact_id IN (SELECT external_id FROM contact_relations WHERE external_table = 'contacts' AND contact_id IN (SELECT contact_id FROM contacts WHERE (company_name like '%%butan%%%' OR country like '%%butan%%%' OR city like '%%butan%%%' OR email1 like '%%butan%%%') AND (company_name is not null and company_name != ''))); Which actually brings up a different but related question: If I have a contact table containing: John Smith,The Fun Factory,555-1212,[email protected] What's the best way to search for that record using "factory" as a search key? Fulltext rarely seems to find items in the middle of a word, for example "actor" should bring up "Factory"

  Read the article

• Anything wrong with this MySQL quert? takes 10 seconds+ to load

  - by user345426

  I have a search that is taking 10 seconds+ to execute! Keep in mind it is also searching over 200,000 products in the database. I posted the explain and MySQL query here. 1 SIMPLE p ref PRIMARY,products_status,prod_prodid_status,product... products_status 1 const 9048 Using where; Using temporary; Using filesort 1 SIMPLE v ref PRIMARY,vendors_id,vendors_vendorid vendors_vendorid 4 rhinomar_rhinomartnew.p.vendors_id 1 1 SIMPLE s ref products_id products_id 4 rhinomar_rhinomartnew.p.products_id 1 1 SIMPLE pd ref PRIMARY,products,prod_desc_prodid_prodname prod_desc_prodid_prodname 4 rhinomar_rhinomartnew.p.products_id 1 1 SIMPLE p2c ref PRIMARY,ptc_catidx PRIMARY 4 rhinomar_rhinomartnew.p.products_id 1 Using where; Using index 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 rhinomar_rhinomartnew.p2c.categories_id 1 Using where MySQL Query: select p.products_id, p.products_image, p.products_price, p.products_weight, p.products_unit_quantity, s.specials_new_products_price, s.status, pd.products_name, pd.products_img_alt from products p left join vendors v ON v.vendors_id = p.vendors_id left join specials s on s.products_id = p.products_id left join products_description pd on pd.products_id = p.products_id left join products_to_categories p2c on p2c.products_id = p.products_id left join categories c on c.categories_id = p2c.categories_id where ( ( pd.products_name like '%apparel%' ) or p2c.categories_id IN (773, 132, 135, 136, 119, 122, 124, 125, 126, 1749, 1753, 1747, 123, 127, 130, 131, 178, 137, 140, 164, 165, 166, 167, 168, 169, 832, 2045 ) or p.products_id = 'apparel' or p.products_model = 'apparel' or CONCAT(v.vendors_prefix, '-') = 'apparel' or CONCAT( v.vendors_prefix, '-', p.products_id ) = 'apparel' ) and p.products_status = '1' and c.categories_status = '1' group by p.products_id order by pd.products_name

  Read the article

• connecting to mysql from excel: ODBC driver does not support the requested properties.

  - by every_answer_gets_a_point

  i am trying to add data to mysql from excel. i am getting the above error on this line: rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic here is my code: Dim oConn As ADODB.Connection Private Sub ConnectDB() Set oConn = New ADODB.Connection oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=employees;" & _ "USER=root;" & _ "PASSWORD=some_pass;" & _ "Option=3" End Sub Function esc(txt As String) esc = Trim(Replace(txt, "'", "\'")) End Function Private Sub InsertData() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset ConnectDB With wsBooks For rowCursor = 2 To 11 strSQL = "INSERT INTO tutorial (author, title, price) " & _ "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _ "'" & esc(.Cells(rowCursor, 2)) & "', " & _ esc(.Cells(rowCursor, 3)) & ")" rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic Next End With End Sub whats wrong with rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic ? why am i getting the odbc error?

  Read the article

• MySQL connection attempt works fine in 5.2.9 but not in 5.3.0 - Help?

  - by Rich

  Hi, I'm having trouble making a secondary MySQL connection (to a separate, external DB) in my code. It works fine in PHP 5.2.9 but fails to connect in PHP 5.3.0. I'm aware of (at least some) of the changes needed to make successful MySQL connections in the newer version of PHP, and have succeeded before, so I'm not sure why it isn't working this time. I already have a db connection open to a local database. This function below is then used to make an additional connection to a separate, remote directory. The included config file simply contains the external database details (host, user, pass and name). I have checked and it is being included correctly. function connectDP() { global $dpConnection; include("secondary_db_config.php); $dpConnection = mysql_connect($dp_dbHost, $dp_dbUser, $dp_dbPass, true) or DIE("ERROR: Unable to connect to Deployment Platform"); mysql_select_db($dp_dbName, $dpConnection) or DIE("ERROR 006: Unable to select Deployment Platform Database"); } I then attempt to make this new connection simply by calling this function externally: connectDP(); But when loading the page (in 5.3.0), I get the message: ERROR: Unable to connect to Deployment Platform I'm using the optional new_link flag boolean as the fourth argument in the mysql_connect() function and it's still not working. I've been wracking my brain this morning trying to figure out why this connection doesn't work (while I've done something very similar elsewhere to a separate second database that does work). Any help would be appreciated. Thanks! Rich

  Read the article

• MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

  - by Aaron

  I'm using the America/New York timezone. In the Fall we "fall back" an hour -- effectively "gaining" one hour at 2am. At the transition point the following happens: it's 01:59:00 -04:00 then 1 minute later it becomes: 01:00:00 -05:00 So if you simply say "1:30am" it's ambiguous as to whether or not you're referring to the first time 1:30 rolls around or the second. I'm trying to save scheduling data to a MySQL database and can't determine how to save the times properly. Here's the problem: "2009-11-01 00:30:00" is stored internally as 2009-11-01 00:30:00 -04:00 "2009-11-01 01:30:00" is stored internally as 2009-11-01 01:30:00 -05:00 This is fine and fairly expected. But how do I save anything to 01:30:00 -04:00? The documentation does not show any support for specifying the offset and, accordingly, when I've tried specifying the offset it's been duly ignored. The only solutions I've thought of involve setting the server to a timezone that doesn't use daylight savings time and doing the necessary transformations in my scripts (I'm using PHP for this). But that doesn't seem like it should be necessary. Many thanks for any suggestions.

  Read the article

• How should I solve this MySql problem (PHP) ? (Beginner)

  - by Camran

  I have several tables in a MySql database. I have a classifieds website, and at the bottom I display the users last visited classifieds. I do this by storing the ID:s of the ads to an array in the cookie. Now, my db is made up like this kindof: Main Table: // Stores global information, ie these fields have to be filled out in every record, never be blank ID Price category Seller Item Table: // Stores descriptive info about whats for sale ID AD_ID (FK) //This is the same as ID in the MAIN TABLE Color Size Mileage etc My problem is that I need to know what category the ad is in, in order to query mysql for the right information I think. So I need two variables, but the cookie only has one (ID) stored. Offcourse I could make two queries, first one just matching the ID to the main_table and fetch the category from the Main_table. Then make the second query and fetch all other info from the right table. Here is an example if the category was Vehicles: SELECT * FROM main_table, vehicles_table, WHERE main_table.id=$id_from_cookie AND main_table.ad_id=vehicles_table.ad_id As you can see above, I need the category to write in what table to check, right? But I think there must be a smarter way, like fetching them in one single query using only one variable (id from cookie)? How should I do this? Understand? Let me know if you need more input... Thanks

  Read the article

• How to limit a user to entering 10 keywords or less using PHP & MySQL?

  - by G4TV

  I'm trying to limit my users to entering at least 10 keywords and was wondering how would I be able to do this using PHP & MySQL with my current Keyword script? Here is the add keywords PHP MySQL code. if (isset($_POST['tag']) && trim($_POST['tag'])!=='') { $tags = explode(",", $_POST['tag']); for ($x = 0; $x < count($tags); $x++){ $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $query1 = "INSERT INTO tags (tag) VALUES ('" . mysqli_real_escape_string($mysqli, strtolower(htmlentities(trim(strip_tags($tags[$x]))))) . "')"; if (!mysqli_query($mysqli, $query1)) { print mysqli_error($mysqli); return; } $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT id FROM tags WHERE tag='" . mysqli_real_escape_string($mysqli, strtolower(htmlentities(trim(strip_tags($tags[$x]))))) . "'"); if (!$dbc) { print mysqli_error($mysqli); } else { while($row = mysqli_fetch_array($dbc)){ $id = $row["id"]; } } $query2 = "INSERT INTO question_tags (tag_id, question_id, user_id, date_created) VALUES ('$id', '$question', '$user', NOW())"; if (!mysqli_query($mysqli, $query2)) { print mysqli_error($mysqli); return; } } }

  Read the article

• How to solve - Illegal mix of collations in mysql?

  - by rocksolid

  Am getting the below error when trying to do a select through a Stored procedure in mysql. Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' Any idea on what might be going wrong here? The collation of the table is latin1_general_ci and that of the column in the where clause is latin1_general_cs Thanks!

  Read the article

• Fixing MySQL Replication

  Continuing last months article on "MySQL Replication Pitfalls," Sean Hull discusses what to do to make your replication setup more resilient.

  Read the article

• Video: Plotting Map Markers Dynamically Using the Google Maps API, jQuery, PHP, and MySQL

  Learn how to add Google Maps markers and store their coordinates in a database using a blend of jQuery, PHP and MySQL.

  Read the article

• How backup and restore Horde manualy

  - by Thomas

  how can I backup and restore my horde sql database The Databse ist located in /var/lib/mysql/horde There are many *.frm files and one db.opt My Server ist broken, so I want to reinstall it. Can i copy this files to a USB Stick, then restore the entire server without horde and then simply copy the files in the same directory? Or habe I do something like "mysqldump" to delete an reinztall the database? Thank you, Thomas

  Read the article

• Report: Novell Linux Appliances Boost Ingres over MySQL

  Novell partners with database vendor Ingres to make it easier to build and deploy software appliances -- but what about MySQL?

  Read the article

< Previous Page | 234 235 236 237 238 239 240 241 242 243 244 245  | Next Page >