Notes in Informix

#in squirrel you can disable the colon character of parameters inline request by unloading the plugin sqlparam

#enter in interactive mode only commands
#ctrl+d for closing the program
dbaccess - -

#command to connect a DB inside to dbaccess - -:
database your_db;


#import only the schemas of the database
dbschema -t all -d sicobf3 -nw -q -ns dboutput3.txt

#schema data model of sysmaster
#see http://www.informix.com.ua/articles/sysmast/sysmast.htm

#list databases
select * from sysmaster:sysdatabases;


#create database in informix with support of autocommit in jdbc
#see https://bugs.eclipse.org/bugs/show_bug.cgi?id=218190
#see https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0368.htm
#see https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0369.htm
create database sicobf3 with log;


#see http://stackoverflow.com/questions/36165976/invalid-default-value-for-column-variable-ed-fec-sol
#Below are to know how was set the variables from your client, especially the date format
SELECT      envses_name, envses_value
FROM        sysmaster:sysenvses
WHERE       envses_name IN (
                            'DBDATE', 
                            'GL_DATE', 
                            'CLIENT_LOCALE'
                            )
            AND envses_sid = DBINFO('sessionid')
ORDER BY    DECODE(envses_name,
                'DBDATE',           0,
                'GL_DATE',          1,
                'CLIENT_LOCALE',    2
            );
            
SELECT * FROM        sysmaster:sysenvses WHERE   envses_sid = DBINFO('sessionid');
SELECT  env_name, env_value FROM    sysmaster:sysenv WHERE   env_name = 'DBDATE';
SELECT  CURRENT::DATE FROM    sysmaster:sysdual;
SELECT  TODAY FROM    sysmaster:sysdual;

#set properties in the jdbc driver of informix, in this case GL_DATE since DBDATE is deprecated
#see https://groups.google.com/forum/#!msg/comp.databases.informix/iHH3LGuiP1o/AA5czvKNYSAJ
#see https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_263.htm
#see https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_266.htm
jdbc:informix-sqli://192.168.32.128:9088/sicobf3:informixserver=ol_informix1210;GL_DATE=%m/%d/%Y
#or with DBDATE=MDY4

#to know the current database used
select * from sysmaster:syssqlcurses

#to know the version of the DB
SELECT  DBINFO('version','full') FROM    sysmaster:sysdual;

#To know the schema of a table in informix:
dbschema -d DB_sicobf3 -t Table_t5641rstockestab

Notes on Oracle DB XE

SELECT username, privilege FROM USER_SYS_PRIVS;
SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS;
select user from dual;
select sys_context( 'userenv', 'current_schema' ) from dual; 
select * from all_users;

-- drop user
drop user soademo_01 cascade;

-- Create user in XE
create user soademo_01 identified by "soademo_01"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
grant dba, resource, connect to soademo_01;
ALTER USER soademo_01 DEFAULT ROLE "CONNECT",
                                  DBA,
                                  "RESOURCE";
								  
connect soademo_01/soademo_01; -- doesnt work in sql gui jdbc


-- northwind in oracle
-- http://blog.i-m-code.com/2013/03/28/northwind-for-oracle/
-- https://gist.github.com/segilbert/5095533

-- access to HR sample schema in XE
ALTER USER HR ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY HR; -- to pass the expired account
CONNECT HR/HR -- doesnt work in sql gui jdbc

#http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#05_05
#How do I connect as SYSDBA or SYSOPER?
#Properties props = new Properties(); props.put("user", "scott"); props.put("password", "tiger"); props.put("internal_logon", "sysoper");

#see http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html
#see all the variables related to language in oracle client jdbc
SELECT * FROM V$NLS_PARAMETERS
SELECT * FROM NLS_SESSION_PARAMETERS;
SELECT USERENV ('language') FROM DUAL;

#modify params of session because to_date('08-DEC-48','DD-MON-RR') was giving me errors
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American' -- <- here without semicolon!

#sqldeveloper change idiom of the gui
#http://stackoverflow.com/questions/7768313/how-can-i-change-the-language-to-english-in-oracle-sql-developer
#http://stackoverflow.com/questions/2333994/how-to-avoid-variable-substitution-in-oracle-sql-developer-with-trinidad-toba

#Shows the current schema
select sys_context( 'userenv', 'current_schema' ) from dual; 

#List all the tables accessibles for the user.
SELECT * FROM dba_tables where table_name like '%CATALOGO%';

#List all the synonyms accessible for the user.
select * from all_synonyms where table_name like 'T%'