|  | March 2002
| Magic MSsql 7 Gateway for Magic Version 8.30 version 8-20 |
| Dear Developer, This readme file contains the following information about the Magic Database Gateway for MSsql 7. Changes in the current release New features - Physical locking - Temporary tables in TempDB database - MCF owner Magic support for MSsql 7 Known problems We hope you will find working with our product enjoyable and fruitful. |
| ### Changes in the current release: 8-20 QCR 427287 - When working with code page 850 (multilingual) special characters (like '?’) were not displayed correct. 8-19 Fixed QCR QCR 987434: Tables with a memo field longer than 7622 bytes can not be opened. 8-18 New Features Unicode support - Support for NCHAR , NVARCHAR , NTEXT data types. In direct SQL there is no support for this data types. MGTIME - A new Magic data type to map between Magic's TIME datatype and MSSQL's DATETIME datatype. Use the MGTIME keyword in the SQL type. Fixed QCR QCR 426482: Calling an SP with DML statements and a SELECT statement. APG does not create the returned variables in Magic. Executing this task will cause Magic to crash. QCR 773424 : In some cases, working with Team Development when the MCF is stored in MSSQL, Magic crashed with the error message "Unexpected Error #10". QCR 752697: The Magic function DBDISCNT() didn't work properly with SQL SERVER 2000. QCR 983707: When a trigger was invoked and the trigger returned no records, Magic rollback the operation. 8-17 A fault in the LRU command mechanism causes an error message “No more connections available” to appear 8-16 Magic’s previous behavior regarding the Cursor setting was: 4.
| Default: |
|  | Magic opens a server cursor when accessing main tables and a command when accessing a linked table.
| No: | Magic uses commands for main and linked tables.
| Yes: | Magic uses server cursors for main and linked tables.
Magic current behavior changed as follows:
| Default: |
|  | Magic opens a server cursor when accessing main tables in online tasks.
|  | Magic opens a command when accessing main tables in batch tasks.
|  | Magic opens a command when accessing a linked table.
Magic now accesses applications stored in SQL server using array processing. The array size is 64K/MCFRECLEN, specified in DBMS section Fixed QCR QCR # 760462 – Executing Direct SQL in an online task with a comment in the first line return an error massage. QCR # 762124 - Magic creates different snapshot tables for each user when the MCF is stored in MS-SQL and the SQLOWNER parameter is used in the database properties. The problem is when connecting with the NT Authentication for SQL server. Magic adds the SQLOWNER only to the MCF and not to the snapshot table. QCR 744941 –Transaction is turned off ('No') and locking is set to 'No Lock' in an online task created by Magic APG (browse program). Magic crashes when tried to create a record in a table, which has a unique index on an identity column. 8-15 QCR # 995526 - Working with MSSql 2000 and sorting on a field that does not have an index in the database causes error message ‘Multiple-step OLE-DB operation generated errors’. 1. QCR # 751048: When working with MSSql 2000 client, Hebrew data is not entered correctly. 2. QCR # 757184 - Magic gives Error: "Servername : Operand type clash: varbinary is incompatible with float " if one of the table’s column name begins with the characters 'TS'. 3. QCR # 1491 - Executing a certain Direct SQL program returns the error ‘ Incorrect Syntax near "|" ’. 4. QCR # 972760 – a CONTAINS function cannot be executed for more than one word in a Direct SQL program. 8-14 QCR # 426066 - deleting the last record of a temporary table gives an error message. 2. QCR # 439157 - When an Identity column is defined as unique index, creating records and pressing Up arrow duplicates the last entered record on the screen. 3. QCR # 781480 – the Cast and Convert functions performed on a date column cause the optimizer to use a wrong execution plan. 4. QCR # 993652 - Milliseconds are not displayed correct in magic for an alpha 23, DATETIME field. 5. QCR # 504204 - Working with a language different then English - When Viewing by Key (CTRL+K) on a datetime key field, then the error message "Conversion of a char data type to a datetime data type resulted in out-of-range datetime value" appears. 8-13 QCR # 745916 - Importing an mssql7 MCF to a new mssql7 MCF, then a Direct SQL program with Result DB=MSsql returns 'incorrect syntax near ')' '. QCR # 782038 - Magic crashes when using Link without specifying the index for the link, if the linked table contains an alpha field with size>250. QCR # 714353 - When a Record Flush action is executed on a temporary table, Magic displays a DB Error with a blank window. QCR # 428475 - When working with local temporary tables with Cache Strategy as None or Position, adding records with not all the fields entered and trying to scroll the data, an error message appears. QCR # 442755 - Link Join with Range on a Datetime field gives error:'..Incorrect syntax near '(' '. QCR # 968701 - An empty DB error message, and "failed to open table" error appears when browsing a local table with Cursor = No. QCR # 4962 / 581033 - Magic crashes on browsing a table having DATETIME field with DB default value. QCR # 426066 - An online program browsing a local/global temporary table, on trying to delete all records Magic displays a blank db error window when deleting the last record. QCR # 441535 - Using Link Join when the linked table is in Share=Read, If SQL_PHYSICAL_LOCKING=Y, then both tables get locked. QCR # 440028 - When a parent task creates a record with link create, by using 'Identity' field, the subtask fails to do the same for another table. QCR 969941: Creating records in Modify mode in a table with IDENTITY column, each new record overwrites the previous one on the screen (but the records are created properly in the DB). Notes regarding the work with local temporary tables: Local temporary tables cannot be created in a sub-task. In order to create a local temporary table on the fly, you must define it in the DB table of the parent task. Another solution is to work with global temporary tables. Local temporary tables cannot be viewed using Direct SQL. Use global temporary tables instead. Local temporary tables cannot be created when the MCF file is stored in MSsql too. In order to do so, define 2 separate DB entries in Magic - one for the MCF file and another for the data. Another solution is to work with global temporary tables. 8-12 The default mapping for Alpha/Memo attributes have changed. According to the following table: MSSQL 6.5 gateway: Length | Alpha | Memo <=255 | CHAR | BINARY >255 | TEXT | IMAGE MSSQL 7 gateway: Length | Alpha | Memo <=8000 | CHAR | BINARY >8000 | TEXT | IMAGE For example, a table that was defined with an Alpha field with length 400, was mapped to TEXT in the MSSQL 6.5 gateway. In order to continue working with that exact same table using the MSSQL 7 gateway, the SQLTYPE column of that field should hold ‘TEXT’ in it. When defining an Alpha field with a length greater than 8000, no change is needed. QCR# 420702 - Mapping of datatype date and time to char shows corrupted data. QCR # 144602 – When using physical locking (SQL_PHYSICAL_LOCK=Y) with no Magic lock (Magic Locks=None), whenever a lock is encountered in a join operation, there is no retry. The message "waiting for lock.." appears once, and then the message "record has been lost" appears. QCR # 784815 – When using physical locking (SQL_PHYSICAL_LOCK=Y) with no Magic locks (Magic Locks=None), locking a Join cursor locks only the joined table and not the main table too. QCR # 996119 – Transactions are not closed and error message "failed to open transaction" appears when a batch Direct SQL subtask is called from an online parent task. This happens only when the subtask’s End condition is set to ‘Yes’ and the parent task transaction mode is not ‘Prefix’. This QCR is also solved when installing MSsql server SP2. QCR # 953958 – The DB error 'Cannot create new connection because in manual transaction mode' is displayed on executing a simple Direct SQL batch task with 'select * from table' statement and a link in that task. The DB error is displayed only for the first connection. On executing the same program for the second time, the error message 'Failed to open transaction table' appears. QCR #454471 – The format of the date values sent to the DB is changed according to the language’s date format. This is done in order to support different date format in different languages. QCR # 766834 - Magic range doesn't work for records with date fields which their time portion is not 00:00:00. QCR # 769181 – Getting definition of a table created through MSSQL 6.5 gateway with an alpha field bigger than 500 characters shows garbaged records. QCR # 307934 – When a toolkit sort is given on a table having a Memo field (Picture=10000), the memo field’s data is shown garbaged. QCR # 977455 – Using a date key to browse a table, shows the first record duplicated. QCR # 750280 – When using Database Default='NULL' in a property of an alpha field, after Get Definition the value received in that field is ‘NU’ instead of ‘NULL’, which gives a checker error. QCR # 910406 – When a program with Task level transaction calls program A and program B, a rollback is performed for program A if program B has a sort. This happens only if the program opens any MSSQL table (in its DB table). QCR # 777429 – When trying to open a view from Magic and the cursor is set to default or yes Magic hangs. When trying to do the same with commands Instead of cursors everything works fine. It happens only on views with group by clause or with aggregate functions (count/max/avg etc.). Happens also for Direct SQL tasks. QCR #241568 – A Batch Delete task does not delete all the records from the table. Does not happen if the table properties/Cursor = No QCR # 985716 – ‘Unexpected error 10’ error message appears when importing a program via MSSQL 7 gateway using Magic SP4 or SP5. QCR # 719599 - When trying to re-connect to the server, an error message appears. QCR # 984928 - Magic terminates with the error message "Invalid bookmark Insert duplicate key row in object ' when using sort and pressing page down or ctrl+End. QCR # 707210 – The DB error - 'Incorrect syntax near '('. ' is displayed on modifying values of date field. QCR # 4838 - Magic terminates with the error message "file manager fatal error" on executing a program having blob field with Cache Strategy set to ‘None’. 8-11 QCR # 356338 – Magic gives a blank DB error window upon executing a stored procedure in Direct SQL that has text or blob fields in it. When the resultant database is the memory database, the error message 'Fatal error' appears and Magic terminates. QCR # 876795 - Magic shows garbled values when executing a stored procedure for the fields whose properties are: Properties/Def.Nulls/Null Default=Yes. QCR # 758929 - APG on a task for a statement that returns char or varchar datatypes truncates the last letter. QCR # 315073 - APG on a Direct SQL task that selects a variable without a FROM clause (like "select @@version") causes Magic to hang. QCR # 777429 - When trying to open a view that uses a GROUP BY clause or an aggregate function using APG in a Direct SQL task, Magic hangs. QCR #: 979921 - If the parent task does not open a transaction, the transaction is not committed in a specific execution of a subtask. QCR # 10325 - Locate/Incremental locate does not work on alpha fields when the server is defined with a different character set than the client's operating system. QCR #879703 - When Magic Locking=None and SQL_PHYSICAL_LOCKING=Y, Magic issues the DB error 'Incorrect syntax near UPDLOCK' upon modifying values of fields from a program having Link Outer Join. QCR #680624 - After executing the DBDISCNT() function, the entry is still listed in the sp_who procedure output. QCR #706317 - Magic crashes when modifying a date field that has Properties/Def.Nulls/Null Default=Yes. QCR #485500 – Upon connecting from row 0 using Get Definition to an Mssql 6.5 server using an Mssql 7 (mgms732.dll) gateway, the list of tables displayed shows all '?' chars in the table names. QCR # 420178 - When creating a table with a numeric field that is also an index, and then performing a Get Definition of it to Magic, a locate returns: "arithmetic overflow error converting numeric to data type numeric". ### New features Physical locking The new Magic gateway for MSsql 7 supports physical locking at the row level. In order to use physical locking, add the flag: SQL_PHYSICAL_LOCKING=Y in the Database Information field in the database properties. When using this flag, Magic will send the UPDLOCK table hint in the FROM clause as described below: SELECT a,b FROM tab1 (UPDLOCK) ORDER BY a Unless this flag is specified, the locking behavior will be a logical locking as it was in previous versions. Note: Updating blobs or memos are available only when using the physical locking flag. Temporary tables in TempDB Temporary tables are tables that are dropped automatically by MSsql when finishing working with them. There are two kinds of temporary tables: Local temporary tables: A Local temporary table is visible only in the current session and dropped automatically at the end of the current session. Its name is prefixed with a single number sign (#table_name). Global temporary tables: A global temporary table is visible to all sessions. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stop referencing them. The association between a session and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended. Global temporary table names are prefixed with a double number sign (##table_name). Global and local temporary tables automatically created in TempDB by MSsql, no matter from which database the CREATE command was issued. Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE. The new gateway for MSsql 7 supports storing temporary tables in the TempDB database simply by adding the number sign ('#' or '##') as the prefix of the table name in the Table Repository. MCF owner The new gateway for MSsql 7 supports opening an MCF file whose owner is different from the connection owner and is identified by NT authentication. A user wishes to use NT authentication instead of SQL user, just needs to leave the user and password fields empty in the database entry of the database repository. In this case, the owner is determined according to the login information specified in the MSsql server. The problem is that you cannot use the NT authentication when your MCF belongs to another owner (other than the one specified in the login information of the MSsql server). To solve the problem, a new flag was added. In order to use it, simply specify the owner of the MCF in the Database Information field of the database entry in the format: SQLOWNER="xxxx" when 'xxxx' is the owner name. Magic support for MSsql 7 #### Magic MSsql gateway for Open\Client configuration: Mgms732.dll – Magic MSsql gateway for MS SQL 7 and MS SQL 2000. Known Problems QCR # 287513 - When executing a Direct SQL program, if the alpha field is updated with a value greater than its defined size, the Db Error "String or binary data would be truncated" appears and the records are not updated. QCR # 487502 - Magic terminates when specifying SQLOWNER parameter with some syntax error when trying to open the MCF file, which is owned by other user. QCR# 976684 - When trying to create in a Direct SQL task output parameters for a stored procedure with the local temp table via the APG button, an error message will appear due to an OLE-DB problem. |
| Thank you, The Magic Version 8 Team |