next »

[Paul M.]

I am working the leap from BDL 2.1 to 3.20 and must be missing something simple.

my code:
declare a_curs cursor for select * from PermitList where PermitCode = 'BLUE'
foreach a_curs into r_permits.*
...
end foreach

returns
Program stopped at 'parking.4gl', line number 70.
SQL statement error number -206 (-208).
[FreeTDS][SQL Server]Invalid object name 'permitlist'

modified code
let qrystr= "select * from PermitList where PermitCode = 'BLUE'"
prepare q1 from qrystr
declare a_curs cursor for q1
foreach a_curs into r_permits.*
...

works just fine. What am I missing?

I am connecting to SQL Server 2012 with BDL 3.20.11 on RHEL 7.9
fglprofile has the driver name and driver   (used msnc and mftm)
Got my fgldbsch  working fine.
my 4gl code links with libfgl4js.42x for  utilities. 

[Sebastien F.]

Hello Paul,

I assume you have a case sensitive collation (CS option) with your SQL Server database, can you confirm ?

Check what SQL is produced by the compiler from static SQL by doing fglcomp -S packing.4gl ...

Recent Genero versions convert table and column names to lower case for static SQL.

Dynamic SQL (PREPARE) text is not touched by the compiler.

Even if Genero BDL allows to write:

   DECLARE c1 CURSOR FROM "SELECT ..."

I think you better keep using static SQL, and either change le SQL Server DB collation to case-insensitive (CI option), or rename table and column names to use lowercase only.

Depends if data search must be case sensitive or case insensitive.

Sorry for that, but SQL Server team did a strange decision to bind DB object name case sensitivity to data case sensitivity.

Consider also to upgrade you SQL Server 2012 to a more recent SQL Server version such as 2019.

Seb

[Paul M.]

It was the collation.   I changed to CI and everything worked just fine.   I will need to consider this in future development.

[Sebastien F.]

Paul,

I am glad that you could solve this.

Just some precision so we can properly document:

What "2.1" version did you use before? I assume it's a 2.10.xy ?

I have tested the oldest version I have on my laptop (2.11.14) and that version is already converting tab/col names to lowercase.

sf@toro:/tmp$ fglcomp -V
fglcomp 2.11.14 build-1169.162
Built Apr  7 2009 11:11:48
(c) 1989-2009 Four J's Development Tools

sf@toro:/tmp$ cat z.4gl
MAIN
    SELECT * FROM PermitList WHERE PermitCode = 'BLUE'
END MAIN

sf@toro:/tmp$ fglcomp -S z.4gl
z.4gl^2^SELECT * FROM permitlist  WHERE permitcode = 'BLUE'


So if fact it's not in "recent" FGL versions as I wrote, it's there for a while now...

I can dig in our source versioning system to figure out the exact version where this change/fix occurred.

Seb

[Sebastien F.]

FYI:

We have tested FGL versions down to 1.20.1a, and seems the conversion of table/column names in static SQL has always existed.

c:\fjs\tests\cases\sf>fglcomp -S test.4gl
test.4gl^5^SELECT COUNT(*) FROM customerinfo  WHERE custtype = 'BLUE'

c:\fjs\tests\cases\sf>fpi -l

Four J's Development Tools
Genero Business Development Language Version 1.20.1a
(c) 1989-2003 Four J's Development Tools

 ...


So your Genero programs (pcode) have always used lowercase tab/col names and the only reason was the CS/CI option difference in your SQL server database collation.

You may want to confirm that you have used different SQL Server databases/collations in the "2.1" and 3.20 configuration.

Seb

[Paul M.]

We had been using 2.10.4 with INFORMIX.  With the multitude of Databases I deal with, I just never noticed all the genero based databases/tables were in lower case.  This was a new program for a new database. I appreciate the quick response.

[Sebastien F.]

For the record, each DB engine brand has it own rules to resolve SQL object names.

Some DB engines are case insensitive.

Some like Oracle DB are case sensitive, but convert to UPPERCASE automatically, if you do not enclose in " " quotes
So for ex:
   create table customer      =>  converted to CUSTOMER in the table definition
   select * from customer     =>  converted to CUSTOMER and matches definition
   select * from CuSToMer    =>  converted to CUSTOMER and matches definition
   select * from "CuSToMer" =>  fails because of mismatch

Others DBs use same technique, but convert to lowercase automatically ...

So at first glance it looks case insensitive but it is not. That's my point.

Using lowercase tab/col names everywhere is best portable solution to me.

Take some time to read or SQL portability doc if not yet done ;-)
https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_sql_programming_084.html

Seb