Home about IT Motivation Course Sales Project About Me

Thursday, July 18, 2019

EXPORT & IMPORT SQL FILE TO MYSQL TABLE

#IMPORT SQL FILE TO TABLE
Login to mysql database first then run below script:
source namefile.sql

or from logout of mysql
mysql -u root -p DBName < NameofImportTable.sql

#EXPORT TABLE TO SQL FORMAT
exit from mysql sql first then run below script:
mysqldump -u root -p DBname TableName > NameofExportTableFile.sql

LOAD MANY TEXT FILE TO MYSQL TABLE

#01.5  the script executed from out of mysql with run file coba.bat
All file: bat file & txt file stored in folder c:\xampp\mysql\bin

echo on
setlocal enabledelayedexpansion
FOR %%f IN ("*.txt") DO (
mysql -e "LOAD DATA local INFILE '"%%f"' INTO TABLE tabelname FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'" -u root --password=password DBname
DONE
)

Saturday, March 08, 2014

PHPMAKER - ORACLE: ORA-01005: null password given; logon denied

error message: " ORA-01005: null password given; logon denied" when create view at phpmaker and no display pop up when generated file success.

environment:
  • database: oracle
  • front end: phpmaker

setting database in phpmaker ok but error when create VIEW

please checked below:
  • make sure netconfig setting at oracle successcfull login
  • make sure ODBC setting to database successfully login 
  • going to php.ini then make sure "extension=php_oci8.dll" unremark




Thursday, December 26, 2013

applikasi acak hadiah/ random number: VB6 - SQL 2005 - CR8

Berikut Applikasi untuk acak hadiah, biasa digunakan untuk family gathering atau sejesnisnya.
diiringi dengan musik / lagu.
environment:
  1. sqlserver 2005 di local database
  2. VB6
  3. Crystal report v8
  4. file applikasi ( source tak ada ) 
  5. koneksi ODB.
  6. file mp3 dan rename dengan nama " sajojo.mp3"
cara kerja
(1) masukkan data hadiah, urut dari terbesar ( mahal) ke yang terkecil ( murah) ke table "tbl_gift"
(2) masukkan data peserta ( acak boleh, urut boleh) ke table "tbl_user"
(3) applikasi ( file exe, rpt, mp3) simpan di drive C dan dalam folder "undian"
(4) data peserta akan diacak dan diberi nomor random lalu diurutkan di ambil 10 tertinggi ( TOP 10)
(5) data TOP 10 yang dihasilakn akan dipasangkan dengan hadiah, dari yang terendah.
(6) setelah berpasangan antara 10 peserta yang dapat dengan hadiah, maka 10 peserta tadi akan dikeluarkan dari list berikutnya. Tidak ikut diacak lagi, begitupun juga dengan 10 hadiah tadi.
(7) demikian dilakukan sampai habis
(8) ada menu reset jika ingin mulai dari awal (acak dari awal lagi)

Inti dari process random aplikasi ini adalah:
  1. penggunaan "ORDER BY NEWID()" di SQL Server 2005 untuk generate unique code !
  2. kemudian sort base on unique code tadi dan exclude data yang telah tampil
  3. newid(), hanya ada di SQL Server mulai versi 2005.


berikut table yang perlu dicreate:

CREATE TABLE IF NOT EXISTS `tbl_final` (
  `user_id` int(11) NOT NULL,
  `hadiah_id` int(11) NOT NULL,
  PRIMARY KEY (`hadiah_id`)
);



CREATE TABLE IF NOT EXISTS `tbl_gift` (
  `id_gft` int(11) DEFAULT NULL,
  `nm_gft` varchar(50) DEFAULT NULL,
  `lvl_gft` varchar(50) DEFAULT NULL
);



CREATE TABLE IF NOT EXISTS `tbl_list` (
  `name_lst` varchar(20) DEFAULT NULL,
  `gift_lst` varchar(20) DEFAULT NULL
)  ;


CREATE TABLE IF NOT EXISTS `tbl_newfinal` (
  `user_name` varchar(50) NOT NULL,
  `user_gift` varchar(50) NOT NULL
) ;

CREATE TABLE IF NOT EXISTS `tbl_user` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `user_gift` varchar(20) NOT NULL
);



# untuk file silakan download disini:

connetion ODBC to MYSQL Linux error: Connection Failed: [HY000] ][MySQL][ODBC 3.51 Driver] Can't connect to MySQL server on ..

error message on ODBC:
Connection Failed: [HY000][MySQL][ODBC 3.51 Driver] Can't connect to MySQL server on '192.168.100.3'(10061)

environment:
Server: Linux + MySQL
Client: Windows 7 + VB6 + ODBC connection

Solution:
follow below steps:
(1) go to your MySQL Server:
      edit on file : /etc/MySQL/my.cnf
      changes:  bind-address = 127.0.0.1
      become to : bind-address = 0.0.0.0

