quote:

---

Hello everyone

Now that the forum key issue is out of the way (triggers will be used for Oracle) I went ahead and pushed all of the Access forum tables through ODBC to an Oracle 8i schema. It wenk ok... i guess... All of the tables and fields within were pushed over ok. However, I had to remove all of the relationships first. When a table is pushed to Oracle, regardless of which ODBC driver is used, all of the data types are change to VCHAR2. As you know most of the relationships are based on numerical fields. So now begins the long process of re-structuring the fields from VCHAR2 back to number. It looks as though all of the "NULL" and length attributes of the original text fields are still intact. Man... I am not looking foward to re-structuring that FORUM_CONFIG table... wow look at all of those fields...

Once this process is finished it should only take a day or so to replace all of the autonumber fields with triggers.

I will keep you all posted... Happy Holidays!

---

hi, h20, I am fresh about snitz forum, and just read your guys post about ID's issue.

After I got this 3.1 release, I try to migrate to Oracle 8.1.5, also through ODBC, and most fields r quite OK, some fields even can keep the length of NUMBER type. the first problem I met is about MEMO type date in ACCESS, ODBC try to convert to LONG type in Oracle, but Oracle has lots limitation about LONG type, one cause the problem is one table can not have more than one LONG Type fields, so I restructured the forum_members table. after that seems all tables convert successfully.

Becos I migrated some ACCESS table to Oracle b4, so when I found some autonumber field (IDs), I just created 5 or 6 sequence number and put sequencenumbername.nextval in to insert sql statement, then it fix the problem about IDs, I didn't think much about compability with another database only try to make it work for me

After all those has been done, another issue is datatype, all NUMBER type fields in Oracle r not treated as integer by ODBC or ADO, have to manually add many Cint(rs("xxxx")) like that to make things work. also the Oracle ODBC driver don't accept ";" char at end of SQL statement, I need take them off manually as well.

Finish all steps, the forum finally runing for me, most functions r working perfectly. but another problem come when I try to use search function, I can't use "like %xxxx%" on LONG type field, and also can't use it simply on CLOB as well, then I try to use VARCHAR2 (4000) to instead of LONG, I failed to test string which is 3980 chars long, error message said SQL statement can't be over 4000 then I have to use PL/SQL anonmyous block to write this string in.

I haven't go through all code yet, but I am sure I will hook up with this forum for long time, I wish I can do something for immigrating this forum to ORACLE platforum

-----------------------
Response.redirect HELL

Edited by - dust on 04 December 2000 01:52:15<

quote:

---

Becos I migrated some ACCESS table to Oracle b4, so when I found some autonumber field (IDs), I just created 5 or 6 sequence number and put sequencenumbername.nextval in to insert sql statement, then it fix the problem about IDs, I didn't think much about compability with another database only try to make it work for me

---

The trick is ofcourse to get it to work with all the other supported database, but you're right: getting it to work on Oracle is step one...

btw, maybe you and h20 can join your efforts ? it looks like you allready got the triggers, h20 might have thought of things you didn't etc.

quote:

---

After all those has been done, another issue is datatype, all NUMBER type fields in Oracle r not treated as integer by ODBC or ADO, have to manually add many Cint(rs("xxxx")) like that to make things work. also the Oracle ODBC driver don't accept ";" char at end of SQL statement, I need take them off manually as well.
[quote]
hmm, the Cint() would add some extra overhead to the code, though is would also work with the other databases.
As far as I know none of the other database require the ';' at the end of the SQL statement. So removing that wouldn't be a big issue.

[quote]
Finish all steps, the forum finally runing for me, most functions r working perfectly. but another problem come when I try to use search function, I can't use "like %xxxx%" on LONG type field, and also can't use it simply on CLOB as well, then I try to use VARCHAR2 (4000) to instead of LONG, I failed to test string which is 3980 chars long, error message said SQL statement can't be over 4000 then I have to use PL/SQL anonmyous block to write this string in.

---

It might just not be possible to use 100% identical code for every database. We had do add some extra code for MySql because it doesn't support paging i.e.
If you need extra code for sql-statements that are used a lot, that is bad because that would mean a lot extra databasespecific code. If it are things like this single searchpage that is no big problem.



Pierre Gorissen

If I only had a little humility,
I would be perfect.
Ted Turner<

quote:

---

. Finish all steps, the forum finally runing for me, most functions r working perfectly. but another problem come when I try to use search function, I can't use "like %xxxx%" on LONG type field, and also can't use it simply on CLOB as well, then I try to use VARCHAR2 (4000) to instead of LONG, I failed to test string which is 3980 chars long, error message said SQL statement can't be over 4000 then I have to use PL/SQL anonymous block to write this string in.

---

quote:

---

Hey dust... sounds like you are close. I have a couple of questions regarding your achievements so far.

---

hi, H20, No worries

quote:

---

- What version of Access are you using?
- What ODBC driver are you using (Oracle’s Driver or MS Driver)
- How exactly are you getting the tables into Oracle?
Are you saving the Access tables to binary files then importing into Oracle?
Or are you saving as ODBC?
(I know you mentioned that you were using ODBC. However, I have tried many times
to keep the data types at number, but I get a "Zero length fields not allowed" ODBC
error. Just curious as to how you got it to work.)

---

