FAQ Database Discussion Community


Do I need to sanitize the input? PDO and mysql escape

php,html,mysql,pdo,database-administration
//create user $create_user = $dbh->prepare('INSERT INTO users (email, passwordhash) VALUES (:email, :passwordhash)'); Do I still need to use some kind of mysql_escape_string if there is the prepare statement as above?...

How to insert data into tables that have foreign keys

sql,database,oracle,database-administration
SQL> desc invoices Name Null? Type ----------------------------------------- -------- ---------------------------- INVOICE_ID NOT NULL NUMBER(6) COMPANY_ID NUMBER(6) STUDENT_ID NUMBER(6) BILLING_DATE DATE SQL> I would like to insert some sample data into this table. The company_id and student_id are foreign keys. This is what I'am entering: INSERT INTO invoices VALUES (SEQ_INVOICE.NEXTVAL,[what1],[what2],SYSDATE); I don't...

Deleting records from a huge table

sql,oracle,oracle11g,database-administration
I have table in my application which stores the data like pid (primary key) and (inst id) which stores keeps the history data and rows with latest inst id has the latest data. sample data has below: Ex: tablename is TESTTABLE ( pid number,instid number,datacol1 varchar2(100),datacol2 date); pid instid datacol1...

ORA-12545 : Oracle SID Suddenly Stopped working

oracle,database-administration,sid,ora-12545
I have a baffling problem. I have an Oracle 11g set up on Linux that was working fine. I was able to connect remotely from other machines using a SID based connection string such as sqlplus [email protected]:1521:orcl then for some inexplicable reason it stopped working. I'm not sure what I...

how to convert some mysql into pdo [closed]

php,mysql,pdo,database-administration
Can somebody help me convert this into PDO // verify email and password pair $userid = 0; $query = sprintf("SELECT id FROM users WHERE LOWER(email)='%s' AND passwordhash='%s'",strtolower($email),$pwdhash); $resource = mysql_query($query); if ($resource) { $row = mysql_fetch_row($resource); if (isset($row[0])) $userid = $row[0]; } This is my attempt: $userid = 0; $resource...

How to export 100 Oracle schemas to dump files

database,oracle11g,database-administration
We have about 100 Oracle schemas which should be exported to dump files. I'm not an Oracle expert and our DBA's doesn't seem to be very interested in helping this, or they don't know how to do it easily enough. This might be my naive thinking, but I would assume...

How does Write Concern in MongoDB Work at Thread level?

mongodb,database-administration,mongo-java,replicaset
As I see we can set write concern from the application, I was wondering how will MongoDB handle a scenario like this: Lets say we have two task/Process (P1 and P2) running on same database (The database has a replica set of three nodes, 1 primary and 2 secondary). P1...

PL/SQL error: PLS-00103: Encountered the symbol “EXCEPTION”

oracle,plsql,database-administration,error-handling
I'm making a PL/SQL error handling template. I'm getting this error: 'PLS-00103: Encountered the symbol "EXCEPTION"' and I can't figure out what's wrong. We're using an Oracle database. DECLARE err_num VARCHAR(100); -- We're not gonna do math on this so I made it a string. err_msg VARCHAR2(100); program VARCHAR2(100); statement...

Multiple foreign keys and multiple constrains

sql,oracle,database-administration
Let's assume I have a table called boxes with the box_id attribute as the PK. There are two other tables. The first one is red_boxes and the second blue_boxes. I have added a constraint to the red_boxes table ALTER TABLE red_boxes ADD CONSTRAINT fk_box_id FOREIGN KEY (box_id) REFERENCES boxes (box_id);...

Query to find the find the Data Sources used in the Scheduled jobs

sql,sql-server,sql-server-2008,ssis,database-administration
Is there is any query to find the data sources used in the scheduled jobs. I need to find the user ID used for each source and destination pointing the SSIS package. I need to get the following information through query. ...

PDO not being able to return the last_insert_id

php,mysql,pdo,database-administration
<?php session_start(); $userid = $_SESSION['userid']; $categorynumber=0; $categorynumber = create_category($userid); //keep the category number for future reference if($categorynumber > 0){ $_SESSION['categorynumber'] = $categorynumber; } echo $categorynumber; ?> echo $categorynumber prints 0 function create_category($userid) { // connect to database with PDO $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_DATABASE; $dbh = new PDO($dsn, DB_USER, DB_PASSWORD); //...

How to track the login details in SQL Server who deleted the data

sql-server-2012,database-administration,sql-delete
I came across a weird situation in SQL Server db. I see few developer are randomly deleting/updating the data over multiple tables. Is there any mechanism exits in SQL Server to capture all information like who deleted the records or who updated the records ext? Details: Microsoft SQL Server 2012...

Load data from excel file into a temporary table

sql,oracle,plsql,temp-tables,database-administration
I needed to load 100,000 rows of data from an excel file into a temporary table that I created using "on commit preserve rows". But somehow the most efficient methods did not seem to populate the temporary table due to session issues? I used Toad to Import Table Data and...

Error Code 13 in Mysql on UNIX. Can anyone Help me?

mysql,sql,unix,mysqldump,database-administration
I'm Using MySQL Server on UNIX System, when i'm execute the below query SELECT * INTO '/home/krunal/backupData/myBackup1.txt' FROM tbl_Property; it shows the error ERROR 1 (HY000): Can't Create /Write to file >'/home/krunal/backupData/myBackup1.txt' (Errorcode : 13) Can anyone tell me that how can i solve this error **Note** : I have...

Why my log file need 3 times the size of physical table in sql server

sql-server,database,database-administration
I have a table with approx 600 million records with table size of approximate 45 GB. I just fired a query to add another bigint column to it i.e. which should take approx 4GB to store i.e. 4GB = 600 Million * 8bytes There is no index on this table....

Is it safe to add a datafile to a tablespace which is being used by a index which is currently in the process of creation?

oracle,indexing,database-administration,tablespace,data-files
The situation is that I ran a create index query on a table (index type ctxsys.context) about 36 hours ago. The tablespace it refers to has 128gb allocated (4x32gb datafiles). As per latest situation the index is still in progress of being built however it has already taken up 100gb...

How to increment identity column in pair with another column?

sql,sql-server,database-administration
Suppose I want to create a table in SQL Server 2008 R2 as follows (this is probably wrong but it helps illustrate the point): CREATE TABLE BANK_ACCOUNT ( BankID INT NOT NULL, RecID INT IDENTITY(1,1) NOT NULL, IDValue varchar(100) NOT NULL, ) ON [PRIMARY] GO ALTER TABLE BANK_ACCOUNT ADD CONSTRAINT...