dblookup join

[RICK L. PRITCHETT]

Does anyone have an example of a join using the advanced database lookup?  Not sure if Cloverleaf does not like my join or if I passed the variable incorrectly.

we are on version 2002.09

Topic

[Jim Kosloskey]

Are you doing SQL or Stored Procedure?

I am currently trying to get advanced lookup via Stored Procedure with a postgresql DB (Cloverleaf 19.1).

If you are doing Stored Procedure, perhaps what I discover could be of use to you.

I have been fighting my way through a lot of unknowns but making some progress (no join in my SP)

If you are using SQL, I am afraid I cannot be any help at this point.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[RICK L. PRITCHETT]

Using SQL. I thought we could use a join in the newer versions.

[Jim Vilbrandt]

Hi Rick,

Here is a simple join for a MSSQL Database:

select d.name_doc, lower(d.feld9) as file_ext, m.name as medium from object65 d, medien m where d.feld32 = <docid> and d.flags in (1,2,16) and m.id = d.medium_doc

The SQL will differ depending on what database you are trying to access (ie: Oracle, MSSQL, SQLite, PostgreSQL, etc.). I always develop the query in the native Database browser first. If it works there, then in should work from Cloverleaf.

Best Regards, Jim

[RICK L. PRITCHETT]

Thanks, guys. I got it working. Jim K. Let us know how the PostgreSQL project goes.

[Jim Kosloskey]

I am glad you got that working.

As for SP postgresql, not good.

First, I am attempting this on CL 19.1 which does not natively support postgresql. We got a driver and installed that, but then needed to install postgresql libraries. Finally got connectivity. A simple query works fine.

But calling a SP fails. After trying everything I could I came to the conclusion the issue is Cloverleaf only supports IN and OUT parameters to a called procedure (at least in 19.1).  That is what is documented. Postgresql does NOT support OUT parameters it only supports IN and INOUT parameters.

CL 2209 documentation still indicates only supporting IN and OUT parameters for SP but CL 2209 claims support for postgresql. I am unsure if a postgresql Stored Procedure with INOUT parameters can be successfully called from CL 2209.

Unfortunately, at this point, I cannot reference the DB I am using in CL 19.1 from CL 2209. Otherwise, I could determine if CL 2209 has the same issue.

If someone out there has successfully called a postgresql SP with INOUT parameters in post 19.1 Cloverleaf, I would be interested in conversing with them.

If I finally determine 2209 capability vis-a-vis postgresql and my memory allows, I will post here.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

Quick update — I got the invocation of a Stored Procedure for Advanced Table Lookup working in CL 2209. I will produce a White Paper on how I did it and what I found out and post it as soon as it is done.

But, in the meantime, if you want to do that (or any DB Lookup that supports Stored Procedures) I am willing to assist.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

I have attached a white paper on using a Stored Procedure for Table lookup. This happens to be for a postgresql db but the techniques should work for any DB that has Stored Procedures.

Attachments:

You must be logged in to view attached files.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Author]

Replies