Access version 97.
Oracle's ODBC driver
Use ODBC, I got your error message b4 when I tried to import another access table into Oracle, but I didn't get any error message this time about type. another most import thing is if table name is not upper case or any space inside, the table name xxx xxx will change to "xxx xxx" , this is really drived me crazy for a while, becos most OCBC driver can't seperate xxx xxx and "xxx xxx" .

quote:

---

Varchar2 is definitely the way to go... in 8i the max length is 32,767 bytes. The issue your running into when you tried to run a query using 3980 characters is unclear. Was the query string 3980 or was the actual post length 3980 characters? If you are referring to the post being 3980 then that sounds like an ADO issue to me.

---

follow is Oracle documentation
at "\Ora815\doc\server.815\a67779\ch2.htm"
1 VARCHAR2(size) Variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for VARCHAR2.

I never try over 4000, so don't know can use over 4000 or not. I will try it now :)

the string which I wanna insert is 3980 long, and I got error message from oracle, ADO can pass it to oracle without problem.


Response.redirect HELL<

quote:

---

If you are using a queries string that is 3980... ( wow ) try to run two queries. First run one that is about 2000 characters long. Then against that result set run the remaining 1980 characters of the query. For example:

-Instead of looking for red+dogs+longhair+housetrained
-first look for red+dogs... than against all of the red dogs look for longhair+housetrained

If the length of the post is 3980 and that is causing the problem then I wonder if the issue is localized on the search function only. Try to view the post manually by navigating to the forum where is resides.

---

quote:

---

It might just not be possible to use 100% identical code for every database. We had do add some extra code for MySql because it doesn't support paging i.e.
If you need extra code for sql-statements that are used a lot, that is bad because that would mean a lot extra databasespecific code. If it are things like this single searchpage that is no big problem.

---

quote:

---

Save me !!!!!!!!!!!!!!!!

I drop old Forum_reply table and create a new one, after that, I found this problem, I worked whole day on it, can't solve it

---

quote:

---

try to use this...

rs.open strSql, my_Conn, adOpenStatic, adLockReadOnly

---

I tried all cursor type, whatever forwardonly or static or dynamic..., seems nothing wrong with this, the problem just appeared after I change the table.

quote:

---

You can not have an ado recordset object created by an Oracle connetion that is ReadWrite... you have to force the recordset to be Read only. Oh and by the way... you may be able to side step this issue all together if you would just upgrade your MDAC to 2.6 you kind find it here:

http://www.microsoft.com/data/download.htm#26info

---

2.6 don't include any new ODBC or Jet driver than 2.5, I got 2.6 installed

quote:

---

If you still get an error after you change the statement above... try to emulate the query in SQL Plus. Get the SQL statement working first on the Oracle side then implement into your ASP. The "Unspecified Error" means that Oralce has sent an error back to the OLE/DB provider... and the provider has no idea what it means.

---

I did that, I try to copy the query string to Sqlplus to run it, sqlplus said miss FROM keyword, then I copy whole string to text edit, change one line to multi-line, rerun multi-line query in sqlplus, it successed. I don't know why, I can't see anything wrong with SQL statement, but just that error message

quote:

---

I found a way to create the tables within ASP which works much better for Oracle then regular SQL pass-through queries. Basically the idea is passing actual PL/SQL statements to the Oracle DB through ADO instead of just standard SQL. Here is an example:

First you have to create a command object for the Oracle connection like this:

With cmd
.ActiveConnection=conn
.CommandType=adCmdText
.CommandText=" CREATE TABLE TESTTABLE (FIELD VARCHAR(13) NOT NULL, FIELD VARCHAR(13)_
NOT NULL);".Execute
end With

This way is extreamly powerful for Oracle databases. First off this statemnent is sending pure PL/SQL statements (Oracle's native procedure language) to the Oracle DB. No need to worrie about what database version is beeing used... will work for all Oracle DBs. Try this.. let me know how it goes...

---

u r right, I am just lazy, so use script in sqlplus create table, when I start to put all scripts in ASP installation parts, certainly will use your way.

quote:

---

I am going to try and use the Oralce ODBC driver instead of Microsofts ODBC driver for Oracle to see the datatypes stay at number and not VARCHAR.

---

good luck :)



[/quote]

--------------------------------------------
Programming is like sex : One mistake and you have to support for a lifetime.<

quote:

---

I tried all cursor type, whatever forwardonly or static or dynamic..., seems nothing wrong with this, the problem just appeared after I change the table.

---

quote:

---

SELECT FORUM_MEMBERS.M_NAME, FORUM_MEMBERS.M_ICQ, FORUM_MEMBERS.M_YAHOO, FORUM_MEMBERS.M_AIM, FORUM_MEMBERS.M_TITLE, FORUM_MEMBERS.M_Homepage, FORUM_MEMBERS.M_LEVEL, FORUM_MEMBERS.M_POSTS, FORUM_MEMBERS.M_COUNTRY, FORUM_REPLY.REPLY_ID, FORUM_REPLY.R_AUTHOR, FORUM_REPLY.TOPIC_ID, FORUM_REPLY.R_MESSAGE, FORUM_REPLY.R_DATE FROM FORUM_MEMBERS , FORUM_REPLY WHERE FORUM_MEMBERS.MEMBER_ID = FORUM_REPLY.R_AUTHOR AND FORUM_REPLY.TOPIC_ID =41 ORDER BY FORUM_REPLY.R_DATE

---

quote:

---

What error does SQL Plus give you when you enter this...?

---