MySQL Forums - Newbie

Problem with stored procedure (1 reply)

Timothy Ferrell — Tue, 24 Nov 2009 19:23:56 +0000

I'm having some issues with a stored function, essentially what I'm trying to do is check the fields pulled down by the SELECT and check if they are NOT NULL, and setting the 's' varchar to the field that is NOT NULL.

I'm new to this, so any help would be greatly appreciated. Thanks in advance.

DELIMITER //

CREATE FUNCTION entryStatusProgress (g INT)

RETURNS VARCHAR(3)

DETERMINISTIC

BEGIN

DECLARE s VARCHAR(3);

#g = GLASS

SELECT `assignedToSelf` ats, `ack_rr` ack, `attempt1` at1, `attempt2` at2, `responseProvidedID` res, `serviceProvided` ser, `fulfilled` ful, `closed` clo
FROM gc_entry_status;

IF ats IS NOT NULL THEN;
IF ack IS NOT NULL THEN;
IF at1 IS NOT NULL THEN;
IF at2 IS NOT NULL THEN;
IF res IS NOT NULL THEN;
IF ser IS NOT NULL THEN;
IF ful IS NOT NULL THEN;
IF clo IS NOT NULL THEN;
SET s = 'clo';
ENDIF;
SET s = 'ful';
ENDIF;
SET s = 'ser';
ENDIF;
SET s = 'res';
ENDIF;
SET s = 'at2';
ENDIF;
SET s = 'at1';
ENDIF;
SET s = 'ack';
ENDIF;
SET s = 'ats';

END

DELIMITER ;

Access to Mysql migration (1 reply)

Mickael Wenders — Tue, 24 Nov 2009 16:29:53 +0000

Hello,

I am tired of using Access. Now I want to use Mysql. Only problem is I have all my customer addresses and other information on Access.

Can you help to get them working?

Thank you.

Stack Trace (-1 replies)

James Hawkins — Tue, 24 Nov 2009 15:13:54 +0000

-- moved topic --

mysqldump: Got error: 1045: only from batch file. (no replies)

Steve Audus — Tue, 24 Nov 2009 14:36:20 +0000

Hi,

I am running mysql on a windows 2003 server.

In the command line if I run this command

D:\mysql\bin\mysqldump.exe -u root -p mypassword moodle > D:\backup\SQLDump.sql

It works fine, but if I stick the file in a batch file and run it, it generates this error.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using pas
sword: YES) when trying to connect

Any suggestions welcome.
Thank you

What's the meaning of this error code? (1 reply)

Bruce Gareth — Tue, 24 Nov 2009 15:26:26 +0000

Last day, when i do some work in my site http://www.video-converter-best.com, there is an error: MySQL ERROR 1005: Can't create table (errno: 150), what is the meaning of this error?

Show last 2 day data (3 replies)

Agron Nikaj — Tue, 24 Nov 2009 13:20:03 +0000

Hi all,
I want to show last 2 days articles where date is stored as time(); function on database.
Select id, article, time on articles where time <....??

this on PHP

Reusing passed variables? (2 replies)

Gary Lockett — Tue, 24 Nov 2009 14:08:30 +0000

Hi guys, this is something that comes up quite a lot with me and I'm not sure where to post this.

If I use stored procedures, how can I reuse a variable?

e.g. To get vehicles similar to a particular vehicle by vehicle model (but excluding said vehicle in the returned list)...

SELECT * FROM vehicles WHERE id <> ? AND model = (SELECT model FROM vehicles WHERE id = ?);

The two different place holders are actually the same variable, and instead of passing it twice, can I use something within the SQL statement itself? I tried to use VALUES(id) but this doesn't work.

The answer may be simply, no there is no functionality to do this, just pass the variable twice, but for purposes of optimisation, I have to ask.

Thanks,

Gary

MySQL Database creation error (no replies)

Majeri Mourad — Tue, 24 Nov 2009 11:46:51 +0000

Hello guys

I ran into an error since today with the creation of SQL database

here is the error

. MySQL database failed! : SQL flush privileges failed : Table 'mysql.servers' doesn't exist at ../web-lib-funcs.pl line 1323.

How to fix that prorblem ?

I search trough the net a solution and founded this one from andrey:

"One possible fix would be to locate the "mysql_fix_privilege_tables" that's provided with your version of MySQL, and to run that -- it may be able to solve that for you."

