Latest Databases Posts

Hi Simonloa,
I have years and years of sql server experience. I would like to help you but it is hard to follow what your tables are and what the goal is. Are you running these queries from an application? If so, what language?

In general UNION is a very inefficient query operator, but is required in some cases. I suspect in this case the results could be achieved with something like " where (x and y and z) or (x1 and y1 and z) or (x and y2 and z)" etc.

Perhaps you could post sample table data for each table(4 or 5 rows in each and ALL columns) and the result set you would like to see.

Sean

You're also missing the word "where" before your case. Lastly, comma joins are pretty old and likely will be deprecated at some point...

As I recall from my old SQL days, the syntax is not

WHEN fld='value'
THEN var='other value'

it's

SELECT fld1, fld2,
    CASE
        WHEN fld2='val1' THEN 'new val1'
        WHEN fld2='val2' THEN 'new val2'

This maps database values for fld2 onto user specified values. Sorry I can't actually try this out. I haven't had SQL installed for some years.

rproffitt commented: And as the examples I read, FROM is done at the end. +0

Your where clause always have to result in a boolean to check equivalency. For example "where 1 = 1". From reading this, it does not look like the case has a match, and therefore it is in error.

Ex: 1 = case u.foo when t.foo then 1 else 0 end and ....

I'd consider rewriting this. Use the code format you see at https://www.w3schools.com/sql/func_mysql_case.asp

Hello i am getting an error on a CASE WHEN THEN syntax on my mySql

         SELECT  DISTINCT u.uid,c.c_id,u.name,u.profile_pic,u.username,u.email,c.time
                FROM conversation c, users u, conversation_reply r
                CASE
                WHEN c.user_one = '99'
                THEN c.user_two = u.uid
                WHEN c.user_two = '99'
                THEN c.user_one= u.uid
                END
                AND (
                c.user_one ='99'
                OR c.user_two ='99'
                ) AND u.status='1' AND c.c_id=r.c_id_fk AND u.uid<>'1'ORDER BY c.time DESC LIMIT 15

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE
            WHEN c.user_one = '99'
            THEN c.user_two = u.uid
            WHEN c.user_t' at line 3

something like this is the solution.

            (SELECT DISTINCT M.msg_id as msg_id, U.username,S.uid_fk, S.ouid_fk as ouid_fk , M.group_id_fk,M.message, S.created, '0' as type, '' as group_name
FROM
messages M, users U,message_share S
WHERE
U.status='1' AND
M.uid_fk = S.ouid_fk AND
S.msg_id_fk = M.msg_id AND S.uid_fk<>'99' GROUP BY M.msg_id)

UNION
(SELECT
DISTINCT '0' as msg_id,U.username, X.uid_fk as uid_fk, '0' as ouid_fk , X.group_id_fk as group_id_fk, '0' as message, X.created, '4' as type, G.group_name as group_name
FROM users U, groups G, group_users X
WHERE G.uid_fk=U.uid AND G.group_id=X.group_id_fk AND U.uid='99' AND X.uid_fk<>'99' AND X.status='1' AND U.status='1')

UNION
   (SELECT
DISTINCT M.msg_id as msg_id, U.username,M.uid_fk as uid_fk, '0' as ouid_fk , M.group_id_fk as group_id_fk, M.message as message, M.created, '5' as type, '' as group_name
FROM users U, messages M,group_users G
WHERE G.uid_fk=U.uid AND G.group_id_fk=M.group_id_fk AND U.uid='99' AND M.uid_fk<>G.uid_fk AND G.status='1' AND U.status='1')

UNION
(SELECT DISTINCT M.msg_id  as msg_id, U.username,S.uid_fk, S.ouid_fk as ouid_fk, M.group_id_fk,M.message, S.created, '1' as type, '' as group_name
FROM
messages M, users U,message_points S
WHERE
U.status='1' AND
M.uid_fk = S.ouid_fk  AND
S.msg_id_fk = M.msg_id AND S.uid_fk<>S.ouid_fk AND S.uid_fk<>'99' GROUP BY M.msg_id)

UNION
(SELECT DISTINCT M.msg_id as msg_id,U.username, S.uid_fk, M.uid_fk as ouid_fk, M.group_id_fk,M.message, S.created, '2' as type, '' as group_name
FROM
messages M, users U,comments S
WHERE
U.status='1' AND
M.uid_fk ='99' AND
S.msg_id_fk = M.msg_id  AND S.uid_fk<>'99'  GROUP BY M.msg_id)
ORDER BY created DESC

Hello i have a database which users make groups or send messages to other registered, in our project, users. So you have messages as M,users as U, groups as G. So you want to show all updates that happen in these tables WHERE (uid - thats the user id column on U table.). All tables have CREATED as datetime column. Users yable is uid on messages is uid_fk and to groups uid_fk. Can i do that with a single statement or do i need to do anything else?

