MYSQL :: VIEW In MySQL

MySQL View For A DB2 Database
I want to create a view in MYSQL, but the database/table is in a DB2 database. Is it possible ? I have heard that such a thing is possible between say ORACLE and SQL Server.

Bottom line, my tables are there in DB2, and i want to create a view in MySQL database. The reason of doing this is that i'm using Ruby/RubyonRails on AIX platform, since i could not find a DB2 Driver on AIX for RAILS, i want to go for this indirect approach.

(Rails works fine with MySQL). hence from Rails i want to access the MySQL (Indirectly to DB2)

How To View Catalog In MySQL
How can I query mysql to view the catalog? (By the catalog I mean the
special internal database that holds the meta-information about all
other databases in the system, including descriptions of all the tables
and their fields.) I know how to do this in psql, so I imagine there
is a similar facility in mysql. FWIW, my OS is Linux.

View MYSQL TABLES VIA FTP
I have been asked to play around with the current website and make some changes etc, before i launch into it i want to get a good idea of how it is structured etc. It's written in PHP with a MYSQL backend.

I have setup a test environment on my local machine and copied the files from the existing site by grabbing them via FTP ... now everything runs via localhost except i cannot connect to mysql via phpmyadmin i played around with the settings in the config.inc.php file and it allowed me into phpmyadmin but there was no database??

How do i see the actual mysql tables or at least get a .sql file dump so i can recreate an identical test db ??

MySQL View Using Data From Firebird Db
The website uses data from a MySQL database by I need to also access data from a Firebird database.

There is a common key between the 2 databases and I would like to create a View in MySQL that reads data from the Firebird database into the View and then displayed on the website. Is this possible in MySQL?

I know that I could use a PHP script in the website itself to do something similar, but I would prefer to perform this at the database level as I believe the performance would be better.

MySQL 5.0.1 : CREATE VIEW And UNION
I don't manage to create a view from the UNION of two tables.

This statement works all right :
CREATE OR REPLACE VIEW myview AS SELECT 1 AS mycol UNION ALL SELECT mycol FROM mytable2;

This one does not :
CREATE OR REPLACE VIEW myview AS SELECT mycol FROM mytable1 UNION ALL SELECT mycol FROM mytable2;

and I get the error "ERROR 1143 (42000): create view command denied to user 'root'@'localhost' for column 'mycol' in table 'myview'", although I am creating the view using the "root" account.

Version used : MySQL 5.0.1 alpha snapshot (Windows binary).

View Mysql Users Password
How can i view a mysql users account's password, i have got all privileges so it should be possible? When i have a look in the mysql table the passwords are coded.

MySQL View And Mathematical Formulas
ok, i have a mysql query,

(CREATE VIEW `Book`.`view` AS
SELECT ID, Description, Size, Pack, List, Net, Discount, NetBtl, SRP, Vendor, Date, CONCAT(ROUND((srp-netbtl)*100/srp),'%') as GPM FROM Products;)

and i need to add in the formulas (List-Discount=Net)and (Net/Pack=NetBtl) along with it, but all attempts have caused syntax errors. also, i want to add dollar signs in front of the numbers on list,net,discount,netbtl,and SRP.

MySQL View Points From Enterprise DBAs
One of our companies clients has asked us about redoing one of their highly profitable web applications in PHP and MySQL (The had an outside consultant recommend it). Their current application is built in C#.NET and SQL and is not currently clustered or load-balanced. They want to rebuild it as they have learned a lot about what they want since the original design and they know they need to make significant changes to the application to get the new features they are after and also address functionality that their current customers don't like.

The company contacted the original .NET author (who also happens to be an experienced Microsoft SQL DBA) to see about redoing the applciation in PHP and MySQL and the author panned MySQL and stated that their own company had attempted to migrate one of their applications to MySQL and that from his perspectives there were many things that causes A GREAT DEAL of extra DBA overhead.

