freefly
一门技术,如果不能讲出来,那么就是没有理解,如果不能很好的讲出来,那么就是理解不够透彻!
posts - 9,comments - 3,trackbacks - 0
1。我们可以做一个平凡的人,但决不能做一个平庸的人
2。给生活一个笑脸,生活会给你一个太阳
3。路漫漫其修远兮,情深深且珍惜
posted @ 2007-01-16 13:56 freefly 阅读(199) | 评论 (0)编辑 收藏
  1. create view

          ORACLE:    CREATE OR REPLACE VIEW V_FA_ADD_CO AS

             SQLSEVER:    IF EXISTS (SELECT 1
                                                            FROM  sysobjects
                                                            WHERE  idobject_id ('V_FA_ADD_CO')
                                                             AND   type = 'V')
                                    DROP VIEW V_FA_ADD_CO;
                                    CREATE VIEW V_FA_ADD_CO AS

    
2. the equal between join and (+)
      
       the table(its fields stay with (+)) right  join another  corresponding   table.
       for example: select * from a,b where a.id=b.id(+)
               equals  select * from b rigth join a on a.id=b.id
               equals  select * from a left join a on a.id=b.id

        notes:join先匹配有对应的记录,
              (+)却是按顺序来的
 

    3. substr,substring
     
for example: there is a table's field "userName" and it's value is "wanghuiling".
       sqlserver: substring(userName,0,4) = "wan",substring(userName,1,4) = "wang"
       oracle:  substr(userName,0,4)="wang",substr(userName,1,4)="wang"
   
 4. link sign
     sqlserver: "+"
    
oracle:"||"
 
5. update a table's some fields

     for example:there are two tables: students1 and students2

     sqlserver:update students     (can't use alias)
                   set name=s2.name,sex=s2.sex,age=s2.age,tel=s2.tel
                   from students s2 where s1.id=s2.id;
     oracle: update students1 s1
               set (name,sex,age,tel)=
               (select name,sex,age,tel from students2 s2 where s1.id=s2.id);

6. Date
    for example:there are a field of  date type:input_date  and its value is 2007-08-09.   
    sqlserver : year(input_date)=2007,month(input_date)=8,day(input_date)=9
     oracle : to_char(input_date,'YYYY')=2007, to_char(input_date,'MM')=8, to_char(input_date,'DD')=9

     sDate : a java String variable
     sqlserver : input_date = '"+sDate+"' 
     oracle : input_date =  to_char( '" + sDate + "',' 'YYYY-MM-DD'')

posted @ 2006-12-31 15:42 freefly 阅读(557) | 评论 (0)编辑 收藏

      Ultimatelly,I solved my problem.    My work is switch my application from mysql to sqlserver database .   At the very start,I forgot start sqlserver service,so the error "refused connect"  always appear,(so stupid error).Next,ther error  "Hibernate operation: could not execute query; uncategorized SQLException for SQL [select user0_.ID as ID, user0_.USERNAME as USERNAME0_, user0_.PASSWORD as PASSWORD0_ from user user0_]; SQL state [S1000]; error code [156]; 在关键字 'user' 附近有语法错误。; nested exception is java.sql.SQLException: 在关键字 'user' 附近有语法错误."   comes out ,  the problem scratchs my head over .this moring i got one friend's help and eventually found the "user" should not be used as a table name,because "user" is a key in sqlserver.  
      Below is work I have done : (Note: my env is eclipse+myeclipse)
      First:modify applicationContext.xml:
      
      From:
      1.<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
           <property name="driverClassName">
                 <value>com.mysql.jdbc.Driver</value>
           </property>
           <property name="url">
                <value>jdbc:mysql://localhost:3306/test</value>
           </property>
           <property name="username">
                <value>root</value>
           </property>
           <property name="password">
               <value>whl</value>
           </property>
        </bean>
     2. <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
     
     To:
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
              <property name="driverClassName">
                   <value>net.sourceforge.jtds.jdbc.Driver</value>
              </property>
              <property name="url">
                   <value>jdbc:jtds:sqlserver://localhost:1433/test</value>
               </property>
               <property name="username">
                    <value>sa</value>
               </property>
               <property name="password">
                    <value>mssqlsys</value>
              </property>
         </bean>

        <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
      Second: import the sqlserver driver:jtds-1.1.jar

posted @ 2006-04-11 12:41 freefly 阅读(342) | 评论 (0)编辑 收藏