Uniface MS - SQL remote database connection

Author: stephane.allam@issa.ch (StephAll)

Hi All, Did someone actually performed a connection to a remote MS-SQL from uniface?   Stéphane

10 Comments

  1. Hi Stéphane Uniface uses ODBC to connect to MS-SQL. If you can test the connection from \windows\system32\odbcad32.exe (on Win64 it's \windows\syswow64\odbcad32.exe) - then you should be able to get to MS-SQL. Once you can connect to MS-SQL using the ODBC test connection, conneting from Uniface is easy. You'll need an entry in your [DRIVER_SETTINGS] section in ASN file: MSS U5.0 ; this denotes the Uniface driver version usys$mss_params iso:ru, ents:lower, procs:off ; these are driver instructions In the [PATH] section: $MSS MSS::|| $DEF $MSS and that's about it....


    Author: Knut (knut.dybendahl@gmail.com)
  2. KnutD said \windows\syswow64\odbcad32.exe

    Hi Stéphane Just in case, you ask yourself, why did Knut wrote:"\windows\syswow64\odbcad32.exe"? That's easy to explainSmile UnifAce uses the 32-bit ODBC drivers. On a 64-bit machine, Windows will normaly shows you the 64-bit ODBC setting mask, but you need the 32-bit one. And all 32-bit programms are "hidden" in this SYSWOW64 directory. SIC! WOW stands for WindowsOnWindows, but why 64? Don't ask meLaugh Ingo


    Author: istiller (i2stiller@gmx.de)
  3. WOW stands for WindowsOnWindows, but why 64?   WOW64 should be read as "Windows On Windows64"


    Author: ulrich-merkel (ulrichmerkel@web.de)
  4. Thank you so much guys for your answers. I managed to connect but when trying to use this connection in my uniface program I get this error: SQLServer System Information : ------------------------------------------------ Driver : SQLSRV32.DLL 06.01.7601 Server name : TIWSDBTE01\DEV Data source : tiwsdbte01 ODBC Version : 03.80.0000 Driver ODBC Version : 03.52 SQLServer2 Database Information : ------------------------------------------------ Database name : XHRMDTAISSA User name : dbo DBMS name : Microsoft SQL Server DBMS Version : 11.00.3000 (Full server version not available, last SQLResult = -1) select from hrmfie where 1=0 I/O function: O, mode: 0, on file/table: hrmfie 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'from'. This occurs after a simple READ statement. Any clue?


    Author: StephAll (stephane.allam@issa.ch)
  5. Are you making a:

    retrieve/e "HRMFIE"

      or are you doing a simple "sql" instruction? Make sure you are creating the sql script for the table from Uniface create scripts utility. It will generate 5 stored procedures required to insert/select/update/delete the data from sql server. If you are using a simple sql instruction make sure it has the right syntax. i.e.:

    sql "select count(*) from hrmfie", "DEF"

      Regards


    Author: danieto (alejandron@afsi.com)
  6. I suspect it's something like, you have 'automatic' or 'user defined' fields set for the entity on the component, and the fields then selected are, for whatever reason, all non database. That read statement is designed to verify the field list is correct (the 1=0 prevents it from returning any data, and it's therefore there to get the field list). If the (database) field list required by the component evaluates to null, that could cause this issue. (Possibly), although I would have thought the select always included the primary key for the table (it does have a primary key defined?)   Regards,    Iain


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  7. Hi danieto, Thank you very much for your answer.Actually,I use an Alias to perform a simple retrieve  from the remote HRMFIE,named CGFHRMFIE. [ENTITIES] cgfhrmfie.cg $TI:hrmfie.*   I didn't created the script indeed so I guess I'll try that first.Is it a problem if the Uniface entity and the sql database doesn't have the same name?   Stéphane


    Author: StephAll (stephane.allam@issa.ch)
  8. and WHY did Microsoft decide to call the 64bit drivers odbcad32.exe ??? -  the two versions 32bit/64bit look identical, so it is not possible to tell by looking at them which one you have opened.


    Author: obiron (aaron.reese@sanderson.com)
  9. Guys,I finally got it working. Actually the Uniface entity I created got his definition directly from the sql server table...And that table had no primary key! I know it's weird but anyway...It's working! Thank you guys!


    Author: StephAll (stephane.allam@issa.ch)
  10. istiller said
    KnutD said \windows\syswow64\odbcad32.exe
    Hi Stéphane Just in case, you ask yourself, why did Knut wrote:"\windows\syswow64\odbcad32.exe"? That's easy to explainSmile UnifAce uses the 32-bit ODBC drivers. On a 64-bit machine, Windows will normaly shows you the 64-bit ODBC setting mask, but you need the 32-bit one. And all 32-bit programms are "hidden" in this SYSWOW64 directory. SIC! WOW stands for WindowsOnWindows, but why 64? Don't ask meLaugh Ingo

    Actually, if you are using the 64 bit version of Uniface, then Uniface uses 64 bit ODBC libraries.  if you are using the 32 bit version of Uniface on 64 bit version of Windows, then it uses the 32 bit ODBC client layer. 


    Author: Adrian Gosbell (adrian.gosbell@synapse-i.jp)