But I don't know how to it by the command line in the shell?

Note: i'm using "Virtualmin" on a fresh centos 5.4
cheers.

DB Permissions for a Mobilized Application (no replies)

Omkumar Muthusamy — Tue, 24 Nov 2009 09:37:38 +0000

Hi,

We have a mobile application which can access a mySQL database in our network.

As a DB admin I would like to know what objects i shall give permissions to the mobile client user

Shall the mobile client's access be similiar as that of desktop based accession of a DB Server (All objects,Read,Delete,Update Tables,SPs,Triggers,Functions etc)

Or do i need to limit the permissions.

Please suggest your thoughts,
Omkumar.M

how to Add UUID() function as default value for a column like in ms sql (3 replies)

satyam p — Tue, 24 Nov 2009 11:31:18 +0000

hai
i am new to mysql environment,
i want to Add UUID() function as default value for a column like in ms-sql
how can i do it?
i know one solution is creating trigger for that table. but trigger cannot add for temporary table.
my requirement is Add UUID() function as default value for a column for temporary table like in ms-sql?

can some one help me...............

Multiple Result Sets on Mac OSX (no replies)

Joel Caballero — Tue, 24 Nov 2009 06:40:55 +0000

This has to be a newbie question. I've been pulling out my hair. Is there something I'm missing? Are multiple result sets in a stored procedure not supported on mac?

I thought it was a server issue but it seems to work from a PC.

I've tried using query browser and navicat, but both will only return the first set.

I'm on Snow Leopard.

Can anybody help me?

trigger UNIQUE constraint off and on to get around duplicate entry problems on UPDATE (2 replies)

Grant Gallagher — Tue, 24 Nov 2009 18:25:42 +0000

So here's the deal. I have a table, and for the majority of intents, I want a column to have a unique key constraint. All the data in this table initially came from one source. Now, I am in the process of augmenting this data via data from another source. Seldom, this augmentation results in a duplicate entry. How do I allow this.

I have two scripts:
1.) grabs data from one source, don't want duplicate entries.
2.) grabs data from second source. Updates the column with a unique key constraint which may cause duplicate key entries (I'm OK with this, but mysql is not).

Both scripts run frequently but by no means at the same time, so I'm thinking I need to have a third script.

1.) grabs data from source 1, then turns UNIQUE off.
2.) grabs data from source 2.
3.) reorders data so that uniqueness is no longer violated. Turns UNIQUE on.

I'm a php guy, not a sql guy, anybody know what the third script/query would look like?

Table is something like this after second script:

| id - index | attribute 1 | attribute 2 | address - not unique | myId |
|---- 1 -----|--- at1-1 ---|--- at2-1 ---|-- Carlsbad, CA ------| k162 |
|---- 2 -----|--- at1-2 ---|--- at2-2 ---|-- Encinitas, CA -----| k489 |
|---- 3 -----|--- at1-3 ---|--- at2-3 ---|-- Encinitas, CA -----| k213 |

Here is what I want after third script:

| id - index | attribute 1 | attribute 2 | address - unique |--- myId ---|
|---- 1 -----|--- at1-1 ---|--- at2-1 ---|-- Carlsbad, CA --| k162 ------|
|---- 2 -----|--- at1-2* --|--- at2-2* --|-- Encinitas, CA -| k489, k213 |

*notice the attributes don't matter, I just want to preserve the myId's

Any help would be appreciated. Thank you.

Using drbd with dcm4che?? (no replies)

ruby leung — Tue, 24 Nov 2009 04:30:27 +0000

I have 2 servers machine A (primary) and machine B (secondary) configured with dcm4che, drbd and heartbeat.

I want to set up something like: when machine A is down, the client computers can still query machine B to get the studies. Are there any ways for machine B to "pretend" to be machine A? Say, machine B can somehow "acquire" the IP address and AE title of machine A, so that the client computers would recognize machine B as machine A without noticing that machine A is down?

Any help will be appreciated! Thanks!

Optimize talbe and replication in mysql 4.0.20 (no replies)

jefix jefix — Tue, 24 Nov 2009 04:13:04 +0000