' Check if any rows exist
            If rd.Read() Then
                If rd.GetString(3) = "small" Then
                    MessageBox.Show("small")
                ElseIf rd.GetString(3) = "Big" Then
                    MessageBox.Show("big")
                ElseIf rd.GetString(3) = "Midium" Then
                    MessageBox.Show("Mid")
                End If
            End If

This is not the proper way to check any record if it exists.
datareader.Read() always produce a single record of a data table. To read multiple record you must do a loop by using datareader.Reaad().

To check a conditional record exists in a table or a datareader you create by using a SQL query statement, you must first do the checking if the datareader holds any record or not by using datareader.HasRows() function, which supplies a boolean value if the datareader holds any record or not. without checking it datareader.Read() can give you an exception/error if it doesn't hold any record.

' Check if any rows exist
    if rd.HasRows() then
        rd.Read()
        If rd.GetString(3) = "small" Then
           MessageBox.Show("small")
        ElseIf rd.GetString(3) = "Big" Then
           MessageBox.Show("big")
        ElseIf rd.GetString(3) = "Midium" Then
           MessageBox.Show("Mid")
        End If
    End If

Hope it can help you to solv your problem

pty 867

They are synonymous, if you check the official docs it says as much. The reason they're both there is for familiarity, SQL Server and DB2 use lcase, Oracle and PostgreSQL use lower.

Dani 1,700

What's the difference between LCASE() and LOWER() in MySQL?

I found places on the web that said they're similar but not a synonym for each other, so I'm not sure what the differences are?

Small world. An ancient app that I wrote in VB6 with a SQL connection is dead on 64 bit Windows.

Because the company wants to move to newer Windows after 15 years they contacted me and we are working on a new version.
I can't tell what your app is or does but sometimes you have to get back to the developer to fix or upgrade.

Previously used winXP operating system running normally (very good) when switching to win7 operating system, this error, read errors from the SQL Server data, what is the error ? http://www.mediafire.com/view/4q5ux6e66ds4avb/ErrorOpenSQL.jpg/file

Use "Update" statement to update database feilds value without "Select" statement with "Where" clause.
Secondly, use parametarised query statement to prevet SQL injections in lieu of direct using of objects.

Hi guys,

This is my first post and so glad to start sharing the knowledge.

I am using Mysql database with VB.NET and I have this code :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim str As String = "Server=localhost;Port=3306;Database=testdb;Uid=root;Pwd=password"

        Using con As New MySqlConnection(str)

            Dim query As String = "select * from testdata where rfid_tag='" & TextBox3.Text & "' and Date_serve<= '" & Date.Now.ToString("yyyy-MM-dd ") & "' and Start_Time<= '" & Date.Now.ToString("HH:mm:ss ") & "' and End_Time>= '" & Date.Now.ToString("HH:mm:ss ") & "' or amount_serve='' "
            Dim cm As New MySqlCommand(query, con)

            con.Open()

            Dim rd As MySqlDataReader = cm.ExecuteReader()

            ' Check if any rows exist
            If rd.Read() Then
                If rd.GetString(3) = "small" Then

                    MessageBox.Show("small")

                ElseIf rd.GetString(3) = "Big" Then

                    MessageBox.Show("big")

                ElseIf rd.GetString(3) = "Midium" Then

                    MessageBox.Show("Mid")

                End If

            End If
        End Using

    End Sub

what I want after take action after any of If function like

If rd.GetString(3) = "small" Then

                        MessageBox.Show("small")

need to update End_Time to be 00:00:00 in the element which chose in select with rfid_tag number in the table.

this my database table: for example if in "select" command choose RFID_TAG for John then I need to update the End_Time in table to be 00:00:00 with "update" command!

How can I achieve this?

  ID   First_Name      Date_Serve     Start_Time      End_Time             RFID_Tag           Amount_Serve
---------------------------------------------------------------------------------------------------------------------------------
    1     John           17-11-2018       10:00:00      10:15:00          hdgdYun8JH      Small
    2     George         18-11-2018      11:00:00       11:15:00           kdjfHluhHB        Big

Thanks for all

Dani 1,700

Also, it looks like READ COMMITTED likes to lock things. I need to do as little locking as possible, otherwise the server will crash pretty quickly.

Dani 1,700

Please don't be disappointed in me.

What I was doing, out of simplicity's sake, was starting a transaction in the constructor of the database class, and committing the transaction in the destructor of the database class, for all POST requests. (POST requests are less than 1% of all web requests.) Could that have been the problem??

pty 867

Did lowering the transaction isolation level to READ COMMITTED make any difference to the timing?

