#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
Thursday, July 18, 2019
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
)
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
)
Labels:
Mysql
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:
setting database in phpmaker ok but error when create VIEW
please checked below:
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) 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:
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:
diiringi dengan musik / lagu.
environment:
- sqlserver 2005 di local database
- VB6
- Crystal report v8
- file applikasi ( source tak ada )
- koneksi ODB.
- file mp3 dan rename dengan nama " sajojo.mp3"
(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:
- penggunaan "ORDER BY NEWID()" di SQL Server 2005 untuk generate unique code !
- kemudian sort base on unique code tadi dan exclude data yang telah tampil
- 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:
Labels:
VB
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.
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
Labels:
Linux
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
#
# 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
Labels:
Linux
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
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
Labels:
Linux
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
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
Labels:
Linux
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:
# 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:
Labels:
juniper
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
Labels:
oracle
SQLSTATE = 22005 [Oracle][ODBC][Ora]ORA-01722: invalid number
environment:
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:
- clients pc: windows XP sp2/sp3
- oracle 11g
- powerbuilder 9
- connection use ODBC to oracle server
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.
Labels:
oracle,
powerbuilder
Subscribe to:
Posts (Atom)