Hello, everyone
I'm using mysql 4.0.20, and I do 'optimize table' command in master , and it seems optimize not replicated to slave , I googled a few and found 'Before MySQL 4.1.1, FLUSH, ANALYZE, OPTIMIZE, and REPAIR statements are not replicated.' , So how to optimize table in mysql 4.0.20 slave?
Can I just run the 'optimize table' command in slave?

Thanks.

Is it possible to convert a varchar column to autoincremented primary key? (2 replies)

Lance Monotone — Tue, 24 Nov 2009 20:09:09 +0000

I have a table that was not very smartly designed to use another db's id field as the primary index to join other records to. The problem is that the values in that field have been stored as varchars and not as autoincremented ints. What I'd like to do is convert that column to sequential ints and set it as the primary key, auto, without destroying my existing records. I also need to change the foreign key of all the tables that rely on that column. For example,

id      field
--------------
8292    foo
203875  bar
1375    morefoo

should be

id      field
--------------
1       foo
2       bar
3       morefoo

And other tables should be converted from

id      foreign_id
--------------
32      8292  [...]
97      203875[...]   
388     1375  [...]

id      field
--------------
32       1  [...]
97       2  [...]
388      3  [...]

Your help would be appreciated. Thanks.

How come my query wont return all 'categories'? (no replies)

Tim McCune — Mon, 23 Nov 2009 23:01:02 +0000

My mistake. Works like a champ.

Debian/JDBC: unable to connect to the database (-1 replies)

Jaws Jaws — Tue, 24 Nov 2009 19:27:36 +0000

-- moved topic --

Still having column problems (3 replies)

D A — Tue, 24 Nov 2009 01:24:08 +0000

im still having problems with this query.....i looked into pivot tables...but its not displaying the columns how i need them to be displayed.

my table is set up as followed:

Table name = shows
columns = id,venue,date,cites,states,info,address

i just need the query to display like this

&id=id01|id02|id03

not like this

&id=ido1|
&id=ido2|
&id=id03|

and then i need to display each column this way.....please keep in mind i just started with mysql and php last week, so i am learning very quickly i am still very new at this...i just an example and i can go from there or someone to point me in the right direction...

Comparing Data set in same table (no replies)

David Jata — Mon, 23 Nov 2009 21:14:36 +0000

Let me preface this by saying that I am open to an alternate method of doing what I am trying to accomplish.

What I am trying to accomplish is grab selected columns from a mssql db and dump it into a mysql db daily and then compare the data sets. I have no problems with grabbing the data, i assign each row a difference 'id' per day. I must do it this way as I was told not to create addition sql dbs on our mssql server. whatever. I then use php to do searches, compares and then output to xls format. This is accomplished by basic while function in php.

Well, i was reading about comparing tables using the EXISTS and NOT EXISTS function and i figure I would give it a try. Well, my queries resulted in zero results found. Example: SELECT * FROM table1 where recordset = 3 AND NOT EXIST (SELECT * FROM table1 WHERE recordset = 2)

I am looking for rows in one recordset that are not in the other recordset (like it data changes in recordset)

programming commands for mysql (1 reply)

Larry Dick — Mon, 23 Nov 2009 20:40:27 +0000

I've found programming reference/samples for opening/closing a db. Selecting/Inserting from an existing db. But have not found a reference for Creating a database, creating tables, dropping tables, etc. which API is appropriate for these type of commands? BTW I'm using C# and ado.net.

Thanks.
Larry

Mysql php column display problem (4 replies)

D A — Mon, 23 Nov 2009 21:04:48 +0000

I am trying to display all the information in several columns to display each column as a row instead of stacked on top of each other for example

need to display data from mysql database like this....below

&example_column_venue=venue01|venue02|venue03example_column_date=date01|date02|date03
&example_column_cites=cites01|cites02|cites03

i have been searching everywhere online for an answer on how to do this.( which means i havent been explaning it right or two its to easy)

i am puting a togther a website with flash and need to display the data this way to get all the information to show up from the php file to the flash action script code
thanks
D

query problem (3 replies)

Ian Mullineaux — Tue, 24 Nov 2009 15:01:21 +0000

I'm new to php and mysql and seem to be stuck with the following -

I have a table showing placement hours for students and I want to produce a total number of hours for each type of supervisionlevel for each student.

The query works if I dont incluse the where statement but the idea is that each student will se the total for theor own number of hours - the code is:

