?
Solved

Select the database base name in a select Statement (SQL-92 Sntax)

Posted on 2011-09-30
14
Medium Priority
?
403 Views
Last Modified: 2012-05-12
Any idea what syntax to use to show the DB name in a column of the select?

I am using crystal server and do a mufti-connection universe and need to communicate with multiple MySQL DB's using the SQL-92 standard.

Thanks,
John-Michael
0
Comment
Question by:John-Michael_Davis
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36894986
DBname.dbo.TableName

Example:   SampleCompanyDB.dbo.SalesDetails
0
 
LVL 1

Author Comment

by:John-Michael_Davis
ID: 36895122
I was thinking more like this

Select (DB NAME), *
From Table

Union

Select (DB Name), *
From Table (other DB)

DBName, Data1, Data2, Data3
DBName, Data1a, Data2a, Data3a
DBName,....
DBName2...
DBName2...

This way I can use the DBName for row level user validation since I am Unioning to together several tables.

I hope this helps clarify
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36895289
Select Column1, Column2 from DB_Name1.dbo.TableX
union all
Select Column1, Column2 from DB_Name2.dbo.TableY
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 36895362
Try this

Select Column1, Column2, "DB_Name1.dbo.TableX" AS DBName from DB_Name1.dbo.TableX
union all
Select Column1, Column2,  "DB_Name1.dbo.TableY" AS DBName from DB_Name2.dbo.TableY

mlmcc
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36895363
Select 'DBName1' as DB_Name, * from Table1
Union
Select 'DBName2' as DB_Name, * from Table1

0
 
LVL 1

Author Comment

by:John-Michael_Davis
ID: 36895496
Thanks for the input,

I should have been clearer that I may not necessarily be able to see the DB Name when I am building the union. Due to the nature of Crystal server and Data federation I can only see the ODBC connection String Name when unioning. I can get the DB Name but I requires a little know how and I was hoping for a more dynamic approach in case a future admin needs to work on this as well.

Thank you again for the responses.

0
 
LVL 1

Author Comment

by:John-Michael_Davis
ID: 36895499
Something similar to MS SQL's DB_Name() as 'Database Name' would be great

*this grabs the DB Name like a gettime()
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36895534
In MySQL you have to know the database names of the tables you are accessing if they are not in the current database of the session.  Maybe you can query information_schema.tables for the database names you need.  This is a view that gives info on all tablea the current user has access to.  Relevant columns would be table_schema and table_name.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36895543
One idea - build a stored procedure that queries information_schema.tables then dynamically builds the query, executes it and returns the results.  I dont have my references with me but you should be easily able to google for examples for building dynamic queries in MySQL stored procedures.

Or you can do the same in ruby, perl or whatever language you're comfortable with.
0
 
LVL 1

Author Comment

by:John-Michael_Davis
ID: 36895560
Johanntagle

will that be compatible with the SQL-92 standard that must be used for Crystal to access each database correctly?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36896132
Yes, using Stored Procedures in Crystal is SQL-92 compatible. You may face some issues if you're using older versions of Crystal.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37049905
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 37049906
I think solutioon has been given to the author.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question