Databases

57K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am trying to read  a date  field into my model
I have a postgresql database and i am doing the read from
a c# method  (see the code below)

[]{
                        LoansDetailModel model = new LoansDetailModel();

                        if ((string)reader["ent_code"] != null)
                        {
                            //model.Id = (int)reader["id"]; //id is the column name in the database
                            model.staff_no = (string)reader["staff_no"]; //absecode is the column name in the database
                            model.ent_code = (string)reader["ent_code"]; //absedesc is the column name in the database
                            model.loanid = Convert.ToInt32(reader["loanid"]);
                            //model.date_taken = (DateTime)reader["date_taken"];
                            model.date_taken = DateTime.ParseExact("date_taken", "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
                            model.amt_taken = (decimal)(reader["amt_taken"]);
                          //  model.ded_date = Convert.ToDateTime(reader["ded_date"]);
                            model.date_taken = DateTime.ParseExact("ded_date", "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
                            model.amt_left = (decimal)reader["amt_left"];
                            model.amt_paid = (decimal)reader["amt_paid"];
                            model.amtpay_todate = …
0
Should you be charging more for IT Services?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi experts,

I need SQL syntax help please.

Here is my business scenario; I need to get a count by distinct employee type of 4 different measures. If the count of employees by each measure does not equal zero/null then show the total count. If the count of employees by each measure equals zero or null, show zero.

I'd like to use PHP to echo the results in a table that would take the following format:

distinct_type | MeasureA | MeasureB | MeasureC | MeasureD
Emp Type X | 1 | 0 | 1 | 1
Emp Type Y | 0 | 1 | 0 | 1
Emp Type Z | 1 | 1 | 0 | 0

Something like...I know I am way off...

(SELECT DISTINCT employee.type AS  'distinct_type' FROM mydatabase ORDER BY employee.type)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureA' FROM my database WHERE employee.type=distinct_type AND measures.a=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureB' FROM my database WHERE employee.type=distinct_type AND measures.b=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureC' FROM my database WHERE employee.type=distinct_type AND measures.c=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureD' FROM my database WHERE employee.type=distinct_type AND measures.d=1 GROUP BY employee.type ORDER BY employee.type ASC)
0
hi Experts,

We have a column DOB that was mistake defined as text and we are planning to convert to Date column.
However there are many records with invalid dates in it.
Wondering if there is a function that will correct this when something is obvious, like only missing slashes or removing spaces etc...
See attached for sample data.

Thanks
Untitled.png
0
I enter the date value in this format.
oracle gives me
 ORA-01843: not a valid month error.
NOTE:(you can better understand the question in the pictures.
Please see the pictures.hatali.png
but
I enter the date value in this format.
Oracle is running.
ikinci_resim.png)
I want to change the date format.
I don't want to use this date (13.jun.2014),
 I want to use this date = (13.06.2014),
how can I do this?

Result of query 'select sysdate from dual'
17/09/2019 17:46:00

Open in new window

0
Problem with my query, when adding a value that has a single quote to a database field.

eg. //-- values
        $value1 = "Peter";
        $value2 = "O'Leary";
        $value3 = "Contact Person";
     //-- query
        $stmt = $mysqli->prepare("INSERT INTO table1 (field1, field2, field3) VALUES (?,?,?)");
        $stmt->bind_param('sss',  $value1, $value2, $value3);
        $stmt->execute();
        $newId = $mysqli->insert_id;
        $stmt->close();

This does not insert anything, and $newId has no value.

So it seems I have a really silly question... I'm sure it's something really obvious, but I'm dead tired and can't figure it out.

I've tried...
$value2 = htmlspecialchars("O'Leary", ENT_QUOTES, 'UTF-8');
$value2 = mysqli_real_escape_string($mysqli, "O'Leary");
0
hi expert;
How do I put a breakpoint in plsql code?
I would like to read the plsql code line by line.
I don't want to use the 'dbms_output.putline' command.
Is there such a method?
how can i solve it?
thank you.
declare
sayi number; 
begin
    sayi:=0;
    for x in 1..5 loop
     sayi:=sayi+1;
    end loop;
end;

Open in new window

0
I am trying to filter a result set based on a DATETIME column
select * FROM [ReportServer].[dbo].[ExecutionLogStorage]  where timestart>=2018-12-31 

Open in new window


here is the what is displayed typically for a TimeStart value
TimeStart
2018-08-03 09:08:24.937

Open in new window


however the filter is not correct, all rows in the table are currently returned.
Any guidance appreciated, thanks
0
are ReportServer & ReportServerTempDB MSSQL default databases, and does their presence in an instance of MSSQL indicate any specific feature has been installed? What data do they hold/and could the databases hold personal or sensitive information, or is it more generic configuration type info?
0
I have a python script on python 3.7.2 that I have pieced together. The script will first accept user input for a string and store into a variable and then iterate through each function call.

Depicted here:

while True:
    uin = input("Please enter a uin or 'enter' to quit: ")
    if not uin:
        break
    startTime = datetime.now()
    selectCustomer(uin)
    selectVehicle(uin)
    selectHRO(uin)
    selectHLABOR(uin)
    selectHPARTS(uin)
    selectINV(uin)
    selectSUPPLIER(uin)
    selectLABOR_OP(uin)
    selectSOURCE(uin)
    selectRO(uin)
    selectLABOR(uin)
    selectPARTS(uin)
    selectEHRO(uin)
    selectEHLABOR(uin)
    selectEHPARTS(uin)
    endTime = datetime.now() - startTime
    print('It took', endTime, ' seconds to run...')

Open in new window


Each function (for the most part is exactly identical and is composed of:

  • opening up a SQL connection
  • running a query
  • storing the output in an ordered dictionary
  • creating a JSON file and naming it with the user input (in our case UIN)
  • closing connection and file

Depicted here:

def selectEHPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, 

Open in new window

0
I'm trying to run a subquery that will only be valid if only 1 row exists in my subquery.

For example:
select o.*
from orders o
where o.a = 'a'
and o.c = 1
and exists (select a.a
from address a
where o.a = a.a
and only 1 row exists)

If I'm supposed to use a "having (a) = 1" I really don't know the syntax for it in a subquery.

Also, I know I could use a proc to easily solve this but unfortunately I don't have access to implement that so I need to do it in a SQL statement. If it's even possible, that is.

Would a case statement work for this?

Please note that I'm working on Oracle 12c if that helps any.

Thanks,
Larry
0
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

I am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.
SELECT DISTINCT
  FormResults.FormResultKey AS [FormResults FormResultKey]
  ,FormResults.FormID
  ,FormResults.FormReference
  ,UserForename + ' ' + UserSurname as [Full Name]
  ,FormResults.FormResultDate
 
 

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised


FROM
  FormResultAnswers
  LEFT OUTER JOIN Documents
    ON …
0
I think this is going to be a very difficult one for me to explain, but I'm going to try.

The server has to be updated to php 7, so this has been done but not visible yet. I changed all mysql calls to mysqli. I "think" the problem is in the below code - it's maybe not in the correct format for mysqli calls? I did a test separate of everything with just basic code and I do get a positive that it's correctly connecting to the database. But I can't even figure out how to get it to respond to something in the below code to tell me anything. It's not giving any errors to me on screen.

The server tech only showed this in logs:
> [Thu Sep 12 10:41:31.223508 2019] [php7:warn] [pid 6093]
> > > > > [client 73.15.109.72:45414] PHP Warning:  mysqli_error()
> > > > > expects exactly
> > > > > 1 parameter, 0 given in
> > > > > /home/atra/public_html/couplings/connection.class.php on line
> > > > > 36,

Which is what is leading me to think it's there in the connection call in the below code.

I'd appreciate any help/insight. I know it's difficult since I can't show a link or attach all the files. I can definitely say all include files are being included and the config file with database details is correct.
<?php

class connection
{
	var $db;
	var $tables;
	var $domain;
	var $user;
	var $password;
	var $connection;
	
	function connection()
	{
		$tables = array();
	}
	
	function makeConnection($d_base, $ip, $user, $password)
	{
		$ip = 'localhost';
		

Open in new window

0
Any one know a good example how save outlook msg into a mysql database?

im using VB.net with mysql and I would like to save the file msg as binary un mysql
0
I am having a strange issue trying to an import a *.csv file into a Microsoft access database table for analysis. The data is actually a csv representation of a complete windows 7 security.evtx which from within windows 7 event viewer application I have selected to 'save all events as', and then selected evtx. When I open the csv file in Microsoft excel, it loads up fine, where one row of data represents one event from the event log. However when I then try and import that data into Microsoft access it goes horrendously wrong, and it seems to split the data into approximately 22 rows for a single event. In the 'import text wizard' in access (2010), when I select the csv file, it initially defaults to 'fixed width', and the preview already shows the mess of an import that is due to follow. If I manually select delimited it does not amend itself back to one row of data per event. So my query is what can I do to make the import more efficient, and how come excel can handle the data in a 1 row per event format but access cannot?

To add to my misery after the import goes horrendously wrong, I did also spot the dreaded importerrors table, of which there are 94813 Field Truncation errors on a field called keywords. No idea why though, as keywords is a fairly simple field which will have basic entries such as 'Audit Success'. It actually appears to be the 6th field in the data which may be the problem, that has lots of text, e.g. a sample value I found had over 1000 characters …
0
I have an issue with a SQL database. We have an application that uses SQL and was on a Windows 2008 server running SQL 2014 Standard. I had some issues with backups in the beginning and for some reason could not get my backup solution to do diff/full backups. So I set it to do a full backup every night.
Move forward a couple of years and we needed to migrate the application and SQL database to a new Windows 2016 server running SQL 2017 Standard.
When I backed up the database using SQL management studio the database size was 8gb..... when I went to restore the database on the new 2017 SQL Server, it almost filled up the hard drive. I did some checking and realized that the LDF (transaction log files) file was a whopping 120gb.
I am trying to figure out the following:
1. How do I reduce the size of the transaction log file or at least keep it from growing any larger. I have read that I should NOT try to shrink the database or accompanying log files as this can cause performance issues.
2. Do I use a simple recovery mode or Full recovery mode (on SQL 2014 it was set to full recovery mode)
3. Do I need to setup a maintenance job to backup the transaction log files sepearately and will that shrink or reduce the LDF file size?
0
I save a class with various properties into a the current user session like this :
    Public ReadOnly Property UserSession As cUserSession
        Get
            If HttpContext.Current.Session("UserSession") Is Nothing Then
                HttpContext.Current.Session.Add("UserSession", New cUserSession)
            End If
            Return DirectCast(HttpContext.Current.Session("UserSession"), cUserSession)
        End Get
    End Property

Open in new window

I would like to save details in the class to a database before the session is lost.  If I rely upon Session_End in global.aspx, the object is already lost when I go to save it.  I tried to implement idisposable in the cUserSession but this didn't fire.  Whilst I can see how I can manually save the contents of the session by user action or property change, I would like to only do that when the session is about to be lost.

Is this achievable?

Thank you,
0
I have an older Access database (probably created in 2003) that I am opening in newer versions (tried 2010 and 2016). It is a rather complex database. Everything works except one button that calls a vbscript to update data using delete, update, and insert into statements. I cannot say for certain that it worked perfectly in previous versions as I inherited this from a former employee.

The vbscript concatenates data based on information it receives from the queries. The problem I am encountering is that after I click the button, the query inserts special characters instead of the text that it is supposed to grab from one of the other tables.

The fields that are showing correctly in the source table are Long Text data types with Plain Text text format.

I attached a screenshot of a brief sample of the problem I am experiencing.

Special Characters
0
When I encounter a duplicate PN in this case example 001030-503, I want to choose the one with MFG_CODE = 'SG361' and MFG_NAME = 'AEROLITE MAX BUCHER AG', not MFG_CODE '99999' and MFG_NAME = 'DEFAULT'. I need the select statement to do this for all duplicate PN's that contain a MFG_CODE = 99999 with it's corresponding MFG_NAME.

Would coalesce be a good option? If yes, what is best practice?
data_example.csv
Capture.PNG
0
Dear All,

I am new to SQL Server and would like your assistance to a query that has been raised to me recently. A client has approached me to build SQL Reports for them and is willing to provide me with the database backup in order to achieve this. First and foremost, how can I go about building SQL reports from the database, and also how can I achieve this as quickly as possible?

I would appreciate any links that I can use as a starting reference point.

Your help will be much appreciated.
0
Rowby Goren Makes an Impact on Screen and Online
LVL 13
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

In a software function, I receive a connection to a database server.  How can I find out using the connection whether I'm connected to Sql Server, Oracle, MySql etc.?
0
I have found that people use terms schema and database interchangeably.
when we say schema in the context of a database , what exactly one would describe?
1
I'm trying to retrieve data from my remote MySQL database from C++ under Ubuntu.

#include <mysql++.h>
#include <stdlib.h>
 
using namespace std;
using namespace mysqlpp;
 
int main() {
    
    cout << "Trying to connect..\n";

    try {
        Connection conn(false);
         conn.connect("Sql737674_5", "62.149.xxx.xxx", "Sql737674", "xxxxxxxxx");
        Query query = conn.query();
        
        if (conn.connected()){
        cout << "OK\n";
        } 
        else {
        cout << "NOT CONNECTED\n";
        }

        cout << "Eseguo Select\n";
        /* Now SELECT */
        query << "SELECT * FROM Utenti LIMIT 10";
        StoreQueryResult ares = query.store();
	cout << "Ciclo FOR\n";
       // printf("numero: %d", ares.num_rows());
        for (size_t i = 0; i < ares.num_rows(); i++){
	cout << "Name: " << ares[i]["Nome"] << " - Cognome: " << ares[i]["Cognome"] << endl;
         }
        
    } catch (BadQuery er) { // handle any connection or
        // query errors that may come up
        cerr << "Error: " << er.what() << endl;
        return -1;
    } catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    } catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;

Open in new window

0
I need to create a user form for a SQL Server database.  I have experience using MS-Access as a front end, but the environment that I am currently working with does not have Access.  I am looking for a tool that will let me create a couple data entry forms, without a steep learning curve.
0
what kind of formulas determine how long you should retain your SQL Server database backups for? Our admins seem to be using a variety of different retention schedules for backups of different critical databases, some databases there are 1 weeks worth of SQL backups, others there are 6 weeks worth. I am trying to determine how exactly you would determine how long to keep database backups for, and for example have you ever restored data from say a 6 week old backup, which would surely incur huge data loss? Or under what circumstances would you need to keep backups for longer than say 7 days? I guess the same principle applies regardless of database platform hence adding it to other DB topic areas.
0
Hi Experts,

How can I create a formula field as follows.

CASE 
WHEN [@Field:Status] <> 'Draft' Then (select NotificationEmail from Caregivers where TimeAttendancePIN = [authfield_Nurse_UserName] and [No_Auto_Emails]= 0) 
Else  ' ' 
End

Open in new window


Getting error attached.

Purpose, I need to send an auto email for users filling a form with Status field = "Completed". (Caspio forms)

Thanks
0

Databases

57K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.