mysql_select_db($database_PEF, $PEF);
$query = "SELECT supervisionlevel, SUM(totalhours) FROM placement GROUP BY supervisionlevel WHERE studentID = '$studentID'";
$result = mysql_query($query) or die(mysql_error());

Can someone please help me out with this - its driving me daft :0)

Help on efficient JOIN query needed (3 replies)

Lance Monotone — Mon, 23 Nov 2009 20:09:37 +0000

I know there's a better way to write this query. It runs quickly on my local db but hangs on the remote (mine is 5.0.27, theirs is 4.1.25). It seems to be creating a large temp table before returning the results. Here's the query...any help would be appreciated. I didn't write this but I can probably explain it if you need more info.

SELECT DISTINCT
tkg_property.id AS id,
tkg_property.name as name,
tkg_property.description as description,
tkg_town.name AS town,
tkg_town.county AS county,
tkg_town.state AS state,
tkg_property.pending AS pending,
tkg_property.price AS price,
tkg_property.town_id AS town_id,
tkg_property.school_district_id AS school_district_id,
tkg_school_district.name AS school_district_name,
tkg_property.acreage AS acreage,
tkg_property_building.bedrooms AS bedrooms,
tkg_property_building.bathrooms AS bathrooms,
tkg_property_building.id as property_building_id,
tkg_property_media.path as path
FROM
tkg_property,
tkg_property_building,
tkg_property_media,
tkg_town,
tkg_school_district
WHERE
tkg_property.id = tkg_property_building.property_id AND
tkg_property.id = tkg_property_media.property_id AND
tkg_property.school_district_id = tkg_school_district.id AND
tkg_property.town_id = tkg_town.id AND
tkg_property_media.order = 1 AND
tkg_property.active != 0

mysqlreport, com_change_db value (no replies)

pepejose pepejose — Mon, 23 Nov 2009 17:35:18 +0000

hi,

this is the result of mysqlreport (mysql 5 + php5 [PEAR:DB] )

why com_ (change_db) is so high???

I'd appreciate any other recommendations

thank you very much

__ Key _________________________________________________________________
Buffer used 246.00k of 16.00M %Used: 1.50
Current 1.95M %Usage: 12.16
Write hit 100.00%
Read hit 99.74%

__ Questions ___________________________________________________________
Total 62.71M 175.2/s
Com_ 31.36M 87.6/s %Total: 50.00
QC Hits 24.02M 67.1/s 38.30
DMS 6.42M 18.0/s 10.24
COM_QUIT 905.34k 2.5/s 1.44
+Unknown 6.05k 0.0/s 0.01
Slow 5 s 11.52k 0.0/s 0.02 %DMS: 0.18 Log: ON
DMS 6.42M 18.0/s 10.24
SELECT 5.94M 16.6/s 9.48 92.54
UPDATE 476.13k 1.3/s 0.76 7.41
INSERT 2.53k 0.0/s 0.00 0.04
DELETE 440 0.0/s 0.00 0.01
REPLACE 0 0/s 0.00 0.00
Com_ 31.36M 87.6/s 50.00
change_db 31.35M 87.6/s 49.99
set_option 2.60k 0.0/s 0.00
show_fields 2.04k 0.0/s 0.00

__ SELECT and Sort _____________________________________________________
Scan 303.36k 0.8/s %SELECT: 5.10
Range 1.13M 3.2/s 18.99
Full join 64 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 898.94k 2.5/s
Sort range 2.33M 6.5/s
Sort mrg pass 3.21k 0.0/s

__ Query Cache _________________________________________________________
Memory usage 84.89M of 128.00M %Used: 66.32
Block Fragmnt 20.68%
Hits 24.02M 67.1/s
Inserts 5.37M 15.0/s
Insrt:Prune 12.76:1 13.8/s
Hit:Insert 4.47:1

__ Table Locks _________________________________________________________
Waited 38 0.0/s %Total: 0.00
Immediate 13.57M 37.9/s

__ Tables ______________________________________________________________
Open 1076 of 2048 %Cache: 52.54
Opened 1.08k 0.0/s

__ Connections _________________________________________________________
Max used 36 of 90 %Max: 40.00
Total 905.34k 2.5/s

__ Created Temp ________________________________________________________
Disk table 636.01k 1.8/s
Table 2.50M 7.0/s Size: 128.0M
File 2.10k 0.0/s

