RE: general questions
Or alternatively, (as I read it), use a char(0). Poss. Values NULL or '' (empty string). Kyle -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 11:04 AM To: [EMAIL PROTECTED] Subject: Re: general questions Actually, the ideal type for boolean operations is TINYINT(1), which, according to the MySQL Column Types page, is the equivalent of a BOOL or BIT column. http://www.mysql.com/doc/en/Column_types.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: "Kieran Kelleher" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, 02 June, 2003 16:23 Subject: RE: general questions Define the field as type ENUM [NOT NULL] with values "N" and "Y", but read and understand the ENUM type before you use it so you understand what happens when someone tries to insert something other than Y or N. ENUM (Y/N) is what MySQL uses in the mysql permissions database. So, examine some of these tables if you like by performing an EXPLAIN table or SHOW CREATE TABLE table http://www.mysql.com/doc/en/ENUM.html -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Monday, June 02, 2003 3:58 PM To: [EMAIL PROTECTED] Subject: general questions Greetings, I am trying to figure out how to define a field type as boolean. Is there a way? Or must I define the field as integer and represent true/false with 1/0 ? I have a schema defined for a database. I would like to copy that schema to another database. Is there a simple way to accomplish this ? thanks for the info, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: About JDBC
As I understand it, You're actually better off, if not HAVE TO, just putting the 3.0.6 connector jar file into your /common/lib directory in order for Tomcat to be able to see it. K -Original Message- From: Todd O'Bryan [mailto:[EMAIL PROTECTED] Sent: Saturday, 5 April 2003 19:44 To: ™âR Cc: [EMAIL PROTECTED] Subject: Re: About JDBC It should be enough to place the .jar file in the jre/lib/ext folder inside your Java installation folder. If you don't want to do that, you could modify your CLASSPATH environment variable to include wherever you decide to place the .jar. Todd On Saturday, April 5, 2003, at 09:05 AM, 徐祿政 wrote: > Sorry~ > I have a problem about JDBC now, I use mySQL as my database > and tomcat as my server I downloaded the JDBC driver > (mysql-connector-java-3.0.6-stable.zip) and unziped it, but now I > don't how to use it? How to let my jsp pages connect to my database > Would u tell me how to do?? > thanks a lot!! > > > L.c.Hsu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot Update MYSQL Database
I think you probably need to do one of the following;
Insert into info set referer = ?
Or
Insert into info (referer) values (?)
But I could be wrong.
-Original Message-
From: jsp [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 25 March 2003 07:17
To: 'Tomcat Users List'
Cc: [EMAIL PROTECTED]
Subject: Cannot Update MYSQL Database
I'm running this code with no errors but it's not inserting the string
into the database ?
public void addInfo( String referer )
throws SQLException, Exception {
if (con != null) {
try{
PreparedStatement updateInfo;
updateInfo = con.prepareStatement(
"insert into info(?)");
updateInfo.setString(1, referer )
updateInfo.execute();
}
catch (SQLException sqle){
sqle.printStackTrace();
}
}
else {
error = "Connection with database was lost.";
throw new Exception( error );
}
}
This is my database
mysql> desc info;
+-+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-+---+
| ID | int(11) | | PRI | 0 | |
| REFERER | char(200) | YES | | NULL| |
+-+---+--+-+-+---+
2 rows in set (0.00 sec)
Any Idea why nothing is showing up. Referer is a String its printing out
on the page but not updating the database?
Thanks anyone
-wiley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to implement this query - Can you help pls?
Roger, Thanks for the reply. That certainly doesn't give any errors but 'NATURAL JOIN' also unfortunately doesn't work. If I just take the first section; SELECT P.prodID, P.prodName FROM categories C NATURAL JOIN products P; I get an empty set. However; SELECT P.prodID, P.prodName FROM categories C LEFT JOIN products P ON P.catID = C.catID; I get what I would expect (i.e. a full list of prodNames). It has however given me more or less an idea of what I need to do. If it makes any difference to the NATURAL JOIN, I'm using "3.23.51-nt". Cheers -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 16:48 To: [EMAIL PROTECTED] Cc: Kyle Lange Subject: Re: How to implement this query - can you help pls? * Kyle Lange > Try as I might, I can't seem to work out the SQL for this query. Can > you help please? I'll try. > I need to retrieve a list of products within a certain category with > their relevant prices obtained from a specific (date-based) *valid* > pricelist relevant to a particular person. > > TABLE: persons - perID > TABLE: priceList - listID, validFrom, validTo > TABLE: intPerPriceList - perID, listID > TABLE: categories - catID > TABLE: products - prodID, catID, prodName > TABLE: priceListItems - prodID, listID, itemPrice Something like this: SELECT P.prodID, P.prodName, PLI.itemPrice FROM categories C NATURAL JOIN products P, persons PE NATURAL JOIN intPerPriceList IPPL LEFT JOIN priceList PL ON PL.listID = IPPL.listID AND CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo LEFT JOIN priceListItems PLI ON PLI.prodID = P.prodID AND PLI.listID = PL.listID WHERE C.catID = "$catID" AND PE.perID = "$perID" Some comments: NATURAL JOIN means the join is performed on any/all columns with the same name in the two joined tables. Normally in a multitable SELECT, you select FROM one table, join another on some condition, join another on some condition and so on. In this case, two separate "threads" of joins are made; first categories and products(=P), then persons, intPerPriceList and priceList(=PL), and finally the two are 'glued together' with the priceListItems table, which has a condition to match both P.prodID and PL.listID. http://www.mysql.com/doc/en/JOIN.html > HTH, -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to implement this query - can you help pls?
-Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 16:48 To: [EMAIL PROTECTED] Cc: Kyle Lange Subject: Re: How to implement this query - can you help pls? * Kyle Lange > Try as I might, I can't seem to work out the SQL for this query. Can > you help please? I'll try. > I need to retrieve a list of products within a certain category with > their relevant prices obtained from a specific (date-based) *valid* > pricelist relevant to a particular person. > > TABLE: persons - perID > TABLE: priceList - listID, validFrom, validTo > TABLE: intPerPriceList - perID, listID > TABLE: categories - catID > TABLE: products - prodID, catID, prodName > TABLE: priceListItems - prodID, listID, itemPrice Something like this: SELECT P.prodID, P.prodName, PLI.itemPrice FROM categories C NATURAL JOIN products P, persons PE NATURAL JOIN intPerPriceList IPPL LEFT JOIN priceList PL ON PL.listID = IPPL.listID AND CURRENT_DATE BETWEEN PL.validFrom AND PL.validTo LEFT JOIN priceListItems PLI ON PLI.prodID = P.prodID AND PLI.listID = PL.listID WHERE C.catID = "$catID" AND PE.perID = "$perID" Some comments: NATURAL JOIN means the join is performed on any/all columns with the same name in the two joined tables. Normally in a multitable SELECT, you select FROM one table, join another on some condition, join another on some condition and so on. In this case, two separate "threads" of joins are made; first categories and products(=P), then persons, intPerPriceList and priceList(=PL), and finally the two are 'glued together' with the priceListItems table, which has a condition to match both P.prodID and PL.listID. http://www.mysql.com/doc/en/JOIN.html > HTH, -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to implement this query - Sorry about the receipt folks
Sorry people. I switched the receipt off originally, but the filter on the server sent it back and I just replied forgetting the receipt 2nd time round. Apologies. Kyle -Original Message- From: Kyle Lange [mailto:[EMAIL PROTECTED] Sent: Friday, 14 March 2003 15:46 To: [EMAIL PROTECTED] Subject: How to implement this query - can you help pls? Hi, Try as I might, I can't seem to work out the SQL for this query. Can you help please? I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice I have the basics; SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL, categories C .. < I can't figure this bit out> WHERE PL.validFrom < CURRENT_DATE AND PL.validTo > CURRENT_DATE AND C.catID = AND PE.perID = But what I keep getting all messed up is the joining up in between. I think I need to INNER JOIN to the intersection table, but how to then add the joins to the other relevant tables. Can you help please? Thanks in advance. Kyle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to implement this query - can you help pls?
Hi, Try as I might, I can't seem to work out the SQL for this query. Can you help please? I need to retrieve a list of products within a certain category with their relevant prices obtained from a specific (date-based) *valid* pricelist relevant to a particular person. TABLE: persons - perID TABLE: priceList - listID, validFrom, validTo TABLE: intPerPriceList - perID, listID TABLE: categories - catID TABLE: products - prodID, catID, prodName TABLE: priceListItems - prodID, listID, itemPrice I have the basics; SELECT P.prodID, P.prodName, PLI.itemPrice FROM products P, priceList PL, priceListItems PLI, persons PE, intPerPriceList IPPL, categories C .. < I can't figure this bit out> WHERE PL.validFrom < CURRENT_DATE AND PL.validTo > CURRENT_DATE AND C.catID = AND PE.perID = But what I keep getting all messed up is the joining up in between. I think I need to INNER JOIN to the intersection table, but how to then add the joins to the other relevant tables. Can you help please? Thanks in advance. Kyle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERT ... SELECT - A slightly different question
That makes sense. Thank you Tore. Kyle -Original Message- From: Tore Bostrup [mailto:[EMAIL PROTECTED]] Sent: Friday, 21 February 2003 01:12 To: Kyle Lange; [EMAIL PROTECTED] Subject: Re: INSERT ... SELECT - A slightly different question The typical syntax for this would be: INSERT INTO phone (personID, phoneNr, email, type) SELECT personID, '1-xxx-xxx-', \N, 'OFF' FROM persons WHERE lastName='' HTH, Tore. - Original Message - From: "Kyle Lange" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 20, 2003 6:59 PM Subject: INSERT ... SELECT - A slightly different question > Hi all, > > I'm trying to load a 1:M table with rows. I've been thru the > manual and discussion list and each comes close but not quite. I'm > trying to load a full table with 4 cols, but one of them needs to be a > key to another table SELECTed enroute. 'LAST_INSERT_ID()' doesn't help > because the main table has also been loaded with rows. > > The 1st row of my statement looks like; > > INSERT INTO phone (personID, phoneNr, email, type) VALUES ((SELECT > personID FROM persons WHERE lastName='), '1-xxx-xxx-', \N, > 'OFF'), or > INSERT INTO phone SET personID=(SELECT personID FROM persons WHERE > lastName=''), phoneNr='1-xxx-xxx-', email=\N, type='OFF' > or > INSERT INTO phone (personID, phoneNr, email, type) SELECT personID FROM > persons WHERE lastName='', '1-xxx-xxx-', \N, 'OFF', > > None of which seem to work. At this point the only alternative I can > see is to multiple 'personID SELECTs each followed by an INSERT INTO > phone. > > Or is there some fundamental SQL or mySQL concept I'm missing please? > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT ... SELECT - A slightly different question
Hi all, I'm trying to load a 1:M table with rows. I've been thru the manual and discussion list and each comes close but not quite. I'm trying to load a full table with 4 cols, but one of them needs to be a key to another table SELECTed enroute. 'LAST_INSERT_ID()' doesn't help because the main table has also been loaded with rows. The 1st row of my statement looks like; INSERT INTO phone (personID, phoneNr, email, type) VALUES ((SELECT personID FROM persons WHERE lastName='), '1-xxx-xxx-', \N, 'OFF'), or INSERT INTO phone SET personID=(SELECT personID FROM persons WHERE lastName=''), phoneNr='1-xxx-xxx-', email=\N, type='OFF' or INSERT INTO phone (personID, phoneNr, email, type) SELECT personID FROM persons WHERE lastName='', '1-xxx-xxx-', \N, 'OFF', None of which seem to work. At this point the only alternative I can see is to multiple 'personID SELECTs each followed by an INSERT INTO phone. Or is there some fundamental SQL or mySQL concept I'm missing please? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
