AspWebSolution Forums » Products » ASP Report Wizard v2 » database views (server side)

index | search | no replies | posting guidelines | help

login | register

» »

:: database views (server side) :: (23)

Post a New Message | Post a Reply

Sep 25 2004, 10:51 PM

database views (server side)

harveyb

from: ny when i set this up for my sybase anywhere database, it shows tables but not views. is there a way to configure this? doing queries and filters without the benefit of server side views renders the tool useless. there are over 30 million records in our database which are unkempt at best without the views.

thanks
harvey

Sep 27 2004, 5:29 AM

Re: database views (server side)

ghost

Send this author a private message

Visit author's homepage

from: Washington, DC

There seems to be a bug in the connection string identifier.
try this:
right after you initialize your connection string add the following line and let me know if fixes your problem:

<%

objWiz.Init(Your Connection  String)
objWiz.DatabaseType = "SQLSERVER"
'...
objWiz.DisplayReports

  Set objWiz =  Nothing

%>

-------------------------
Ghost

Sep 27 2004, 1:12 PM

Re: database views (server side)

harveyb

from: ny actually, now it does not even show the tables. what other values can you post in there besides "SQLSERVER"?

what is the command you send to the database server to get the tables and views? perhaps it needs to be different for SYBASE Anywhere?

is there something i can send you to help with this?

Sep 27 2004, 1:48 PM

Re: database views (server side)

ghost

from: Washington, DC Actually, I just checked it and it seems it is using xtype instead of type for the sysobjects column name. Let me know if the following statements works for you so we can include it and publish it tomorrow:

SELECT [TYPE], [NAME] FROM SYSOBJECTS WHERE STATUS>0

Where U implies table, V - View & P-procedure

-------------------------
Ghost

Sep 27 2004, 2:17 PM

Re: database views (server side)

harveyb

from: ny SELECT [uid],[type],[name] FROM SYSOBJECTS where [uid] = '1'

there seem to be system tables and views of UID 0, 3, 4 which i am not sure what the significances of the numbering is except for perhaps something to do with synchronization and replication.

i think you want objects of type 'V' or 'U' and [UID] = 1

if you email me, i will email the results of the

select * from sysobjects

so you can see it. the above will show all dba tables (or so it would seem) and i know i do not want to let anyone see more than that.

harvey

Sep 27 2004, 2:29 PM

Re: database views (server side)

harveyb

from: ny in thinking about it a bit more, perhaps a new method for the select for a particular would be better, that way if someone wants to select for DBA or some other criteria for there database, they could override the settings?

ie:

objWiz.DatabaseSysTableQuery = "Select [Type], [Name], [UID] From SysObjects where [UID] = '1' "

that way it could limit the scope of the available tables, views for reporting but still allow a higher level (full system level) by default. i have seen sybase as well as Sybase Anywhere show many tables you would not want people playing with or reviewing show up when you open a database, but others may want to be able to review all of that for their purposes.

Sep 27 2004, 3:06 PM

Re: database views (server side)

ghost

from: Washington, DC Will make sure this is added including the new property you suggested - There should be a new build later tommorw.

Also, you can email us using the support email: support@aspwebsolution.com - Just add the discussion subject to the email subject.

thanx!

-------------------------
Ghost

Sep 27 2004, 10:10 PM

Re: database views (server side)

harveyb

from: ny great,

thanks

harvey

Sep 28 2004, 9:41 AM

Re: database views (server side)

ghost

Send this author a private message

Visit author's homepage

from: Washington, DC

Try the latest version 2.4.62 build (2.0.0.462).
By default the statements are as follows:

--tables
SELECT [Name] FROM SysObjects WHERE [TYPE]= 'U'AND [UID] = 1 ORDER BY 1
--views
SELECT [Name] FROM SysObjects WHERE [TYPE]= 'V'AND [UID] = 1 ORDER BY 1

You can still overwrite them using the following properties:
objWiz.DatabaseTablesQuery = "SELECT ..." and objWiz.DatabaseViewsQuery

Let me know if this helps..

-------------------------
Ghost

Sep 28 2004, 11:16 PM

Re: database views (server side)

harveyb

from: ny hi,

if you repair instead of removing and reinstalling, you get a network read error.

what is the database name supposed to be for this setting?

should the default behavior show tables, views or tables and views?

thanks
harvey

Sep 29 2004, 5:54 AM

Re: database views (server side)

ghost

from: Washington, DC if you repair instead of removing and reinstalling, you get a network read error.
This is a windows problem I suggest you uninstall the previous version before installing any newer version.

what is the database name supposed to be for this setting?
Which database? Use a connection string as before

should the default behavior show tables, views or tables and views?
Not sure what you mean. Try running your reports page as before

-------------------------
Ghost

Sep 29 2004, 6:54 AM

Re: database views (server side)

harveyb

from: ny -->if you repair instead of removing and reinstalling, you get a network read error.
-->This is a windows problem I suggest you uninstall the previous version before installing any newer version.

[hb] this worked, it might be a good idea to put this in a readme or faq ...

-->what is the database name supposed to be for this setting?
Which database? Use a connection string as before

[hb] this is my connection string stuff:
'Make sure you initialize before adding any HTML text
objWiz.Init("PROVIDER=ASAProv;DATA SOURCE=research-frodo;")

objWiz.DatabaseType = ""

that is still showing me just the tables, no views.

if i set objWizDatabaseType = "SYBASE"

