Enabling Multiple Statements
I'm trying to execute the following multiple statements using MySql w/ the ODBC connector.
INSERT INTO Sites(Hostname) values('zzz');
INSERT INTO Hosts(SiteID, Hostname) values(last_insert_id(), 'zzz');
I get the following error:
[1064] [1] [0] "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 ';
INSERT INTO SiteHosts(SiteID, Hostname)
VALUES(@@IDENTITY, 'zzz')' at line 2"
Is there something I have to do to enable multiple statements?
View Complete Forum Thread with Replies
Related Forum Messages:
PHP Multiple SQL Statements
When i try to do the following in PHP I get errors. Is this not permitted or is this due to a setting somewhere? Can i do only one SQL statement per call to mysql_query? $sql = "CREATE TEMPORARY TABLE tmp SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'; INSERT INTO tmp SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; SELECT * from tmp; DROP TABLE tmp;"; $res = mysql_query($sql);
View Replies !
Multiple AND / IN Statements
answer_ID | question_ID | visit_ID | patient_ID 1 3 4 1 2 3 3 2 3 4 4 1 SELECT * FROM my_table WHERE patient_ID IN (1,2) AND WHERE visit_ID IN (4,3) AND WHERE question_ID IN (1,2,3)
View Replies !
Multiple SQL Statements
I wanted to know how I can make multiple select statements returin into one single row. I tried, but something funny happens: this works, I get one row with columns currentdate and total filled with data... select curdate() as currentdate, sum(amount) as total from bills where date="xx-xx-xxxx" this DOES NOT work, I expected to get one row, with column 'deposits' and column 'total' filled select sum(amount) from deposits where date="xx-xx-xxxx", sum(amount) as total from bills where date="xx-xx-xxxx" using mysql 4.1 Is this a standard SQL limitation or am I not aware of something?
View Replies !
Multiple Distinct Statements
how i would select distinct rows based on 3 columns. Eg. "Select Distinct month, year FROM ..." So i want it to gather just all the distinct month and year values. like jan 06, feb 06, march 06 etc that there is data for ?
View Replies !
Multiple Statements In One Query
Anyone have an example of how to configure a Tomcat JNDI datasource so that you can execute multiple statements in one query string? I'm hoping there is a <parameter> of some kind that I can set for the resource in my context.xml file where I define the datasource. Also, how to you configure the Query Browser to support multiple statements in one query string?
View Replies !
Multiple WHERE Statements In SELECT
How can I have more than one WHERE clause, like this: SELECT DISTINCT email FROM ehousebo WHERE waterway="1000 Islands" OR waterway="Alum Creek Lake" I tried using HAVING, but that gave an "unknown column" error. SELECT DISTINCT email FROM ehousebo HAVING waterway="1000 Islands" OR waterway="Alum Creek Lake" Invalid query: Unknown column 'waterway' in 'having clause'
View Replies !
MyODBC 3.51 Executing Multiple SQL Statements
I am using MyODBC from VB and I want to submit a batch of insert statements in one call from my App. This is more efficient than making multiple calls from code because of the communication overhead. If I send a batch multiple statements separated by ; or ; + newline I get syntax errors pointing at the start of the next statement.. If I fire each statement one at a time with the ; at the end there's no problem. I cannot be the first person with this problem. Is it common practice to make multiple calls from your application or is there a magic trick I am missing?
View Replies !
Combining Multiple Statements To Delete
I have three tables: Principal: id Bonds id Prin_bond: bondID principalID I am TRYING to delete all "Bonds" where the "principalID" in "Prin_bond" = 0. I have been playing with it for a while and here is my best atmysqlt: delete from bonds where id = (select id from prin_bond where INNER JOIN bonds ON bonds.id = prin_bond.bondID WHERE prin_bond.principalID = '0')
View Replies !
Executing Consecutive Multiple Statements
I'd like to execute 4 INSERT statements as a single query to the database, constructing a single string (in php), and executing a mysql_query with sql like this... INSERT INTO users VALUES(NULL, 'steve', ...); INSERT INTO users_groups VALUES((SELECT LAST_INSERT_ID()), 1); INSERT INTO users_groups VALUES((SELECT LAST_INSERT_ID()), 2); INSERT INTO users_groups VALUES((SELECT LAST_INSERT_ID()), 3); In the final 3 statements (inserts into users_groups), the first field value must be the auto-increment value of the first insert statement (insert into users). However, the users_groups table also uses an auto_increment value, and so will the final two calls to LAST_INSERT_ID() return the id of the last insert to the users_groups table? I can see this could be a problem. If so, is there anyway to achieve what i'm trying to do without resorting to multiple queries? Something like setting a variable with the value of LAST_INSERT_ID() and then using that variable in the subsequent inserts?
View Replies !
Multiple ALTER TABLE Statements
I've got a C# app that uses the MySQLCommand object as follows: MySqlCommand myCommand = new MySqlCommand(myStatements); myCommand.connection = myConnection; myCommand.ExecuteNonQuery(); The myStatements string has the following SQL statements in it: ALTER TABLE `mydb`.`table2` DROP FOREIGN KEY `fk_table2_id`; ALTER TABLE `mydb`.`table3` DROP FOREIGN KEY `fk_table3_id`; ALTER TABLE `mydb`.`table4` DROP FOREIGN KEY `fk_table4_id`; and then some others to change the constraint to be ON DELETE CASCADE. They look like this: ALTER TABLE `mydb`.`table2` ADD CONSTRAINT `fk_table2_id` FOREIGN KEY `fk_table_id` (`fk_the_id`) REFERENCES `mydb` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; When I run this, I always get the following error: Error on rename of '.mydb able2' to '.mydb#sql2-6a8-3f' (errno: 152) Seems like if I have more than one ALTER TABLE statement where I'm dropping a foreign key, it will do this.
View Replies !
Execute Multiple Statements Dynamically
I know how to execute a single statement dynamically without any problem but I was wondering if it is possible to execute multiple statement dynamically. declare strSql varchar(4000); set strSql = concat('statement<1>;' , 'statement<2>' ); prepare dynSql from @strLine; execute dynSql; DEALLOCATE PREPARE dynSql;
View Replies !
Share Prepared Statements On Multiple Connections
Prepared sql queries are nice, especially, as is often the case with websites, you have about 100 queries that are reused over and over and over again with slightly different parameters. There is just one hitch. From what I can tell, prepared statements belong to a mysql connection. I.E. if you have a hundred open connections, you cannot share prepared queries among them. Is this true? I use a connection pool to hand out a mysql connection to every incoming request. If prepared queries can not be shared among connections, do you think it would be a good or bad idea to prepare all 100 sql queries for all of the (possibly over 100) connections? (10,000 prepared queries)?
View Replies !
Enabling InnoDB
Actually am using mysql 5.0 version, default it using MyISAM engine, but i wnat ot use InnoDB engine. I tried to change the my.cnf file with entry "default_server_engnie=InnoDB".but there is no use.
View Replies !
Enabling SSL & Setting Up A Certificate
I have MySQL 5.0.27 on XP Pro. I have not been able to find the correct syntax for starting MySQL so that SSL is enabled.I've searched the forums and the Manual. ALso, can anyone point me to a location which provides clear guidance on setting up an SSL certificate? %.9.7.4 of the Reference Manual wasn't too helpful for me.
View Replies !
Enabling InnoDB Error
I've successfully enabled InnoDB for my database but I'm getting the following error when I try and set on delete cascade for a foreign key. Error SQL query: ALTER TABLE `editors` ADD CONSTRAINT `participants_editors` FOREIGN KEY ( `account_id` ) REFERENCES `participants` ( `id` ) ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './isimodev_co_uk_main/#sql-7d1_4c69.frm' (errno: 150)
View Replies !
Enabling Binary Log In Mysql 5.0.45
I wrote log_bin=[filename] in my.inf and restarted ,eventhough if checked at commandprompt through show variables statement, its showing log_bin =off.I tried through mysql adminstrator stratup variables also. But no change at all.can anyone tell me how to enable binary logs and further process for replication.Awaiting for your valuble replies.
View Replies !
Enabling Remote Connections
Can someone tell me how to set up the mysql server to accept remote connections? Im using mysql 4.1 on Linux Ubuntu 5.10 I have tried editing my.cnf and changing the bind-address from 127.0.0.1 to the server's IP address, but this just results in the server failing to restart with the following error: Starting MySQL database server: mysqld...failed. Please take a look at the syslog. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! What do I have to do to get it to accept local and remote connections? Code:
View Replies !
Using IF Statements
This is the first time I've used an IF statement so I'm not sure how it works. I've worked with databases and php though for years. Here's what the tables look like: CREATE TABLE `dd` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `dd` varchar(5) NOT NULL default '', `fig` varchar(15) NOT NULL default '', `direction` set('f','b') NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM; CREATE TABLE `rename` ( `id` int(11) NOT NULL auto_increment, `sid` int(3) NOT NULL, `uid` int(11) NOT NULL, `newName` varchar(100) NOT NULL, `creation` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; Basically what I'm doing here is I have a trampoline program that calculates difficulty and I'm allowing people to rename the skills so that they can have their own custom names (there's several different ways of expressing the names of different skills). There's going to be many users, though, so I can't simply just rename the skill, instead, I have a separate table (rename) so that individual skills can be renamed for each person. Now, however, I have a text box that the person can use to start typing the name of the skill and through AJAX a list of matching skills appears below the box. I need the box to search the newName in the rename table if has been renamed, but search the name field in the dd table if it hasn't. Here's what my query looks like so far; it doesn't work but I know I'm somewhat close but I don't know the correct syntax for this if statement. SELECT * FROM `dd` LEFT JOIN `rename` ON dd.id=sid AND uid=1 WHERE IF rename.newName IS NOT NULL THEN `newName` LIKE 'Triffus%' ELSE `name` LIKE 'Triffus%' ORDER BY `name` ASC
View Replies !
Importing Sql Statements
I have a text file with about 90,000 insert statements (as below). Is there an easy way I can get these into my mysql database in one fell swoop? Can anyone tell me how and perhaps that the syntax is?
View Replies !
Updates And UDF Statements
I have a particular UDF function called pcd which returns the first 1 or 2 letters of a postcode (postcode area), it works fine in selects but when i use it in a update the column being updated ends up with the first column in the table, 10 spaces, then the 2nd column in the table, instead of whatever the UDF function returns. Has anybody else experienced similar problems, im losing my faith in mysql a bit, im trying to use it for serious business applications but might have to find an alternative. I prefer linux servers to windows, any advice, like Max DB?
View Replies !
Order Of Statements
Does the order of the statements in a mysql WHERE clause have an importance of how mysql evaluates the query? Ex: are those equal? WHERE id=5 AND title="horse" and WHERE title="horse" AND id=5 thx
View Replies !
Getting The Create Statements
I have created some simple tables in mysql and i am wondering... is there any way of generating the create statements for the tables and saving them in a txt file. To make sense I want to know if mysql has any automated features that can output the create statements of any given table into a txt file.
View Replies !
Merge Two SQL Statements
i have two sql statements, that i really need to merge into one. here is the first one: SELECT loanhistory.Loan_id, loanhistory.user_id, bookrecord.book_id, bookdata.title, bookdata.BookMedium FROM loanhistory, bookdata, bookrecord WHERE Date_ret IS NULL AND Date_due<'$today' AND bookrecord.book_id=loanhistory.book_id AND bookdata.ISBN=bookrecord.ISBN; and i want to merge this one into it: SELECT COUNT(*) as Num FROM loanhistory WHERE book_id=book_id AND loan_type=1;
View Replies !
Delete Statements
How do I merge these two statements? DELETE FROM mytable_thingy WHERE username = 'charley'; DELTE FROM mytable_thing2 WHERE username = 'charley'; Thank you for any help. I tried: DELETE FROM mytable_thingy, mytable_thingy2 WHERE username = 'charley'; it doesn't work.
View Replies !
Nested IF Statements
Basically i'm asking why doesn't this sql work? One If works fine but i'm not sure if you can nest them like this.. is there a better way? This is narrowing down a products table by a variable being passed that is either subcategory, category or neither and then it narrows down by a brand if a brand is being passed. SELECT * FROM prodSubCat INNER JOIN (prods INNER JOIN prodCat ON prods.prodCatID = prodCat.prodCatID) ON prodSubCat.prodSubCatID = prods.prodSubCatID WHERE (IF(subCatIDParam = 0,(IF(catIDParam = 0, prods.prodCatID LIKE '%',prods.prodCatID = catIDParam)), prods.prodSubCatID = subCatIDParam)) AND (IF(brandIDParam = 0,prods.prodBrandID LIKE '%',prods.prodBrandID = brandIDParam)
View Replies !
Online Statements.
I've been directed here b/c I work for a gym who is retooling their website. One feature we've seen on other club sites is the ability to view statements online. Is this related to MySQL? I've heard it's either this or php... We have a mysql database for our members, if that info helps any.
View Replies !
Case Statements
Is this a valid query in 4.1? select sum(amt) * case when username = 'bob' then 1 else 0 end as 'Bob', sum(amt) * case when username = 'ted' then 1 else 0 end as 'Ted', sum(amt) * case when username = 'alice' then 1 else 0 end as 'Alice' from sales This structure would work for me in Oracle but in mySQL 4.1 the result sums are wrong.
View Replies !
Join Statements
If I have a table full of company names, and the company can be in one or more catagories, i.e. shipper, reciever, holder, etc. and I want to pull out a recordset that shows the shipper, holder, and reciever of a product... Code:
View Replies !
Grant Statements...
i understand Grant statement is used to specify access limitation to certain users in what they can do to the tables/database ~ i have a table as shown in the statement below:- create table admin(UserID int(4) zerofill not null auto_increment, UserName varchar(15) not null, Password varchar(15) not null primary key(userId) ); i can create a set of access privileges for one particular username ~ what if there are some new users being registered into this table??does it means that i have to type the grant statement for each and every new users again and again~ to specify the limit for each new users?
View Replies !
Not In SELECT Statements
what the most efficient way of doing the following in MYSQL is? I have 2 tables. Table A with 20,000 records. Table B with 40,000 thousand records. I want to randomly return one record from Table A where it's primary key does not exist in table B I've tried the following, but this seems to just lock the db server select * from tablea where id not in (select tablea_id from from tableb) order by rand() limit 1
View Replies !
Union Statements
I am trying to create a union statement to join two queries together. one of the fields on one of the queries is a constant, ie because it is from the table i want it to output the letter 'B'. When i try to union this with a variable in another query, i get the error: "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'"
View Replies !
Insert Statements
I'm using bugzilla on Solaris 9. I'm trying to determine why bugzilla displays some bugs, but not others. Below I have two mysql insert statements. The first displays properly. The 2nd does not. I wonder if someone can/would tell me why? [ Yes, I know this is a mysql forum. I would appreciate it you can tell me how these two statements differ from that degree. ] INSERT INTO bugs VALUES (2,1,'','normal','CLOSED','2004-09-24 21:47:49',20040925181616,'TEst 2','Windows XP','P2',2,'PC',1,'1.0',2,'INVALID','-- -',0,'',0,'','2004-09-25 18:16:17',1,1,1,0.00,0.00,NULL); INSERT INTO bugs VALUES (15,1,'','normal','NEW','2004-10-06 09:54:25',20041024210155,'Logging configuration doesn't work correctly','Windows XP ','P2',2,'PC',1,'1.0',21,'','---',0,'',0,'','2004-10-24 21:01:56',1,1,1,0.00,0.00,NULL); Also, how would I reverse/delete either of these statements?
View Replies !
Mysql IF Statements
I looked through the mysql manual but their examples are pretty confusing anyone think they could show me the correct way to write an IF statement inside of a query like this one? SELECT orders.order_id, students.fname, students.lname, order_status.order_status_name, course.name AS course_name, date_format(class.startdate, '%m/%d/%Y') AS startdate, date_format(class.enddate, '%m/%d/%Y') as enddate, order_type.order_type FROM orders LEFT JOIN students ON orders.student_id = students.student_id LEFT JOIN order_status ON orders.order_status = order_status.order_status_id LEFT JOIN class ON orders.class_id = class.class_id LEFT JOIN course ON orders.course_id = course.course_id LEFT JOIN order_type ON orders.order_type = order_type.type_id IF(order_type.type_id == 1) THEN SELECT lm_number FROM lmcharge WHERE orders.order_id = lmcharge.order_id; END IF WHERE orders.order_id = '8'
View Replies !
Can You Merge These Two SQL Statements?
The first one is to get the row numbers for my paging. The second one is what is ran for the actual paging. I assume you can't join the two because of the LIMIT and offset values. Am i right in thinking that? Thanks Matt $query = "SELECT COUNT(ct_vehicles.id) AS numrows FROM ct_vehicles WHERE (".$where.")".$minPriceDB.$maxPriceDB." ORDER BY ct_vehicles.price;"; $search = "SELECT ct_vehicles.* FROM ct_vehicles WHERE (".$where.")".$minPriceDB.$maxPriceDB." ORDER BY ct_vehicles.price LIMIT ".$offset.", ".$rowsPerPage.";";
View Replies !
Using IN Or Join Statements ?
i have browsed through some online documents but still confused on which one is a better method if you have nested queries, some people suggest using IN but some ppl says INNER JOIN/JOIN is better and such. Could any database expert suggest which one is faster ? Or if you are too busy could you show me any good links that could explain thoroughly ? $sql = "SELECT * From member WHERE MemberID IN (SELECT MemberID FROM tradesman_to_category WHERE CategoryID IN (SELECT CategoryID FROM category WHERE ParentCategoryID = '$category_id'))"; (IN clause statement) $sql ="SELECT * From member, tradesman_to_category, category WHERE member.MemberID = tradesman_to_category.MemberID AND tradesman_to_category.CategoryID = category.CategoryID AND category.ParentCategoryID = $category_id"; (INNERJOIN/JOIN/Implicit join statement, whatever you called it) Let say if member table has 100 rows, tradesman_to_category table has 100 rows, category table has 100 rows, could you possibly explain the execution process using both IN and INNER JOIN?
View Replies !
Logging MySql Statements
My server load goes up to 100 and I don´t know why. What I know, if I shot down the site, the server calms down and it works again. I have figured out that i have a mysql problem but I can t figure it out. Is it possible to log all MySql Statements which are running on my database ? If the server goes strange again I could check the log file for the problem.
View Replies !
Create View Statements
I've read the help page on the mysql.org site but still cannot get create view employee as select emp.name from emp, dept where dept.id=emp.dept; The select statement works fine on its own and I've changed the view name a couple of times to avoid reserved words but still get Error 1064 : you have an error in your sql syntax near... which leaves me none the wiser.
View Replies !
|
TRACKER
