Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Unanswered: Problem Embedded SQL

    Hello,

    I hope this is the corect place for this Thread, if not please move it

    I have a Task to do for School.
    We use a Oracel Databank for Towns, signposts, locations of signposts, and so on...


    So now we have to programm an Embedded SQL code where the user has to enter the Name of one Town to retrieve the information about it.


    So:
    The entered Name will be saved in a VARCHAR Array.
    This Array should be used as a "WHERE" comparison.

    It would be look like this:

    EXEC SQL DECLARE Gemeinde_cursor CURSOR FOR
    SELECT GNAME FROM Gemeinde WHERE GNAME = :input;


    ("input" would be the array. "Gemeinde" is a german word and it means a little town. )

    But if I convert the code with the Preecompiler and than start the C-code, i get an error at the part where the Cursor should be opened: ORA-01450: maximum key length

    But if I change the "input" array to a Town name from the Databank (so that the user entry does not even matter) it works fine.


    I hope somebody could help me. And Sorry for my bad English. If something is unclear please ask and I try to explain it in a other way.


    thank you,



    Here is the ESQL code if somebody need it.

    (Code is not finished yet, some variables are german words, but i think it's not important to understand the code.)


    Code:
    #include <stdio.h>
    #include <string.h>
    
    EXEC SQL INCLUDE sqlca; /* SQL communication area */
    
    
    int main (){
    EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR user[16];
    VARCHAR pwd[10];
    VARCHAR input[20];
    VARCHAR gname[20];
    int gemeinde_input;
    int laenge;
    EXEC SQL END DECLARE SECTION;
    
    
    
    
    EXEC SQL WHENEVER SQLERROR GOTO sqlerror; /* unspezifische Fehlerbehandlung */
    
    
    
    /*Benutzer nach Name und Passwort fragen*/
    
    printf ("Benutzername und Datenbankverbindung: ");
    gets(user.arr);
    user.len = strlen (user.arr);
    printf("Passwort: ");
    gets (pwd.arr);
    pwd.len = strlen (pwd.arr);
    
    EXEC SQL CONNECT :user IDENTIFIED BY :pwd;
    
    if (sqlca.sqlcode == 0){
    printf ("Connected to Oracle as %s\n", user.arr);
    
    printf("Bitte geben Sie den Namen einer dieser Gemeinde ein: ");
    
    printf("\n Baienfurt, Baindt, Weingarten, \n Ravensburg, Wolpertswende, Meckenbeuren, \n Friedrichshafen, Waldburg, Bodnegg, \n Amtzell \n");
    printf("Gemeinde: ");
    gets(input.arr);
    laenge = strlen (input.arr);
    input.arr[laenge] = '\0';
    }
    
    EXEC SQL DECLARE Gemeinde_cursor CURSOR FOR /*Cursor für Gemeindedaten */
    SELECT GNAME FROM Gemeinde WHERE GNAME = :input;
    
    
    EXEC SQL OPEN Gemeinde_cursor; /*Abfrage ausführen*/
    
    
    EXEC SQL FETCH Gemeinde_cursor INTO :gname; /* Gemeinde Holen */
    
    
    if (sqlca.sqlcode == 0){
    gname.arr[gname.len] = '\0';
    printf("Gemeinde %s wurde gewählt \n\n", gname.arr);
    }
    EXEC SQL CLOSE Gemeinde_Cursor;
    
    
    
    gets(user.arr); /*NUR DAMIT DIE KONSOLE OFFEN BLEIBT */
    return (1);
    
    sqlerror: printf ("Fehler bei DB-Zugriff % .70s \n", sqlca.sqlerrm.sqlerrmc);
    gets(user.arr);
    return(0);
    
    }

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Your SQL steps don't seem to be correct (I can't comment C part of your script). Here's how it is supposed to look like; compare it with your code and try to adjust it. Shortly:
    - declare both cursor and a cursor variable (I think that you didn't do that right)
    - open a cursor
    - fetch
    - do something with the value
    - close a cursor
    Code:
    SQL> set serveroutput on
    SQL> l
      1  declare
      2    cursor c1 is
      3      select count(*) cnt from emp
      4      where deptno = &deptno;
      5    c1r c1%rowtype;
      6  begin
      7    open c1;
      8    fetch c1 into c1r;
      9    dbms_output.put_line('Number of employees: ' || c1r.cnt);
     10    close c1;
     11* end;
    SQL> /
    Enter value for deptno: 10
    old   4:     where deptno = &deptno;
    new   4:     where deptno = 10;
    Number of employees: 3
    
    PL/SQL procedure successfully completed.
    
    SQL>

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What is the definition of the Gemeinde table? Is it possible that the column GNAME is shorter than 20 bytes?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •