ORACLE TIPS AND TECHNIQUES RECORD LOCKING Q: I have noticed that when I lock a record on a SQL table, I can go to another PC, lock that same row and The client returns true when I do a lockRecord(). Is it really locked? A: No. The Borland SQL-Link uses what is called "Optimistic Locking." That is to say that the BDE/SQL-Link selects a row when an SQL row is locked, makes a copy of it in memory and provides you with a third, modifiable row allowing you to make modifications to it as needed. Come time to commit the work done to that row, The client re-selects the base row using the second row in memory as the basis of the select. If the two do not match completely, the error message Cannot perform edit. Record was changed by another user." will appear and discard all the work done up to that point. In other words, it hopes the row has not changed. Most SQL servers available on the market today, do not support row-level locking. Of the SQL servers that do support row-level locking, Oracle stands out with the best lock mechanism. Here is a sample: select * from tableName where column=condition for update This statement will select the row or rows in the set for update. When Oracle encounters this, it locks the result until it is committed or rolledback. Be sure to include NOWAIT in youe select statement. Otherwise, the risk of locking that resource indefinitely - like when someone may go to lunch for an hour - will cause that row to be completely inaccessible. NOWAIT will at least allow a read of the data. Any updates or deletes to the row will be denied. CONNECTING Q: What is the proper syntax for the "SERVER NAME" field when connecting to Oracle? A: It can be any combination. Imagine an NLM server setup with SPX/IPX as the protocol. Provided you have an .INI file path for Oracle in your WIN.INI and it [the .INI file] has X:SERVERNAME, you would only need the server name for your alias. The "@" is generally reserved for DOS connections which is ignored in The client for Windows but neccessary if your are using NETTEST or SQL*Plus. The connect string looks like this: @X:ServerName:serverID If it is a TCP/IP protocol that you are using, then it becomes VERY TRICKY. If you have everything setup correctly in the ORACLE.INI file, you will not need to input the server name at all. But, a perfect world, it is not. There are some issues that require entering the entire connect string to establish the connection. Here are some samples: serverName serverName:serverID t:serverName:serverID serverName/1525:serverID t:serverName/1525:serverID t:143.186.50.52:serverID 143.186.50.52:serverID 143.186.50.52/1525:serverID The point is, for certain protocols, you'll need to try different things. Especially with TCPIP stacks. Special Note for SQL*Net 2.0 With SQL*Net 2.0, the rules change a little. You'll find that protocol information, server info, etc. is handled or defined in the TNSNAMES.ORA file. This will effect the ORACLE.INI file as well in that the params for LOCAL and REMOTE will look something like: REMOTE=TNS:SID TNSNAMES.ORA may be on the client machine or on a network drive. If Oracle client configuration information has been exported from the server then the file NAMES.ORA contains a pointer back to your network definition database. When it comes time to connect through BDE/IDAPI, you should enter the your server name or server alias (you may need to use "TNS:srvrname" or one of the examples above) into the SERVER NAME entry and exclude the protocol (TCP/IP, SPX/IPX, etc.) from the ORACLE driver altogether (this would be done by using the Idapi or BDE configuration utility to remove the "mynetprotocol" from the ORACLE driver and save the changes. If an alias is chosen in an Idapi client the driver defaults will be used as connection parameters. RECORD LOCKING Q: I have noticed that when I lock a record on a SQL table, I can go to another PC, lock that same row and The client returns true when I do a lockRecord(). Is it really locked? A: No. The Borland SQL-Link uses what is called "Optimistic Locking." That is to say that the BDE/SQL-Link selects a row when an SQL row is locked, makes a copy of it in memory and provides you with a third, modifiable row allowing you to make modifications to it as needed. Come time to commit the work done to that row, The client re-selects the base row using the second row in memory as the basis of the select. If the two do not match completely, the error message“Cannot perform edit. Record was changed by another user." will appear and discard all the work done up to that point. In other words, it hopes the row has not changed. Most SQL servers available on the market today, do not support row- level locking. Of the SQL servers that do support row-level locking, Oracle stands out with the best lock mechanism. Here is a sample: select * from tableName where column=condition for update This statement will select the row or rows in the set for update. When Oracle encounters this, it locks the result until it is committed or rolledback. Be sure to include NOWAIT in youe select statement. Otherwise, the risk of locking that resource indefinitely - like when someone may go to lunch for an hour - will cause that row to be completely inaccessible. NOWAIT will at least allow a read of the data. Any updates or deletes to the row will be denied. CONNECTING Q: What is the proper syntax for the "SERVER NAME" field when connecting to Oracle? A: It can be any combination. Imagine an NLM server setup with SPX/IPX as the protocol. Provided you have an .INI file path for Oracle in your WIN.INI and it [the .INI file] has X:SERVERNAME, you would only need the server name for your alias. The "@" is generally reserved for DOS connections which is ignored in The client for Windows but neccessary if your are using NETTEST or SQL*Plus. The connect string looks like this: @X:ServerName:serverID If it is a TCP/IP protocol that you are using, then it becomes VERY TRICKY. If you have everything setup correctly in the ORACLE.INI file, you will not need to input the server name at all. But, a perfect world, it is not. There are some issues that require entering the entire connect string to establish the connection. Here are some samples: serverName serverName:serverID t:serverName:serverID serverName/1525:serverID t:serverName/1525:serverID t:143.186.50.52:serverID 143.186.50.52:serverID 143.186.50.52/1525:serverID The point is, for certain protocols, you'll need to try different things. Especially with TCPIP stacks. Special Note for SQL*Net 2.0 With SQL*Net 2.0, the rules change a little. You'll find that protocol information, server info, etc. is handled or defined in the TNSNAMES.ORA file. This will effect the ORACLE.INI file as well in that the params for LOCAL and REMOTE will look something like: REMOTE=TNS:SID Come time to connect through BDE/IDAPI, you'll need to use "TNS" for the server name and exclude the protocol (TCP/IP, SPX/IPX, etc.) from the ORACLE driver altogether (this would be done by using the Idapi or BDE configuration utility to remove the "mynetprotocol" from the ORACLE driver and save the changes. If an alias is chosen in an Idapi client the driver defaults will be used as connection parameters. The 2.0 version of SQL*Net uses something called TNS (Transparent Network Substrate) that can subsitute for both the net protocol and the server name. The pieces are put together by both the server and the oracle configuration file on the local client machine. Presumably the TNS is meant to make server administration and local configuration easier. Problem: ORA-09241 This error message is obtained when the connection cannot locate the message files needed by SQL*Net. These files are usually found in the \BIN directory of the \ORAWIN directory. Be sure that the ORACLE_HOME setting in the ORACLE.INI file is set to \ORAWIN {whatever the path may be}. Additionally, make sure the ORA_CONFIG setting the WIN.INI is set to the proper location of your ORACLE.INI file. Lastly, make sure you HAVE the message files to begin with. They are the files having the extension *.MSB. Problem: ORA-03121 This can be caused by a number of things. But, more often than not, it is caused by not having your SQL*Net driver loaded in memory. The DLLs vary from flavor to flavor, but, here is a sampling of them: SPX/IPX SQLSPX.DLL TCP/IP SQLTCP.DLL & SQLTCP1.DLL For SQL*Net 2.0 look for the DLLs: SQLTNS.DLL and SQLTNS1.DLL. In all cases of this error, it means that the DLL(s) did not load. Check to insure that the following is correct: Make sure ORAWIN\BIN is in your path; Do not have multiple copies of these DLLs; Keep them in one location; For SQL*Net 2.0, do not include the NET PROTOCOL in BDE/IDAPI config. for the ORACLE driver. Problem: ORA-06105 I get this error when I try to connect to an Oracle server residing on a UNIX machine. This also happens when I run NETTEST.EXE. I am using SQL*Net 1.1 and the server version Solution: Chances are that if you cannot connect with NETTEST.EXE, your client configuration is not installed properly. Here is a checklist: 1: Make sure you have a path to your ORAWIN\BIN directory. 2: Make sure the [ORACLE] section in your WIN.INI file is correct: [ORACLE] ORA_CONFIG=C:\WINDOWS\ORACLE.INI 3: Make sure that you have a HOSTS file containing you hosts IP address and hostname: 123.234.56.567 hostname 4: Make sure that in addition to having a HOSTS file, you should also have a SERVICES file in the same location as the HOSTS file. It should contain the ORASRV process or service: orasrv 1525/tcp NOTE: just as a precaution, create a directory off the root of C and call it ETC. Many connection problems with FTP software have been solved by having the HOSTS and SERVICES files in the C:\ETC directory 5: Check to insure that your ORACLE.INI file has the appropriate settings. Here is a sample ORACLE.INI file: LANGUAGE=American_America.US7ASCII ORACLE_HOME=C:\orawin MACHINE_TYPE=0 WIN_REMOTE_SESSIONS=4 TCP_Vendor=winsock TCP_services_file=c:\etc\services TCP_hosts_file=c:\etc\hosts REMOTE=t:hostName:SID {server ID or default database name} ORACLE_DBS=c:\orawin\bin NOTE: This sample is indicative of a client configuration using FTP PCTCP as the TCP stack. Different vendors may require different settings. After you have gone through this checklist, attempt your connection with NETTEST.EXE. If you are successful, use the connect string you provided. Here, again, is a sample connect string: t:hostname:SID If this does not work, try using the ip address of the host in place of the hosname. And remember, when connecting through The client, exclude the "t:" altogether. Problem: ORA-06122 "NETTCP: setup failure" This error occurs when trying to connect with SQL*Net TCP/IP has not been setup correctly. ALTERING and IDENTIFYING OBJECTS Q: When I try to alter a table in Oracle where IDAPI was used to create it, I get an "Invalid Column Name" error message when I try to add a PRIMARY KEY to the table on a field called "Autoincrement". Additionally, if I try to do select statements mfrom that same table, I get the same error (Oracle '904' error - invalid column name.) One of Oracle's object naming rules is that "Names are not case- sensitive". However, there is an exception to this rule. If a name is enclosed in double quotes, it retains it's case. If an object is created with mixed case, then you must use quotes when accessing the object. If a name is not enclosed in quotes, then the server will treat it as UPPER CASE. Since the table was created with the mixed- case column name, Title, it must be accessed with quotes. The next two queries will fail: select title from test AND select Title from test because the server is looking for TITLE when the real column name is Title. If you put Title in quotes ("Title") then it will work. Please ensure that you are using Borland SQL-Links 2.5 for full support of quoted mixed case objects on an Oracle server. Problem: Slow performance connected to an Oracle server There is really only one solution to solve this problem. That is to give/add a PRIMARY KEY constraint to your Oracle tables. Make no mistake about it. If you are told that there is a PRIMARY KEY on a one million row table and it is still slow, a UNIQUE INDEX has probably been mistaken for a PRIMARY KEY. To verify that there is is fact a PRIMARY KEY, query the table SYS.ALL_CONSTRAINTS. You can use the following select statement: select * from SYS.ALL_CONSTRAINTS where TABLE_NAME = 'usersTableName' If the result does not have a CONSTRAINT_TYPE of 'P' or if the the users table does not appear in that result, then the table does NOT have a PRIMARY KEY. Reassure the user that this will GREATLY improve performance. Problem: TCursor and NRecords() is REAL SLOW (PDOXWIN, other) Alas, this is definitely a problem even with a PRIMARY KEY. The fastest way to get the total number of records for an Oracle table is to use: select count(*{or columName}) from table name You would use passthrough, of course, to get the number of rows with this select statement. It's fast, painless. Amaze your family, friends and pets with this technique. Problem: Locate and Zoom are REAL slow (PDOXWIN, other) Indeed, it is slow. You can use passthrough SQL statements to locate the value or you can use the The client procedures SetGenFilter(), SetRange() or QLocate(). All are very fast and will impress all people of the opposite gender. Problem: TCursor.Add() is REAL slow (PDOXWIN, other) This is especially worrisome if you have - literally - millions of rows. If you can, avoid this method of migration altogether. Oracle has a utility that is specifically catered to moving large amounts of data into tables quickly and painlessly. It's called SQL*Loader. This will take virtually all forms of text files and - for lack of a better word - import or load your data into your target table. To use SQL*Loader on the NLM, you'll need to use RCONSOLE. Additionally, you'll need to define a control file. It will have a *.CTL extension and it contains info about the source file, column information (name and datatype), etc. Here is a sample control file: OPTIONS (DIRECT=TRUE) LOAD DATA INFILE 'C:\SORCFILE.TXT' INTO TABLE TARGETTABLE FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (CUSTOMERID, FIRSTN, LASTN, COMPANY, ADDRESSA, ADDRESSB, CITY, STATE, ZIP_CODE) BEGINDATA At the command prompt, type: LOAD SQLLOAD USERNAME=USERNAME CONTROL=CONTROLFILE ERRORS=(# of errors) LOAD=(#rows to commit) Problem: TSR/DLL version incorrect For some reason I cannot connect to my Oracle server that resides on a UNIX based machine. It used to work fine. When I got the error, I was instructed to open the file MSOCKLIB.TXT for more info regarding the error. It is there that I found the message TSR/DLL version incorrect. You may have an older/incompatible MSOCKLIB.DLL in your \WINDOWS directory. Renaming the file and using the MSOCKLIB.DLL that comes with your TCP stack (FTP, LanWP,etc.) should clear up the problem. Problem: SetGenFilter() on DateTime Oracle columns (PDOXWIN, other) When I run some code that uses SetGenFilter() on a date column in an Oracle table, I get the error "Expression in field fieldName is invalid. Capability not supported". This error will occur if you pass a date value mm/dd/yyyy and only that. Date/time columns in Oracle default to a time of 12:00:00 AM if you do not provide one [a time]. Hence, just a date value is invalid. However, just adding the time to your date value may not be enough. In extreme situations, passthrough may need to be used to make use of Oracle's toDate() function.