[Oledb-dev] PgOleDb and Crystal Reports (and MSSQL)

[Arvo]

Hello Konstantin,

(I started new topic here.)

> you said in one of your previous posts that you were able to make Crystal work with the provider. What did you do? Maybe it would be useful to integrate those fixes into CVS?

I was able to make CR work partially, but this does break driver compatibility.
Currently there are two problems:

1. For specifying database on pg server pgoledb does use property INIT_LOCATION (PgDs.h and related). Unfortunately CR OLEDB connection dialog cannot understand this property; if we use INIT_CATALOG instead, then CR connection dialog allows choose database too.

According to MSDN DBPROP_INIT_LOCATION is typically used as server name (if used at all).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbdbprop_init_location.asp
In role of database (catalog) name should be used DBPROP_INIT_CATALOG. 
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbdbprop_init_catalog.asp

Of course we cannot just change one property to another - all current installations may stop working. If we could develop code, allowing use both properties (note: internally they should point to same value!), then compatibility would be retained.

(As a temporary workaround, we can use Microsoft data link file and connect CR using such file - unfortunately this is not very usable, especially having multiple databases and when needed quickly reconnect CR to another db.)

2. No columns schema support. While adding tables to CR, it does ask for columns information, without schema support CR just errors (or at least doesn't show any colums/fields to choose).

As workaround, I could use direct SQL query (CR has possibility to use direct queries instead of tables) - this way CR didn't ask for columns info. Such workaround could be usable, if we had few static predesigned reports - but our application includes about 200 general reports, additionally some hunderds customer-specific reports, in addition every user (having CR) can design his own reports - there's no way we could redesign all these. And we could loose database independency - currently ALL our OLEDB designed reports work on MSSQL and with MSAccess.

-------------

> What did you mean by "MSSQL server cannot use linked postgresql server"?

Microsoft SQL server has ability to link other servers and use their tables afterwards. Other servers can be any ADO/OLEDB compatible data sources, including ODBC data sources.

In ideal case remote server tables can used like own tables just by specifying linked server name in right place, like next:

  select lc.code, rc.name
  from clients lc inner join remotesrv.mydb.xxx.clients rc on rc.id=lc.id

- but this needs full 3-part naming support (database.owner.table in MSSQL terms). Currently only driver (from what I'm used), which supports this, is MS SQL driver itself :)

Another possibility is to use remote queries, syntactically:

  select code from openquery (remotesrv, 'select code from table')

- where openquery() can be used anywhere instead of local table name (including insert and update and delete queries). This is very inefficient, but does work with most ODBC drivers at least.


I do not remember exactly, what problems did arise. Using PGODBC, there's possible to execute remote queries over link and read returned data, no direct table support is possible. Using PGOLEDB no table support either, but there were some other problems, probably with types. I need to set up some testing environment to specify problems, maybe after week or two.

-- 
Best regards,
 Arvo                            mailto:arvox at hot.ee