Q: ADO, RDO, OLE DB, what to use?
Hi!
The scenario is the folowing.
100 terminals, not running terminal server.
Possibility to switch between 10 databases in SQL server.
One folder with CRW reports for all 10 databases.
What is the best way to make the reports change the database upon loading.
I'd rather not using ODBC and those DSN.
By the way is ODBC a faster way to connect to an SQL server rather than
using OLE DB?
Regards
Martin
Re: ADO, RDO, OLE DB, what to use?
Hi Martin -
Others will probably chime in with more, but here's what my little brain
"knows":
RDO is considered obsolete, so I'd avoid it. In the VB world it was last
used in the early 90's.
ODBC is (generally) the slowest way to access data - as it imposes an extra
layer between you & the data.
ADO utilizes OLE DB in the background - so I'll guess that OLE DB is faster.
HTH
Garret
"Martin Arvidsson, Visual Systems AB" <martin.arvidsson@vsab.net> wrote in
message news:pETCTDJJJHA.4840@dacmail.dataaccess.com...[color=blue]
> Hi!
>
> The scenario is the folowing.
>
> 100 terminals, not running terminal server.
> Possibility to switch between 10 databases in SQL server.
> One folder with CRW reports for all 10 databases.
>
> What is the best way to make the reports change the database upon loading.
>
> I'd rather not using ODBC and those DSN.
>
> By the way is ODBC a faster way to connect to an SQL server rather than
> using OLE DB?
>
> Regards
> Martin
>[/color]
Re: ADO, RDO, OLE DB, what to use?
Martin,
ODBC is designed for accessing set oriented databases. SQL Server is a set
oriented database. OLE/DB is designed for accesing any type of data as long
as it can be represented as rows in a table. Both ODBC and OLE/DB are native
API's to for SQL Server. There is a returning story that ODBC is slow, this
is not true when accessing SQL Server data. I would run a test to compare
the two.
I do not know enough about Crystal to say anything about how easy (or
difficult) it would to switch database upon loading.
Regards,
Ben Weijers
Re: Q: ADO, RDO, OLE DB, what to use?
Hi Martin,
To switch database upon loading we use the attached package.
Just base the crystal on the cMycCrystal like below
Object oCrystalReport is a cMycCrystal
Set psReportName to "ScheduledProjects.rpt"
Procedure OnInitializeReport handle hoReport
Forward Send OnInitializeReport hoReport
Send SetConnectionParameters hoReport {DSN} {UID} {PWD}
End_Procedure
...
End_Object
Rename, reuse etc as much as you like.
Cheers,
Marco
Martin Arvidsson, Visual Systems AB wrote:[color=blue]
> Hi!
>
> The scenario is the folowing.
>
> 100 terminals, not running terminal server.
> Possibility to switch between 10 databases in SQL server.
> One folder with CRW reports for all 10 databases.
>
> What is the best way to make the reports change the database upon loading.
>
> I'd rather not using ODBC and those DSN.
>
> By the way is ODBC a faster way to connect to an SQL server rather than
> using OLE DB?
>
> Regards
> Martin
>
>[/color]
//***************************************************************************
//*
//* Class: cMycCrystal
//* Package Name: cMycCrystal.pkg
//*
//***************************************************************************
Use cCrystal.pkg
Class cMycCrystal is a cCrystal
// Construct_Object: Object constructor.
Procedure Construct_object
Forward Send Construct_Object
// Define new Properties: Property {Type} {pxName} {initial_value}
// Create child objects
// Set property values:
End_Procedure
Procedure SetConnectionParameters Handle hoReport String sDSN String sUsername String sPassword
Handle hoConnectionProperties hoConnectionProperty
Variant vConnectionProperties vConnectionProperty
Boolean bAttached
Handle[] hoTables
Integer iTableCount iTableItem
Handle hoDatabaseTable
Get TableObjects Of hoReport to hoTables
Move (SizeOfArray(hoTables)) to iTableCount
For iTableItem From 0 To (iTableCount-1)
Move hoTables[iTableItem] To hoDatabaseTable
If (hoDatabaseTable<>0) Begin
Get create U_cCrystalConnectionProperties to hoConnectionProperties
Get ComConnectionProperties of hoDatabaseTable to vConnectionProperties
Set pvComObject of hoConnectionProperties to vConnectionProperties
Get IsComObjectCreated of hoConnectionProperties to bAttached
If (bAttached) Begin
// Only set the DSN if it is defined (not in native DB2, but is in ODBC)
Get ComItem Of hoConnectionProperties "DSN" To vConnectionProperty
Get create U_cCrystalConnectionProperty to hoConnectionProperty
Set pvComObject of hoConnectionProperty to vConnectionProperty
Get IsComObjectCreated of hoConnectionProperties to bAttached
If (bAttached) Begin
Set ComValue of hoConnectionProperty to sDSN
End
Send Destroy Of hoConnectionProperty
// Reset the Database
Send ComDelete Of hoConnectionProperties "Database"
Send ComAdd Of hoConnectionProperties "Database" sDSN
// Reset the User id
Send ComDelete Of hoConnectionProperties "User ID"
Send ComAdd Of hoConnectionProperties "User ID" sUsername
// Change the password if present, otherwise add.
Get ComItem Of hoConnectionProperties "Password" To vConnectionProperty
Get create U_cCrystalConnectionProperty to hoConnectionProperty
Set pvComObject of hoConnectionProperty to vConnectionProperty
Get IsComObjectCreated of hoConnectionProperties to bAttached
If (bAttached) Begin
Set ComValue of hoConnectionProperty to sPassword
End
Else Send ComAdd Of hoConnectionProperties "Password" sPassword
Send Destroy Of hoConnectionProperty
End
End
Loop
End_Procedure
End_Class // cMycCrystal
Re: ADO, RDO, OLE DB, what to use?
So in all, it is not a downfall to use ADO OLE DB in order to get rid of the
DSN settings on every client.
Since i have a code for changing settings in a crystal report before it
starts...
Regards
Martin
"Ben Weijers" <a@b.c> skrev i meddelandet
news:sY5yD7LJJHA.1980@dacmail.dataaccess.com...[color=blue]
> Martin,
>
> ODBC is designed for accessing set oriented databases. SQL Server is a set
> oriented database. OLE/DB is designed for accesing any type of data as
> long as it can be represented as rows in a table. Both ODBC and OLE/DB are
> native API's to for SQL Server. There is a returning story that ODBC is
> slow, this is not true when accessing SQL Server data. I would run a test
> to compare the two.
>
> I do not know enough about Crystal to say anything about how easy (or
> difficult) it would to switch database upon loading.
>
> Regards,
>
> Ben Weijers
>[/color]
Re: ADO, RDO, OLE DB, what to use?
Martin,
ODBC support DSN-less connnections so that is not a reason to switch...
Regards,
Ben Weijers
Re: Q: ADO, RDO, OLE DB, what to use?
Hi Marco, I am a new SQL user and am attempting to use your procedure, but have an error on this line:
> Set pvComObject of hoConnectionProperty to vConnectionProperty
I get an illegal datatype error, and I am passing the DSN as:
Send SetConnectionParameters hoReport "APPLAUSE-HP\SQLEXPRESS" "Applause" "password"
Could you suggest a solution? Thanks
Re: Q: ADO, RDO, OLE DB, what to use?
Hi
It looks like the automation session pointer is not correct. Have a look in de help for pvcomobject.
Cheers
Marco
Re: Q: ADO, RDO, OLE DB, what to use?
Hi Marco,
I steered Peter to your post, along with other posts by Clive, Chuck, etc. to illustrate techniques for programmatically setting the connection string in a Crystal Report. I also steered Peter into using OLE-DB connection in his crystal reports rather than ODBC.
Does your code example require defining an ODBC DSN on the client computer?
Thanks,
Bob
Re: Q: ADO, RDO, OLE DB, what to use?
Possibly...
Try with one, to test.
I've not setup crystal for a while now...