Re: Getting data from 2 tables if records have same date!
On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green shawn.l.gr...@oracle.comwrote: On 2/29/2012 5:54 PM, LUCi5R wrote: JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Try this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12 This will give you a list of all customers for a given date and a list of every call they made on that date. If a customer made no calls on a date, then all of the columns for that table will be NULL. If you only want a list of customers and details about the calls on a date then an INNER JOIN is appropriate. If you want to see the full list of customers and any calls on that date use this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 If you only want a list of customers that made any calls on a given date, you can use the EXISTS comparator like this: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12) http://dev.mysql.com/doc/**refman/5.5/en/exists-and-not-** exists-subqueries.htmlhttp://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html It's possible to get you any combination of data you want, we just need you to clarify the relationship you are trying to find and how much data you really want to get back. NOTE: the name of the column date is using a reserved word. You may want to enclose it in backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date literals uses ISO notation. So instead of using 02/28/12 (using double quotes) I expected to see '2012-02-28' (using single quotes) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN Another gem from Shawn. ;-)
Re: credit where due
On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman mdyk...@gmail.com wrote: While we have him online, I think we could all take a moment and be grateful for the contributions of Shawn Green. When I see the Oracle-bashing on this list, I am often reminded that we still have a hard-core MySQL developer who has survived the ride to Sun and again to Oracle who is still providing us with timely expert advice. Please, all of you, think twice before cutting up Oracle for their lack of MySQL support. Shawn has been plying this list forever doling out sound advice and I have never heard him complain as we as we indirectly besmirch him over and and over. Thank you Shawn. -- - michael dykman - mdyk...@gmail.com May the Source be with you. I wholeheartedly agree with Michael about Shawn's contributions to this list, and I have not been bashful about saying so publicly and privately. David
Re: MySQL Enterprise support now at Oracle?
On Thu, Mar 10, 2011 at 5:05 PM, Jim McNeely j...@newcenturydata.com wrote: Shawn Green works for Oracle and has been very helpful, and I am happy to eat a little bit of shoe leather! Thanks Shawn! Jim Check the archives for Shawn's posts. IMNSHO, they are unparalleled in clarity and depth and breadth of useful information. David
Re: GRANT ALL error - newbee
On Wed, Sep 15, 2010 at 12:10 PM, Gary Roach gary719_li...@verizon.netwrote: I'm attempting to set up a Linux Apache Mysql PHP (LAMP) system for the first time. On my internal network (behind firewall) I have a computer (cruncher) that is acting as the web server. Another computer (supercrunch) is being used as the home for Dupal6. I connected to the cruncher system from supercrunch with mysql -u root -h cruncher -p. This seemed to work fine. But, one of the setup statements follows along with the result. I can't find the error. Help! mysql GRANT ALL PRIVILAGES ON *.* TO 'g...@supercrunch' IDENTIFIED BY 'password' WITH GRANT OPTION; ERROR 1064 (42000): 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 'PRIVILAGES ON *.* TO 'g...@supercrunch' IDENTIFIED BY 'qatip' WITH GRANT OPTION' at line 1 Leaving out the quotes makes no difference. Gary R Gary, if that is the actual command, you've misspelled PRIVILEGES. HTH, David
Re: how things get messed up
On Thu, Feb 11, 2010 at 8:56 AM, Martijn Tonies m.ton...@upscene.comwrote: Hello John, About 5 years ago, I was asked to write a php app for my department. The app keeps track of graduate school applicants to my department at the university. The main data elements are the scores each professor gives to each applicant. There are only about 400 applicants each year so even with all the personal data, scores, transcripts, etc for each student, it's not much. for the first 2 years, it was under a meg of data. Well, then the selection committee asked me to add something so that if a student e-mailed the department a document, say a paper he'd written or a photo of himself, or whatever, it could be tacked on to the info they saw about him while grading the applicant. So I said, Well, there is only going to be maybe 10 or 20 of those a year. And even if all 400 applicants submit a PDF of a paper they'd written, it would be only 400 docs. 4,000 after 10 years. Yeah, lets just create a documents table in the database and store them in mysql. For the first 2 years, only 2 students sent in documents to attach to their application. I figured I'd wasted my time. Then the next year, the graduate school changed their web application form to allow students to upload documents. Fine, I said, My worst case scenario has already come true. But, well, this is why you plan for the worst case. Then they started taking letters of recommendation as PDF documents. In fact, they started requiring PDF docs. Each student has 3 to 6 letters of recommendation. All in all, I figure we're at about 100 times as many docs in our database as I originally expected and about 10x my worst case scenario. I should either be fired or shot. Maybe fired *then* shot. Actually, its not as bad as all that. I can pretty easily write a perl script to export the docs to files and access them via a network mounted filesystem. After all, saving myself 5 hours of work 5 years ago is worth what? -- maybe 10hours today? It is amazing how often quick dirty turns out just being dirty in the end. Not sure what the problem is really... What are you running into? I think John is just sharing an experience - a lesson learned if you will. With the same spirit in mind, many projects in my work culture begin with a specification of, Just put up anything so our (internal) users can react to it. Talk about vague. Geesh! However, a senior programmer told me years ago that the life of a programmer is often filled with doing, undoing, and redoing. And not enough appreciation for the work involved. I try to keep that in mind. David
Re: Is anything ever equal to NULL?
On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante.
Re: Is anything ever equal to NULL?
On Mon, Dec 28, 2009 at 5:41 PM, Carsten Pedersen cars...@bitbybit.dkwrote: David Giragosian skrev: On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? ... What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante. Oddly enough, that page fails to mention the = operator for which NULL does indeed equal NULL. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to / Carsten Good pick-up, Carsten. And that's definitely a new concept for me. David -- There is more hunger for love and appreciation in this world than for bread.- Mother Teresa
Re: inserting sets of data
On Sat, Dec 12, 2009 at 9:54 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I have a column defined as a set. How do I insert data into that column? Please give me an example. TIA, Victor Lots of examples here: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html David -- There is more hunger for love and appreciation in this world than for bread.- Mother Teresa
Re: Another Join Problem
On Fri, Oct 2, 2009 at 10:53 AM, Victor Subervi victorsube...@gmail.comwrote: Hi; I get the following error: *SQL query:* SELECT ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; *MySQL said:* #1052 - Column 'ID' in field list is ambiguous Please note the error is about ambiguity. products has an ID field and so does categories. If I run the statement taking out the ID from the select, it runs. So, where is the ambiguity?? TIA, V Just prefix the ID with either table name like products.ID or categories.ID. David
Re: Create Syntax (easy)
On Tue, Sep 29, 2009 at 11:09 AM, Victor Subervi victorsube...@gmail.comwrote:
Hi;
Please give me the syntax below such that I can force the insert statements
to use only selected values (item1, item2, item3):
create table (field SOMETHING_HERE item1 item2 item3,
...
)
TIA,
Victor
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
From: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
David
--
There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa
Re: Resetting MySQL Root Password
On 4/27/09, Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com wrote: Hi All, CentOS 5.3 I installed MySQL Server via yum and started it. I tried entering: mysqladmin -u root password yourrootsqlpassword mysqladmin -h server1.example.com -u root password yourrootsqlpassword But I get: r...@server1 ~]# /usr/bin/mysqladmin -u root -h localhost password mypassword /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' How can I reset this and allow Root access, otherwise nobody has access! Thanks, -Jason You need a -p before the password in your command line. David
Re: A good US Hosting Site?
I would recommend PilotPig http://www.pilotpig.net/ Good service, good support, reasonably priced, and integrity to boot. David
Re: how do I select multiple conditions from 1 table column?
On Wed, Apr 1, 2009 at 1:27 PM, PJ af.gour...@videotron.ca wrote: I am trying to select all books whose authors last names begin with I, J or K. I have 3 tables: book, author and book_author. The following query works with one condition but not with three. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I')); This does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I' LEFT(last_name, 1 ) = 'J' LEFT(last_name, 1 ) = 'K')) ; But this produces irrational results - there are no author names with the last names starting with I, J or K. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')) ; I'm a little lost here. Could somebody explain, please? Maybe the LIKE operator would be sufficient: SELECT * from book WHERE last_name LIKE I% OR last_name LIKE J% OR last_name LIKE K%; David
Re: why is this happening?
On 3/19/09, Jim Lyons jlyons4...@gmail.com wrote: I have a rather odd problem with a replication slave. The slave has been down for a number of hours. Of course, both io and sql threads are stopped - from SHOW SLAVE STATUS: Slave_IO_Running: No Slave_SQL_Running: No However, the relay logs are still being written to. I was under the impression that the slave's io thread was what brought data from the master's bin log to the slave's relay log. With the io thread stopped, the relay logs should stop filling up - right? Mine are definitely filling up. Does anyonee know why the relay logs keep filling up after replication has broken? Thanks -- Jim Lyons Web developer / Database administrator http://www.weblyons.com I noticed a similar occurrence the last time my replication broke. Someone here suggested running show processlist. What does yours show? David
Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
On 1/7/09, Jim Lyons jlyons4...@gmail.com wrote: There are other factors. If a table is completely fixed in size it makes for a faster lookup time since the offset is easier to compute. This is true, at least, for myisam tables. All books on tuning that I have read have said the CHAR makes for more efficient lookup and comparison that VARCHAR. Also, I was told by the instructor at a MySQL class that all VARCHAR columns are converted to CHAR when stored in memory. Can anyone else confirm this? That's my recollection, also, derived from a MySQL class. IIRC, the char length is equal to the longest varchar record in the column. David
Re: Trying to Create a Trigger
On 12/5/08, Lola J. Lee Beno [EMAIL PROTECTED] wrote: I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record end if; end // delimiter ; When I run the set query without the and old.jobpost_id line, it runs correctly. So the syntax problem is elsewhere, but where? I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David
Re: MySQL Server 5.1.30 has been released
On 12/1/08, Daevid Vincent [EMAIL PROTECTED] wrote: Monty Widenius (MySQL co-founder who recently left Sun)... What's the news on this? David
Re: Monty left sun...
Thanks. Just finished the blog, and I think I'm getting the drift. On 12/1/08, Daevid Vincent [EMAIL PROTECTED] wrote: Do a quick google search for Monty Widenius left sun and behold... On Mon, 2008-12-01 at 13:45 -0600, David Giragosian wrote: On 12/1/08, Daevid Vincent [EMAIL PROTECTED] wrote: Monty Widenius (MySQL co-founder who recently left Sun)... What's the news on this? David
Re: Displaying information from table graphically
On 11/21/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I have a PHP application that accesses data from MySQL. There is table called rooms, and table called beds. There is another table called patients. Patients are being placed into beds, and beds are in the rooms. PHP application currently displays all information in textual mode via regular HTML tags. But I would like to have that information displayed in graphical mode instead of textual mode. Is there a way to display this information from the database graphically. Graphic would represent a room, and it would contain beds inside. You would be able to see visually which beds are occupied and which are free by looking at the graphics. User of the system wants pictures instead of text displayed via HTML tables as a list of entries. Anyone knows anything like this? Thanks, Dzenan PHP has the GD library with a fairly extensive set of functions for creating images http://us2.php.net/gd. You can grab data from MySQL and then use the GD functions to create images dynamically. It can be tedious, as you create the image pixel by pixel, but the results are very good. David
Re: What is the user account's password, Why is most of the things in the reference manual does not work as stated?
On Sat, Oct 4, 2008 at 11:33 AM, Varuna Seneviratna [EMAIL PROTECTED] wrote: What is the user account's password.I used the command C:\mysql -u user -p Enter password: ** ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: Y ES) How can I find it? Varuna Making a number of assumptions here... Don't enter a password. Just hit enter. David
Re: MySQL and SAN storage - a request for comments
On Fri, Sep 26, 2008 at 1:54 AM, Aaron Blew [EMAIL PROTECTED] wrote: Hi Michael, Overall and most of the time, SANs are a good thing. They have several advantaged over dedicated directly attached storage arrays: 1.) They're generally a lot smarter about how and when they write and read to the disks. Often they understand what's going on down at the head level, and can leverage that for better performance. 2.) They've generally got a lot more cache than a directly attached array (some systems can have up to 256GB of cache) 3.) They're a lot more reliable than many direct attached arrays. There have been many many hours put into algorithms to detect and predict disk failures by these SAN vendors, and they're designed to keep that data online as much as possible as their reputation rides on their availabity. Hitachi Data Systems (as one example) even offers configurations with a 100% data availability guarantee (so long as the unit has power) 4.) Having all those spindles under one management/virtualization framework makes you a lot more agile with how you can make use of your storage. The MySQL workloads your environment has may not all be striped across all the spindles within the SANs, segregating the workloads. However, using all the spindles available can have advantages in some workloads as well, since not all databases will be hammering down to the spindle all the time. A SAN environment isn't always a trivial thing to operate, but it will save a lot of time over managing 100s of direct attached arrays and can offer performance capabilities way beyond what can be practically achieved by using direct attached storage. -Aaron On Thu, Sep 25, 2008 at 6:38 PM, Michael Dykman [EMAIL PROTECTED] wrote: Hello all, I recent started employment with a company which has a lot of mysql servers (100+ is my best estimate so far) and have all of their database servers, masters and slaves alike, using one of 2 SANs for data storage. They servers are connected to dedicated switches with fibre to to SANs and the SANs themselves seem to be well configured and tuned. However, it seems preposterous to me that all those very busy databases should, by design, have a common bottleneck and share a single point of failure. I am not deeply knowledgeable about SANs or their performance characteristics; my reaction thus far is pretty much intuition but I help can't but picture the simple analogue of single disk or a RAID 10 with synchronized spindles frantically thrashing back and forth to respond to tens of thousands of queries per second. Would anyone care to comment? Is my concern justified or am I merely confused? I can't comment on the details, but I know our large medically based institution uses a SAN, and the transition to it was well thought out and implemented. To my knowledge, client applications have always been the cause of downtime, not bottlenecks on the SAN.
Show Master Status
Hurricane Ike has caused our replication set-up to misbehave. I've stopped the (one and only) application that inserts data into the master, but show master status's 'position' field continues to increment. Have I missed something or is this unexpected behavior? version 5.0.22 on CentOS 5. Thanks, David
Re: Show Master Status
On Wed, Sep 17, 2008 at 12:54 PM, Martin Gainty [EMAIL PROTECTED] wrote: Dave- we havent had a hurricane since up here since 1938..if you want to relocate your servers just give a holler and i'll lend you mine! Martin __ Thanks, Martin. Wish I had a choice... LOL. Up here meaning how far North? David
Re: Show Master Status
On Wed, Sep 17, 2008 at 12:47 PM, [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: Hi, What's in the bin-logs? (mysqlbinlog log) cheers, Doug Doug, We capture time-sensitive data, and after checking all the counts on tables known to accept inserts, I restarted the server. That stopped the incrementing, and resyncing the slave has succeeded. But you've asked the right question without a doubt. I'm sure there'll be a next time... David
Re: Selecting a column with a regular expression applied to it?
On 9/11/08, Ryan Stille [EMAIL PROTECTED] wrote: Thank you for the link Darryle, but I don't think you read through my whole question. I have already read through that page, and I see how to use regex as a condition for a select. What I am interested in is applying a regex to a column as I select it.Selecting a varchar column with all the non-ascii characters removed, for example. -Ryan I'd be curious if you could touch in any way non-printable characters using just SQL queries. A few weeks ago I posted here a question about removing form feed characters from a text column and never received any response. I also did extensive Googling and didn't find any workable solutions using just SQL queries. There were a number of proposed solutions whereby a programming language was used, C/C++ and Perl, IIRC, but I didn't go down that route. FWIW, I did try all manner of using relace() to remove the FF's and nothing worked for me. David
Remove form feed characters from a text field
I've extracted text from approx 1600 pdf files using pdftotext.exe and inserted it into a table. Now I see there are form feed characters in the field, and I would suspect other special characters, also. I'm not having much luck trying to remove them. Any pointers appreciated. Thanks, David
Re: can some please help me -- REPLICATION
On 8/10/08, Jim Lyons [EMAIL PROTECTED] wrote: you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. What's the command or setting for this action? --David.
Re: MySQL Administrator Login Error
On 8/8/08, AndrewMcHorney [EMAIL PROTECTED] wrote: Hello I tried to login using localhost and root as user id with no password since I temporarily did not enter one when I installed the software. I am getting the following error message: MySQL Error Number 1045 Access denied for user '[EMAIL PROTECTED]' (using password:NO) The port select is 3036. What does this error mean? Andrew The default MySQL port is 3306, I believe. Do you have a typo above or have you set MySQL to use a different port? David.
Re: MySql Administrator Tool Login
On 8/7/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello I successfully installed the mysql database on my pc. I also installed the mysql administrator tool. I am trying to login and I need some assistance. It is asking for stored connection, server host, username and password. I am not sure what to put in here. When I installed the database I did not give a password to the administrator question. I do not remember the administrator id. Would someone be so kind as to assist me with getting logged in on this tool? Thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Maybe try username= and password= and host=localhost -- --David.
Re: Why people don't use engine named BDB?
On 7/21/08, Moon's Father [EMAIL PROTECTED] wrote: Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn Maybe something to do with this: *BDB support will be removed. * Note that, as of MySQL 5.1, BDB isn't supported any longer. http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html But you're right that as a storgage engine, there have been very few questions related to it, on this mailing list anyway. -- --David.
Re: unescaping strings with the C api
On 6/12/08, Tim Johnson [EMAIL PROTECTED] wrote: On Thursday 12 June 2008, Warren Young wrote: Tim Johnson wrote: Not sure what you mean by directly usable. I mean directly usable. :) If I do an insert statement with a backslash, for example: headline\one, I will retrieve headline\\one, and that will need to be unescaped, because it is not a true representation of what was submitted by the original insert. My perspective is a little different from yours: as the maintainer of MySQL++ (http://tangentsoft.net/mysql++/), I have never actually used the C API directly. I don't have any pure C sample code here to tweak to try things. Me neither - not any more anyway, since I quit coding in C C++ years ago.. Furthermore, I am also working with a scripting language new to me. My experience is with python - where unescaping is _not_ an issue and rebol, where unescaping _is_ an issue. Python using the API (somewhere buried deep in the MySQLdb modules, and rebol using a direct socket connection on port 3306. Instead, I changed one of the MySQL++ examples to insert a string with a backslash into the DB, and on retrieving the rows, I get a single backslash. In the C++ code, the backslash is doubled due to C/C++ string parsing rules, but that's only one character in the underlying string data. Due to the way this example uses MySQL++, that string gets automatically escaped on DB insertion, so I presume it's sent over the wire as two backslashes, though I haven't verified it. Then when you retrieve rows through MySQL++, it returns a fairly direct copy of the data the C API gives you, with no real translation going on. I'm seeing the same that you are with the language (newlisp) that I am playing with. MySQL++ doesn't have an unescape function, so I don't see why your program would need one. I believe that you are correct. If not a single regex should handle it, and be fairly fast. Thanks for the input. I really appreciate it. Best regards tim(looking at MySQL++) The same is true of mysql_real_escape_string() in PHP. You escape special characters upon update or insertion, but upon viewing the data in the DB or retrieving it programmatically, the data simply appear as it was originally before use of the function. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
