Solved

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

Posted on 2011-09-30
14
331 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
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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 100

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now