Need ODBC help New to Progress 3

[franklin1232]

We are currently running 9.0B, but will be upgrading to 9.1c in the next couple of weeks. We are using Merant 3.5 drivers and they stink. We have many application and other databases that need data from Progress. Problem is the speed is so slow I end up downloading the data a couple of times a day instead of in real-time.

Is there a better driver or better way?

Most of the outside applications are written in Visual Basic, SQL server, Access.

 

[custersb]

Hi, I'm making progress with my connection to Progress , but now I getting the error:
Table/View/Synonym not found (7519)
I've read through the progress Knowlegde Base, but I can't find a way to find my datafield. It is giving me the above error, but I want to select a field from the database with the help of the properties field of a textfield. The datasource I can select!! So that's not the problem. The knowledge base says I must use a prefix, but I don't know how I must add that prefix to my program.

 

[custersb]

Th error is solved, but now I want to get information from the database with SQL. I'm trying to search the name of the customer when I'm having the phone number. When I fill in a phone number I press a button, then a SQL statement has to return the name of the customer. My code:

Private Sub Command1_Click()

Dim db As Database
Dim rs As Recordset
Dim SQLString As String

Set db = OpenDatabase("Path to database&quot

SQLString = "SELECT customername From customertable Where((phonenumber)=" & Text3.Text & &quot"

Set rs = db.OpenRecordset(SQLString)

If rs.BOF = True And rs.EOF = True Then
MsgBox ("Can't find number in database!!&quot
rs.Close
db.Close
Exit Sub
End If

Text1.Text = rs.Fields("Customername&quot

End Sub

Is this the right way to get it from the database, or what do I have to change? I'm getting the user type not defined at db As Database.

 

[franklin1232]

Dim Db as DAO.Database
Dim RS as DAO.database

If you use DAO the openrecordset method works ADO uses something alittle different. Plus SQL 92 queries are not the same as Jet or Msaccess queries. I suggest getting the SQL92 guide from Progress's website.

There are a number of different dataset types out there. DAO, ADO, and RDO. By not defining the variable as a specfic type you get this error. I would really recommend getting a book or doing some research on these different types each has it's advantages and disadvantage. I will say that ADO is the newer and most recommended way.

 

[Bernie5412]

I'm trying to connect to Progress via VBA ADO and the Merant SQL92 ODBC DSN I setup up. I get an error that says 'cant connect to the db, more info is needed on the connect string'.
Here is my vba code:

Set db = New Connection
db.Open "DSN=pv92SQL997base;owner=base,uid=cnsstaff;pw=****"

Do I need to put a Provider= on the connection string? Anyone know what the it should be for Meriant SQL92? Or is there another parameter I'm missing?

Thanks for the help.

 

[TimDude]

Quick steps to get ADO/ASP/VB connections working.


REQ: WindowsServer, Progress 9.1C

Note: all of these answers are assuming a Windows Server, however, several ideas will work on other OS's

1. install a full progress installation on server running windows NT4.0Sp5 or Above.

2. make sure you have a progress client network license.

3. your progress server will need to be started to handle both TCP and SQL connections. (see manual/kb for instructions or use Progress Explorer to start/stop the servers)

4. the username that was logged into windows and created the progress DB you wish to connect to is the Main owner of the DB and needs to be used to complete a ADO/ASP ODBC connection. Use this user name in your connection string... see below.

5. since you did the full install you will find the Merant SQL-92 ODBC driver available for a data source. Make a SYSTEM DSN with this driver and enter the DS name, DS Description, hostname, portnumber(port number can be same as TCP port or can be changed in the server command parameters), and database name, leave userid blank.

6. create an .asp file with some sort of data connection information ... such as below...

7. The first time you make a connection to the database there will be a delay of 2-3 seconds.. after that it should fine.

8. Be sure you have enough servers in your server start. or this connection will tie up all your DB ... you will need to use ProMon to disconnect that user.

9. Thats it... the rest is pretty much SQL / VB ...
10. Good Luck....

This took me a bit to get all the steps down... but the above should get you in the right direction. Read subnotes as well.

Thanks and hopefully this will help .... Tim


example.asp code(taken from progress kb with small changes)

'function to get error codes and display
(see kb page for actual code)

'actual db connection string

myDSN = "dsn=mydbname;uid=dbowner;pwd=;"
mySET = "set schema 'pub'"
mySQL = "select * from table "

set conntemp = server.createobject("adodb.connection&quot
conntemp.open myDSN
conntemp.Execute(mySET)
set rstemp = conntemp.Execute(mySQL) 'put results into record set '

' HERE you would work with your data.

DisplayODBCErrors() 'function from above'

'close db connection
set rstemp = nothing
conntemp.Close


Subnotes:
Page Notes:
1. ALWAYS remember to close your DB connection when you are done with it, and set it to nothing.

SQL Queries:
1. its a good idea to use a "set schema 'pub'" as first command, this will eliminate the "Table/View/Synonym Error" that many have seen.

2. when accessing table names such as 'table.customer-name' its important to have the quotes around the hypened name. such as
"select * from table."customer-name" where "cust-id"='3' "

 

[Bernie5412]

Thanks , I'll give this a try. I'm doing this all in VB so I'll tr to extropolate from the asp code.
Thanks for the help
Bernie

 

[harebrain]

In your VB program, add a project reference to "Microsoft ActiveX Data Objects 2.6 Library" (or whatever version of MDAC you have installed.) Then, in the above code, replace the line

set conntemp = server.createobject("adodb.connection&quot

with

Set conntemp = New Connection

You want to reference the ADO components on the machine your program runs on, rather than across the network on a Web server as you would with ASP.

--David

 

[kikigirl]

I've read in this thread that there is an update to the Merant 3.6 ODBC driver for Progress. I've searched Progress' website as well as Merant. Is there an update, to what version, and where can it be found?

Thanks!

 

[Kassad]

Could someone tell me how to obtain the Progress Explorer Tool? I have software that uses a Progress database but the database is installed as part of software and I don't believe that the Explorer Tool is part of it.

 

[DrBean]

I'm home!

We are running an ERP system from Epicor Vantage on Progress 9.1d, Merant SQL92 etc., Crystal Reports 8.5, ODBC, and ASP. I'm currently reading the years of posts for this thread.

My contributions: CR works great for us, Progress Explorer setup & ODBC connections also works fine. Our ERP implementation has been underway for over a year, a lot of customizing.

I have 5 years experience programming with PASCAL and about a year with ASP/VB/JavaScript. Plus, a knowledge of manufacturing methods

My questions:

1. I get an error trying to delete a record using Progress's SQL Explorer, even though it reads the record fine using the same where statement. Note: one of the fields in the where statement is blank. For more details see dbforums.com thread "Deleting Progress 9.1d ODBC ASP." I may copy the information here.

2. Using MS Access with a linked ODBC table, I blanked out a key field of several rows, but CR and Vantage, both, see my new "blank fields" as different than the "blank fields" created by Vantage. So, selecting by field="" only gives me the Vantage fields, not the MS Access edited fields.

I hope to be able to contribute to everyone's questions.

 

[ceesql]

Just finished reading the thread-Wow. We are in the midst of converting a Platinum DB (SQL) to Apprise (Progress V9.1D08). Part of this coversion is having to re-write all Crystal Reports 8.5. The registry has been updated to allow left joins; Using ODBC Merant 3.60 32-bit Progress SQL92 v 9.1D driver.

1)Currently using WinSQL to query tables in the database - is there a way to register the Progress DB in Sql 2000 Enterprise?
2) Notice that report processing is much slower and wonder if this might be caused by the way the tables are linked in the report.