How to fix 22022: indicator_overflow in PostgreSQL

-- Enable statement logging temporarily
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_error_statement = 'DEBUG3';
SELECT pg_reload_conf();

-- Or for current session only
SET log_statement = 'all';
SET log_min_error_statement = 'DEBUG3';

/* C embedded SQL - WRONG */
short indicator;  /* Only stores -1 to 32767 */
char name[50];
EXEC SQL SELECT name INTO :name:indicator FROM users WHERE id = 1;

/* CORRECT */
int indicator;    /* Stores much larger values */
char name[50];
EXEC SQL SELECT name INTO :name:indicator FROM users WHERE id = 1;

# For ODBC (Linux)
# Update your ODBC driver package
sudo apt-get install odbc-postgresql  # Ubuntu/Debian
# or
sudo yum install postgresql-odbc       # RHEL/CentOS

# For JDBC, update in your pom.xml or build.gradle
# <dependency>
#   <groupId>org.postgresql</groupId>
#   <artifactId>postgresql</artifactId>
#   <version>42.7.0</version>  <!-- Use latest -->
# </dependency>

# For Python (psycopg)
pip install --upgrade psycopg[binary]

-- First, check the actual column sizes in your table
SELECT
  column_name,
  data_type,
  character_maximum_length
FROM information_schema.columns
WHERE table_name = 'your_table'
ORDER BY ordinal_position;

/* Allocate buffers generously - no penalty for extra space */
#define NAME_BUFFER_SIZE 256  /* Larger than column maximum */
#define DESC_BUFFER_SIZE 1024

char name[NAME_BUFFER_SIZE];
char description[DESC_BUFFER_SIZE];
int indicator_name, indicator_desc;

/* Now safe to fetch */
EXEC SQL SELECT name, description
INTO :name:indicator_name, :description:indicator_desc
FROM products WHERE id = 123;

// JDBC example - configure how NULLs are handled
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();

// Use getXxx methods with column index and check for NULL
ResultSet rs = stmt.executeQuery("SELECT name, age FROM users");
while (rs.next()) {
  String name = rs.getString(1);  // Driver handles indicator internally
  if (rs.wasNull()) {
    System.out.println("Name is NULL");
  } else {
    System.out.println("Name: " + name);
  }
}

-- WRONG - unclear parameter types and sizes
PREPARE get_user AS SELECT * FROM users WHERE name = $1;

-- BETTER - explicit types
PREPARE get_user (text) AS SELECT * FROM users WHERE name = $1;
PREPARE get_user_batch (text[], integer) AS
  SELECT * FROM users WHERE name = ANY($1) AND age > $2;

/* Set appropriate cbColumnDef (column definition size) */
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
                 50,  /* cbColumnDef: actual column size */
                 0, name_value, strlen(name_value), NULL);

-- Test 1: Simple SELECT with no NULL handling
SELECT id, name FROM users LIMIT 1;

-- Test 2: SELECT with guaranteed NULL values
SELECT id, name FROM users WHERE name IS NULL LIMIT 1;

-- Test 3: SELECT with large text columns
SELECT id, description FROM products WHERE length(description) > 1000;