Date: 07/02/07 (MySQL Communtiy)    Keywords: mysql, sql

I'm beyond rusty on my MySQL, and I don't have my book handy (I'm at work, it's not). I'm plugging along working on this little program and I run into this little quandry. I'm positive it's an exceptionally easy question, but sometimes (especially with me) the easiest questions are the ones that I just have no flipping clue on.

So...would this be a legal SELECT statement in MySQL?

SELECT this_value FROM table_a AND this_other_value FROM table_b WHERE `this` = `that`;

I'd upload it to the server and test it out, but I haven't bothered to put any data up yet. I'm just trying to get this down before I forget.

Source: http://community.livejournal.com/mysql/116029.html

Date: 07/03/07 (Code WTF)    Keywords: sql

int AddFLReportRec(long lIDRecord, long bank, short oper_i, long isSlType, long isTerr, short idPdi,
                    char * fio, char * series, char * number, char * inn, long id_client, long id_contact,
                    short kop, short mop, short oper, long lNopod, long lType, short sSign)
{
l
R_SL_PL e_pl;
    ...
    e_pl.A_SYSID = lIDRecord;
    e_pl.A_DATE_OP = date;
    e_pl.A_TIME_OP = time;
    e_pl.A_BANK  = bank;
    e_pl.A_OPER_I  = oper_i;
    e_pl.A_SLTYPE = isSlType;
    e_pl.A_ISTER = isTerr;
    e_pl.A_SYSID_PDI = idPdi;
    e_pl.A_ID_CLIENT = id_client;
    e_pl.A_SYSID_CONTACTER = id_contact;
    e_pl.A_KOP = kop;
    e_pl.A_MOP = mop;
    e_pl.A_OPER = oper;
    e_pl.A_NOPOD = lNopod;
    e_pl.A_TYPE  = lType;
    e_pl.A_SIGN  = sSign;
    strncpy(e_pl.A_FIO,fio,sizeof(e_pl.A_FIO)-1);
    strncpy(e_pl.A_SERIES,series,sizeof(e_pl.A_SERIES)-1);
    strncpy(e_pl.A_NUMBER,number,sizeof(e_pl.A_NUMBER)-1);
    strncpy(e_pl.A_INN,inn,sizeof(e_pl.A_INN)-1);
  
    EXEC SQL INSERT INTO R_SL_PL VALUES(:e_pl);
    return 1;
sql_error: sql_error_func("AddFLReportRec");
}

int AddFLReportRec(R_SL_PL * e_pl)
{
    int result=0;
    if(!e_pl) return 0;
    result = AddFLReportRec(e_pl->A_SYSID, e_pl->A_BANK, e_pl->A_OPER_I, e_pl->A_SLTYPE, e_pl->A_ISTER,
                   e_pl->A_SYSID_PDI, e_pl->A_FIO, e_pl->A_SERIES, e_pl->A_NUMBER, e_pl->A_INN,
                   e_pl->A_ID_CLIENT, e_pl->A_SYSID_CONTACTER, e_pl->A_KOP,    e_pl->A_MOP,
                   e_pl->A_OPER, e_pl->A_NOPOD, e_pl->A_TYPE, e_pl->A_SIGN);
    return result;
sql_error: sql_error_func("AddFLReportRec");
}

Source: http://community.livejournal.com/code_wtf/92713.html

Date: 07/08/07 (MySQL Communtiy)    Keywords: programming, mysql, sql

Short info:
Is there a way to use a * operator in a query like so:

`SELECT newSrc.*, oldSrc.* FROM current_raw_table newSrc LEFT JOIN yesterday_raw_table oldSrc ON(newSrc.id = oldSrc.id) WHERE newSrc.* != oldSrc.*);`

to use mysql to compare two tables of data created by a `load data infile` where one table is today's and the other is yesterday. My tests with using *'s failed so plan B is to use my l33t programming skillz to generate a bunch of
`if(newSrc.{fieldName} != oldSrc.{fieldName},"fieldName") as "{fieldName}",` then wrap the whole mess with concat_ws(' ',...);

but before I use said l33t skelz, I figured it might be smart to ask before I reinvent something else.

Source: http://community.livejournal.com/mysql/116735.html

Date: 07/08/07 (MySQL Communtiy)    Keywords: mysql, database, sql

Quick question to see if anyone has a better way.

I have 3 development environments: Workstation at work, my laptop, and the workstation at home... my code is moved between all these machines with svn and it works perfectly, so it makes me wish I could do the same with development schema's or whole Databases.

