Digital Workshop

Welcome to the Digital Workshop Message Boards
It is currently December 23rd, 2024, 8:49 am

All times are UTC [ DST ]




Post new topic Reply to topic  [ 14 posts ] 
Author Message
 Post subject: Counting columns
PostPosted: February 28th, 2009, 1:01 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
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.


Top
 Profile  
 
 Post subject:
PostPosted: February 28th, 2009, 3:15 pm 
Offline

Joined: April 1st, 2005, 6:59 pm
Posts: 98
Location: Maastricht, The Netherlands
Opus: V7
OS: Mac OS X 10.7.2 | Windows 8 Dev
System: i5 2.7 | 8G Ram | 1 T HD
Code:
SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable'


Regards,

Ronnie


Top
 Profile Visit website  
 
 Post subject:
PostPosted: February 28th, 2009, 3:35 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
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.


Top
 Profile  
 
 Post subject:
PostPosted: February 28th, 2009, 7:15 pm 
Offline

Joined: April 1st, 2005, 6:59 pm
Posts: 98
Location: Maastricht, The Netherlands
Opus: V7
OS: Mac OS X 10.7.2 | Windows 8 Dev
System: i5 2.7 | 8G Ram | 1 T HD
Code:
function countColumns()
{
var rest = db.ExecuteSQL("SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyDB'");
countcolumns=rest.count
}


Top
 Profile Visit website  
 
 Post subject:
PostPosted: March 2nd, 2009, 4:06 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
Yes, I guess it will work. Thanks!

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


Top
 Profile  
 
 Post subject:
PostPosted: March 2nd, 2009, 9:52 pm 
Offline

Joined: April 1st, 2005, 6:59 pm
Posts: 98
Location: Maastricht, The Netherlands
Opus: V7
OS: Mac OS X 10.7.2 | Windows 8 Dev
System: i5 2.7 | 8G Ram | 1 T HD
Hi,

Just set the dsn in the required format.

Regards,

Ronnie


Top
 Profile Visit website  
 
 Post subject:
PostPosted: March 3rd, 2009, 2:13 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
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.


Top
 Profile  
 
 Post subject:
PostPosted: March 3rd, 2009, 2:28 pm 
Offline

Joined: April 1st, 2005, 6:59 pm
Posts: 98
Location: Maastricht, The Netherlands
Opus: V7
OS: Mac OS X 10.7.2 | Windows 8 Dev
System: i5 2.7 | 8G Ram | 1 T HD
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


Top
 Profile Visit website  
 
 Post subject:
PostPosted: March 3rd, 2009, 5:35 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
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.

Image


Top
 Profile  
 
 Post subject:
PostPosted: March 3rd, 2009, 8:53 pm 
Offline

Joined: April 1st, 2005, 6:59 pm
Posts: 98
Location: Maastricht, The Netherlands
Opus: V7
OS: Mac OS X 10.7.2 | Windows 8 Dev
System: i5 2.7 | 8G Ram | 1 T HD
Hi,

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

Regards,

Ronnie


Top
 Profile Visit website  
 
 Post subject:
PostPosted: March 3rd, 2009, 11:34 pm 
Offline
Godlike
Godlike
User avatar

Joined: March 21st, 2007, 10:44 am
Posts: 3188
Location: UK
Opus: Evolution
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

_________________
When you have explored all avenues of possibilities, what ever remains, how ever improbable, must be the answer.

Interactive Solutions for Business & Education
Learn Anywhere. Learn Anytime.

www.interaktiv.co.uk
+44 (0) 1395 548057


Top
 Profile Visit website  
 
 Post subject:
PostPosted: March 4th, 2009, 4:22 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
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?


Top
 Profile  
 
 Post subject:
PostPosted: March 4th, 2009, 5:12 pm 
Offline
Godlike
Godlike
User avatar

Joined: March 21st, 2007, 10:44 am
Posts: 3188
Location: UK
Opus: Evolution
if (typeof obj[i] != 'function' ) dbProps = "\n" + obj[i];

Mack

_________________
When you have explored all avenues of possibilities, what ever remains, how ever improbable, must be the answer.

Interactive Solutions for Business & Education
Learn Anywhere. Learn Anytime.

www.interaktiv.co.uk
+44 (0) 1395 548057


Top
 Profile Visit website  
 
 Post subject:
PostPosted: March 4th, 2009, 7:07 pm 
Offline

Joined: January 31st, 2009, 3:32 pm
Posts: 7
Location: Hungary
It's finally working!!! Thank you so much for all your help!


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 33 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group