Re: Create Temporary Table, incorrect rows
On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Thanks. That is a much better way to find dupes. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:11 AM To: Paul McNeil; MySQL General Subject: Re: Create Temporary Table, incorrect rows A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
On Tue, Jul 06, 2004 at 10:30:38AM -0400, Paul McNeil wrote: Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 I presume there's a typo here: count(*) should have returned a larger number than distinct(myData), not smaller. So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; This is a different query than above. What cout do you get from this query: Select distinct(mydata),rowID from myTable; Yes, it will be huge, but you want that count of selected rows. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Yes I am using mysqlcc. Is that why the 1000 row limit? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:05 AM To: [EMAIL PROTECTED] Subject: Re: Create Temporary Table, incorrect rows On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- 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: Create Temporary Table, incorrect rows
On Tuesday 06 July 2004 17:20, Paul McNeil wrote: Yes I am using mysqlcc. Is that why the 1000 row limit? Yes. It's under the server options tag (don't have it in front of me to give exact details). If you right click the server connection, under I think properties, there's a query limit. Set to 0 and no limit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
In your VB code, are your connections pooled, or are you using the same
connection for each database call?
Original Message
On 3/3/04, 1:12:07 PM, Jacque Scott [EMAIL PROTECTED] wrote regarding
CREATE TEMPORARY TABLE:
I am trying to create a temporary table through code in my DB. I can do
it when I use MySQL Control Center or something like that, but when I
try to do it through my VB program it doesn't work.
Here is the sql string:
CREATE TEMPORARY TABLE IndenturedList (Level_1 TEXT, Level_2 TEXT,
AssemblyNumber TEXT, NSIPartNumber TEXT, RecordID INT NOT NULL
AUTO_INCREMENT, PRIMARY KEY (RecordID));
If I paste this into MySQL Control Center I have no problem creating
it. Here is the code from my VB program:
Dim cn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim fld As ADODB.Field
Dim DataArray() As String
Dim i As Long
Dim lngRecordAffected As Long
Set cn = New ADODB.Connection
strSQL = CREATE TEMPORARY TABLE IndenturedList (Level_1 TEXT,
Level_2 TEXT, AssemblyNumber TEXT, NSIPartNumber TEXT, RecordID INT NOT
NULL AUTO_INCREMENT, PRIMARY KEY (RecordID));
cn.Open Driver={MySQL ODBC 3.51 Driver}; Server= gStrSQLServer
;database=NSITbls; uid= gStrCurrentUser ; Password=
gStrCurrentUser ;
cn.Execute strSQL, lngRecordAffected
Nothing happens when I run this code. Is the SQL string only for a DAO
connection? The reason I ask is because when I need to write data to
the db I just change the SQL string and it works.
Does anyone know what is wrong?
Thank you,
Jacque
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
In my original post I didn't add that I close the connection just before I exit the function. If lngRecordAffected 0 Then ' if the execute was successful then let's commit the updates cn.CommitTrans WriteData = lngRecordAffected Else ' otherwise let's rollback to before the execute command. cn.RollbackTrans WriteData = lngRecordAffected End If If cn Is Nothing Then Else cn.Close End If Exit Function I do not really understand your question about pooled connections. Does the above code answer your question? In your VB code, are your connections pooled, or are you using the same connection for each database call?
Re: Create Temporary Table
For what it's worth, I get the same problem with 4.0.13, and have posted the same question a few times with no response. It works fine at the command line, but not through the mysql++ API. It doesn't work on my home/work machines (both running XP), though my colleague doesn't experience the problem at all even though he's using 4.0.13 on 2000/XP. - Original Message - From: Russell R Snella [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 5:57 PM Subject: Create Temporary Table Hello, Mysql Version 4.0.11a-gamma When I try and run the following query create temporary table russ1 as select rcust.* from rcust; and I receive the error message: Error Code : 1044 Access denied for user: '[EMAIL PROTECTED]' to database 'customer' (0 ms taken) I can run the query from the command line of the server and the query works correctly. I have run the query GRANT ALL PRIVILEGES ON customer.* TO [EMAIL PROTECTED] In addition, I flushed the privileges, and I stopped and restarted the database. Thank you, for you help in advance. Russell R. Snella -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table problem
Further to this, I should point out everything works fine in mysql-front or
at the mysql console.
The problem shows up when using mysql++, a BadQuery exception is thrown.
query.reset();
query CREATE TEMPORARY TABLE sTemporary TYPE=HEAP
MAX_ROWS=1 subselect;
try
{
query.parse();
query.execute();
}
- Original Message -
From: Phil Bitis [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, July 09, 2003 12:41 PM
Subject: Create Temporary Table problem
As a way of getting around the lack of subselect (I'm aware this is coming
soon) we're parsing sql queries, running subselects and storing their
results in a temporary table and replacing the subselect in the sql with
the
temporary table name.
This has been working fine, but on upgrading to v4.0.13 the CREATE
TEMPORARY TABLE some sql part is failing with this error:
Access denied for user: '@localhost' to database 'uclan_database'
Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table problem
Further to this, I should point out everything works fine in mysql-front
or at the mysql console.
The problem shows up when using mysql++, a BadQuery exception is thrown.
query.reset();
query CREATE TEMPORARY TABLE sTemporary TYPE=HEAP
MAX_ROWS=1 subselect;
try
{
query.parse();
query.execute();
}
This is simple to solve.
As recommended in MySQL++ manual, use stream only for queries returning
result set. For the queries like above use exec() method.
Point taken, changed it to just use exec() and the same problem occurs. It
was working previously with the code above though (mysql 3), and it works
fine entered at the mysql console.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
And what would that be? Phil Dowson wrote: Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
Phil Dowson [EMAIL PROTECTED] wrote: I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running What exactly problem do you have? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
Sorry the problem fixed itself, and I mistakenly sent this email - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Phil Dowson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:01 am Subject: Re: CREATE TEMPORARY TABLE And what would that be? Phil Dowson wrote: Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE TEMPORARY TABLE
Thank You. That is what I was hoping for. I would assume that this would be faster than actually coming up with a random name, using srand and rand, for a table, creating it and deleting it when done. Thanks Steve Buehler At 02:19 PM 4/8/2002, you wrote: Yes a TEMPORARY TABLE is specific to the connection if 100 people have the same name on a Temporary table they are still separate tables that only exist to the connection that created them. -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 2:40 PM To: mysql Subject: CREATE TEMPORARY TABLE If I want to use temporary tables I know I can create tables that are unique without using CREATE TEMPORARY TABLE table_name. What I want to know, because it might be more efficient, is if I use CREATE TEMPORARY TABLE table_name and more than one person hits the page (I am using PHP to access the MySQL DB), does the second person have to wait until the first person is done and the temporary table is gone before the temporary table is created again, or is it unique for each connection. Example: if 100 people hit the page at the same time, will each have a temporary table called table_name that only their connection can see, meaning that there would actually be 100 tables with the same name? I sure hope that makes since. Thanks Steve Buehler - 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
Re: CREATE TEMPORARY TABLE
On Mon, Apr 08, 2002 at 01:40:02PM -0500, Steve Buehler wrote: If I want to use temporary tables I know I can create tables that are unique without using CREATE TEMPORARY TABLE table_name. What I want to know, because it might be more efficient, is if I use CREATE TEMPORARY TABLE table_name and more than one person hits the page (I am using PHP to access the MySQL DB), does the second person have to wait until the first person is done and the temporary table is gone before the temporary table is created again, or is it unique for each connection. There should be no waiting involed, other than the time required to create the table(s). Example: if 100 people hit the page at the same time, will each have a temporary table called table_name that only their connection can see, meaning that there would actually be 100 tables with the same name? Yes. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 60 days, processed 1,630,321,161 queries (313/sec. avg) - 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
