Counting columns

Tailgunner · **Posted:** February 28th, 2009, 1:01 pm

Hello everyone!

I would like to ask for your help.

I want to count the number of the columns in a database and store the result in a variable, but I haven't found any working solutions for it yet. I hope you know any. I'm using opus XE.

Ronnie Dackus · **Posted:** February 28th, 2009, 3:15 pm

Code:

SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable'

Regards,

Ronnie

Tailgunner · **Posted:** February 28th, 2009, 3:35 pm

Ronnie Dackus wrote:

Code:

SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable'

Regards,

Ronnie

Hi Ronnie

Thank you for your answer!

I already tried this method, but I guess my real problem is to retrieve the result of the sql query and put it into variable with opus script. (I'm a beginner at OOP programming)

I tried the following:

function countColumns()
{
var columns_num = db.ExecuteSQL("SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyDB'");
Debug.trace(columns_num)
}

But that does not give me any result.

Ronnie Dackus · **Posted:** February 28th, 2009, 7:15 pm

Code:

function countColumns() 
{ 
var rest = db.ExecuteSQL("SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyDB'"); 
countcolumns=rest.count
} 

Tailgunner · **Posted:** March 2nd, 2009, 4:06 pm

Yes, I guess it will work. Thanks!

By the way, is there any solution like this if I use MS ACCESS DB?

Ronnie Dackus · **Posted:** March 2nd, 2009, 9:52 pm

Hi,

Just set the dsn in the required format.

Regards,

Ronnie

Tailgunner · **Posted:** March 3rd, 2009, 2:13 pm

I already did that. My code looks like the following now:

Code:

function countColumns()
{
var db = new Database("FILEDSN=C:\\Databases\\Linkdata.dsn;")
var rest = db.ExecuteSQL("SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LinkDB'");
countcolumns = rest.count;
Debug.trace(countcolumns)
}

But I got the following error message:

HY000: fffff8ed: [Microsoft][ODBC Microsoft Access Driver] Could not find file: 'D:\Opus\getdata\INFORMATION_SCHEMA.mdb'.

I guess I can't use Information Schema with MSACCES databases.
Is there any other method?

Thanks again for the help.

Ronnie Dackus · **Posted:** March 3rd, 2009, 2:28 pm

Code:

var db = new Database("FILEDSN=nameofdsn.dsn");
var rest = db.ExecuteSQL("SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LinkDB'"); 
countcolumns = rest.count; 
Debug.trace(countcolumns) 

Regards,

Ronnie

Tailgunner · **Posted:** March 3rd, 2009, 5:35 pm

There's no erros this time, but the "countcolumns" variable still didn't get any value. It remains "undefined".

I have no idea what can be wrong.

Ronnie Dackus · **Posted:** March 3rd, 2009, 8:53 pm

Hi,

Post your imp include the database please, or mail it to rdackus@decoplus.nl

Regards,

Ronnie

mackavi · **Posted:** March 3rd, 2009, 11:34 pm

I don't think that you can do this through SQL for MsAccess.

However, the dataset is an object and like all objects you can access it's properties and count the fields that way.

Mack

Tailgunner · **Posted:** March 4th, 2009, 4:22 pm

Hi mack,

This is an excellent suggestion! Thank you so much!

I tried to put together a script that reads the first row of my table through SQL and displays the Dataset object's properites. (so I can count them)

Code:

function dumpProps(obj) {
   for (var i in obj) {
      var dbProps = "\n" + obj[i]; 
    Debug.trace(dbProps)

   }
}

function countColumns()
{
var sql ="SELECT TOP 1 * FROM `LinkDB`" 
var rest = db.ExecuteSQL(sql);
dumpProps(rest)
}

It looks like its working except that It also gives me back 10 lines of "[Function built-in]" string as well.

How can I get rid of them?

mackavi · **Posted:** March 4th, 2009, 5:12 pm

if (typeof obj[i] != 'function' ) dbProps = "\n" + obj[i];

Mack

Tailgunner · **Posted:** March 4th, 2009, 7:07 pm

It's finally working!!! Thank you so much for all your help!

Digital Workshop

Counting columns

Who is online