|
Hello,
I have installed the MySQL database program on my computer. It came with 2 databases installed and I created another called "Test" for experimentation. I can access all the information from WITHIN the proggram using the internal commands, however, I am looking to be able to access table information from an outside program, so I need to be able to get table information into that program (C#) to create queries. I can successfully list the names of the databases in the program, however, I have an issue getting the table names. I have used both of the following which are supposed to list the tables:
And I have tried several variations of the above. All seem to either return only the names of the 3 installed databases or nothing at all. Can anyone supply me with an SQL statement that will simply return the names of the tables inside a specifically defined database (thisDb) and, as long as I am on the topic, if your know this answer, how about another script to do the same thing for the table columns of each table. Thank you for your input and assistance....Pat
|
|
|
|
|
This works for me:
USE thisDb;
SHOW TABLES;
|
|
|
|
|
Thank You Shameel....once again you have bailed me out. I was actually going to email you with this question as I remember that we used to be able to do that here, but I could no longer find a link for it. In any event, and to help any others that are suffering along and who find this question in a search, I am enclosing the final code that I used for my application. And once again Shameel, I have marked your answer as correct. For other interested parties, the C# code is mine (simple and clean for better or worse), so don't blame Shameel for it...LOL. Best Regards, Pat
string thisDb = listViewDatabases.SelectedItems[0].Text;
string Sql = "Use " + thisDb + ";" + " Show Tables; ";
try
{
MySqlCommand Comm = new MySqlCommand(Sql, Conn);
MySqlDataAdapter da = new MySqlDataAdapter(Comm);
DataSet ds = new DataSet();
da.Fill(ds);
int count = ds.Tables[0].Rows.Count;
string[] tables = new string[count];
for (int i = 0; i < count; i++)
{
tables[i] = ds.Tables[0].Rows[i][0].ToString();
listViewDbTables.Items.Add(tables[i].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex.Message, "Error");
}
|
|
|
|
|
Anytime, glad to be of help
|
|
|
|
|
With ADO.NET, the DataReader's
GetSchemaTable() method can be used to retrieve column information for a specific table. For example,
DataTable dt = reader.GetSchemaTable();
foreach (DataRow dr in dt.Rows) {
foreach (DataColumn dc in dt.Columns) {
Console.WriteLine(dc.ColumnName + " = " + dr[dc].ToString());
}
Console.WriteLine();
}
|
|
|
|
|
Thank you for your reply Ginger. I will add your code to my snippets for future use. The simple string from Shameel added to my own standard population method solved the problem for me. Thank you for your input and advice....Best Regards, Pat
|
|
|
|
|
Is it a must in star-schema modeling that we have a single fact table?In essence can we have multiple fact tables in a cube and still arrive at a star-schema model.
Thanks
Current
|
|
|
|
|
|
Dear all,
Please advise how to get the record count of Cursor ?
DECLARE @vendor_cursor CURSOR FOR
SELECT BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;
OPEN @vendor_cursor;
FETCH NEXT FROM @vendor_cursor
INTO @vendor_id, @vendor_name;
Thanks and best regards
|
|
|
|
|
Do you need the count before, during or after the cursor has run.
Before - use select count(*) with your cursor query
During and After - set up a @Count variable and increment it within your cursor
Set @Count = @Count + 1
This question shows a dramatic lack of thinking, the solution is so simple.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Thank you for the reply
@@CURSOR_ROWS works for me.
|
|
|
|
|
Excellent, there is another potentially useful keyword I have never used (or knew about).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI all, I am new to cursor and i have a doubt... I have one vb code in which few sql statements are written. I need to convert these sql codes into pl/sql sotred procedures. But, in vb, the sql query is dynamic on the basis of values passed like..
If Trim(txtEqpId) <> "" Then
Sql = Sql & vbCrLf & " AND c.eqpid LIKE '" & Trim(txtEqpId) & "%'"
End If
This sql query is first generated and then it is being called from vb code.
Now the problem is while creating pl/sql procedures, I need to make a cursor where the cursor definition has to be created dynamically {not in BEGIN block and it is not fixed}...
So, can we add if condition in cursor definition statement or any other way to achieve it...
csetopper_bhanu
|
|
|
|
|
You are looking for a dynamic where clause, take a look at come of these responses[^]
Most of these will work with a cursor.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a query that returns a table something like this:
102
NULL
101
NULL
the 101 & 102 are FKs into another table, and I need to "expand" those... so what I want to end up with is:
102 b
102 a
<some kind="" of="" marker="" to="" indicate="" the="" null="">
101 b
101 a
<some kind="" of="" marker="" to="" indicate="" the="" null="">
so I figured I'd create a cursor on the first table and loop through the 102, 101, etc. and then do an inner select to "expand" the 101 and 102... this works fine... I end up with 4 result sets though. One for the 102, one for the first null, one of the 101 and one for the second null.
The empty result sets are significant to the query... I figured I could insert a dummy record in there with a special ID... unless there is a better way, but not sure how to combine the 4 result sets into a single one with out losing the empty ones?
|
|
|
|
|
I would as a default try and avoid cursors, they really are a last resort tool.
Have you tried UNION or UNION ALL on the 2 queries.
Select This, null That
from SomeTable
Union
Select This, That
from SomeTable
left join ThatTable on ...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear you try.......
you can reduce your null selection by is null clause.
you can use union or union all are two method combine multiple results set.
select 101
union
select null
union
select 102
union
select null
union all result will be as...
null,
null
101
102
101
102
create table temp table
insert into @temptable(ID)
select 101
union
select null
union
select 102
union
select null
select * from @temptable where id is not null
.........................
try this......
|
|
|
|
|
Hello,
I am trying to return the values of the column names from a table in an Oracle 10g database. I have Googled this "ad-nausium" and have found some code which seems to be most preferred:
select column_name from all_tab_columns where table_name = 'TABLE_NAME' order by column_id"
However, this returns the Column Header Stamp "Column Name" for me instead, and I have seen others reply with this same issue; I have not yet found anyone that can explain or follow this up with a correction. I certainly can not figure it out. The complete context code is below as follows:
listViewColumns.Items.Clear();
try
{
OleDbDataAdapter da = new OleDbDataAdapter(Sql, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (object col in dt.Columns)
{
listViewColumns.Items.Add(col.ToString());
}
}
For background, the program is a C# 4.0 Framwork application and I am using an OLEDB provider for Oracle. Some assistance would be greatly appreciated. Thank You, Pat
|
|
|
|
|
Hi,
I don't know for Oracle, however I did similar things with OLEDB for Access, basically it consisted of:
- opening a connection
- creating an OleDbCommand with the appropriate SQL, probably SELECT * FROM tableName
- getting a OleDbDataReader by executing OleDbCommand.ExecuteReader (CommandBehavior.SchemaOnly should suffice);
- getting the table's schema from OleDbDataReader.GetSchemaTable.
- cleaning up.
The schema itself is a DataTable that holds all the field information. You'll have to work out the details and try it on Oracle.
|
|
|
|
|
Thank you for your input Luc. I recall that you have supplied me with some other solutions in times past and I appreciate your input. I will try your scripting later or tomorrow as time permits and post it here if it works. Thanks again...Pat
|
|
|
|
|
You're welcome.
In the mean time I found a matching code snippet:
using (OleDbConnection con=new OleDbConnection(connectionString)) {
con.Open();
using (OleDbCommand cmd=new OleDbCommand("SELECT * FROM ["+tableName+"]", con)) {
DbDataReader rdr=cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema=rdr.GetSchemaTable();
foreach (DataColumn col in schema.Columns) log("colName="+col.ColumnName);
}
}
|
|
|
|
|
Luc,
A minute of my time to thank you for your time. I received this solution (below) from Mr Holmes that is short and sweet. I am sure that if I had a better understanding of the "schema" calls, this would have worked as well, but when I ran it, it placed all the schema headings into the listview. I am sure there is some small argument that would fix it, and I should look nto it as this might help with some other calls. Thank you for your help...I hope that you will answer my 'other issues' as they come up. I appreciate your time and skill...Pat
|
|
|
|
|
I have the script in the office that gets the table names of tables and views - if you don't have an answer I'll post it tomorrow.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank You RAH,
I am going to try Luc's Schema solution later and see if it works, but I would still like to have a look at your proven solution. A post tomorrow, as well as your reply today, is much appreciated. Thank You, Pat
|
|
|
|