Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-30
14
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
14 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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