Accessing DB data into a CSP page

[mdwyer]

I am looking for a model to copy where a CSP page accesses data from a database. Preferably the DB is Oracle, but ODBC access is kind of assumed, so Oracle-specific code is not necessary. Cold Fusion may have the capability needed, but I'm not conversant enough to build a page with it.

Thanks for any help!

- Mike

 

[Turkbear]

Hi,
Since CSP is just a variant of ASP any ADO type code will get you the data - for example:
( This uses the OLE DB method, not ODBC- much betetr performance, in my experience)

<%
' Create and establish data connection
Set objDC = Server.CreateObject(&quot;ADODB.Connection&quot;)
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30

'Code to connect to Oracle Warehouse
 objDC.Open &quot;Provider=MSDAORA.1;Password=secret;User ID=wmsreport;Data Source=whs1;Persist Security Info=TRUE&quot;
SqlStrSup = &quot;Select posn_nbr,empl_nm  from warehouse.metro_sup_link order by empl_nm&quot;
SqlStrppe = &quot;Select to_char(pay_end_dt,'YYYY,MM,DD') from warehouse.hr_ppe_dates where pay_end_dt between (sysdate - 180) and sysdate&quot;
Set rs1 = objDC.Execute(SqlStrSup)
Set rs2 = objDC.Execute(SqlStrppe)
arrSupData = rs1.GetRows()
arrppeData = rs2.GetRows()
   rs1.Close
   Set rs1 = Nothing
   rs2.Close
   Set rs2 = Nothing
   objDC.Close
   Set objDC = Nothing
iRecFirstSup   = LBound(arrSupData, 2)
iRecLastSup    = UBound(arrSupData, 2)
iFieldFirstSup = LBound(arrSupData, 1)
iFieldLastSup  = UBound(arrSupData, 1)
iRecFirstppe   = LBound(arrppeData, 2)
iRecLastppe    = UBound(arrppeData, 2)
iFieldFirstppe = LBound(arrppeData, 1)
iFieldLastppe = UBound(arrppeData, 1)
%>

The above code creates 2 arrays with data from the Oracle database tables - they can then be used in a variety of ways on the page ( to populate a drop-down option list for parameters, for instance -
that is what this code is used for in my app).

 

[mdwyer]

Thanks, Turkbear!

I found similar code on another web site and developed the following code. It seems simpler, but works for my drop-down parameter lists. Would you care to critique in case I may have missed something, like the timeout setting?

[This seems like such a straightforward concept. I'm amazed that Crystal doesn't offer it in CE. Of course, a quote for 24 hours - and $5100 - to custom-develop the feature is probably a good explanation.]

Also, my next goal is to write the user's selection to a cookie and seed the drop-down from that. I'm close, but no cigar yet. If someone's got code to share on that, it would be helpful. I am wading through the logon token code, but another approach may help me see the principles more clearly.

The following code is in schedule.csp, parameter dialog section, in place of an <input> tag for the parameters:

Response.Write(&quot;<select name='discretesingle&quot;+i+&quot;'>&quot;

// This added code provides the <option> elements to the
// parameter <select> element, using a real-time query to
// the database.

// Establish a database connection
var connSimple = Server.CreateObject(&quot;ADODB.Connection&quot;
connSimple.Open(&quot;DSN=LCW&quot;, &quot;EFBRS&quot;, &quot;EFBRS&quot; // (ODBC)

// Query the database for a View named according to the
// parameter field being populated (E.g., PARAM_ORG)
var results = connSimple.Execute(&quot;SELECT * FROM PARAM_&quot; + prompti.ParameterName);

// Loop through the rows and display them in <option> tags
// (The variables are used to make the tag code legible; it
// may not be the most efficient approach.)
var pval;
var pname;
while ( ! results.EOF )
{
pval = results.Fields(&quot;PARAM_VALUE&quot.Value;
pname = results.Fields(&quot;PARAM_NAME&quot.Value;
Response.Write(&quot;<option value='&quot; + pval + &quot;'>&quot; + pname);
results.MoveNext
}

// Close the DB cursor and connection. Release memory
results.Close;
connSimple.Close;
results = &quot;&quot;;
connSimple = &quot;&quot;;

Response.Write(&quot;</select>&quot;

 

[Turkbear]

HI,
Yours looks OK
( there is more than one way to skin a cat
- it still tastes like chicken)

..I just kept all the settings I have been using for my ADO objects in other apps, so I am not sure if any harm is done by leaving some of them, like Timeout, off..

I like using arrays because I use mostly multiple drop-down lists for my parameter selections and using well named arrays, populating all of them at once and closing the connections seems a little 'cleaner' and more efficient in my case...

Have fun...