RE: Using MySQL as backend
Try http://us2.php.net/manual/en/ref.mysql.php --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 July 2006 1:54 PM To: sharvan kumar Cc: mysql@lists.mysql.com Subject: Re: Using MySQL as backend sharvan kumar wrote: Dear Sir/Madam I am working on a project where I am to use PHP as front end and MYSQL as backend.MySQL stand alone is working properly,but it is not connecting with PHP.Please tell me solution. Connect it. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- 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: MS Access gives error no. -7776.
Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: d -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 8:29 AM To: mysql@lists.mysql.com Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hello, I recently converted a good size (1M records) access database to use MySQL as the backend. I had to do a number of conversion steps. All tables must have a primary key. After the tables were exported, I checked them to see that they did with a perl script. If they did not, I added a field called primary_key not null auto_increment and made it a primary key. I also had trouble getting forms to allow me to add records. I found that having the data source be a query, rather than a table (possibly with a filter) was the cause. All tables must have one and only one timestamp field. This was the worst issue. Access wants one timestamp field that auto updates so that it can do it's optimistic locking routine. I had to write a script to go through my database and convert all timestamp fields to datetime fields. Then, I had to go back and add a last_changed timestamp field to every table. The big issue here is that timestamp fields cannot be set to default to CURRENT_TIME or now(). I had to work through all of the forms and queries in the database and explicitly set them to now() as needed. Be careful of bit fields. In general, the conversion tool I used to initially populate the MySQL tables (sqlYog) seems to want to set bit fields to unsigned. This is bad for access, as it uses -1 for true. Make sure your bit fields get set to unsigned in the Mysql table definition. The other thing I've noticed is that datatype mappings aren't necessarily intuitive. I think currency fields wind up something like 19/4 in the mysql table. I tried changing their definition to something like 9/2, but then access starts throwing errors. Lastly, the original creator of our database did not set up any relationships. I had to manually add all of the foreign key constraints, and in order to do that I had to clean up the child tables such that there weren't any records that had missing parent records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS Access gives error no. -7776.
On 7/4/06, C K [EMAIL PROTECTED] wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: d -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 8:29 AM To: mysql@lists.mysql.com Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hello, I recently converted a good size (1M records) access database to use MySQL as the backend. I had to do a number of conversion steps. All tables must have a primary key. After the tables were exported, I checked them to see that they did with a perl script. If they did not, I added a field called primary_key not null auto_increment and made it a primary key. I also had trouble getting forms to allow me to add records. I found that having the data source be a query, rather than a table (possibly with a filter) was the cause. All tables must have one and only one timestamp field. This was the worst issue. Access wants one timestamp field that auto updates so that it can do it's optimistic locking routine. I had to write a script to go through my database and convert all timestamp fields to datetime fields. Then, I had to go back and add a last_changed timestamp field to every table. The big issue here is that timestamp fields cannot be set to default to CURRENT_TIME or now(). I had to work through all of the forms and queries in the database and explicitly set them to now() as needed. Be careful of bit fields. In general, the conversion tool I used to initially populate the MySQL tables (sqlYog) seems to want to set bit fields to unsigned. This is bad for access, as it uses -1 for true. Make sure your bit fields get set to unsigned in the Mysql table definition. The other thing I've noticed is that datatype mappings aren't necessarily intuitive. I think currency fields wind up something like 19/4 in the mysql table. I tried changing their definition to something like 9/2, but then access starts throwing errors. Lastly, the original creator of our database did not set up any relationships. I had to manually add all of the foreign key constraints, and in order to do that I had to clean up the child tables such that there weren't any records that had missing parent records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FOUND_ROWS UNION
Hi everyone, I have a couple of questions invovling using FOUND_ROWS() and UNION. The manual states that: The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate. I am using UNION DISTINCT. In what way is the value approximate? I have tried some test queries using UNION DISTINCT and it seems to give the correct number of results (ie: FOUND_ROWS isn't including the rows that appear twice). Perhaps the 'duplicate removal occurs' statement means that FOUND_ROWS doesn't find the number of rows in the UNION of the result sets because DISTINCT wasn't specified (ie: it gives the number of rows after duplicates have been removed)? If it is the case the sometimes/all the time FOUND_ROWS doesn't give the correct number of distinct results, ss there anyway of getting over this limitation apart from executing the query twice (once with the limit and once without the limit)? Thanks Taras Test code: = (select SQL_CALC_FOUND_ROWS * from s where ID = 20)UNION(select * from s where ID = 50) LIMIT 0,30; select found_rows(); Gives 50 results, which is the correct value. If duplicate entries were being counted, the above query would result in 70 rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar(5) and select question
Hi,
2006/6/29, Joerg Bruehe [EMAIL PROTECTED]:
Hi Pooly, all,
Pooly wrote:
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
The rules of SQL allow you to compare even such values which you could
not assign.
So you may compare values of character string columns of different
length, and the SQL specification is that the shorter string is
effectively right-padded with blanks before they are compared
(in other words: trailing blanks are insignificant).
Ok, thanks for the explanation !
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data truncated during character set conversion
On Mon, Jul 03, 2006 at 01:55:26PM +0400, Timur Izhbulatov wrote: I'm following the instructions [1] to convert character set. Unfortunately I get warnings about truncated data for some rows in several columns. All the truncated columns are text type. Assuming the `col' column is text type and actually contains correct utf8 data but has wrong character set I use the following queries to fix it: ALTER TABLE table MODIFY col BLOB; ALTER TABLE table MODIFY col TEXT CHARACTER SET utf8; After the second query I get warnings about truncated data for some rows and I can actually see the rows truncated. I played a bit with different data types (LONGBLOB/LONGTEXT) but wasn't successful. So my question is what can be the cause of the data loss and how to avoid it? After some additional investigations I found the cause. It was some exotic non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Seems the problem arises because the table itself also has wrong default character set (latin1). Setting default character set to utf8 with ALTER TABLE doesn't solve the problem. Bug if I create a new table with utf8 as default character set convertion works fine. What's happening? Please see the testcase attached. My character set settings are: SHOW VARIABLES LIKE '%char%'; +--+--+ | Variable_name| Value| +--+--+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--+--+ Cheers, -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL dump 10.9 -- -- Host: localhostDatabase: tizhbulatov_aquarium -- -- -- Server version 4.1.19 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stories` -- DROP TABLE IF EXISTS `test_table`; CREATE TABLE `test_table` ( `overview` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ALTER TABLE `test_table` DEFAULT CHARACTER SET utf8; INSERT INTO `test_table` SET `overview` = '–'; SELECT * FROM `test_table`; ALTER TABLE `test_table` MODIFY `overview` BLOB; ALTER TABLE `test_table` MODIFY `overview` text CHARACTER SET utf8; SHOW WARNINGS; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data truncated during character set conversion
On Tue, Jul 04, 2006 at 01:55:30PM +0400, Timur Izhbulatov wrote: After some additional investigations I found the cause. It was some exotic non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Sorry, I was wrong concerning Russian letters. Acutally *any* non-ASCII character causes the problem. The problem seems to be limited only to TEXT fields, non-ASCII characters (including Russian) in CHAR/VARCHAR fields are OK. -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Its the same program, just Improved :-) People hate things they do not understand. You're probably right, and if I had memorized all the commands to be able to edit text, and had gotten used to it, I may like it. I've just gotten used to free-format editing w/o having to enter any commands to do it. Yeah, you'll probably be better sticking with your old program. Don't even look at GVIM, its VIM with a Graphical Interface, and that is VI Improved with a lot of features. So, you wouldn't like it. I downloaded and installed it, since it was pretty small. You're right, it's pretty much VI for Windows in a GUI. Probably not something that I'll use that often. What can I say?! Its a taste thing! But the fact that you had problems with your editor may indicate you'll have more problems in the future. VI is in every distro of Linux I've ever used so far, so, it was best to learn it in order to admin my servers in a better way, porting it for Windows saved me a lot of work (learning a new, win tool). Besides, what simple, fast and reliable editor you know can: VI is kind of like the old edlin program in DOS. I really HATED that one too. Talk about a bad editor, but that was a bad editor. You had to edit line-by-line. At least with VI, you can edit the whole thing at one time, and go back and forth. It's really a safe editor too, you have to do something to change the text, which is kind of good. I use Linux very infrequently, but I can always rely on VI to allow me to edit a configuration file, or whatever. 1) Automatically backup files. 2) Auto-ident code. 3) Highlight code from at least 100 languages (including SQL, C, PHP, Java, HTML). 4) Show you differences between files (oh, that helped me a lot with my.cnf) 5) Keep versions of old edited files 6) Remember the position where you left editing the file 7) Line numbering, jump to, copy paste visual or command, delete lines by number, delete multiple and much more editing features. 8) Much more stuff I don't use/know Multi-Edit does all of these things that you mentioned above, and much more! That's one of the reasons I like it so much. I've never really ran into a problem with this before, and it's possible that it's just a setting in my editor some where. There are SO MANY settings. Anyway, resolved for now, and I'll never throw away old faithful Multi-Edit (unless something better comes along). Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need one query
Hi Venu, all! VenuGopal Papasani wrote: Hi all, I Have a table with followin structure where i have orgunit and parent and value orgunitparent value 12 10 x 1512 y 1612 z 1712 p Now here 15,16, and 17 are the children of 12 and 12 is the child of 10.Now i need a query which gives the sum of all the children of 12 and nturn 12 itself . i.e I need the sum(values(15+16+17+12)).Is there a query which gives the sum of all these.It will be very helpful if i am able to get it in a single query. Sure there is: SELECT SUM(value) FROM his_table WHERE (orgunit = 12 OR parent = 12) This is fairly basic SQL, nothing tricky involved. This does not descend into deeper hierarchies directly, for that you would need a more elaborate statement. If you have to deal with deeper hierarchies, you might search for the nested set model which makes it easier to work on them than the traditional parent pointer approach you have used here. Sorry, the only URL I have ready is in German, which may not help you much. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calling SP from ASP
I'm running into problems with both ASP.Net and ASP with this thing, and
it's driving me nutz. I have the same stored procedure which I'm calling
from my ASP app (some asp pages, some asp.net pages). Here is the
procedure:
CREATE PROCEDURE `sp_GetNextInv`(
in nChapterID Int,
out cInvNo VarChar(7)
)
BEGIN
Declare cPrefix VarChar(1);
Declare cNextInv VarChar(7);
Set cInvNo = '';
IF nChapterID 0 THEN
SELECT TempInvNo INTO cInvNo FROM Chapters WHERE ID=nChapterID;
END IF;
IF (cInvNo = '') or (cInvNo IS NULL) THEN
SELECT NextInvoiceNo INTO cInvNo FROM Config;
SET cNextInv = Right('000' + CONVERT(CONVERT(cInvNo, UNSIGNED) + 1,
CHAR), 7);
UPDATE Config SET NextInvoiceNo=cNextInv;
IF nChapterID = -1 THEN
Set cInvNo = CONCAT('L',Right(CONCAT('00',cInvNo),6));
END IF;
IF nChapterID = -2 THEN
Set cInvNo = CONCAT('C',Right(CONCAT('00',cInvNo),6));
END IF;
IF nChapterID 0 THEN
SELECT CT.InvPrefix INTO cPrefix FROM ChapterType CT, Chapters C
WHERE C.ID=nChapterID AND CT.ChapterType=C.ChapterType;
Set cInvNo = CONCAT(cPrefix,Right(CONCAT('00',cInvNo),6));
UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
END IF;
END IF;
END
Here is the asp code:
function GetNextInv(nChapterID)
Dim adocmd
Set adocmd = Server.CreateObject(ADODB.Command)
adocmd.CommandText = sp_GetNextInv
adocmd.ActiveConnection = Conn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter(?nChapterID, adInteger,
adParamInput, 16, nChapterID)
adocmd.Parameters.Append adocmd.CreateParameter(?cInvNo, adVarChar,
adParamOutput,7)
adocmd.Execute
GetNextInv = adocmd.Parameters(?cInvNo).Value
set adocmd=Nothing
end function
The error I'm getting is MySQL][ODBC 3.51 Driver][mysqld-5.0.15-nt]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 '{ call sp_GetNextInv(326,
'') }' I have double-checked, and it appears to be putting curley brackets
around the function call. I have tried this sp in straight MySQL command,
and it works fine, it's just calling it from ASP and ASP.net that seems to
be causing the problem... Does anyone know how to resolve this?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum query results strange.
Tanner Postert wrote:
so i am doing a query on an enum field:
when i do this query:
select *, id as vid, user_id as uid from video where (file_complete =
'true') order by undt desc limit 0,10;
the results are 0.16 or 0.17 seconds.
instead of saying file_complete = 'true. if i say file_complete != to the
other 5 possible enum values. the query returns in 0.00 sec.
there is an index on that field... shouldn't the = 'true' query be faster?
i just dont understand.
Are there more rows returned for 'true' than for the other values?
Have you tried using something other than a reserved word for 'true'?
What other values for file_complete could there be than true and false?
Why not define e.g. upload_status enum ('working', , 'complete').
(BTW you don't need those (parentheses) in your query.)
-J
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE Based on Relation
I need to be able to do an UPDATE based on a relation. The following code works in Microsoft SQL. What is the MySQL Equivalent? UPDATE Chapters SET MatSentDate='2006-07-04' FROM Invoices I JOIN Chapters C ON C.ID=I.ChapterID JOIN Schools S ON S.ID=C.SchoolID WHERE I.InvoiceDate = '2006-01-06' AND I.InvoiceDate = '2006-01-31' AND MatSentDate IS NULL Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More Stored Procedure Issues
VB is not case sensitive, so changing the case didn't make any difference.
I'm still getting the same non-sensical error message.
Jesse
- Original Message -
From: DG @ NEFACOMP [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]; MySQL . Net List [EMAIL PROTECTED]
Sent: Tuesday, July 04, 2006 10:23 AM
Subject: Re: More Stored Procedure Issues
On my side your code works (in C#) but I had to change one line
Cmd.Parameters.Add(?cInvNo,MySQLDBType.VarChar)
I changed it to:
Cmd.Parameters.Add(?cInvNo, MySqlDbType.VarChar)
(The difference is on the casing of MySqlDbType)
Hope this helps
Emery
- Original Message -
From: Jesse [EMAIL PROTECTED]
To: MySQL . Net List [EMAIL PROTECTED]
Sent: Tuesday, July 04, 2006 15:20
Subject: More Stored Procedure Issues
OK, same SP, different problem. I did some very minor editing of my SP,
and got rid of some space between parameters, and moved a parenthesis,
made sure that my user had Execute permissions for SP's, and now, I'm
getting a different error. I get the error, 42000Incorrect number of
arguments for PROCEDURE bpa.sp_GetNextInv; expected 2, got 0, which
makes absolutely no sense to me, because I HAVE defined 2
parameters Here is my ASP.Net code:
%@ Import Namespace=System.Data%
Script Runat=Server
function GetNextInv(nChapterID As Integer) As String
Dim Cmd As MySQLCommand, Conn AS MySQLConnection
Conn = OpenConn
Cmd = New MySQLCommand(sp_GetNextInv, Conn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(?nChapterID,nChapterID)
Cmd.Parameters(?nChapterID).Direction = ParameterDirection.Input
Cmd.Parameters.Add(?cInvNo,MySQLDBType.VarChar)
Cmd.Parameters(?cInvNo).Direction = ParameterDirection.Output
Cmd.ExecuteNonQuery()
GetNextInv = CStr(Cmd.Parameters(?cInvNo).Value)
Cmd=Nothing
Conn.Close
Conn=Nothing
end function
/script
here is the stored procedure:
CREATE PROCEDURE `sp_GetNextInv`(
in nChapterID Int,
out cInvNo VarChar(7)
)
BEGIN
Declare cPrefix VarChar(1);
Declare cNextInv VarChar(7);
Set cInvNo = '';
IF nChapterID 0 THEN
SELECT TempInvNo INTO cInvNo FROM Chapters WHERE ID=nChapterID;
END IF;
IF (cInvNo = '') or (cInvNo IS NULL) THEN
SELECT NextInvoiceNo INTO cInvNo FROM Config;
SET cNextInv = Right('000' + CONVERT(CONVERT(cInvNo, UNSIGNED) + 1,
CHAR), 7);
UPDATE Config SET NextInvoiceNo=cNextInv;
IF nChapterID = -1 THEN
Set cInvNo = CONCAT('L',Right(CONCAT('00',cInvNo),6));
END IF;
IF nChapterID = -2 THEN
Set cInvNo = CONCAT('C',Right(CONCAT('00',cInvNo),6));
END IF;
IF nChapterID 0 THEN
SELECT CT.InvPrefix INTO cPrefix FROM ChapterType CT, Chapters C
WHERE C.ID=nChapterID AND CT.ChapterType=C.ChapterType;
Set cInvNo = CONCAT(cPrefix,Right(CONCAT('00',cInvNo),6));
UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
END IF;
END IF;
END
If anyone has any clue why it's telling me I haven't defined parameters
when I have, I would greatly appreciate it.
Thanks,
Jesse
--
MySQL on .NET Mailing List
For list archives: http://lists.mysql.com/dotnet
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]
Newbie - CREATE VIEW Question
Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Thank you -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5168593 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
array type
Hi, Is it possible to create a table which has an array type in it? For example, something like: CREATE TABLE temp (id INT, email[] TINYTEXT); Thanks Nolan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - CREATE VIEW Question
Search the Manual for CONCAT. SELECT /Peter -Original Message- From: z247 [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 10:02 PM To: mysql@lists.mysql.com Subject: Newbie - CREATE VIEW Question Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Thank you -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5168593 Sent from the MySQL - General forum at Nabble.com. -- 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: array type
mm.. I'm not sure what you're trying to achieve here? Why do you want an array in there? I'm pretty sure it's not possible (correct gang?), but I'm wondering why you'd want that. What you *can* do is just store a serialized array in a textfield. What i'm trying to do is dump a log file into a table. This log file contains various information like message ID, timestamp, etc. One of the fields in the logfile is a variable length -- recipients. So what i'd like to do is when I generate usage reports, for example, how many messages a user sent and received, then I can do something like the following: SELECT COUNT(*) FROM temp GROUP BY recipient; and it will list all the email addresses and how much email they sent/received. I suppose I can create another table which handles the variable length recipients but I would like to avoid that as that would make the SQL statements very complex and very hard to administer. Thanks NOlan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array type
On Tuesday 04 July 2006 16:21, Nolan Rumble wrote: SELECT COUNT(*) FROM temp GROUP BY recipient; and it will list all the email addresses and how much email they sent/received. I suppose I can create another table which handles the variable length recipients but I would like to avoid that as that would make the SQL statements very complex and very hard to administer. Trying to store multiple unique items in a single field is generally considered bad normalisation. Use linking tables, and store each e-mail address in a separate row. If table 1 contains an id for the mail and other unique data, table 2 contains one recipient per row (with unique IDs per recipient (use lowercase forcing on inserts so case differences don't matter)) and table 3 maps recipient IDs to mail IDs: SELECT count(*),recipient FROM table1 t1 LEFT JOIN table2 t2 ON t2.m_id=t1.m_id LEFT JOIN table3 t3 ON t3.r_id=t2.r_id GROUP BY recipient Not that complex, assuming I typed it right. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
z247 schrieb:
Say I have the following tables;
siteID,name
--
site1, XYZ
site2, RSQ
ID,site,data
1, site1, M
2, site2, Q
3, site2, Y
4, site1, P
... etc.
And I want to create a view like this;
siteID,name,data
--
site1, XYZ, (M,P)
site2, RSQ, (Q,Y)
where all the related column data in the second table is placed in another
column. How can I do this? Is there a function that can group these values
into one variable or array?
Thank you
CONCAT_WS(', ',siteID,name,data)
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array type
Nolan Rumble schrieb: mm.. I'm not sure what you're trying to achieve here? Why do you want an array in there? I'm pretty sure it's not possible (correct gang?), but I'm wondering why you'd want that. What you *can* do is just store a serialized array in a textfield. What i'm trying to do is dump a log file into a table. This log file contains various information like message ID, timestamp, etc. One of the fields in the logfile is a variable length -- recipients. So what i'd like to do is when I generate usage reports, for example, how many messages a user sent and received, then I can do something like the following: SELECT COUNT(*) FROM temp GROUP BY recipient; and it will list all the email addresses and how much email they sent/received. I suppose I can create another table which handles the variable length recipients but I would like to avoid that as that would make the SQL statements very complex and very hard to administer. Thanks NOlan why is it complex? It's a simple JOIN then. Isn't it? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re[2]: Newbie - CREATE VIEW Question
z247 schrieb:
Say I have the following tables;
siteID,name
--
site1, XYZ
site2, RSQ
ID,site,data
1, site1, M
2, site2, Q
3, site2, Y
4, site1, P
... etc.
And I want to create a view like this;
siteID,name,data
--
site1, XYZ, (M,P)
site2, RSQ, (Q,Y)
where all the related column data in the second table is placed in another
column. How can I do this? Is there a function that can group these values
into one variable or array?
Thank you
CONCAT_WS(', ',siteID,name,data)
Barry
To those who responded - read the question.
He wants to combine the values from the data column of *2* rows into one, not
just a straightforward string concatenation.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum query results strange.
The parantheses come from a php function that is writing the statement. i
know they aren't needed.
I've seen the same results on another table with an enum with values like
'a','b','c','d', so although i haven't modified this specic query to not use
the reservered word true. i know the same results are happening elsewhere
where i don't use it. and since it's a string, it wouldn't know that its a
reserved word.
both queries would have the limit on them, so they would only return 10
rows. but yes, there are probably about 10x as many records with true than
with anything else.
On 7/4/06, John Hicks [EMAIL PROTECTED] wrote:
Tanner Postert wrote:
so i am doing a query on an enum field:
when i do this query:
select *, id as vid, user_id as uid from video where (file_complete =
'true') order by undt desc limit 0,10;
the results are 0.16 or 0.17 seconds.
instead of saying file_complete = 'true. if i say file_complete != to
the
other 5 possible enum values. the query returns in 0.00 sec.
there is an index on that field... shouldn't the = 'true' query be
faster?
i just dont understand.
Are there more rows returned for 'true' than for the other values?
Have you tried using something other than a reserved word for 'true'?
What other values for file_complete could there be than true and false?
Why not define e.g. upload_status enum ('working', , 'complete').
(BTW you don't need those (parentheses) in your query.)
-J
Re: Newbie - CREATE VIEW Question
Hi, Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Requires 4.1 or higher: SELECT table1.siteID, table1.name, GROUP_CONCAT(table2.data SEPARATOR ,) AS all_data FROM table1 JOIN table2 ON table1.siteID=table2.site GROUP BY table1.siteID Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum query results strange.
Hi, both queries would have the limit on them, so they would only return 10 rows. but yes, there are probably about 10x as many records with true than with anything else. If there are only six possible values, and one values occurs ten times as often as the other five values, that means it occurs more than 50% of the time. As a general rule, an index on such a column will NOT be useful for selecting rows having the often-occurring value. Such an index is only useful if you *only* need to select the non-occurring values. What you CAN, perhaps do, though, is create a multi-column index instead, so that MySQL doesn't need to scan all rows and order them for you. Your query was: select *, id as vid, user_id as uid from video where (file_complete = 'true') order by undt desc limit 0,10; Creating an index on (file_complete, undt) should work nicely: ALTER TABLE video ADD INDEX (file_complete, undt); Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Thank you! -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5171108 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Hi, the GROUP_CONCAT worked. Thank you. However, I'm getting duplicates in the all_data column. Is there a function like array_unique in PHP to remove these duplicates? I tried DISTINCT but that did not work. Thank you. -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5171910 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Money
In an older mysql version there was a numerical thing called money and it was supposed to round all things to just 2 numbers after the decimal and maybe make sure the 2 numbers exist even if it's a zero. But this function is not part of mysql version 4.1. I tried INTEGER and CHAR and REAL(x,2) and that was close. Then I looked at 'info mysql' and went to numerals and down to the section talking about what to use. There it said if your table has money things like salary you use DECIMAL(x,2). I have that now in my DB and as I Query money now it looks like money. I have not yet found how I can put a $ in front of all the money columns :-) But to date I have become real pleased with mysql and all the SQL I learned from using Oracle works fine. Karl Larsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS Access gives error no. -7776.
C K wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: Contrary to the advice given on working with MS Access, I've found that I can *only* get things to work with Access 2003 if I remove the timestamp field. Also, make sure your primary key column isn't larger than an int ( ie don't use int unsigned, and don't use bigint ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help! InnoDB won't shut down...
Hi, I'm running mysql 4.0.16 on RedHat Linux. I tried shutting down mysql more than 2 hours ago, by executing 'mysqladmin shutdown', and it's still running. Here is the end of the log file /var/log/mysqld.log: 060624 09:31:54 mysqld started 060624 9:31:55 InnoDB: Data file /var/lib/mysql/ibdata2 did not exist: new to be created 060624 9:31:55 InnoDB: Setting file /var/lib/mysql/ibdata2 size to 50 MB InnoDB: Database physically writes the file full: wait... 060624 9:31:56 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.16-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 060624 10:40:31 /usr/sbin/mysqld-max: Normal shutdown 060624 10:40:31 InnoDB: Starting shutdown... 060624 10:40:36 InnoDB: Shutdown completed 060624 10:40:36 /usr/sbin/mysqld-max: Shutdown Complete 060624 10:40:37 mysqld ended 060624 10:44:15 mysqld started 060624 10:44:23 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.16-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 060704 12:16:25 /usr/sbin/mysqld-max: Normal shutdown 060704 12:16:25 InnoDB: Starting shutdown... The processes mysqld_safe and mysqld-max are still running, both at 0.0% CPU time. What's unusual and maybe relevant is that recently, 10 days ago to be precise, is that the innodb tablespace file filled the entire external drive, where the datadir variable was pointing to. So I had shut down the database, moved all the files except the ibdata1 to the new datadir on the internal drive, edited my.cnf and pointed the datadir the new location, and set the innodb_data_file_path to include the old file with the space rounded down to the nearest megabyte and the the new file at the new location. The server came up and ran without any apparent problems, as can be seen from the log file above. Here is the data file path variable: set-variable= innodb_data_file_path=/mnt/vault/mysql/ibdata1:129685M;/var/lib/mysql/ibdata2:50M:autoextend After that I recycled the server and everything was fine. What to do now? Killing mysql is probably a bad idea... Thanks in advance for your help, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB crash recovery and innodb_support_xa, sync_binlog
Hello, I have some question about InnoDB crash recovery. Q: I understand transaction and write to disk sequence as following figure. Is this correct? w/ innodb_flush_log_at_trx_commit = 1 innodb_support_xa = on sync_binlog = 1 skip-innodb_doublewrite BEGIN INSERT \ +-- store -- biglog_cache (memory) +-- fsync? -- innodb_log_file (disk) (A) == COMMIT \ +-- fdatasync-- binlog (disk) (B) ==| +-- (prepare) fsync? -- innodb_log_file (disk) (C) ==| +-- (commit) fsycn? -- innodb_log_file (disk) (D) == Q: How InnoDB crash recovery when suddenly OS crash at (A)..(D)? (A) roll back by innodb_log_file. (B) roll back by innodb_log_file and remove INSERT from binlog. (C) roll back by innodb_log_file and remove INSERT from binlog. (D) roll forward by innodb_log_file. Q: If sync_binlog = 0, what happen? If still binlog did not sync to disk ... (B),(C) does mysqld failed to removing INSERT from binglog in crash recovery sequence? (D) INSERT in binlog is vanished? If so, table data in replicated master and slave is collapsed? (master has INSERTed row but slave doest not have.) Q: If innodb_support_xa = off, what happen? (B),(C) When roll back by innodb_log_file, mysqld does or does not remove INSERT from binlog? Q: What is best setting for crash recovery? I suppose, innodb_support_xa = on and sync_binlog = 1 is best setting. But mysqld is VERY VERY slower when enable innodb_support_xa and/or sync_binlog. xa=on + sync_binlog=0 is2 times slower than xa=off + sync_binlog=0. xa=on + sync_binlog=1 is 4..7 times slower than xa=off + sync_binlog=0. # This benchmark done with hard disk that have 128MB write cache and # battery backup unit. but xa=on + sync_binlog=1 is very slower... Please any answer or advice. -- HIROSE, Masaaki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
