NTWS app - changed TPS table to ODBC(PostGreSQL) PROBLEMS?

[Johan van Zyl]

THIS IS A SHOWSTOPPER!
Started this table (tpeople) as TPS and have been using it for a while, have now changed File Driver to ODBC using global connection string.
That table can be browsed but one cannot insert or edit anything. On Save it just bombs out of server app.
This site can?t be reached
localhost refused to connect.
Search Google for localhost Browse People
ERR_CONNECTION_REFUSED
Also, the locator on that browse does not work as it should, if you type one letter it will filter the browse on that one letter, which is still fine, but if you type anymore than one, the browse becomes empty.
people
Locate (Contains) fullname:   
zy
Search
Clear
no people

How now brown cow?

[Bruce]

ODBC to what backend?

Most likely you've not got your connection strings set up and so on.
I recommend making a small desktop app to access the database if you're not sure.
If you have a working desktop app, then moving that to NetTalk is trivial (since your WebServer is just a Clarion program).
If you can't make it work in the desktop then at least you know it's not NetTalk and you can look for the issue elsewhere.

cheers
Bruce

[Johan van Zyl]

PostGreSQL

I have other Browses and Forms connecting to SQL tables in the same app so the connection string is not the problem.
And I can browse the tpeople table
I have a desktop app mx_utl that contains FM3 runtimefilemanager with the same connection string that works perfectly, it shows tpeople with the number of records etc.
Ok now I have created tpeople browse and form, giving me compile errors, complaining about tpe:tpeposition and tpe:date.
Now I remember that I changed the name from position to tpeposition in the tpeople SQL table as well as in the DCT, because in SQL running a script changed the name from position to POSITION, so I assumed it did like position(reserved word?) Now I vaguely remembered that you cannot just do that in a FM3 enabled app. I have now done the OldName thingy in DCT but it does not help. Cannot remember that my only date related field was ever called date, it is addtimestamp? Now how do I fix this. OK now maybe not a NTWS problem but a FM3/DCT problem?

[bshields]

Hi Johan,

Either don't use sql reserved keywords or use the external name in the clarion dictionary to "rename" them in the database but not in your clarion code.

Regards
Bill

[Johan van Zyl]

Thx.
Apparently position is not a reserved word in PostGreSQL(possibly in other flavors) but when I noticed that inside Navicat that position was renamed POSITION I wrongly? assumed the name position is a problem for PostGreSQL, and I maybe overreacted and changed it to tpeposition in DCT and on the backend, without informing FM3 about this!
All my other SQL tables work just fine, only this one that I have messed up does not.
Maybe the answer lies within FM3 Upg table?

[Bruce]

Perhaps your locator appearance gives you a hint.
Turn on the Debugview for the browse filter, and let's see what comes out when you locate on 2 or more characters.

cheers
Bruce

[Johan van Zyl]

Since removing the relationship between tpeople ODBC and the TPS table error 48 at least has gone away, I can now add to and update tpeople in Desktop and web app.

But error 33 still occurs when I run the  Process.

And the locator problem is still there, only locates on one character.

!jvz20180410
loc:flag01 = 0
if clip(tpe:username) = ''
    loc:flag01 = 1
    tpe:username = 'Not SET!'
    !tpe:username = tpe:cellno
END
!IF clip(tpe:password) = '' or len(clip(tpe:password))<4
!    loc:flag01 = 1
!    tpe:password = st.random(4)
!
!END

!
!if tpe:isavailable = ''
!    tpe:isavailable = 'Never'
!    Access:tpeople.Update()
!end
!if tpe:preference = ''
!    tpe:preference  = 'Never'
!    Access:tpeople.Update()
!end
!if tpe:fromdb = 'peopleold'
!    tpe:guid = st.makeguid()
    loc:flag01 = 1
!    Access:tpeople.Update()
!end

if loc:flag01 = 1
    Access:tpeople.Update()
end

[Bruce]

do you have the global clarion option;
global properties, Actions, File Control tab
"Enclose RI code in Transaction Frame" on?
It must be off if you are mixing file drivers (and should be off in a Web app anyway since, ahem, you surely don't allow them to change primary key values...)

cheers
Bruce

[Johan van Zyl]

Can Insert Update in tPeople now.
1. When I update tpeople record the changes do not show when returning to browse - only when clicking Next and Previous.
2. Also locator still only works on one character only.

[Bruce]

Perhaps your locator appearance gives you a hint.
Turn on the Debugview for the browse filter, and let's see what comes out when you locate on 2 or more characters.

cheers
Bruce