it does the same thing. what do i need to put into the asp to show both tables and views? is there a new DatabaseType for Sybase anywhere where the query below changes to the default based on sybase anywhere? keep in mind that sybase and sybase anywhere are not necessarily the same.

-->should the default behavior show tables, views or tables and views?
-->Not sure what you mean. Try running your reports page as before

[hb] you have shown two new properties,
objWiz.DatabaseTablesQuery = "SELECT ..."
objWiz.DatabaseViewsQuery = "SELECT ..."

if i change the asp to include the following line:

objWiz.DatabaseTablesQuery = "SELECT [Name] FROM SysObjects WHERE ([TYPE]= 'U'or [TYPE] = 'V') AND [UID] = 1 ORDER BY 1"

i get queries and views together shown as tables. where does the second property come into place? is there a way to show them separately like it does for SQL Server? ie:

(with above modified 'tablesquery')

Tables reports New Group

All Synthroid Tabs general reports
BG All Rx Per Month Drugname
BG Summary Rx Per Month Drugname TP
Blood Glucose Rxs For Period
Blood Glucose Strips
Classes
Classes with GPI
DEAPrescriber
DrugInfo
GPI _Descriptions
MDDBInfo
Pharmacies
Research
Research with Chain ID
Statins
Statins All Rx Per Day
Statins All Rx Per Day Drugname
Statins All Rx Per Month Drugname
Statins CC Rx Per Day Drugname
Statins New Rx Per Day
Statins New Rx Per Day Drugname
Statins Old way
Statins Rxs For Period
Statins TP Rx Per Day Drugname
Synthroids
test

(desired below)

Tables views reports New Group

All Synthroid Tabs general reports
BG All Rx Per Month Drugname statins
BG Summary Rx Per Month Drugname TP statins all rx per day
Blood Glucose Rxs For Period statins all rx per month drugname
Blood Glucose Strips statins cc rx per day drugname
Classes
Classes with GPI .
DEAPrescriber .
DrugInfo .
GPI _Descriptions
MDDBInfo
Pharmacies
Research
Research with Chain ID

Sep 29 2004, 7:47 AM

Re: database views (server side)

ghost

from: Washington, DC Yes. Set both properties as follows:

--tables objWiz.DatabaseTablesQuery = "SELECT [Name] FROM SysObjects WHERE [TYPE]='U' AND [UID]=1 ORDER BY 1" --views objWiz.DatabaseViewsQuery = "SELECT [Name] FROM SysObjects WHERE [TYPE]='V' AND [UID]=1 ORDER BY 1"

-------------------------
Ghost

Sep 29 2004, 8:44 AM

Re: database views (server side)

harveyb

from: ny it appears to only show the tables either way.

Sep 30 2004, 10:26 AM

Re: database views (server side)

harveyb

from: ny is there something else you want me to try? for the time being, i will combine the querys in the tables part so i have the views to work with as well. but i imagine there is still something wrong.

harvey

Sep 30 2004, 11:36 AM

Re: database views (server side)

ghost

Send this author a private message

Visit author's homepage

from: Washington, DC

Run the following query and let me know what TYPE,UID your views have:

SELECT [TYPE], [Name], [UID] FROM SysObjects ORDER BY [TYPE], [Name]

-------------------------
Ghost

Sep 30 2004, 11:59 AM

Re: database views (server side)

harveyb

from: ny TYPE,Name,UID
'U','Classes',1
'U','DEAPrescriber',1
'U','DrugInfo',1
'U','GPI _Descriptions',1
'U','MDDBInfo',1
'U','Pharmacies',1
'U','Research',1
'U','Synthroids',1
'U','test',1
'V','All Synthroid Tabs',1
'V','BG All Rx Per Month Drugname',1
'V','BG Summary Rx Per Month Drugname TP',1
'V','Blood Glucose Rxs For Period',1
'V','Blood Glucose Strips',1
'V','Classes with GPI',1
'V','Research with Chain ID',1
'V','Statins',1
'V','Statins All Rx Per Day',1
'V','Statins All Rx Per Day Drugname',1
'V','Statins All Rx Per Month Drugname',1
'V','Statins CC Rx Per Day Drugname',1
'V','Statins New Rx Per Day',1
'V','Statins New Rx Per Day Drugname',1
'V','Statins Old way',1
'V','Statins Rxs For Period',1
'V','Statins TP Rx Per Day Drugname',1

Sep 30 2004, 1:13 PM

Re: database views (server side)

ghost

Send this author a private message

Visit author's homepage

from: Washington, DC

Try this and let me know if it helps

<%

  Dim strConn,  objWiz
  Set objWiz =Server.CreateObject("AspWebSolution.ReportWizard2")
strConn = "PROVIDER=ASAProv;DATA SOURCE=research-frodo;"

objWiz.DatabaseTablesQuery = "SELECT [Name] FROM SysObjects WHERE [TYPE]= 'U' AND [UID]=1 ORDER BY 1"

objWiz.DatabaseViewsQuery = "SELECT [Name] FROM SysObjects WHERE [TYPE]= 'V' AND [UID]=1 ORDER BY 1"

objWiz.DatabaseType = "SYBASE"
objWiz.Init(strConnectionString)


objWiz.DisplayReports

  Set objWiz =  Nothing

%>

-------------------------
Ghost

Sep 30 2004, 7:48 PM

Re: database views (server side)

harveyb

from: ny still the same, just the tables show.

harvey

Oct 1 2004, 10:37 AM

Re: database views (server side)

ghost

from: Washington, DC Can I see your source code?

-------------------------
Ghost

Pages: (2) [1] … Last »