Database

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

6-Jan-10 9:36

Hello,

I'm attempting to query a list of tables from an SQLite3 database and have come across the query PRAGMA table_info("table_name") in the SQLite docs.

Here is my code:

OdbcConnection DbConnection;
OdbcDataReader DbReader;

DbConnection = new OdbcConnection("DSN=" + odbcConnName);

DbConnection.Open();

OdbcCommand DbCommand = DbConnection.CreateCommand();
    //check to make sure column names are present
                DbCommand.CommandText = ("PRAGMA table_info(bandwidth_records)");
                DbReader = DbCommand.ExecuteReader();

                while (DbReader.Read())
                {
                    Trace.WriteLine(DbReader["type"].ToString());
                }

DbReader.Close();
DbConnection.Close();

However, DbReader.Read() is not returning true. Very weird.

Any ideas?

Thanks,

Matt

loyal ginger6-Jan-10 9:59

loyal ginger

6-Jan-10 9:59

I am not sure what was wrong with your case, but a test on my system returned the correct info.

Are you sure you want to use the PRAGMA commands in your programs? Based on the documentation future versions of SQLite will not guarantee the backward compatability for these PRAGMA commands.

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

Dimitri Witkowski6-Jan-10 10:23

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

6-Jan-10 10:23

Hm.. loyal ginger, which SQLite-ODBC driver have you used?
I want it too

See my article about Windows 7 Taskbar timer here on CodeProject

Dimitri Witkowski6-Jan-10 10:22

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

6-Jan-10 10:22

This will not work in SQLite via ODBC (if you use SQLite.Data.Dll everything will be ok)

However, using ODBC you can get table fields too. Here's a sample:

DbConnection.Open();
DataTable dt = DbConnection.GetSchema("Columns");
foreach (var col in dt.Columns) {
    Console.Write(col + "\t");
}
foreach (DataRow row in dt.Rows) {
    foreach (object field in row.ItemArray) {
        Console.Write(field + "\t");
    }
    Console.WriteLine();
}

See my article about Windows 7 Taskbar timer here on CodeProject

bbranded7-Jan-10 5:55

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

7-Jan-10 5:55

Thanks! I will look into the DataTable class more.

Perfect.

Dimitri Witkowski7-Jan-10 6:17

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

7-Jan-10 6:17

DataTable class itself isn't exactly what you need - it just contains the data.
You should look at GetSchema method of DbConnection class. This method returns the metaschema of data for different databases.

See my article about Windows 7 Taskbar timer here on CodeProject

bbranded7-Jan-10 6:38

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

7-Jan-10 6:38

Thanks!

DbConnection class

bbranded7-Jan-10 9:00

Re: [SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

7-Jan-10 9:00

Is there a given interface to the ODBC items:

TABLE_CAT
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
COLUMN_SIZE
BUFFER_LENGTH
DECIMAL_DIGITS
NUM_PREC_RADIX
NULLABLE
REMARKS
COLUMN_DEF
SQL_DATA_TYPE
SQL_DATETIME_SUB
CHAR_OCTET_LENGTH
ORDINAL_POSITION
IS_NULLABLE

to only parse certain "fields?"

Thanks for your time,

Matt

Dimitri Witkowski7-Jan-10 9:08

7-Jan-10 9:08

What do you mean as interface to the ODBC items?
I don't completely understand your question. Please be more concrete, maybe I'll be able to help you.

See my article about Windows 7 Taskbar timer here on CodeProject

T-SQL Query Help Needed

#realJSOP6-Jan-10 9:17

#realJSOP

6-Jan-10 9:17

I have a view that contains a list transactions, each of which are associated with a single routing number. Multiple transactions can exist for each routing number. Each transaction can be one of two types (a and b). I need to get the routing numbers, the count of transactions for each routing number, and then the count of those that are of type b, and the result set can only include transactions of type b (along with the requisite counts, of course). Can I get some guidance?

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Corporal Agarn6-Jan-10 9:50

Corporal Agarn

6-Jan-10 9:50

One way is to have a nested query

select a.[transaction], b.tran_count
from transactiontable A
inner join (select [transaction], count(*) as tran_count from transactiontable group by [transaction]) as b 
on a.[trasaction] = b.[transaction];

This is an off-the-cuff answer so it needs some looking into, but should start you down the right path.

#realJSOP6-Jan-10 12:23

#realJSOP

6-Jan-10 12:23

I'll give it a try tomorrow. We came up with a three-select query, but it's evident that we know not what we do. Smile | :)

Tim Carmichael6-Jan-10 10:44

Tim Carmichael

6-Jan-10 10:44

select RoutingNUmber, count(TransactionNumber) as TransCount
from TransTable
where TransType = 'B'
group by RoutingNUmber

Does that help?

Tim