Using dbConnectDSN to connect with Oracle database...

[Sbleck]

Hi,

I made some modifications to the adressbook.eba sample, to establish connection with Oracle XE (a free database with very few limitations, from Oracle: http://www.oracle.com/technology/products/database/xe/index.html), using Oracle Client 9i (because of other needs...).  Was possible to connect (after creating the DSN in ODBC admin, using Oracle ODBC drivers) but encountered problems when trying to insert (or, maybe other DML statement), as mentioned below (was modified only for testing, of course):

<code>

...
SUB OpenDatabase(win as WINDOW),POINTER
DEF pReturn as POINTER
DEF hStmt as UINT
   pReturn = dbConnectDSN("Ora9i2XE","UID=teste;PWD=teste")
   IF pReturn <> NULL
      $IFDEF DEBUG
          DEBUGPRINT "Connecting to Oracle Database..."
      $ENDIF
      MessageBox win,"Connected to the Oracle XE database","Error"
      dbFreeSQL(hstmt_insert)
      $IFDEF DEBUG
          DEBUGPRINT "Inserting a row into address table..."
      $ENDIF
      hstmt_insert = dbExecSQL(pdb,"INSERT INTO addresses (fname,lname,street,street2,city,state,zip,phone,email) VALUES('Test','Test','Test','Test','Test','XX','12345','12345','my@mail.com')")
      errsql = dbGetErrorCode(hstmt_insert)
      $IFDEF DEBUG
          DEBUGPRINT "Row appear to be inserted in the address table..."
         IF LEN(errsql)
             PRINT
             PRINT "Error Code: ", errsql
             PRINT "Error Text: ", dbGetErrorText(hstmt)
             PRINT
         ENDIF
      $ENDIF
      'get the initial count of records if could be opened...
      count = dbCardinality(pReturn,"addresses")
      $IFDEF DEBUG
          DEBUGPRINT "The total rows of the address table is..."+ STR$(count)
      $ENDIF
      dbFreeSQL(hstmt_insert)
      $IFDEF DEBUG
          DEBUGPRINT "Insert statement freed from memory..."
      $ENDIF
   ELSE
      MessageBox win,"Could not connect to Oracle XE database","Error"
   ENDIF
RETURN pReturn
ENDSUB

<code>

What I received back when debugging, was mentioned below:

...
Loading DLL: C:\WINDOWS\system32\iphlpapi.dll
Connecting to Oracle Database...
Inserting a row into address table...
Row appear to be inserted in the address table...
The total rows of the address table is... 0
Insert statement freed from memory...
The program 'C:\Program Files\EBDev\projects\Database samples\Ora_addressbook.exe' exited with code: 0

Could be sended any advice to check this or what is happening ?  I checked the Oracle database, and no row was inserted.  I know that ODBC is autocommited by default, but any advice about this issue would be greatly appreciated...

Tks in adv,
Sven

[acelli]

Sven,

I have used Ebasic to insert and updates rows in Oracle 10g, but you will need to use "COMMIT".  Also, based on other peoples experiences I never use "dbCardinality".  Instead I use "select count(*) from table_name"

The only issues I have had are joining tables.  I believe this is becasue I have been using "Microsoft ODBC for Oracle" instead of Oracle's ODBC driver.

Alan

[Sbleck]

I have used Ebasic to insert and updates rows in Oracle 10g, but you will need to use "COMMIT".  Also, based on other peoples experiences I never use "dbCardinality".  Instead I use "select count(*) from table_name"

The only issues I have had are joining tables.  I believe this is becasue I have been using "Microsoft ODBC for Oracle" instead of Oracle's ODBC driver.

Thanks for your reply, the first after 51 viewings of this post.  But I tried to send a commit too (after sending this post) and had no success either.  I leaved the "dbCardinality" only to check what it returns, also.  I used the Oracle's ODBC only because was a logical choice to do.  Until now, I had no idea about what was wrong with this code or process, and maybe few people are doing the same...

Sven

[Ionic Wind Support Team]

Sven,
Views of the post are meaningless, you need a bit more patience.  The Yahoo and google search engine spiders accounts for most of the 'views'.

I haven't answered because I know nothing about Oracle.  Vikki might be able to help when she gets around to logging in again.

Paul.

[acelli]

Sven,

You were right about the auto commit.  I think your problem might be using mixed case on the table and field names.  I created this table in SQLplus

Code Select Expand



create table ADDRESS_TEST (
FNAME    VARCHAR2(50),
LNAME    VARCHAR2(50),
STREET   VARCHAR2(50),
STREET2  VARCHAR2(50),
CITY     VARCHAR2(50),
STATE    VARCHAR2(50),
ZIP      VARCHAR2(50),
PHONE    VARCHAR2(50),
EMAIL    VARCHAR2(50)     );



and used this code to insert a row

Code Select Expand



IF pdb <> NULL THEN
    PRINT "ODBC Connection Established"

         SQL = " INSERT INTO ADDRESS_TEST (FNAME,LNAME,STREET,STREET2,CITY,STATE,ZIP,PHONE,EMAIL) VALUES ('Test1','Test2','Test3','Test4','Test5','XX','12345','54321','my@mail.com') "
            hstmt = dbExecSQL(pdb,SQL)
            err = dbGetErrorCode(hstmt)                                                       
            IF LEN(err)                                                                       
                PRINT                                                                         
                PRINT "Error Code: ", err                                                     
                PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
                PRINT                                                                         
            ELSE
                PRINT "INSERT SUCESSFUL"
                PRINT                                                                                     
   dbFreeSQL(hstmt)                                                                                                                                       
   SQL = " SELECT * FROM ADDRESS_TEST "
   hstmt = dbExecSQL(pdb,SQL)
   err = dbGetErrorCode(hstmt)                                                       
   IF LEN(err)                                                                       
     PRINT                                                                         
     PRINT "Error Code: ", err                                                     
     PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
     PRINT                                                                         
                   ELSE 
                     count=0
                     WHILE dbGet(hstmt)         
                       count++
                     WEND 
             PRINT "ROW COUNT ",count
             PRINT                                                                                                           
   ENDIF                   
    ENDIF           
            dbFreeSQL(hstmt)                                                                                                                                       
       
ELSE
    PRINT "No Connection Established"
ENDIF
dbDisconnect(pdb)



let me know if it works for you.

Alan

[Vikki]

Sorry guys,

I've been down with a nasty cold. Just coming back to life here. Meantime, looks like acelli may have solved the problem. I'll go download the free Oracle db and play with it a bit.

[Sbleck]

Hi,

Tks for the replies.  I will try what was said and give back a return, as soon as possible...

Sven

[Sbleck]

Hi Alan and others,

As I said before, here are the results I obtained after doing as suggested:

- Creating another table, in UPPERCASE:

SQL*Plus: Release 9.2.0.1.0 - Production on Qua Fev 14 19:02:29 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect teste@oraxe
Enter password: *****
Connected.

SQL> create table ADDRESS_TEST (
  2  FNAME    VARCHAR2(50),
  3  LNAME    VARCHAR2(50),
  4  STREET   VARCHAR2(50),
  5  STREET2  VARCHAR2(50),
  6  CITY     VARCHAR2(50),
  7  STATE    VARCHAR2(50),
  8  ZIP      VARCHAR2(50),
  9  PHONE    VARCHAR2(50),
10  EMAIL    VARCHAR2(50));

Table created.

SQL> disconnect
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

- Doing another test, with the commands issued in UPPERCASE:

DEF pdb,pTables as POINTER
pdb = dbConnectDSN("Ora9i2XE","UID=teste;PWD=teste")
IF pdb <> NULL THEN
    PRINT "ODBC Connection Established"
         SQL = " INSERT INTO ADDRESS_TEST (FNAME,LNAME,STREET,STREET2,CITY,STATE,ZIP,PHONE,EMAIL) VALUES ('Test1','Test2','Test3','Test4','Test5','XX','12345','54321','my@mail.com') "
            hstmt = dbExecSQL(pdb,SQL)
            err = dbGetErrorCode(hstmt)                                                       
            IF LEN(err)                                                                       
                PRINT                                                                         
                PRINT "Error Code: ", err                                                     
                PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
                PRINT                                                                         
            ELSE
                PRINT "INSERT SUCESSFUL"
                PRINT                                                                                     
         dbFreeSQL(hstmt)                                                                                                                                       
         SQL = " SELECT * FROM ADDRESS_TEST "
         hstmt = dbExecSQL(pdb,SQL)
         err = dbGetErrorCode(hstmt)                                                       
         IF LEN(err)                                                                       
           PRINT                                                                         
           PRINT "Error Code: ", err                                                     
           PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
           PRINT                                                                         
                   ELSE 
                     count=0
                     WHILE dbGet(hstmt)         
                       count++
                     WEND 
                PRINT "ROW COUNT ",count
                PRINT                                                                                                           
         ENDIF                   
       ENDIF           
            dbFreeSQL(hstmt)                                                                                                                                       
ELSE
    PRINT "No Connection Established"
ENDIF
dbDisconnect(pdb)
PRINT "Press any key to close this window"
DO:UNTIL INKEY$ <> ""

end

- Produced this result, in console:

ODBC Connection Established
INSERT SUCESSFUL

ROW COUNT 1

Press any key to close this window

- Checking the result, looking in the table:

SQL*Plus: Release 9.2.0.1.0 - Production on Qua Fev 14 19:12:14 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect teste@oraxe
Enter password: *****
Connected.

SQL> select count(*) from address_test;

  COUNT(*)
----------
         1

SQL> col fname format a10
SQL> col lname format a10
SQL> col street format a10
SQL> col zip format a10
SQL> col phone format a10
SQL> col street2 format a10
SQL> col city format a10
SQL> col state format a10
SQL> col email format a15
SQL> select * from address_test;

FNAME      LNAME      STREET     STREET2    CITY       STATE      ZIP        PHONE      EMAIL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------
Test1      Test2      Test3      Test4      Test5      XX         12345      54321      my@mail.com

SQL> disconnect
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production


Well, what could I say ?  That only runs when issuing the commands in UPPERCASE, also when creating the objects ?  I'm reluctant in believing in this, because in other languages doesn't matter...

Maybe anything correlated to how ODBC handles this.  More tests should be done...

Tks in adv,
Sven

[Ionic Wind Support Team]

Sven,
The language has nothing to do with it.  Emergence is simply passing the strings you provide directly to the ODBC layer and the driver interprets the commands.  We don't have any problems using the Access driver for example.

[acelli]

Sven,

Oracle likes tables and columns in uppercase.  I have run into similar issue when exporting tables from Access to Oracle.  In fact if you run this query in SQLplus and then do a describe you will see Oracle it changes things to uppercase.

Code Select Expand



create table address_test2 (
fname    varchar2(50),
lname    varchar2(50),
street   varchar2(50),
street2  varchar2(50),
city     varchar2(50),
state    varchar2(50),
zip      varchar2(50),
phone    varchar2(50),
email    varchar2(50)     );




Also once the table has been created you can use the query below in Ebasic without problems

Code Select Expand



  sql = " insert into address_test2 (fname,lname,street,street2,city,state,zip,phone,email) values ('test1','test2','test3','test4','test5','xx','12345','54321','my@mail.com') "



You can actually access these tables but I think you need to wrap the table name in double-quotes "table_name".  For me it is not worth the effort to embed the double-qoutes inside the string. 

Alan

[Sbleck]

Hi Paul and Alan,

Thanks for your comments.  I know that when describing tables it showed in uppercase, but the need to do anything (in the code) in uppercase is other thing:

SQL*Plus: Release 9.2.0.1.0 - Production on Qua Fev 21 22:25:31 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect teste@oraxe
Enter password: *****
Connected.

SQL> set linesize 80
SQL> desc address_test;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FNAME                                              VARCHAR2(50)
LNAME                                              VARCHAR2(50)
STREET                                             VARCHAR2(50)
STREET2                                            VARCHAR2(50)
CITY                                               VARCHAR2(50)
STATE                                              VARCHAR2(50)
ZIP                                                VARCHAR2(50)
PHONE                                              VARCHAR2(50)
EMAIL                                              VARCHAR2(50)

SQL> disconnect
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

But I'm doing more tests with this issue, in the meanwhile...

Tks in adv,
Sven

[Ionic Wind Support Team]

Are you asking a question?

Again I have to reiterate that Emergence doesn't modify anything before being sent to the driver.  The driver is what interprets the SQL  strings that you send.  If the driver requires it in uppercase, or with double quotes, then that is what you need to do.

dbExecSQL is a very simple wrapper for the ODBC function SQLExecDirect.  So either ODBC (the Microsoft SQL front end) or the Driver (The Oracle back end) is expecting the queries in uppercase.  In fact all of the database commands are wrappers for ODBC but provide simpler usage like taking care of the statement handles, database connection ,etc/

Here is the Microsoft description of the ODBC function used by dbExecSQL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlexecdirect.asp

Paul.

[acelli]

Sven,

It is not just that the DESC displays the table and field names in uppercase; Oracle stores them in uppercase.

The question is how was the original "address" table created?  Did you export it from Access?

If you use the code below you will find you do not need to use uppercase in EBasic code, but Oracle will create and store the table and field names in uppercase.  Becasue Oracle forces table and field names to uppercase that is how it expects to find them.

Code Select Expand



IF pdb <> NULL THEN
    PRINT "ODBC Connection Established"
    PRINT
        sql = "create table address_test3 (fname varchar2(50), lname varchar2(50), street varchar2(50), street2 varchar2(50), city varchar2(50), state varchar2(50), zip varchar2(50), phone varchar2(50), email varchar2(50) ) "
         hstmt = dbExecSQL(pdb,SQL)
         err = dbGetErrorCode(hstmt)                                                       
       IF LEN(err)                                                                       
          PRINT                                                                         
          PRINT "Error Code: ", err                                                     
          PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
          PRINT                                                                         
       ELSE
          PRINT "TABLE CREATED"
          PRINT                                                                                     
        dbFreeSQL(hstmt)                                                                                                                                       
     sql = " insert into address_test3 (fname,lname,street,street2,city,state,zip,phone,email) values ('test1','test2','test3','test4','test5','xx','12345','54321','my@mail.com') "
              hstmt = dbExecSQL(pdb,SQL)
              err = dbGetErrorCode(hstmt)                                                       
            IF LEN(err)                                                                       
               PRINT                                                                         
               PRINT "Error Code: ", err                                                     
               PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
               PRINT                                                                         
            ELSE
               PRINT "INSERT SUCESSFUL"
               PRINT                                                                                     
     dbFreeSQL(hstmt)                                                                                                                                       
            sql = " select * from address_test3 "
             hstmt = dbExecSQL(pdb,SQL)
             err = dbGetErrorCode(hstmt)                                                       
           IF LEN(err)                                                                       
              PRINT                                                                         
              PRINT "Error Code: ", err                                                     
              PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
              PRINT                                                                         
                   ELSE 
                    count=0
                     WHILE dbGet(hstmt)         
                       count++
                     WEND 
                PRINT "ROW COUNT ",count
                PRINT                   
   ENDIF                   
        ENDIF
       ENDIF           
    dbFreeSQL(hstmt)                                                                                                                                               
ELSE
    PRINT "No Connection Established"
ENDIF
dbDisconnect(pdb)






If you ever want to get rid of the original "address" table, you will find the SQL statement below will not work.

Code Select Expand



drop table address;



instead you will need to use

Code Select Expand



drop table "address";



I hope this answers your questions.


-Alan

[acelli]

Sven,

However you created the "address" table; you could use the code below to insert, but IMO it is better to create tables the way Oracle expects.

Code Select Expand



     sql = " insert into \"address\" (\"fname\",\"lname\",\"street\",\"street2\",\"city\",\"state\",\"zip\",\"phone\",\"email\") values ('test1','test2','test3','test4','test5','xx','12345','54321','my@mail.com') "
              hstmt = dbExecSQL(pdb,SQL)
              err = dbGetErrorCode(hstmt)                                                       
            IF LEN(err)                                                                       
               PRINT                                                                         
               PRINT "Error Code: ", err                                                     
               PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
               PRINT                                                                         
            ELSE
               PRINT "INSERT SUCESSFUL"
               PRINT                                                                                     
     dbFreeSQL(hstmt)                                                                                                                                       
            sql = " select * from \"address\" "
             hstmt = dbExecSQL(pdb,SQL)
             err = dbGetErrorCode(hstmt)                                                       
           IF LEN(err)                                                                       
              PRINT                                                                         
              PRINT "Error Code: ", err                                                     
              PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
              PRINT                                                                         
                   ELSE 
                    count=0
                     WHILE dbGet(hstmt)         
                       count++
                     WEND 
                PRINT "\"address\" COUNT ",count
                PRINT                   
   ENDIF                   
        ENDIF



-Alan

[Sbleck]

Hi Paul and Alan,

Thanks for sending your comments, links and samples.  I tried to check it and runs ok...

Sven