I was hoping that more experienced DBAs using MySQL would be able to confirm or deny some of the Microsoft SQL DBAs claims - I have never worked with anything except MySQL and I have not used it for very long or even very often, so I am unable to successfully evaluate the claims.

By no means do I want to get into 'down with Microsoft' thread here.

Here are his claims:You can not centralize stored procedures for multiple databases. If you do you have to open up a whole lot of permissions that expose the system too much?The use of stored procedures and triggers is really complicated and you have to get down to a very low level of coding to understand, create or edit them, which makes the DBA's job harder and even once experienced more time-consuming?It lacks quite a few advanced features that Microsoft SQL has (he didn't cite which ones)?The upgrade path (of MySQL) is not standardizes so features that worked in version x are not guaranteed to work in version x.5 or y?There is no standardized way to backup and restore the databases and between the different methods there is a lack of compatibility. This means that backing up a MySQL database in one way - say for example MySQLdump and trying to restore it using phpMyAdmin or the MySQL administrator will not work.For those of you that have worked with different Databases do you think that MySQL is as easy to administrate and update as other Databases and even more specifically Microsoft SQL?

How Do I Turn On Logging To View Errors In MySQL 5
I'm a new MySQL user. Just switched over from the Bill Gates world and am joining the open source side finally. I setup a website using Joomla! 1.x with PHP5 and MySQL 5 on the backend.

However when I submit a post in my forum on my new site, the page goes blank yet says it's finished loading. In the Microsoft world you can echo back to the screen errors and figure out which row, record or table was causing the error.

Can you do the same using MySQL or does it have a logging feature that could help me troubleshoot and see why my posts are hanging when hitting the submit button via my browser?

Compare Temporary Table And View In Mysql
may i know that what is the dirrerences between temporary table and view (mysql), which method is more suitable in retrieve tons of data.

How Can I View A Mysql Database In A Html Table?
how I can view my database in a HTML table.

MySQL And Borland Database Engine And View
I have an application using Oracle and MSSQL with BDE, and I would extend it to support MySQL now. I have an error - 'invalid table name' - when I open a view. This is works with Oracle and MSSQL, but doesn't work with MySQL.

How To View Arabic Data In MySQL Query Browser
I am making a website on linux and i am using a program called rekall to view the database and also the table was created using this program.

most of the data in the database are in Arabic and rekall views the data in an acceptable manner with some errors and without any configuration

but when I tried MySQL Query Browser on windows and several other programs including navicat, toad, heidiSQL all the arabic data was not viewable except the data in blobs which can be exported to extenal files

The website encoding is [ar-eg] and [utf-8]
I want to view the arabic data stored in the table using a windows program
and if the program is fast and simple it would be much better as I am not the one who is going to use it

Unable To See Queries In MySQL In Database View Dreamweaver...
I have MySql running on my Mac using NaviCat, I have created a few tables and a Query, When I load Dreamweaver I can see the Tables but can not see the Query. Would anyone be able to help me with this?

MySQL VIEW -> MS Word Mail Merge - Grouping Problem
Basically I am trying to create a simple invoice system that takes a query of a particular set of customer orders in the form of a VIEW (see below) and then Microsoft Word's mail merge facilities access this VIEW and then do a mail merge based upon it. The fields in each invoice are things like ShopperID, address1, address2 and other customer info as well as the products bought along with the corresponding quantity, price and amount (this essentially being quantity*price) etc.

MySQL
CREATE OR REPLACE VIEW invoice AS SELECT o.*, i.Product AS Product, i.price AS Price, i.qty AS qty, i.amount AS Amount FROM orders o INNER JOIN items_ordered i ON i.ShopperID = o.ShopperID WHERE o.DATE BETWEEN &#55614;&#57159;-10-01' AND &#55614;&#57159;-10-31'

MS Word's mail merge connects to the database and accesses the VIEW query results just fine. The problem arises with the grouping of products (along with their associated quantity, price etc.) under each Shopper (or ShopperID to be more precise). Now, in the VIEW above I did not GROUP BY ShopperID so I used GROUP_CONCAT in conjunction with GROUP BY ShopperID to get all the products bought by a customer on the same record.

MySQL
CREATE OR REPLACE VIEW invoice AS SELECT o.*, GROUP_CONCAT(i.Product SEPARATOR '
') AS Product, GROUP_CONCAT(i.price SEPARATOR '
') AS Price, GROUP_CONCAT(i.qty SEPARATOR '
') AS qty, GROUP_CONCAT(i.amount SEPARATOR '
') AS Amount FROM orders o INNER JOIN items_ordered i ON i.ShopperID = o.ShopperID GROUP BY o.ShopperID HAVING o.DATE BETWEEN &#55614;&#57159;-10-01' AND &#55614;&#57159;-10-31'
I thought this would work just fine but the strange thing is that MS Word displays the Product field correctly (which has been concatenated using a new line
) but it doesn't display anything for the quantity, price or amount fields. I have tried using different separators like ' / ' and ',' but the same problem occurs. Does anyone have any ideas about why it is doing this and how I can find a solution?

How Do I Upload A File To MySQL Database And View That File
I do not know how to upload a file to MySQL database and view the posting file on the webpage. I followed the code on-line that show me how to upload, but when I click "browse" to open my file in local drive and click "upload". Nothing happen ! How can I view it on the web page to make sure it was saved in data base.

Is This Possible With A View?
I'm trying to cut down on the size of my database so I've changed it so that rather than implicitly setting every value in one table, I've split the data into two tables: one table (iData) contains data for the start of evey week:
iData
-----------------------------
2000-01-01 Volume: 20000000
2000-01-06 Volume: 30000000
2000-01-11 Volume: 40000000
Another table (jData) contains the delta (difference) from the previous iData entry:
jData
-----------------------------
2000-01-02 Volume: 1
2000-01-03 Volume: -2
2000-01-04 Volume: 1
2000-01-05 Volume: 3
2000-01-07 Volume: 2
2000-01-08 Volume: -5
2000-01-09 Volume: 6
2000-01-10 Volume: 1
2000-01-12 Volume: 2
2000-01-13 Volume: -4
2000-01-14 Volume: 4
2000-01-15 Volume: 3

Obviously this will save a lot of space as the delta is much smaller than storing the exact value (standard compression technique).

So what I would like to do is create a view to automatically display all of the data as if it were all stored in one table:

All Data:
---------
2000-01-01 Volume: 20000000
2000-01-02 Volume: 20000001
2000-01-03 Volume: 19999999
2000-01-04 Volume: 20000000
2000-01-05 Volume: 20000003
2000-01-06 Volume: 30000000
2000-01-07 Volume: 30000002
2000-01-08 Volume: 29999997
2000-01-09 Volume: 30000003
2000-01-10 Volume: 30000004
2000-01-11 Volume: 40000000
2000-01-12 Volume: 40000002
2000-01-13 Volume: 39999998
2000-01-14 Volume: 40000002
2000-01-15 Volume: 40000005

I know I can do this programatically, but is it possible to do this with a view? Any pointers would be much appreciated.

View SQL Files
I am about to by the content of a site, but it is in sql files. I need to view this content and extract to make a new site from it in html format.

How do I view SQL files and extract it content? Is there any program that does it?

Can I View .frm, .MYD And .MYI Files?
At my current host, the server crashed and all data seemed to be lost. Lucky for us they had had a backup of all data which they put back on the server.

But now I get errors with some tables in phpMyAdmin where they show up as "in use" and when I view them I get the following error:

MySQL
#1033 - Incorrect information in file: *.frm
or

MySQL
#1016 - Can't open file: '*.InnoDB'. (errno: 1)

Because of the issues, they send me the *.frm, *.MYD and *.MYI files.
But am I able to view these files to see the structure or data?
And can I verify if structure or data is corrupt?

View Implementation
Anybody has implemented View for MySQL?
I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

VIEW Problem
I create the next view:

CREATE VIEW VideoDB.FindCustomer AS
SELECT id, last_name&' '&name&' '&initial AS cname, picture, birthday, birthday_already, account_type, rents, exp_date, deposit, exp_deposit, auto1, kinship1, auto2, kinship2,
auto3, kinship3, auto4, kinship4, auto5, kinship5, auto6, kinship6
FROM VideoDB.customers;

When I execute the next statement the result is 0:
select cname from videodb.findcustomer;

How can I concat the whole name.
last_name&' '&name&' '&initial AS cname

Create A View
I have a master table and five detail tables. Now I want to have an overwiew on all detail tables that are related to the master table. The problem is that there are to many repeated records.

My wish is that to have each record of detail table in a single row with master table information in the view table. I tried to reach this with joins (left or right). No chance.. Code:

Mysqldump View
I try to dump the view definition and records using the following command.

mysqldump -uroot -psecret! --databases test --tables proxy_view > mytest.txt

What all I get is the view definition without records. What is the correct method of dumping a view?

Create View
I currently have a script that I have developed on a MS SQL server. As part of it I use a VIEW to do most of my lookups. I want to transfer it to a MYSql DB, but when I try and CREATE VIEW I get the following message....

mysql> create table t (qty INT, price INT);
mysal> insert into t values (3, 50);
mysql> create view v as select qty from t;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'view testv a
s select qty from t' at line 1
mysql>

I am using MYSQL for Netware. I am using an example that I have found to demostrate it, but it just does not work.

Exponential View
how to store numbers in exponential view to MySQL. E.g. 4,56e-9 = 0,00000000456

I have 4,56e-9 as a variable in perl and want to store it to MySQL. Decimal as typ seems not to work. Does i realy have to convert it to 0,00000000456?

How To View All The Sp Created?
is there a way to view all the stored procedure created in mysql? a command to view all avaialble sp's?

Creating A View
I'm running the following create view:

create view letters_of_recommendation_x_organizations as select * from organizations o left join letters_of_reccomendation l on o.OrgID=l.orgid

, and getting an error "1064 (42000): You have an error in your SQL syntax; (blah blah blah) ... syntax to use near 'view junk as select * from organizations'"

I've checked the select statement - it runs ok.

I've tried a simpler select statement as well, "create view junk as select * from organizations" .

CREATING A VIEW
I am trying to creat a virtual table by using a dynamic sql. This sql needs to be reuseable on different applications. To make matters more simple to explain let's say my table is a 3 column table. The ID column is a process identifier.

ID     Integer
NAME   VARCHAR
VALUE  VARCHAR

The data will appear like this:

1      FIRSTNAME      JOE
1      LASTNAME       SMITH
2      FIRSTNAME      MARY
2      LASTNAME       JONES

I need to create a table that would take the data above and create the table like below:

ID           INTEGER
FIRSTNAME    VARCHAR
LASTNAME     VARCHAR

The data would look like this:

1      JOE            SMITH
2      MARY           JONES

Any ideas on this could work?

View Optimization
In my database I have separate four tables and from that four tables i have created one view. Each of the table contains round five lacs of records. So when view gives result it is approximately around fifteen lacs of records,

When I call that view like "select * from MyView where name='vimal'" it takes too long to give me result. And due to this one query my application server load increases by 80%. When I execute this view that time mysql Memory consumption is approximately 80% of total memory. What I can do to optimize the same? What are the other alternatives?

View Implementation
Anybody has implemented View for MySQL? I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

Create View
I must be going mad! How come I can't create a view?

mysql> create view testview as select * from userdetails;

ERROR 1064 (42000): 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 'view testview as select * from userdetails' at line 1

(View) Performance
I have a huge table with product data from different shops in my database. There is a flag column 'import' witch flagt products with 1 for my PHP script.

To select from one shop all product individually, work with it, and then update these product import flag to 0 in the database, seems to be the slowest way. I thougt about a view about all products of a shop with flag '1'. Code:

If Condition In View
Basically i want to add put a where condition in a view if the user variable '@org_id' is > 0. Here is some pseudo code:

CREATE VIEW view_students AS
SELECT *
FROM students
If (@org_id > 0) THEN
WHERE org_id = @org_id
END IF

I've tried lots of different approaches but i just cant figure out how to do it.

Currency View
When displaying product prices in MySQL database its only showing 17.5 instead of 17.50. Tried different things in Excel to make the view display correctly, but had no luck. Any ideas?????

View Dependencies
When creating a view, it will (almost certainly) take information from one or more existing tables/views in the database. If I come to the database with no knowledge about the view (other than its name), how can I determine which underlying tables it depends on?

(I can do this is PostgreSQL by performing queries against the pg_class & pg_depends internal tables but I haven't discovered any way of doing it in MySQL either with SQL queries or with the C API.

View The TimeStamp
I know you can view when a table was updated in mySQL Administrator. Is there a way in SQL to view that TimeStamp?

View With Parameters
can I put parameters into a view?

example

CREATE VIEW `dmz_sms`.`vwProva` AS
SELECT * FROM tb_sms where id=@ID;

I get error 1351.

SQL Source For A View
How do you obtain the SQL source that created a View?

I can Describe the view, but that doesn't give me the SQL that created it.

. . .

I should have searched the forums a little longer. The answer lies in the

SHOW CREATE VIEW viewName statement.

Can't Create View
I am using MySQL 5.0.0 server Alpha release. Though the manual suggests that views on tables can be created in this version but i am unable to do so.

I have a table named 'ads'.
I am creating a view named 'test' with following syntax..
create view Test as select * from ads;

Msg returned by MySql is:
[MySQL] ERROR 1064: 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 'view Test as select * from ads' at line 1.

UPDATEABLE VIEW
I am getting an error when updating a table from a view in MySQL 5.
I have created a view & then reference it in my update command, by MySQL caomes back with an unknown table error.

I can 'show' & 'describe' the view, so it is definately there.

View Like In Sql2000
I have just started to look at mysql. I had to download mysql 4.0 as i had problems running mysql 4.1+.

Basically I want to know is in SQL2000 i can create views, I was told I cannot create them in mysql and was wondering how I overcome this problem.

CREATE VIEW
My servers DB is using MySQL ver4.1.10.

I need to use a query in the type CREATE VIEW

CODECREATE VIEW currentCostOfStock AS
    SELECT P.partName, P.partNo, P.manufacturer, S.vehicleType, S.years, S.price, S.stockLevel, S.price * S.stockLevel AS productCost, SUM productCost AS stockCost
    FROM Parts P, StockLedger S
    WHERE P.partNo = S.partNo ;

Create View
I'm really really stuck!
I am trying to create a view in the database but I don't know why my syntax won't work. Here is the statment...

[MYSQL]CREATE VIEW options AS SELECT customer_extras.extrasID, customer_extras.itemID, customer_quote.quoteID FROM customer_extras, customer_quote WHERE customer_extras.itemID=customer_quote.itemID;[/MYSQL]
The SQL administrater program says there's a mistake in the SQL so i tried to type it into the command line as well. Neither worked. I haven't got a clue. I have done this LOADS of times before but it won't work for me today.

I'm running the database on an xp pro machine. Latest mysql server and administrator.
Any help whatsoever would be great as I have wasted the last day looking on the internet. It's really annoying me now. I'm ready to give up

VIEW In MySQL

select NO_RESPONSABLE, NOM + PRENOM as NAME from RESPONSABLE Gives me the number of the ID, but also "0" as the result of the concatenated NAME.

select NO_RESPONSABLE, NOM + PRENOM as NAME from RESPONSABLE

Gives me the number of the ID, but also "0" as the result of the
concatenated NAME.