Browse: Page 2
By Chris on September 13, 2014
I was trying to update a table that using sql join but I kept getting ORA-00933
I tried the following:
Using A SQL JOIN In A SQL UPDATE Statement Using A SQL JOIN In A SQL UPDATE Statement (www.bennadel.com)
update table using inner join (venushuynh.wordpress.com)
but I kept getting the ORA-00933
then tried this Oracle Update with Join (geekswithblogs.net) but still didn’t work.
After some deep search I’ve found my error in a oracle forum: SQL Error: ORA-01779: cannot modify a column which maps to a non key-preser
Even though it’s not the same ORA error it fixed the problem nonetheless
Here my sql statement that worked for me:
UPDATE mytable m
set m.status=7
where m.id in (select mytable.id from mytable inner join otherTable on mytable.matchid=othertable.matchid where othertable.userid=1234)
Posted in db | Tagged db, error, oracle |
By Chris on September 7, 2014
Another problem I’ve it recently with saveOrUpdate that can be fix by using the same saveOrUpdate but give the class name.
Using the session.saveOrUpdate(myObject) might give you a unknown entity while using the same method with the class name in parameter won’t.
session.saveOrUpdate(myObject); //give a unknown entity
session.saveOrUpdate("MyObjectClassName", MyObject); //Work
The important difference between the org.hibernate.Session class methods, save & saveOrUpdate is, save generates a new identifier and results in an INSERT query, whereas saveOrUpdate does an INSERT or an UPDATE.
For a more complete summary of what each of these method do check the hibernate site or this stackoverflow question/answer
Posted in db, prog | Tagged db, hibernate |
By Chris on September 2, 2014
If you happen to rename your folder that contain your jsp and js, don’t forget to update the struts config (and the tiles). This will save your precious time even hours …
Posted in java | Tagged java, javascript, js, jsp, struts |
By Chris on August 30, 2014
For some reason Oracle have no Boolean
Since there is no BOOLEAN datatype in Oracle, as far as tables are concerned.
CREATE TABLE BooleanTable (MyBool BOOLEAN);
return :
ORA-00902: invalid datatype
But there is a BOOLEAN datatype in PL/SQL.
What should we do instead?
According to Tom at ThinkOracle you could create the table using char like this :
CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y', 'N' )));
In our case (where I used to worked) we used Number instead. Same thing we did with the char but instead of “y/n” we use ‘0’ or ‘1’ where ‘0’ = false and ‘1’ = true
Related:
http://thinkoracle.blogspot.ca/2005/07/oracle-boolean.html
Posted in db | Tagged boolean, oracle, pl/sql, sql |
By Chris on August 23, 2014
One of our engineer made some change to the Jmeter script and I couldn’t find what was the error. The test was giving me “Error in TestPlan – see log file”
After some digging I found out there was an ext lib missing.
ERROR - jmeter.save.SaveService: Conversion error com.thoughtworks.xstream.converters.ConversionException: kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup
—- Debugging information —-
message : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup
cause-exception : com.thoughtworks.xstream.mapper.CannotResolveClassException
cause-message : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup
class : org.apache.jorphan.collections.ListedHashTree
required-type : org.apache.jorphan.collections.ListedHashTree
path: /jmeterTestPlan/hashTree/hashTree/kg.apc.jmeter.threads.SteppingThreadGroup
Finally this error was due to 2 things
1- We needed to have the same version of jmeter than the other programmer was using (2.5.1 + at the time)
2- We needed a library that I didn’t know about http://jmeter-plugins.googlecode.com/files/JMeterPlugins-0.5.1.zip
Posted in jmeter | Tagged jmeter, load testing |
By Chris on August 9, 2014
When you are handling string you sometime have single quote in your string. I was looking on how to handle that in Oracle and it’s not pretty but you can cover that case with the chr(39) function, 39 being the code for the single quote.
So it you are looking the “it’s” you have to look for “it‘||CHR(39)||’s”
I’ve read I’ve could have use 2 quote or the $ in latter version. In that case:
SELECT 'it''s' name FROM DUAL;
Or use the new (10g+) quoting method
SELECT q'$it's$' NAME FROM DUAL;
Note I’ve used the chr(39) and worked fine for me I didn’t try the other one, so good luck with that.
Posted in db, prog | Tagged oracle, quote, single quote, sql |
By Chris on August 2, 2014
I had in the past had to work with oracle and had to convert it to mysql. Unfortunately (or fortunately) that project was cancel but here are the bit of information I’ve found that I didn’t know about and made me lost a lot of time.
Oracle vs mySQL datatype edition
From mySQL Forum:
NUMBER Any NUMERIC
DEC Any NUMERIC
DECIMAL Any NUMERIC
NUMERIC Any NUMERIC
DOUBLE PRECISION Any NUMERIC
FLOAT Any NUMERIC
REAL Any NUMERIC
SMALLINT Any SMALLINT
VARCHAR <256 VARCHAR
VARCHAR2 <256 VARCHAR
CHAR <256 CHAR
VARCHAR2 >255 TEXT
VARCHAR >255 TEXT
CHAR >255 TEXT
LONG <256 VARCHAR
LONGRAW <256 VARCHAR
RAW <256 VARCHAR
LONG >255 TEXT
LONGRAW >255 TEXT
RAW >255 TEXT
DATE – DATETIME (Since DATE in oracle can include time information!)
From what we needed we had to
decimal(50,0) = NUMBER
decimal(22,0) = INTEGER
DATE = datetime
Now that I look at it I think It would have been better to turn them both to number.
The best course of action is to find a DBA and use tool for the job like omega sync like it’s recommended in the following stackoverflow thread.
Posted in db, prog | Tagged db, migration, mysql, oracle |
By Chris on July 19, 2014
The to_date function we talked earlier make a come back.
The wrong way to do it is :
SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = '12-04-27'
you end up comparing string in that case to work you need to do the following:
SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = to_date('12-04-27','yy-mm-dd')
I saw some example where user needed to use the TRUNC function on the date_and_time, so if it doesn’t work with the previous answer try using the TRUNC function.
Posted in db, prog | Tagged date, oracle, search, to_date |
By Chris on July 12, 2014
I was having problem making one of my SQL query work in ORACLE until I realize that I got the check on the date wrong. It seem that Oracle didn’t interpreted my query has I expected when handling a date with a greater or lesser than operator (< or >).
So this won’t work :
date_and_time <= '01-DEC-02'
After some digging I’ve found out that there is
But this will :
to_date('01-DEC-2002','dd-mmm-yyyy');
The
TO_TIMESTAMP can be used to, check the reference for more information: TO_TIMESTAMP reference and TO_DATE Reference
Posted in db | Tagged date, datetime, oracle, to_date |
By Chris on July 9, 2014
Really quick post to a problem I’ve got un hibernate when converting to mySQL, I’ve found that the problem was because we were using a generator class “sequence” and in mySQL we needed to use “assigned” or “increment” instead.
Note you might not have that problem in your project but that was preventing me from creating my ids my mySQL db.
What are that generator class thing, well it how you generate your id. In oracle it usally use a sequence (hence the setting was sequence in hibernate) and mySQL didn’t support sequence at the time I was trying to convert it.
Sequence is safer if any other program/person is going to insert into the database table. Increment is less safer solution (but portable, to those db that don’t support Sequence) and good for testing and\or getting started. There are better ways than using Increment for use in production.
Note that you could use the generationType.AUTO and hibernate will produce the best strategy for you db.
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
Related:
How to have multiple database in a eclipse Java project (candidjava)
Changing database from Hibernate (stackoverflow)
Hibernate Generator class (javaTpoint)
Posted in db | Tagged hibernate, java, mysql, oracle |
Recent Comments