BusinessObjects Board

streamlining coding in aspx pages

other Xcelsius - Crystal Dashboard Design
#1

This may be completely the wrong forum for this, but since it’s what I’m using to get data for my xcelsius dashboards…

I get my data by writing SQL and embedding it into an aspx script. I found my basic VB code in the Xcelsius user guide and online, but not being a programmer, I’ve been trying to understand what it’s actually doing.

So I went through the tutorial here: http://www.w3schools.com/ado/ado_intro.asp which tells me that what I’m using is ADO

Here’s my basic code. I connected it to our data warehouse for this test script, but just stuck in select sysdate from dual as a quick and easy test.

<% Response.CacheControl = "no-cache" %>
<% Response.Expires = -1 %>
<%@ Page Language="VB" aspcompat=true Debug="true" validateRequest="false" %>
<%

'----create a database connection &amp; open it
dim oCon as object
oCon = Server.CreateObject("ADODB.Connection")
oCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=xxx;User Id=yyy;Password=zzz;"
oCon.Open

'----create a recordset &amp; open it
dim oRst as object
oRst = Server.CreateObject("ADODB.RecordSet")
oRst.ActiveConnection = oCon

dim sSQL as String
sSQL = sSQL &amp; " select sysdate from dual "
oRst.Open(sSQL)

'----output results as xml
dim sXML as String 
sXML = "<data>" + chr(13)
sXML = sXML &amp; " <variable name=" + chr(34) + "Range0" + chr(34) + ">" + chr(13) 

do until oRst.EOF
sXML = sXML &amp; "  <row>" + chr(13)
sXML = sXML &amp; "   <column>" &amp; oRst("sysdate").value &amp; "</column>" + Chr(13)
sXML = sXML &amp; "  </row>" + Chr(13)
oRst.MoveNext()
loop

sXML = sXML &amp; " </variable>" + chr(13)
sXML = sXML &amp; "</data>"+ chr(13)
Response.Write(sXML)

'----close down
oRst.close
oRst = Nothing
oCon.close
oCon = Nothing
%>

Surprisngly, all this makes sense (I’ve done a bit of VB programming in MS Access in a previous life…).

So far, so good. It works fine, as do more complex scripts - this is my template for new stuff. But it says in the tutorial that on a complex query, this might be time-consuming as it has to loop for every record and write it out. It suggests using GetString, but the example used is to output a table to the screen. What I can’t figure out is how to adapt my code to write the results out in one string which includes all the correct xml tags for xcelsius to read in.

If I could do this, it should make my complex queries run much faster (and some are very complex, passing variables in and out of xcelsius). It would also mean I wouldn’t have to explicitly declare every column in my output which would mean less text in my file and make it easier to debug too.

Googled this to several pages deep and I can’t find an answer. It must be doable, surely?

Debbie

Debbie

Debbie :uk: (BOB member since 2005-03-01)

connectivity issue
how to display qaaws output in Crosstab format
Writing Data from Dashboards to Database using XML
[solved] Dashboard Connectivity with MS SQL Server Queries.
#2

(Disclaimer: I use PHP instead of ASP)

I might look into this more carefully tomorrow, but a simple way to speed it up would be to change

oRst(“sysdate”).value

to something like

oRst(1).Value

This could potentially speed you up by a factor of Log(number of variables).

gaffika (BOB member since 2012-08-10)

#3

This appears to work for a simple query:

<% Response.CacheControl = "no-cache" %>
<% Response.Expires = -1 %>
<%@ Page Language="VB" aspcompat=true Debug="true" validateRequest="false" %>
<%

'----create a database connection &amp; open it
dim oCon as object
oCon = Server.CreateObject("ADODB.Connection")
oCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=xxx;User Id=yyy;Password=zzz;"
oCon.Open

'----create a recordset &amp; open it
dim oRst as object
oRst = Server.CreateObject("ADODB.RecordSet")
oRst.ActiveConnection = oCon

dim sSQL as string
sSQL = sSQL + " select sysdate from dual "
oRst.Open(sSQL)

'----output results as xml
dim str as string
str = oRst.GetString(,,"</row><row>","</row></column><column><row>","&amp;nbsp;")

dim sXML as string 
sXML = "<data>" + chr(13)
sXML = sXML + " <variable name=" + chr(34) + "Range0" + chr(34) + ">" + chr(13) 
sXML = sXML + str + Chr(13)
sXML = sXML + " </variable>" + chr(13)
sXML = sXML + "</data>"+ chr(13)
Response.Write(sXML)

'----close down
oRst.close
oRst = Nothing
oCon.close
oCon = Nothing
%>

Now to try it on something more complex…

Debbie

ETA: Yes - seems to work on more complex queries too.

Debbie :uk: (BOB member since 2005-03-01)