(2) go to MySQL database
      Privileges:
      add user root
      host : any host or IP your PC
      password
      Global privileges: grant all table

# restart the server after finish
    

the issue due to authentification issue, only access from localhost then changes to access from others host.




Saturday, December 21, 2013

SQUID example



example SQUID

# WELCOME TO SQUID 2.7.STABLE3

# ----------------------------

 

acl hanyabuka url_regex "/etc/squid/hanyabuka"
acl bloksemua dst 0.0.0.0/0.0.0.0
 
acl bukamanager src "/etc/squid/bukamanager"
acl bukastaff src "/etc/squid/bukastaff"

 

 
acl blokport port 999

acl jamkerja time SMTWHFA 07:00-12:00 13:30-18:00
acl blokflash rep_mime_type video/flv video/x-flv
acl blokflash rep_mime_type ^video/x-flv
acl blokflash rep_mime_type ^video/x-ms-asf
acl blokflash rep_mime_type -i ^video/
acl blokflash rep_mime_type -i ^video\/
acl blokflash rep_mime_type ^video/mpeg4
acl blokflash rep_mime_type application/x-shockwave-flash

 

 

 

acl all src all
acl manager proto cache_object
acl localhost src 127.0.0.1/32
acl to_localhost dst 127.0.0.0/8
 
acl localnet src 192.168.0.0/24 # RFC1918 possible internal network
acl localnet src 10.0.10.0/24
acl localnet src 172.20.10.0/24

 

acl bukaport port 21
acl bukaport port 22
acl bukaport port 25
acl bukaport port 53
acl bukaport port 80
acl bukaport port 110
acl bukaport port 143
acl bukaport port 389
#acl bukaport port 443
acl bukaport port 465
acl bukaport port 993
acl bukaport port 995
acl bukaport port 1521
acl bukaport port 3128
acl bukaport port 7025
acl purge method PURGE
acl CONNECT method CONNECT
acl blokip dst "/etc/squid/blokip"
acl blokfile urlpath_regex "/etc/squid/blokfile"
acl blokurl dstdomain "/etc/squid/blokurl"
acl blokurl_schedule dstdomain "/etc/squid/blokurl_schedule"
acl blokkeyword url_regex -i "/etc/squid/blokkeyword"
acl blokkeyword_schedule url_regex -i "/etc/squid/blokkeyword_schedule"


http_access allow manager localhost
http_access deny manager
http_access allow purge localhost
http_access deny purge
#http_access deny !Safe_ports

#http_access deny CONNECT blokhttps
http_access deny CONNECT !bukaport
##http_access deny CONNECT
 
 

#http_access deny CONNECT blokurl jaringan jamkerja
 

http_access deny !bukaport
##http_access deny

http_access allow hanyabuka jamkerja
http_access deny bloksemua jamkerja !bukamanager
 
http_access deny blokport jamkerja

####http_access deny blokport
 
http_access deny blokflash jamkerja

 
 
 
http_access deny blokip !bukamanager
http_access deny blokfile !bukamanager
http_access deny blokurl !bukamanager
http_access deny blokkeyword !bukamanager
http_access deny blokurl_schedule jamkerja !bukamanager
http_access deny blokkeyword_schedule jamkerja !bukamanager
####http_access allow jaringan !blokhttps
##http_access allow jaringan
http_access allow bukamanager
http_access allow bukastaff

 
 

http_access allow localhost
http_reply_access allow all
http_reply_access deny blokflash !bukamanager

 
 
 
icp_access allow localnet
icp_access deny all

http_port 3128 transparent

 
 
#We recommend you to use at least the following line.
hierarchy_stoplist cgi-bin ?

 
cache_dir ufs /var/spool/squid 500 16 256

 
access_log /var/log/squid/access.log

 
refresh_pattern ^ftp: 1440 20% 10080
refresh_pattern ^gopher: 1440 0% 1440
refresh_pattern -i (/cgi-bin/|\?) 0 0% 0
refresh_pattern . 0 20% 4320
acl apache rep_header Server ^Apache
broken_vary_encoding allow apache
 
extension_methods REPORT MERGE MKACTIVITY CHECKOUT
cache_mgr webmaster@your_email.com

visible_hostname www.your_website.com
hosts_file /etc/hosts
coredump_dir /var/spool/squid
 
 
no_cache deny all

Friday, December 20, 2013

IPTABLES example

#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will "exit 0" on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.

#/usr/bin/setterm -powersave off -blank 0
#/usr/local/bin/cmatrix -C green -u 2
#/usr/bin/clear
echo 1 > /proc/sys/net/ipv4/ip_forward


