Author Archives: kuperusm

MySQL drop all tables using stored procedure

Posted on by
Reply 
DELIMITER $$
DROP PROCEDURE IF EXISTS drop_all_tables $$
CREATE PROCEDURE drop_all_tables(IN scheme VARCHAR(255))
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_tableName VARCHAR(255);
    DECLARE v_cursor CURSOR FOR
        SELECT table_name 
        FROM   information_schema.TABLES
        WHERE  table_schema = scheme;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN v_cursor;
    REPEAT FETCH v_cursor INTO v_tableName;

    IF NOT v_done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', v_tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL v_done END REPEAT;

    CLOSE v_cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;

Filtered type ahead JComboBox

Posted on by
Reply

Swing offers nice possibilities to use a JComboBox with some sort of pre-selecting or type ahead features. One of the major drawbacks is, it does not remove items who don-not match at all (when typing).

In one of my projects it was required to filter out possibilities from the JComboBox when the user starts typing, so the list gets smaller when the user starts typing (large lists!). Of course the list grows back again when backspace is pressed(*).

I’ve found several solutions around, but non of them are completely satisfactory for me, mostly because they are to specific or to complex (maybe my solution isn’t the best either).

Some features and some drawbacks are known for the version presented in this issue:

  • because of using backspace and rebuilding the JComboBox, all items must be present when an instance is created (* as shown earlier)
  • because the user can enter text, the first item should be an empty one to hold the search term
  • when no match occurs, getSelectedItem(0) is there to get the users query

For me it was important to use the getSelectedItem() method instead of the getSelectedIndex() method on an ActionEvent, since the user might have typed some text which was not present yet. The text from getSelectedItem() then is added to the database (in my application). Because of the filtering, double occurrences are minimized this way.

Link: FilteredTypeAheadComboBox.java
Link: FilteredTypeAheadDemo.java
Javawebstart: FilteredTypeAheadDemo.jnlp
(You might want to accept the domain in your Java security setting).
 

Setup and configure Apex mail

Posted on by
Reply

Make sure you have Apex setup to access your mailserver:

sqlplus / as sysdba
alter session set current_schema = apex_040200;
begin
  apex_instance_admin.set_parameter('SMTP_HOST_ADDRESS', 'smtpserver.domain.com');
  apex_instance_admin.set_parameter('SMTP_HOST_PORT', '25');
  commit;
end;
/

Setup ACL:

begin
  dbms_network_acl_admin.create_acl (
   acl         => 'mailserver.xml',
   description => 'permissions to access outgoing mailserver',
   principal   => 'SCOTT',
   is_grant    => TRUE,
   privilege   => 'connect'
  );
  commit;

  dbms_network_acl_admin.assign_acl ('/sys/acls/mailserver.xml','smtpserver.domain.com',25);
  commit;

  dbms_network_acl_admin.add_privilege('/sys/acls/mailserver.xml','APEX_040200',TRUE,'connect');
  commit;  
end;
/

Send some testmail:

declare
    cursor c_ws is
      select workspace_id
      from   apex_workspaces
      where  workspace = 'YOUR_WORKSPACE';

  l_ws_id number;
  l_id    number;
begin
  open  c_ws;
  fetch c_ws into l_ws_id;
  close c_ws;
  apex_util.set_security_group_id(l_ws_id);
  --
  l_id := apex_mail.send(
        p_to        => 'to_address@somewhere.com',
        p_from      => 'from@elsewhere.nl',
        p_subj      => 'subject',
        p_body      => 'text',
        p_body_html => null);
  apex_mail.push_queue;
  commit;
end;
/

Apex ACL: grant access to website / webservice

Posted on by
Reply

Webservice can be a website of course. On sql prompt (sqlplus / as sysdba)

begin
  dbms_network_acl_admin.create_acl (
  acl => 'webservice.xml',
  description => 'permissions to access webservice',
  principal => 'SCOTT',
  is_grant => TRUE,
  privilege => 'connect');
  commit;

  dbms_network_acl_admin.add_privilege(
  acl       => '/sys/acls/webservice.xml',
  principal => 'SCOTT',
  is_grant  => TRUE,
  privilege => 'resolve');
  commit;

  dbms_network_acl_admin.assign_acl(acl  => '/sys/acls/webservice.xml', host => 'asterix');
  dbms_network_acl_admin.assign_acl(acl  => '/sys/acls/webservice.xml', host => 'asterix.bretagne.fr');
  commit;
end;
/