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
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
Quote from: acelli on February 12, 2007, 02:10:31 PM
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
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.
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
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
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
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.
Hi,
Tks for the replies. I will try what was said and give back a return, as soon as possible...
Sven
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
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.
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.
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
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
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
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.
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.
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.
drop table address;
instead you will need to use
drop table "address";
I hope this answers your questions.
-Alan
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.
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
Hi Paul and Alan,
Thanks for sending your comments, links and samples. I tried to check it and runs ok...
Sven