iptables -t nat -A POSTROUTING -j MASQUERADE
iptables -t nat -A PREROUTING -p tcp --dport 80 -j REDIRECT --to-port 3128
iptables -t nat -A PREROUTING -p tcp --dport 8080 -j REDIRECT --to-port 3128

##### block https facebook, but certain IP allow
iptables -I FORWARD -m tcp -p tcp -d 31.13.0.0/16 --dport 443 -s ! 192.168.20.11 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 173.252.0.0/16 --dport 443 -j REJECT



iptables -I FORWARD -p tcp -d 74.115.0.34 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 74.115.0.35 --dport 443 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 74.125.235.3 --dport 443 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 69.171.228.24 --dport 443 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 69.171.0.0/24 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 204.15.0.0/24 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 66.220.0.0/24 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 46.137.3.43 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 174.129.97.137 -j REJECT
iptables -I FORWARD -m tcp -p tcp -d 184.72.181.177 -j REJECT




######bock CAMPROG, voice call

iptables -A FORWARD -p tcp --dport 2779 -j DROP
iptables -A FORWARD -p tcp --dport 5100 -j DROP
iptables -A FORWARD -p udp --dport 5222 -j DROP
iptables -A FORWARD -p tcp --dport 5228 -j DROP




exit 0

CLI: copy hidden file from linux to to other PC

example to copy hidden file from Linux to other PC windows in command line Linux.
The point is your file make sure have permission to copy/ access

(1) check folder contents
root@server02:/home/server02# ls –al /var/log/squid

(2) copy to other folder
root@server02:/home/server02# cp  /var/log/squid/access.log  /home/server02/

(3) changes permission file
root@server02:/home/server02# chmod 755 /home/server02/access.log
 
(4) copy use WinSCP
 
 

SQUID: blocking https facebook

(1) open facebook use https , login then use CMD in windows, check the IP
netstat - n

(2) Use whoisIP make sure facebook IP.

(3) go to SQUID then insert below code in IPTABLES:

iptables -I FORWARD -m tcp -p tcp -d 31.13.0.0/16 --dport 443 -j REJECT


for allow certain IP for access, see below:

iptables -I FORWARD -m tcp -p tcp -d 31.13.0.0/16 --dport 443 -s ! 192.168.20.11 -j REJECT

Tuesday, March 12, 2013

kenalan dengan CLI JUNIPER

berikut cara login:
# via ssh port 22 atau dengan rs232 dari port console

login:
password

cli

configure

untuk menanpilkan script
show

untuk edit script:
edit


contoh script berikut:


Wednesday, February 20, 2013

ORA-12528: TNS:listener: all approriate instances are blocking new connecti

from oracle forum:
 
The error message as below:

connecting ...ORA-12528: TNS: listiner: all appropriate instances are blocking new connections. 
The tes did not succeed.

some of the information you provided may be incorrect. 
Click back to review the information provided for net service name, 
or change login to change username

HOw to find listener to see if the service is in BLOCKED state:
C:\Documents and Settings\chinar>lsnrctl services
  

C:\>lsnrctl



LSNRCTL> services



post the output here.



also



SQL> select instance_name,  status, database_status from v$instance;
 
 
 
C:\Documents and Settings\FAY>sqlplus
 
 
  
C:\Documents and Settings\FAY>lsnrctl
 
 
 
SQL> select instance_name, status, database_status from v$instance;
 
INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
orcl             STARTED      ACTIVE
 
 
 
 
 



SQL> shutdown
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             444599172 bytes
Database Buffers          159383552 bytes
Redo Buffers                7135232 bytes
ORA-00214: control file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
version 1723 inconsistent with file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL' version 1721
 
 
SQL> conn scott/tiger@orcl
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
 
 
Warning: You are no longer connected to ORACLE.
 
 
I had already asked for the database state (select name,open_mode,database_role from v$database) in my previous post.

The database is not getting mounted.The controlfile is not consistent.


control file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
version 1723 inconsistent with file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL' version 1721


1. Shutdown the database

2. Make a copy of all the controflies.

3. replace the control02.ctl with copy of control01.ctl

4. startup the database

5. Then try to login to the database.




https://forums.oracle.com/forums/thread.jspa?threadID=1098776&start=15&tstart=0

SQLSTATE = 22005 [Oracle][ODBC][Ora]ORA-01722: invalid number

environment:
  • clients pc: windows XP sp2/sp3
  • oracle 11g
  • powerbuilder 9 
  • connection use ODBC to oracle server
some users use windows XP have error message as below:
SQLSTATE = 22005
[Oracle][ODBC][Ora]ORA-01722: invalid number
No changes made to database.

when changes value use combo box

below step to solve at clients PC:
  • please unisntall previous oracle clients at their PC,
  • remove ODBC setting and environment DLL for powerbuilder
  • reinstall oracle again and 
  • re setting path DLL files for powerbuilder in environment variabel.