__ Threads _____________________________________________________________
Running 4 of 4
Cached 32 of 64 %Hit: 100
Created 36 0.0/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 0 0/s
Connects 1 0.0/s

__ Bytes _______________________________________________________________
Sent 177.42M 495.8/s
Received 1.58G 4.4k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 763.08M of 900.00M %Used: 84.79
Read hit 100.00%
Pages
Free 8.76k %Total: 15.21
Data 48.44k 84.10 %Drty: 0.08
Misc 394 0.68
Latched 0 0.00
Reads 571.13M 1.6k/s
From file 27.98k 0.1/s 0.00
Ahead Rnd 3 0.0/s
Ahead Sql 364 0.0/s
Writes 5.29M 14.8/s
Flushes 2.02M 5.6/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 70 0.0/s
Current 0
Time acquiring
Total 6796 ms
Average 97 ms
Max 583 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 28.86k 0.1/s
Writes 2.49M 6.9/s
fsync 681.08k 1.9/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 303 0.0/s
Read 48.14k 0.1/s
Written 2.02M 5.6/s

Rows
Deleted 91 0.0/s
Inserted 2.15k 0.0/s
Read 2.26G 6.3k/s
Updated 472.54k 1.3/s

Help with copying database (4 replies)

Barbara Davis — Mon, 23 Nov 2009 19:23:27 +0000

I really need someone to help explain how I copy an entire sql database with its contents to another sql database. I have tried exporting and importing and all I get are errors.

How to resolve this problem? (2 replies)

Rakesh SS — Mon, 23 Nov 2009 17:24:35 +0000

Hi Friends,
I'm new to my Mysql. I have stuck with a problem, Can anyone guide me how to resolve this.
I have two table

Employee (table1)
Emp_id(Primary Key) Name Company Salary

Expenditure(Table2)
Emp_id(foreign key) Amount Date Description

Now the thing is, whenever i make a amount entry in expenditure table the corresponding salary must be deducted in Employee Table.

So, I do not know how to implement. Can anyone help me about this.

Regards
Rakesh

Character set problem (-1 replies)

Alex NS — Mon, 23 Nov 2009 16:46:21 +0000

-- moved topic --

Joining multiple tables in database (4 replies)

Joanna K — Tue, 24 Nov 2009 16:11:28 +0000

Hi everyone,

I'm sorry to post this topic, but I haven't had too much luck finding a basic answer. I have three tables that I'd like to join together when doing a query. The tables are:

Composer_Info
Track_Info
Current_License

and their respective primary keys are:

composerid
trackid
currentid

For example, let's say a query on all currently licensed tracks from the Current_License table comes back with the following:

Composer: 1840
Job Number: 250
Track Title: AT&T Blackberry Jingle
Length: 1 x :30
Company: AT&T
Client: Joe Cellphone
License Duration: 13 Weeks
License Start: November 2, 2009
License End: February 1, 2010

I would like the user to be able to click on any of the fields to provide more detailed information i.e. if they clicked on the "Composer" number, they would get a list of all tracks written by that composer. Or if they clicked on the "Job Number", they'd be able to cross-reference all of the details from that job.

Any help would be greatly appreciated and please let me know if I can provide anything else.

Thank you!!

Allow limited number of duplicates in the table (6 replies)

Cross DeLena — Mon, 23 Nov 2009 18:22:39 +0000

Hello,

I have been searching for this around and couldn't get an answer.

I have a table with some fields and P_ID is not a key of any type.

Fields: P_ID, P_GROUP, P_NAME, P_VALUE

Here, I want to restrict the duplication of P_ID for not more than 10 times. Is there a way in MySQL to restrict this?

Thanks,

-Cross

How to specify a parameter type "like" a table column (1 reply)

Milan Zdimal — Mon, 23 Nov 2009 14:01:12 +0000

I was wondering if anyone knows of a way to set the "type" of a parameter in a stored procedure to be like a field in a table. The idea is that I want to avoid field length issues if the field size changes in the table into which it will be inserted.

This is possible in Oracle:

create or replace procedure
(a_parameter IN .%TYPE)
AS
--variables
v_parameter

.%TYPE;
BEGIN
END;

Does MySQL support this? I checked the documentation on "Create Procedure"; however, I don't see any detail on the options for "type".

Thanks in advance for your replies!

Milan