Issue with Prop:SQL and AcroPDF.PDF.1 (ACTIVE X - viewing pdf files in clarion window)

questions
1

I am using dummy tables in my software to receive results from various sql queries (written by users, programmers…) without knowing what type of data they are using in queries
Everything is working fine until I use OLE with Adobe PDF Viewer.

Below is an example of simple code illustrating the problem (piece of code)

Database: PostgreSQL, Driver: ODBC, Clarion 6.3

! winapi function declaration

MAP
MODULE(‘WinAPI’)
GetLocaleInfo(LONG,LONG,*CSTRING,*ULONG),SIGNED,PASCAL,RAW,NAME(‘GetLocaleInfoA’)
END
END

! table declaration and variables
Loc:Length ULONG(2)
Loc:Parameter CSTRING(255)

LOCALE_USER_DEFAULT EQUATE(00000400h)
LOCALE_SDECIMAL EQUATE(0000000Eh)

dummy_table FILE,DRIVER(‘ODBC’,‘/NESTING=TRUE /VERIFYVIASELECT=TRUE’),OWNER(Glo:Owner),NAME(‘g.dummy_table’),PRE(dum_tab),CREATE,BINDABLE,THREAD
Record RECORD,PRE()
field_str STRING(100),NAME(‘field_str’)
End
End

CODE

! API FUNCTION - RETURNS DECIMAL SIGN FROM WINDOWS REGIONAL SETTINGS
! TO SHOW WHAT IS MY DECIMAL SIGN IN WINDOWS
S# = GetLocaleInfo(LOCALE_USER_DEFAULT,LOCALE_SDECIMAL,Loc:Parameter,Loc:Length)
Message(Loc:Parameter) ! returns: , → (comma)

CheckOpen(dummy_table,1) ! create when needed and open

dummy_table{Prop:SQL} = ‘select 1.23’
Next(dummy_table)
Num$ = clip(dum_tab:field_str)
Message(clip(dum_tab:field_str)&‘|’&Num$)
! returns 1.23 (dot)
! 1.23 (dot)

! A turning point that changes the behavior of the program
! It can be placed on another window, in another thread - it doesn’t matter
! “Listen all of y’all it’s a sabotage” :wink:
?OlePDF{Prop:Create} = ‘AcroPDF.PDF.1’
?OlePDF{PROP:DoVerb} = -4

! nothing has changed in Windows settings: still , → (comma)
S# = GetLocaleInfo(LOCALE_USER_DEFAULT,LOCALE_SDECIMAL,Loc:Parameter,Loc:Length)
Message(Loc:Parameter)

dummy_table{Prop:SQL} = ‘select 1.23’
Next(dummy_table)
Num$ = clip(dum_tab:field_str)
Message(clip(dum_tab:field_str)&‘|’&Num$)
! returns 1,23 → comma not dot
! 1 → without decimal places :frowning:

Why is this convert dot into comma? I want a numeric value with decimal places. What do I need to do to restore the program’s previous behavior? (Deactivate OLE does not working, Closing window with activex too…)

2

Have you tried closing the dummy table and opening it again before the second query?

3

You can close file, window with ocx, start new thread then open another dummy table make a different query and you will receive comma instead dot. Restart of “app” helps :wink: until you use Adobe Active X. If you have a “numeric” field in dummy table everything is working fine. I do not want to change my “dummy” tables. Perhaps there is some kind of method, option in Adobe or ODBC - I don’t know…

4

It looks like your dummy table is not a dummy table.
For a dummy table, you need to use just one driver string /TURBOSQL

dummy_table FILE, DRIVER('MSSQL', '/TURBOSQL=TRUE')
Record     RECORD
Col1        STRING(20)
          END
         END

and try to use not Prop:SQL but Prop:SQLRowSet

5

{Prop:SqlRowSet} doesn’t work in Clarion 6.3 :frowning:
“Dummy” means no records in the table. /TURBOSQL doesn’t work in this case - allways return 0 instead 1.23 (unless you do Col1 real). Maybe in Clarion 10..11 it works in different way…

6

“Dummy” with /TURBOSQL means that the table doesn’t exist.

As you can even mention there is NO name for that table. Clarion just uses this dummy table to connect (using the current connection) to DB and run a query, return the result (or just run a query). The table doesn’t matter.

ok, tables does matter in sense of structure, if you return results in several columns.

7

Interesting thing. /TURBOSQL works when you declare CSTRING insetad STRING…but ADOBE still ruins this solution…

8

not sure why you mention ADOBE ole etc.

As I understand, you just need to get the correct query return…

9

FYI: My prototype of GetLocaleInfo is this..

GetLocaleInfo(LONG,LONG,*CSTRING,LONG),LONG,PASCAL,RAW,NAME(‘GetLocaleInfoA’)

