How to do this with Clarion and PostgreSQL?
Like this:
In a browse it should ‘Just Work’.
I assume you mean putting it all in a SELECT with appropriate Joins?
You might need a little extra on you dct definition.
This is my standard for Postgres tables:
/BUSYHANDLING=2 /ISOLATIONLEVEL=2 /JOINTYPE='ODBC 3.0' /NESTING=TRUE
Note there is also a Windows ODBC Join limitation of 2 or 3 or something. So more complex stuff it’s better to put into a server side view
Carl Barnes: Edit to add links to help for above parameters.
https://clarion.help/doku.php?id=busyhandling.htm
https://clarion.help/doku.php?id=isolationlevel.htm
https://clarion.help/doku.php?id=jointype.htm
https://clarion.help/doku.php?id=nesting_1.htm
/JoinType='ODBC 3.0' is not in help, but it does say
When using ODBC, the ODBC 3.0 standard does support multiple joins, so ODBC 3.0 compliant drivers should not require this switch.
2 Likes
Works in Postgres the same as any other database. If it’s not working for you it’s probably because you have set relationships up wrong in the clarion dictionary. Given what you are showing I would expect that your relacionamento table is a “link” table, and that it has a many-1 relationship with the other two tables, so that you get one row for every row in relacionamento, and that there are no null values allowed in the relacionamento columns that link to the other two tables.
1 Like
What is the error you are getting?
Show us the view that is being generated.
1 Like
And @seanh , excuse the question, but, how do I use the PostgreSQL View in Clarion?
I’m kind of new to ODBC and I’m not very used to it, so I’m learning everything, and you guys from the Forum have been a savior.
You just import the view into the clarion dictionary exactly the same as you would a table. The differences are that: since you are less likely to have an index on a view in the database you have to create them on the clarion side if you need them, plus you will run into far fewer problems if you are able to create a primary key on a columns or set of columns in the view “file” in the clarion dictionary.
Modifying rows in a table based on a view is a bit different. Some views are updateable (i.e. the server will allow you to update them), some are not. In the “not” case, the most likely scenario is that you want to update the “base” table of the view. In your case, you might define the update procedure for your view as UpdateRelacionamento, and when calling it from the browse for your view you do:
rel:tipo = v:tipo
rel:cliente = v:cliente
access:relacionamente.fetch(rel:pk_rel)
so that your update form is editing the matching relacionamente record.
1 Like
Thanks JonW, This information is really good!
And how would you judge the use of ALIAS with ODBC and PostgreSQL? Normal?
You might need an alias occasionally, but I think it should be very rare. For example, if you have a table called People which has columns Mother and Father, which link back to other people in the People table, my preference would be to have a view on the server like:
select p,name,f.name father,m.name mother
from people p
left join people f on f.id = p.father
left join people m on m.id = p.mother
to show a person with their mother and father. If you had to do it in Clarion you would require two alias files.
But if you have a form with two browses based on the same file, like if you were a matchmaker and you had a list of guys and a list of girls from the people file and you were trying to match them up, then you might still choose to do that with an alias on the the people file.
1 Like