Is there anything like svn for Databases? I know about and use replication, but that's a tad too much just to allow me to work from a coffee shop or catch up on some work on the weekends. Right now I have a fetchSchema.bat file for my windows machines that grabs the current schema/data from the dev server using mysqldump.

Which reminds me of another question for another post.

Source: http://community.livejournal.com/mysql/116466.html

Date: 07/08/07 (PHP Community)    Keywords: php, mysql, html, database, sql

I'm sorry to disturb, I'm new in php and I have a trouble with this code:


    mysql_connect($host,$utente,$passwd) or die("Connessione non riuscita: " . mysql_error());
    mysql_select_db("news") or die("Selezione del database non riuscita");
    $np = "SELECT * FROM persona";
    if($riga = mysql_fetch_array($np,MYSQL_ASSOC)) {                        
                                echo "" . $riga["Nome"] . " - " . $riga["Cognome"] . " - " . $riga["Organizzazione"];
                                selected ($riga["id_persona"]);
                                    echo "";
                                  }
    while($riga = mysql_fetch_array($np,MYSQL_ASSOC)) {                        
                                echo "" . $riga["Nome"] . " - " . $riga["Cognome"] . " - " . $riga["Organizzazione"];
                                    echo "";
                                  }
?>
 

It has to extract a row from a mysql database and make a html select of the value id_persona displaying also the value of the other fields (Nome, Cognome, Organizzazione) but it don't works properly. Choices on select are displayed like in the attached photo instead with the correct data .

Source: http://community.livejournal.com/php/575883.html

Date: 07/09/07 (SQL Server)    Keywords: browser, sql

Okay, this should be simple...

I'm trying to find the cube browser in SQL Server 2005. I cannot for the life of me do so. Can anyone point me to it? Thanks.

Source: http://community.livejournal.com/sqlserver/60894.html

Date: 07/10/07 (Web Development)    Keywords: php, mysql, browser, html, database, sql

Ok, so I have a fully UTF-8 MySQL database with a fully UTF-8 table. I have a php page which has "" at the top of the html section and "mysql_set_charset("utf8");" after my connection (it's php 2.2.3, so that's more or less the same as "mysql_query("SET NAMES 'utf8'");).  I have all my php.ini mbstring stuff configured for utf-8:

mbstring.language = Neutral
mbstring.internal_encoding = UTF-8
mbstring.http_input = auto
mbstring.encoding_translation = On
mbstring.detect_order = auto

and for what it's worth, echo mb_internal_encoding(); reports "UTF-8".

when I run a query that pulls out (in this case, Japanese, but say any) UTF8 data, it displays properly in MySQL Query Browser.  With php in the above context however, it does not.  Instead, it prints a ? corresponding to each character.  When I use mb_detect_encoding(); on each such string, it tells me they are encoded as ASCII.  The nearest I can tell is that somewhere between using mysql_query() and mysql_fetch_row(), things got messed up.  Is this a bug?  Is there something I'm missing?  Any clues? Help!

Source: http://community.livejournal.com/webdev/420578.html

Date: 07/13/07 (Java Web)    Keywords: php, mysql, sql, web

This is a very interesting presentation (see below) on the technologies used to make Digg, the challenges faced along the way and how they overcame it. I would recommend it to any Web 2.0 startup architects. In short Digg uses multiple MySQL slaves with a single master, multiple load balanced PHP servers which connects to random [...]

Source: http://blog.taragana.com/index.php/archive/digg-technologies-used-challenges-faced/

Date: 07/15/07 (Javascript Community)    Keywords: php, html, sql, java, web

I'm working on a new website, and for many of the functions, I really want to replace all the damned annoying popups with much more friendly code. Why even have popups? Well, for instance, in the message forum, you often get a popup when you get a new IM. In the Gallery, if you click on a single image, it is annoying to do a new pageload for that, and then go back and reload the old pack just to select another image.

For many functions, Lightbox is a great solution, but it isn't everything I'm looking for. There are plenty of Lightbox variants. My favorite two are the following:

http://www.justinbarkhuff.com/lab/lightbox_slideshow/ - This is based of Lightbox 2, but adds native slideshow support, more keyboard controls, etc.

http://www.ryanjlowe.com/?p=9# - In many ways, head and shoulders above all Lightbox clones. The window can be dragged around, can include html, Flash, forms, whatever. You can have an event to pass information back and forth between the popup and original window, etc.

I love the transition effect of Lightbox 2, and the opacity of the windows in Litbox. I love the prev/next/slideshow features of Lightbox 2, and the fact that you can drag the Litbox windows around. I like the keyboard controls of Lightbox 2, and the fact that you can include all kinds of content in the Litbox window.

I know enough PHP and SQL to get myself in trouble, and enable myself to break/build a site to my liking. However, my javascript knowledge consists of copying and pasting. I have no clue what I'm doing. Is there some soul who like me, sees the benefit of a powerful Lightbox clone that combines many of the features from these two?

I'd be extremely grateful if someone can help me out.

Source: http://community.livejournal.com/javascript/136245.html

Date: 07/17/07 (PHP Community)    Keywords: php, mysql, sql, web

hey everyone,
i have a website: http://thebigone.homeip.net with a form for users to put either 'chat' or news for the site.
my question is: when someone tries posting in either of the text boxes and hits enter to create a new line the php doesn't pick up on this when it posts to the mysql backend. how do i keep the formatting of the textbox for the actual site?

Source: http://community.livejournal.com/php/576562.html

Date: 07/18/07 (Java Web)    Keywords: database, sql, java

HSQLDB is an unique high performing, high quality Java based relational database which can be very easily extended with simple Java static functions. Here we will see how you can easily extend the database capability with a real-life example. I wanted to do a case-insensitive LIKE comparison, specifically get rows where the column data contains the [...]

Source: http://blog.taragana.com/index.php/archive/tip-how-to-extend-hsqldb-rdbms-with-java/

Date: 07/18/07 (PHP Community)    Keywords: php, mysql, database, sql

I have a chronic problem with a fanfiction archive script I made. Since I've put it online, I hit max_user_connections in MySql very often, and my host is losing patience. The script opens a mySql connection (originally a normal one, now a permanent one by my host's suggestion) on loading and should use it for all the mysql calls forward. The archive is famous in the fandom and gets a lot of hits, but this shouldn't happen. I've tried to reduce the mysql calls at minimum, but I only get a few days reprieve. This is the code I use:

In db_layer.php:

Source: http://community.livejournal.com/php/576875.html

Date: 07/20/07 (Java Web)    Keywords: mysql, sql

What is auto reconnect in MySQL? The MySQL client library can perform an automatic reconnect to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed. In this case, the library tries once to reconnect to the server and send the statement again. [...]

Source: http://blog.taragana.com/index.php/archive/how-to-enable-disable-auto-reconnect-in-mysql/

Date: 07/21/07 (Java Web)    Keywords: database, sql, java

I previously discussed how to extend HSQLDB relational database with simple Java functions. The example there actually implements case insensitive like query functionality for HSQLDB. Read the article to get an understanding of how to write such custom functions and how to use them. Then read below to find a solution for performing space crunched [...]

Source: http://blog.taragana.com/index.php/archive/hsqldb-tip-case-insensitive-like-query-in-hsqldb-rdbms-space-crunched-string-comparison/

Date: 07/23/07 (SQL Server)    Keywords: database, sql

Another dumb question. At least I give you guys the easy ones, right? SQL2K5, latest SP, etc etc.

I'm trying to pull everything to the right of a comma in a cell. To spare me having to upload data, I've recreated it in a quick procedure using a variable to store the location of one of the cells in the table:


DECLARE @Author CHAR(50)
SET @Author = 'Aarseth, Espen '
PRINT LEN(@Author)
PRINT CHARINDEX(',',@Author)+1
PRINT (LEN(@Author)-(CHARINDEX(',',@Author)+1))
PRINT 'right(' + ltrim(rtrim(@Author)) + ', ' + CAST(LEN(@Author)-(CHARINDEX(',',@Author)+1) AS char(2)) + ') = '
PRINT right(@Author, (LEN(@Author)-(CHARINDEX(',',@Author)+1)))


My problem seems to stem from SQL treating the LEN() and RIGHT() commands differently, as far as I can tell-- LEN(@Author) returns 16, which is the actual length of the data in the field, but when I use RIGHT(), the padding is included, and consequently I get a field full of spaces. I've attempted every combination of LTRIM(), RTRIM() and @Author I can think of, and I still end up with a field full of spaces as a result set. When I declare @Author as char(16), I get the proper results, but in the table itself, there's no way to do that (multiple author names are, for obvious reasons, variable in length) short of using a cursor and going row by row, and I'd rather not do that (I'm not fond of cursors in the first place, and this is a relatively big table for a scan-and-modify routine, so I may run into time concerns).

I vaguely recall there being some variation on the SPLIT() command in T-SQL, but the sieve that is my brain has forgotten what it's called, and searching on "split" in books online is coming up with squadoosh. Anyone know what it is I'm forgetting? (And whether it's possible to use the command to update two to four colums in the database at once?)

Thanks.

Source: http://community.livejournal.com/sqlserver/60987.html

Date: 07/24/07 (SQL Server)    Keywords: asp, sql

I'm running classic ASP against SQL Server 8, and trying to execute a stored procedure. It's the 4th in a sequence of them so I know that my code, so far, is accurate. Here's the top part of the SP:

CREATE PROCEDURE AddUser (
  @statusCodeValueId int,
  @userCategoryCodeValueId int,
  @AddressId int,
  @ContactMeansId int,
  @firstName varchar(25),
  @middleInitial char(1),
  @lastName varchar(25),
  @username varchar(25),
  @password varchar(100),
  @orgnaizationUserBelongsTo varchar(100),
  @title varchar(20),
  @Comments varchar(2000),
  @inactivationReason varchar(1000),
  @isActive bit,
  @Userid int
 )

Source: http://community.livejournal.com/sqlserver/61512.html

Date: 07/24/07 (SQL Server)    Keywords: sql, web, microsoft

Two questions in one week. Sheesh. I apologize in advance.

As someone on the MS forums said, assigning values to variables in SSIS is way harder than it needs to be. I've read about fifty webpages on this, and they all boil down to the same two basic solutions, and both give me the same errors. I've tried this two different ways:

Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
	Public Sub Main()
        '
        Dim vars As Variables
        Dts.VariableDispenser.LockOneForWrite("intTYPeriodFrom", vars)
        vars(0).Value = CInt(Dts.Variables("intThisWeekSysno").Value) - 51
        vars.Unlock()
        Dts.TaskResult = Dts.Results.Success
    End Sub

Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
	Public Sub Main()
        WriteVariable("intTYPeriodFrom", CInt(Dts.Variables("intThisWeekSysno").Value) - 51)
        Dts.TaskResult = Dts.Results.Success
    End Sub

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Integer)
        Try
            Dim vars As Variables
            Dts.VariableDispenser.LockForWrite(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Source: http://community.livejournal.com/sqlserver/61240.html

Date: 07/25/07 (Java Web)    Keywords: php, mysql, software, sql

A much dreaded MySQL error message during queries is “MySQL server has gone away”. An alternative message is “Lost connection to server during query”. This is a strange problem which afflicts a wide variety of PHP software including but not limited to WordPress. There are several causes for it. Let’s look at the common and [...]

Source: http://blog.taragana.com/index.php/archive/mysql-tip-mysql-server-has-gone-away-or-lost-connection-to-server-during-query-fix/

Date: 07/26/07 (Open Source)    Keywords: mysql, sql, postgresql

The PostgreSQL take on this is people date mySQL, but enterprises marry PostgreSQL.

Source: http://feeds.feedburner.com/~r/zdnet/open-source/~3/137602580/

Date: 07/30/07 (MySQL Communtiy)    Keywords: mysql, sql

I have a table with 341K records. When I query for all records with a certain string that have occurred in the in the last 24 hours, it returns in .66 seconds. When I sort that same query by any field, it returns in 3 minutes. Any thoughts on how to optimize this query for sorting?

mysql> describe eventlog_data ;
+----------+---------------+------+-----+---------------------+-------+
| Field    | Type          | Null | Key | Default             | Extra |
+----------+---------------+------+-----+---------------------+-------+
| host     | int(11)       | NO   | MUL | 0                   |       |
| event    | int(11)       | NO   |     | 0                   |       |
| record   | bigint(20)    | NO   |     | 0                   |       |
| cname    | varchar(255)  | NO   |     |                     |       |
| time     | datetime      | NO   |     | 0000-00-00 00:00:00 |       |
| type     | varchar(16)   | NO   |     |                     |       |
| log      | varchar(16)   | NO   |     |                     |       |
| source   | varchar(128)  | NO   |     |                     |       |
| message  | text          | NO   |     |                     |       |
| notified | enum('y','n') | NO   |     | n                   |       |
+----------+---------------+------+-----+---------------------+-------+
10 rows in set (0.02 sec)

mysql> select count(record) from eventlog_Data ;
+---------------+
| count(record) |
+---------------+
|        341414 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT cname, time, event, log
    -> FROM   eventlog_data
    -> WHERE  time > SUBDATE(NOW(), INTERVAL 1 DAY)
    -> AND    eventlog_data.message LIKE '%adm%';
SOME DATA RETURNED
16 rows in set (0.66 sec)

mysql> SELECT cname, time, event, log
    -> FROM   eventlog_data
    -> WHERE  time > SUBDATE(NOW(), INTERVAL 1 DAY)
    -> AND    eventlog_data.message LIKE '%adm%';
    -> ORDER BY 1
SOME DATA RETURNED
16 rows in set (2 min 53.63 sec)

Source: http://community.livejournal.com/mysql/116972.html