Ansi Sql

[xavito]

Hi, paws.
I'm working in an ASP.NET WebApp connecting a Progress 8.3B database.
I'm using an Openlink ODBC connector but I have some issues.
It looks like suddenly we lost the communication with the broker (ODBC gateway). 1) Do you know what's happenning ?
We're working with WebServices so we're sending SQL Statements and I'd like you to help me to find more info about SQL Standard Statements (I'm working with SQL Server and Progress so I'd like to find neutral SQL statements for both, for example: LIKE in SQL Server works but not in Progress (through ODBC), 2) what's the neutral statement (where can I find more info) ?
Another, 3) is there any graphical query analyzer for Progress ? Like the MS Query Analyzer ?
The last, I'm trying with some statements like CONTAINS but it's very slow, do I have to work with another statement ? Any performance clues ?
Thanks a lot.

 

[BCM]

I recommend that you create a Linked Server on the Sql Server. Have the Linked Server point to the Progess database. Use pass-through queries to access the data in the Progress database. You should know that for SQL access to the Progress database, Progress version 9.x is much better than version 8.x, and Progress version 10.x is supposed to be far superior to version 9.x. Because you are using version 8.3, you will be limited to SQL-89 compatible SQL statements.

* Select statements should be as simple as possible
* The CONTAINS function will be slow. If you are looking the the character string 'redcat' within a column, try LIKE '%redcat%'. This works similar to the OS command DIR *redcat*.
* Ask less of your Progress database and more of your SQL Server.
You can select a recordset into a Sql Server table from a Progress database. Then you can use Sql Server to work with the selected recordset, possibly indexing the set on Sql Server prior to joining it to other recordsets.
* If you need large numbers of records returned from Progress, try to break the retrieval into smaller subsets that can be added to (concatenated) on the Sql Server.
* Avoid using built-in Progress functions in the SQL query.

 

[xavito]

Hi, BCM.
Because of my client policies is impossible to change the database version.
I've been trying to link the Progress database to my SQL Server but I've been getting some errors. What properties should I configure and how I should call the tables (I'd tried "select * from linkedserver.schema.owner.table" but it's not working) ?
Dou you know where can I find info about SQL-89 syntax and reserved words ?
Thanks a lot and best regards ...

 

[BCM]

What ODBC driver are you using, Merant 3.60?

You must have an ODBC DSN created on the machine that is your SQL Server.

Have your SQL Server dba setup a Linked Server to the Progress database. Use the SQL Server Enterprise Manager. Under Security you will find the section for Linked Servers. Right click on the Linked Servers and choose New Linked Server. Give the Linked Server a name. The Server Type is Other data source. Set the Provider name to Microsoft OLE DB Provider for ODBC drivers. Set the Product name to MSDASQL. Set the Datasource to the name of the ODBC DSN on this box.

That is all it takes.

A pass-through query entered from the MS Query Analyzer would look like this:
select * from openquery(LinkedServerName,'select * from pub.ProgressTableName where charcolumn=''text value''')
order by SomeColumnName

!!! The actual SQL Statement passed-through to Progress must be a literal string. It cannot be a variable or string expression. This is a requirement (limitation) of SQL Server. The quotes surrounding 'text value', above are not double quotes; they are two single quotes. This is because whenever a single quote appears within a quoted string, you must use two single quotes.

Find any documentation on SQL-89 regardless of whether it is from Progress. Keep in mind that Progress seems to have a closer relationship to Oracle than to SQL Server. If you must perform joins on the Progress database, use Oracle syntax to place the join conditions within the WHERE clause:
select a."column1", a."column2", b."column10" from pub."table1" a, pub."table2" b where a."column1" = b."column3"

 

[xavito]

BCM, I'll check these settings and get back.
Thank you very much.

 

[xavito]

BCM, we're using OpenLink Generic ODBC Driver 5.0.
Have you use it ?
I've seen some memory leak or bug after some use ..

 

[BCM]

We tried OpenLink. We decided on the DataDirect ODBC driver supplied with our Progress database. The DataDirect driver was formerly known as Merant. However, go ahead and try to establish a Linked Server using the OpenLink ODBC driver.

 

[xavito]

We propoused DataDirect but our customer already had Openlink and he preferred not to make an extra expense.
I'll try and let you know.
Thanks again.