I pass the last parameter by value, not an address

10

It is not my problem :). I just want to show what decimal point is set in my operating system..

12

Yes I receive a correct query return. But in my system there is an option to view pdf files. I am using PDF ACROBAT READER ACTIVEX. When I create an OLE with {Prop:Create} = ‘AcroPDF.PDF.1’ returned results change. Without ActiveX everything works correctly (I always receive 1.23)

13

:thinking:
really strange.

What result going to be changed??? How it is possible???

Do not use implicit variables - use standard declared variables with the proper type. If you need to pass some number - you can FORMAT() it as needed as well.

14

dummy_table{Prop:SQL} = ‘select 1.23’
Next(dummy_table)
Num$ = clip(dum_tab:field_str)
Message(clip(dum_tab:field_str)&‘|’&Num$) ! returns 1.23 (dot) | 1.23 (dot) → (GOOD)

dummy_table{Prop:SQL} = ‘select 1.23’
Next(dummy_table)
Num$ = clip(dum_tab:field_str)
Message(clip(dum_tab:field_str)&‘|’&Num$) ! returns 1.23 (dot) | 1.23 (dot) → (GOOD)

?OlePDF{Prop:Create} = ‘AcroPDF.PDF.1’
?OlePDF{PROP:DoVerb} = -4

dummy_table{Prop:SQL} = ‘select 1.23’
Next(dummy_table)
Num$ = clip(dum_tab:field_str)
Message(clip(dum_tab:field_str)&‘|’&Num$) ! returns 1,23 (comma) | Num$ = 1 without comma and dot → “X FILES” :wink:

There must be something to do with regional settings in operating system. If I insert code below before initiating OCX then results are good.

Loc:Parametr = ‘.’ ! putting dot in Regional Settings (via API function) as decimal sign before initiating OCX
S# = SetLocaleInfo(LOCALE_USER_DEFAULT,LOCALE_SDECIMAL,Loc:Parametr)

?OlePDF{Prop:Create} = ‘AcroPDF.PDF.1’
?OlePDF{PROP:DoVerb} = -4

dummy_table{Prop:SQL} = ‘select 1.23’
Next(dummy_table)
Num$ = clip(dum_tab:field_str)
Message(clip(dum_tab:field_str)&‘|’&Num$) ! returns 1.23 (dot) | 1.23 (dot) → (GOOD)

For obvious reasons I can not do this (others application in WINDOWS use this setting).
If this option in locale is important then why isn’t it applied by the NEXT function before OCX initialization

15

It seems to me that the bad behaviour there is before you call the OCX. If your decimal setting is comma, then SELECT 1.23 should return 1,23 both times. Your query is passing in a numeric value, and you are asking your database to return that as a string, and it should use the locale info to do that.

What would be consistent with what you see is if the Clarion driver, when it starts up, sets the locale to a decimal to a ‘.’ – which it shouldn’t really do – so you get a dot the first time, then when Acrobat starts up it sets the locale back to the default, a comma, and you get what your system says you should see.

16

But this “bad behaviour” before calling OCX suits me fine ;). I don’t want to receive formatted number. Strange thing is that Acrobat does not physically change the regional settings. I also tried with other api function: SetThreadLocale(LONG),BYTE,PASCAL,RAW,NAME(‘SetThreadLocale’), but that didn’t help in my case.

17

Are you using a Dummy table declared in your dct…

DUMMY FILE,DRIVER('MSSQL etc
Record RECORD
String1 STRING(100)
STRING2 STRING(100)

etc

I did this before /TURBOSQL was available, but I had dummy tables for different return types.

If so, what you could try is a new table for your query…

DummyDecimal FILE, DRIVER( etc
Record RECORD
DummyDecimal DECIMAL(10,4)
etc

And return the value to a decimal data type.

18

Looks like you have a solution. I think my theory about the Clarion driver unilaterally overwriting your Windows default is correct is probably only half-right. If Clarion set the local user default sdecimal to ‘.’ and Acrobat was looking the same user locale default at then I think Acrobat would not be changing anything. But given that your numbers are showing up with periods even when the user default is comma, maybe the thing that clarion is setting to do that is the locale_system_default, and that is what the server users to do the conversion. Then when Acrobat starts up it changes the locale_system_default to the locale_user_default value (i.e. back to the comma from the period it had been changed to by clarion).

Also, you are not the only one with this exact problem:

19

Unfortunately I do not have.I can’t change type of fields in my tables which receives results of sql queries. I think I will use another tool to view pdf.

20

Thanks for this link. It showed me an important thing. It’s a matter of ODBC postgresql driver version. The older one works correctly. Thanks a lot…