Posts

How to Create Connection String Easily

Image
will explain to you an easy way to get any Database Connection String so you can use this String to Connect to your database using any developing Language. Right Click on your Desktop Then new Text Document. Change File extension from .txt to .udl file. UDL file is Data Link File, Opened by Microsoft Data Access. Double Click on the file after Changing the Extension. You will see Form Similar to this Form. From Provider Tab, You can choose a type of Database Provider you want to use (Access, SQL Server, Oracle …). Connection Tab will be different according to the provider you choose before from provider tab. The Connection here for SQL Server Provider. The server name is the Database instance Name plus Machine Name. Logging to database require password and username that's only if you use Mixed Mode in Installing SQL Server Database If you don't know the user and password user windows Integrated Security. Then if the information above is correct you will get...

Number of Days in Current Month

This Query to get number of days in Specific month at Specific Year : select to_number(to_char(last_day(to_date('01/02/2008','DD/Mm/yyyy')),'DD')) as days from dual you have to replace date '01/02/2008' with the date you want or the date from sysdate Thanks,

Manage Your Database Connection

This is an Example for how to manage a SQL Server database Connection using Visual Web Developer 2005 Dim _CON As String = "Data Source=MOHAMEDSAMI-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True" Here this is the Connection String and I will tell you in another blog how you can get this String easily, cause it is the most important thing, if you write the right connection you will have a successful connection to the database. Data Source = Your Database Instance, you can type it in many ways like (. /SQLEXPRESS, localhost/SQLEXPRESS, machine name/SQLEXPRESS). Initial Catalog = Your Database Name. Public newSqlConnection As SqlConnection = New SqlConnection(_CON) I defined a new Object from SqlConnection so I can manage and establish the Connection. I have to open the Connection so I can execute any Query I want. Private Function OpenConnection() As Boolean Try                newSqlConnection.Open()    ...

CURRENT_SCHEMA Approach

This method uses the CURRENT_SCHEMA session attribute to automatically point application users to the correct schema. First, we create the schema owner and an application user. CONN sys/password AS SYSDBA -- Remove existing users and roles with the same names. DROP USER schema_owner CASCADE; DROP USER app_user CASCADE; DROP ROLE schema_rw_role; DROP ROLE schema_ro_role; -- Schema owner. CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner; -- Application user. CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT TO app_user; Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects. Next, we create some roles to allow read-write and read-only access. CREATE ROLE schema_rw_role; CREATE ROLE schema_ro_role; We want to give our application user read-wr...

Synonym Approach

This method relies on synonyms owned by the application user to point to the correct location of the schema objects. First, we create the users in a similar way to the previous example. CONN sys/password AS SYSDBA -- Remove existing users and roles with the same names. DROP USER schema_owner CASCADE; DROP USER app_user CASCADE; DROP ROLE schema_rw_role; DROP ROLE schema_ro_role; -- Schema owner. CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner; -- Application user. CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT, CREATE SYNONYM TO app_user; Once again, the application user can connect, but does not have any tablespace quotas. The difference here is that the application user does have the privilege to create synonyms. Next, we create some roles to allow read-write and read-only access and grant the re...

Combine more than one Row in One Row

Working On Oracle Sample Database Tables ( Departments and Employees) 1- Suppose we want to select employees Names in each department Query : SELECT departments.department_id, departments.department_name, employees.first_name || ' ' || employees.last_name FROM employees JOIN departments ON departments.department_id = employees.department_id ORDER BY departments.department_id;   Results : 10 Administration Jennifer Whalen 20 Marketing Michael Hartstein 20 Marketing Pat Fay 30 Purchasing Den Raphaely 30 Purchasing Karen Colmenares 30 Purchasing Alexander Khoo 30 Purchasing Shelli Baida 30 Purchasing Sigal Tobias 30 Purchasing Guy Himuro 40 Human Resources Susan Mavris 50 Shipping Donald OConnell ................................................................. we want to know how we can make the results like : 10 Administration Jennifer Whalen 20 Marketing Michael Hartstein, Pat Fay 30 Purchasing Alexander Khoo, Den Raphaely, Guy Himuro, Karen Colmenares, Shelli Baid...

How to Select Random using Oracle

Working on Employees Table : Query : select * from (select * from Employees ORDER BY dbms_random.value ) where rownum <= 10 Results : 143 Randall Matos RMATOS 147 Alberto Errazuriz AERRAZUR 152 Peter Hall PHALL 135 Ki Gee KGEE 193 Britney Everett BEVERETT 122 Payam Kaufling PKAUFLIN 130 Mozhe Atkinson MATKINSO 181 Jean Fleaur JFLEAUR 110 John Chen JCHEN 105 David Austin DAUSTIN Every time you will run same Query you will have new results i wish this can help you in your Development Process Thanks,