Tips_sql   >   Sql   >   SQL Problem Solving

SQL Problem Solving

This section contains tips on solving various problems you may run into with SQL.

SQL Error Handler

It's a good idea to add a $sqlerror handler method in your base superclass table class.

The built-in table class method call the $sqlerror method if it hits an error.

The following is the code from my base table superclass $sqlerror method.

Switch pkErrorType
   Case kTableSelectError
      Calculate ErrType as 'Select Error'
      Calculate ErrCode as $cinst.$statementobject.$errorcode
      If ErrCode=0
         ; This just means that the select came up with no records. Not an error.
         Quit method kTrue
      End If
   Case kTableFetchError
      Calculate ErrType as 'Fetch Error'
   Case kTableUpdateError
      Calculate ErrType as 'Update Error'
   Case kTableDeleteError
      Calculate ErrType as 'Delete Error'
   Case kTableInsertError
      Calculate ErrType as 'Insert Error'
   Case kTableGeneralError
      Calculate ErrType as 'General Error'
   Default
      Calculate ErrType as 'Unknown Error'
End Switch

; Copy the row to a local row variable.
Calculate Row as $cinst

Calculate SQLClassName as $cinst.$sqlclassname
Calculate Mssg as con("SQL Error: ",ErrType,", SQL Class Name: ",SQLClassName)
Calculate Dtls as con("SQL Error: ",$cinst.$statementobject.$nativeerrorcode," - ",$cinst.$statementobject.$nativeerrortext,", SQL Text: ",$cinst.$statementobject.$sqltext)

Breakpoint

; Log the error.
Do errhndlr.$logSQLError($cmethod,Mssg,Dtls)

Quit method kFalse

Fetch Only Gets One Record

Fetch only brings back one record into your list variable.

  1. You must specify the number or records to $fetch. $fetch() only gets 1 record. Use $fetch(kFetchAll).
  2. Check to make sure your list variable isn't actually a row variable.
  3. $fetch(kFetchAll) only brings back 1 record. Check the $linemax value of the list you are using.

    Calculate #1 as List.$linemax

    If $linemax is only 1, set it to kFetchAll

    Calculate List.$linemax as kFetchAll

Quotes in SQL Text

If you are trying to select LastName = O'Sullivan, the single quote character can give you grief. Use of bind variables will eliminate the problem.

A weaker solution is to replace all the single and double quote characters with underscore characters so that LastName = O_Sullivan. In SQL the underscore character is a single character wildcard.

Unable to Insert a Second Record

You can insert the first record in a table, but you can't insert a second record.

Check the unique index field(s) in the table. You might be trying to insert a second record with the same primary key.

Unable to Insert any Records

You are unable to insert any records into a table.

  1. Do you have a session open with the database? And is it the right one?
  2. Are you logged onto the database?
  3. Did you set the $sessionobject property of the list or row that is bound to the table class?
  4. Is one of the columns in the record a NULL value for a column set to No Nulls?

Unable to Update Records

If you find you can insert records but can't update old records:

  1. Check the schema class. You must have a primary key (Unique field). And it must be set it to kTrue.
  2. Makes sure you are sending the OldRow as a parameter.

    Do Row.$update(OldRow)

Using SQL Reserved Words

Using SQL Reserved Words for table or column names will give you troubles. While I was creating an email module I used the words To and From as column names. The CREATE TABLE statement would not execute. Eventually I found the problem. To and From are SQL reserved words.

StudioTips Documentation - Copyright 2006 Vencor Software