pty 867

All RDBMs have more-or-less the same Transaction Isolation Levels but sometimes they go by slightly different names and MySQL has a few extra oddities. Before going further understanding what a dirty read is and how to avoid it is important.

READ WRITE and READ ONLY set the level of access that transaction has. If you use READ WRITE you can both read from and write to tables inside your transaction. With READ ONLY, you can't modify data. That bit's easy.

WITH CONSISTENT SNAPSHOT is a tiny bit trickier, but essentially when you open the transaction all data used inside the query will be as if it was read when the snapshot was created (either at the start of the transaction or at the point it's first read), rather than how it currently is. From the docs,

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

So, it comes back to transaction isolation levels. READ COMMITTED just means that any data read has beencommitted to the database. If you select a value from a table at the beginning of your transaction, then update it (and commit) in concurrently in another transaction, then finally select it at the end of your first transaction, the value will be the new value.

REPEATABLE READ means that once you've selected a value inside a transaction, it will ...

Dani 1,700

Not sure if it matters, but I was using CodeIgniter's trans_start() and trans_complete() functions instead of implementing transactions natively with MySQLi.

Also, I'd like to point out that I don't want to block any reads, and ideally I wouldn't want to block multiple inserts to the same table at the same time across different transactions. All I'm trying to preserve is that if a single transaction is supposed to make two inserts, but the script dies before the second one completes, that the first one gets rolled back.

Dani 1,700

So the behavior I'm experiencing, and I'm sure those of you who have been active on DaniWeb the past few days have been experiencing, is INSERT queries are taking a realllllly long time (often 15+ seconds). Starting new threads, replying to posts, ... it's all taking a really long time.

I went ahead and disabled transactions (I was just using START TRANSACTION with no flags), and the problem immediately went away. What flag do I need to use to make it non-blocking?

Dani 1,700

I need help with implementing MySQL transactions with PHP. Specifically, I'm confused between the flags WITH CONSISTENT SNAPSHOT, READ WRITE, and READ ONLY, what their differences are, and how they relate to table locking. I already read the MySQL reference manual but I'm still confused.

Follow commented: I am faces same problem and highly interested for solution. https://smartelectricscooters.com/charge-your-electric-scooter/ +0
pty 867

MySQL binds current_timestamp()/now() at the beginning of the statement so I believe all of your records would have the same created_at value.

If you wanted them to differ I think you'd need to use a cursor and separate insert statements.

Dani 1,700

For my purposes, I actually need the timestamps for each row to reflect the time that specific row was inserted, spanning over the five minutes it would take to do the bulk insert. It’s actually an INSERT ... SELECT of about 200,000 rows. Obviously doing it as 200,000 individual queries is not efficient.

I found it was safe to not rely on default behaviour. Pre-2008 (before retirement) the scripts I wrote/maintained had to bulk insert 8000+ records at a time. In order to maintain consistency I would massage the data and add a date/time field to the records. That way I could guarantee the accuracy of the date/time without having to rely on SQL. Also, any delay in the process would not affect the timestamps.

Dani 1,700

If a bulk MySQL insert takes 5 minutes to complete, and one of the columns in the table is a TIMESTAMP column with a default value of CURRENT TIMESTAMP, will the timestamps of the rows that were inserted be reflective of the entire 5 minutes, or will they all default to the same timestamp from beginning or the end of the query? The bulk insert exists within a transaction.

try Username: hr , Password: (the password that you set when you unlock hr during installation in most of the cases the password is 'hr')

But string variable value should be in quotes

$q = mysql_query("SELECT `id` FROM `users` WHERE `name`='".$name."'"); 

this also is not good solution - use prepared statement to prevent from SQL injection

$stmt = mysqli_prepare($link, "SELECT `id` FROM `users` WHERE `name`=?");

and then bind variables, execute statement and fetch result

mysqli_stmt_bind_param($stmt, "s", $name);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

Table name and column name in MySQL query string should be without quotes or you can use backticks.

I want to create a php function that i can use to get a particular user details and display the info gotten in my ProfileActivity when a user profile is been clicked in my app. so i wrote this php code to get user details from my database but when i run the code nothing is returned in my browser. please what could be causing this?

<?php
require("config.php");

function getdetails($id)
{
    $array = array();
    $q = mysql_query("SELECT * FROM 'users' WHERE 'id'=".$id);
    while($r = mysql_fetch_assoc($q))
    {
         $array['id'] =  $r['id'];
         $array['name'] =  $r['name'];
    }
    return $array;
}

function getId($name)
{
    $q = mysql_query("SELECT 'id' FROM 'users' WHERE 'name'=".$name);
    while($r = mysql_fetch_assoc($q))
    {
        return $r['id'];
    }

}   

?>