Embedded MySQL Manager
Hi all, I am wondering if anyone can recommend an application that will allow me to manage an Embedded MySQL database similar to the operation of Navicat or EMS SQL Manager on a normal MySQL server instance? Thanks in advance Martin -- -- [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using a SP return value
Hi all, I have a couple of stored procedures where SP1 is calling SP2. SP2 has a return value which I need for SP1 to continue processing the data. The relevant bit of SP1 looks like this: mlCounter = 12; call SP2(mlCounter, return_val); Relevant bits of SP2 look like: CREATE PROCEDURE `SP2`( IN fromSP1 int (11), OUT valueToReturn int (11)) set valueToReturn = fromSP1 + 10; _ In SP1, I have tried doing set mlReturn_Value = call SP2(mlCounter, return_val); but, on saving, I get a MySQL syntax error. Doing various Googles have not revealed the answer. How do I get the return value from SP2 into an SP1 variable which I can then continue to use in SP1?? Thanks in advance. Martin -- -- [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Field list error in Stored Procedure
Hi all, Running MySQL 5.0.22 Community on Win 2003 Server. Can anyone give me some guidance as to what is wrong with the following SP? It continually errors with 1054 Unknown column 'Individual' in 'field list'. The SP is completing the drop table and the create table, but it is when it comes to run the cursor and to pass one value from that into the insert that the error is thrown (I think). Individual is the expected first record from the cursor query. CREATE PROCEDURE `sp_original`( IN tableName varchar (255)) BEGIN DECLARE var_a varchar (255); DECLARE b int (11); DECLARE MemType CURSOR for select bdbFormValuesControlValue from bdblookuplistvalues where bdbFormValuesControlNameFlag = 4 order by bdbFormValuesDisplayOrder asc; DECLARE CONTINUE HANDLER FOR NOT FOUND set b =1; set @dropTable = CONCAT('DROP TABLE if exists ', tableName, ';'); PREPARE dropTable1 from @dropTable; EXECUTE dropTable1; DEALLOCATE PREPARE dropTable1; set @createTable = CONCAT('CREATE TABLE ', tableName,' (`ID` int(11) NOT NULL auto_increment, `OwnerID` int(11) NOT NULL, `Type` int(1) NOT NULL, `State` int(1) NOT NULL, `Title` varchar(255) NOT NULL, PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;'); PREPARE createTable1 from @createTable; EXECUTE createTable1; DEALLOCATE PREPARE createTable1; OPEN MemType; REPEAT FETCH MemType into var_a; insert into tableName set `OwnerID` = 0, `Type` = 1, `State` = 0, `Title` = var_a; until b = 1 END REPEAT; Close MemType; END; Many thanks in advance Martin -- -- [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field list error in Stored Procedure
Hi Dan, Thanks for the clue! Putting `Title` = 'var_a' (or combinations of \' or ` or \` or ) was putting var_a as the text in the table. Resolution is: begin set @Populate = CONCAT('insert into ', tableName,' set OwnerID=0, Type=1, State=0, Title=\'', var_a ,'\';'); PREPARE populate1 from @Populate; EXECUTE populate1; DEALLOCATE PREPARE populate1; end; And that works! Again, many thanks for putting me onto the right track. Regards, Martin On Tue, 27 Jun 2006 15:03:28 -0500 Dan Buettner said: DB- Martin, do you need to quote it? I think MySQL is telling you it's DB- trying to set DB- `Title` = var_a; DB- which is the same as DB- `Title` = Individual; DB- which is the same as setting the Title column equal to the Individual DB- column. DB- What you want instead is DB- `Title` = individual; DB- Dan DB- On 6/27/06, Martin Lancaster [EMAIL PROTECTED] wrote: Hi all, Running MySQL 5.0.22 Community on Win 2003 Server. Can anyone give me some guidance as to what is wrong with the following SP? It continually errors with 1054 Unknown column 'Individual' in 'field list'. The SP is completing the drop table and the create table, but it is when it comes to run the cursor and to pass one value from that into the insert that the error is thrown (I think). Individual is the expected first record from the cursor query. CREATE PROCEDURE `sp_original`( IN tableName varchar (255)) BEGIN DECLARE var_a varchar (255); DECLARE b int (11); DECLARE MemType CURSOR for select bdbFormValuesControlValue from bdblookuplistvalues where bdbFormValuesControlNameFlag = 4 order by bdbFormValuesDisplayOrder asc; DECLARE CONTINUE HANDLER FOR NOT FOUND set b =1; set @dropTable = CONCAT('DROP TABLE if exists ', tableName, ';'); PREPARE dropTable1 from @dropTable; EXECUTE dropTable1; DEALLOCATE PREPARE dropTable1; set @createTable = CONCAT('CREATE TABLE ', tableName,' (`ID` int(11) NOT NULL auto_increment, `OwnerID` int(11) NOT NULL, `Type` int(1) NOT NULL, `State` int(1) NOT NULL, `Title` varchar(255) NOT NULL, PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;'); PREPARE createTable1 from @createTable; EXECUTE createTable1; DEALLOCATE PREPARE createTable1; OPEN MemType; REPEAT FETCH MemType into var_a; insert into tableName set `OwnerID` = 0, `Type` = 1, `State` = 0, `Title` = var_a; until b = 1 END REPEAT; Close MemType; END; Many thanks in advance Martin -- -- [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choosing a value in a query
Hi all, I am using MySQL 4.1.11nt I have two MyISAM tables: Table 1 holds usage information about users accessing various parts of the database. Relevant fields are Users_Code, Users_action and Affected_Member. Table 2 holds personal information about the Member. Relevant fields are Member_Code, Member_Surname and Member_Groupname Table1.Affected_Member is the link to Table2.Member_Code. Table1.Affected_Member can be a null field. My application is coded so that if Table2.Member_Surname is null, there will be a string value in Table2.Member_Groupname. The reverse is also true. The application is also coded so that Table2.Member_Surname and Table2.Member_Groupname cannot both be null, nor both have a value. I am trying to code the following: 1. If Table1.Affected_Member is not null, then get Table2.Member_Surname where Table1.Affected_Member = Table2.Member_Code, assigning this to the output of the query as Member_Name. 2. If Member_Name is not a string then let Member_Name = Table2.Member_GroupName This will give the result that if Table1.Affected_Member is not null, then the returned value of Member_Name will be either the Surname of the Member or the Groupname of the Member. Although I am having success with coding part 1. of the query, I cannot get Part 2 of the query to give the required result. I can take the resultset from just running a query to get the information from table 1, and then go through this result set, running further queries to get the information required from Table 2, but, as I see it, this will have to be a new query for each record in the Table1.Resultset, which will take up a lot of bandwidth, and make the application slow. Is it possible to code this into one single query that returns all of the values required? Many thanks for your help! Martin -- -- [EMAIL PROTECTED] -- This email, and any attachments, has been scanned for virus contamination using Norton Anti-Virus 2002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: msaccess frontend
On Sat, 18 Oct 2003 09:42:30 -0600 Colleen Dick said: CD- Have anybody ever set up MS Access as a frontend to mysql? CD- CD- the dB is an already established mysql db running on linux CD- I don't want to convert it, just use access to get to it. CD- CD- I have followed the recipe at CD- http://www.washington.edu/computing/web/publishing/mysql-access.html CD- CD- I can see the tables so I know I'm connecting and the linkage CD- is working. I understand that access has issues with CD- enums, so I made a really simple test table with just an auto_inc CD- key and a VARCHAR. When I double click that table icon CD- (it's a globe icon) I get Reserve Error -1104. CD- CD- Would it be better to just redo all the tables from access CD- in the first place? This database isn't huge. Thanks... CD- CD- CD- Reserved Error 1104 is a Jet engine error, and means (IIRC), Invalid Session Handle. Recommended fix is to create a new access mdb and try again. Try another ODBC driver (I have both the 2.5 and 3.51 MySQL drivers - sometimes one will refuse to work correctly, but then changing the driver to the other (and then relinking the tables in access) fixes the problems. There is no regularity / pattern to this. S'pose it just depends on the mood that windoze is in at the time. :-( (In the following, I declare an interest as a satisfied Navicat user - I have nothing to do with the company that has developed Navicat). Finally, I do wonder if you really _need_ access as a frontend? Have you tried, for example, MySqlFront or Navicat? I have Access as a front end for two particular MS diehard employees, whilst the rest of the group use Navicat. We have loads more problems with the Access front end than we do with the Navicat. (Lost connections and corruption of data on the link from the db to access being the main ones.) HTH Martin -- -- [EMAIL PROTECTED] -- This email, and any attachments, has been scanned for virus contamination using Norton Anti-Virus 2002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]