Barranka's questions

Hello!

I have a table with the following structure:

id | fecha_inicio
---+--------------------
10 | 2016-12-01 10:15:00
10 | 2016-12-03 16:02:00
11 | 2016-12-02 11:23:00

I want to build a query that has the start and the end of the period, where, for example, the end date of the first period of the id 10is 2016-12-03 16:02:00. The result I'm looking for is something like the following:

id | fecha_inicio        | fecha_final
---+---------------------+--------------------
10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00
10 | 2016-12-03 16:02:00 | NULL
11 | 2016-12-02 11:23:00 | NULL

Is there a way to build this query using only SQL expressions? I know that it is possible to build it using other languages, but it would be much easier for me to do it directly in the database.

Cheers

I have a little problem with MariaDB:

I have a table with several columns, one of which is called fecha_hora. When trying to insert data with load data infile, MariaDB tells me that it doesn't recognize this column. I've dropped and created the table several times, and yet I still get the same error.

The code I am executing is the following:

drop table if exists mi_tabla
create table mi_tabla (
    id              int unsigned not null auto_increment primary key,
    nombre_archivo  varchar(255),
    res             varchar(100),
    tipo            varchar(10),
    fecha_hora      datetime,
    unidades        int unsigned,
    fecha           date,
    hora            date,
);
-- La tabla se crea correctamente

delimiter $$
create or replace trigger trig_mi_tabla_bi before insert on mi_tabla
for each row
begin
    set new.fecha   = date(fecha_hora);
    set new.hora    = time(fecha_hora);
end $$
delimiter ;
-- La creación del trigger no muestra errores

load data infile 'c:/ruta/a/mi/archivo/mi_archivo.csv'
into table mi_tabla
fields terminated by ',' optionally enclosed by '"' 
lines terminated by '\r\n' ignore 1 lines
(res, tipo, fecha_hora, unidades);
-- Aquí es donde aparece el error:
-- ERROR 1054 (42S22): Unknown column 'fecha_hora' in 'field list'

As far as I can see, there is nothing misspelled...

Here's a sample of the file I'm uploading:

Res,Tipo,Fecha,Unid
res0010,VX,2016-10-07 21:29:56,8
res0010,VX,2016-10-07 21:38:01,60
res0010,VX,2016-10-07 22:30:30,60
res0010,VY,2016-10-09 17:05:56,60
res0010,DD,2016-10-10 08:52:17,159212
res0010,DD,2016-10-10 08:53:37,7465

I am writing a program in which I am being forced to use things like this:

HashMap<String,Object> valores = new HashMap<>();
valores.put("valor1", "abc");              // Se guarda como objeto String
valores.put("valor2", (int) 123);          // Se guarda como objeto Integer
valores.put("valor3", (double) 3.1416);    // Se guarda como objeto Double

Now in another part of the code I need to save these values ​​to a database. I'm using a prepared statement, and I'd like to do something like this:

ps = conn.preparedStatement("insert into tbl (v1, v2, v3) values (?,?,?)")
for(int i = 1; i <= 3; i++) {
    if(/*valores.get(String.format("valor%d",i)) es String*/)
        ps.setString(i, (String) valores.get(String.format("valor%d",i)));
    else if(/*valores.get(String.format("valor%d",i)) es Integer*/)
        ps.setInt(i, (Integer) valores.get(String.format("valor%d",i)));
    else if(/*valores.get(String.format("valor%d",i)) es Double*/)
        ps.setDouble(i, (Double) valores.get(String.format("valor%d",i)));

}

So my specific question is: How can I, at runtime, get the type of a variable saved as Object?

Dears, I have a small problem with a Java application that I am writing.

It is an application that connects via JDBC to MySQL. When saving the records, the characters are saved correctly (if I write "Mexico" in a JText box and send it to the database, it is saved as "Mexico"); but when reading them back using the method ResultSet.getString()the "special" characters (accents and "ñ") appear wrong ("México" is read as "M��xico").

I think it's something to do with the "encoding" of the characters, but I don't know specifically what it is. The MySQL database uses encoding utf8_spanish_ci, and the function Charset.defaultCharset()returns UTF-8.

So my specific question is: How to get strings read from MySQL containing special characters (that were correctly stored) to display correctly in the Java application?

Update (partial fix):

After some more searching I found this question and its answer which helped me. Specifically, what it says is that when opening the connection you have to specify the character set that will be used; in my case:

DriverManager.getConnection(
           "jdbc:mysql://" + host + "/" + dbName 
           + "?useUnicode=true&characterEncoding=utf8_spanish_ci", 
    user, pass);

However, it only partially fixes the problem:

When reading field data VARCHARthe special characters are read correctly. However, when reading from fields JSON, values ​​that have special characters are still displayed as "strange".

Update (final):

The problem has to do with the encoding that MySQL uses to save the JSON data; The answer below illustrates the procedure I followed to fix it.

I'm using LyX to create a report, and needed to add (as an attachment) a multi-page PDF document.

Is there a way to do it? If so, how?

I have an NTFS-formatted (external) hard drive, and I need to copy files from this drive to my computer, which is running Debian Linux. Usually, just plug in any USB drive and Debian automatically recognizes it; however, it does not seem to automatically recognize this NTFS drive.

How can I mount this disk in Linux?

R's graph generation system ggplot2is surprisingly versatile, but I haven't found a way to generate a waterfall chart similar to this:

How can I, using ggplot2, generate this kind of chart?

Sample data (similar to the graph shown:

df <- data.frame(
  montos = c(420, 210, -170, -140),
  row.names = c('Ingresos', 'Otros ingresos', 'Costos fijos', 'Costos variables')
)
df
##                  montos
## Ingresos            420
## Otros ingresos      210
## Costos fijos       -170
## Costos variables   -140

When writing documents in LaTeX (specifically, I use LyX for it), I'd like to use some custom color for the text; for example, instead of using \textcolor{red}{abc}(red RGB(255,0,0), which works perfectly) I'd like to use something like \textcolor{#990000}{abc}(dark red RGB(153,0,0)this throws an error).

So my specific question is:

• How can I define the color of the text in LaTeX?
• Is it possible to define a custom color to use within my document?
  For example, would it be possible to define a color rojo_oscurowith value #990000and use it in my document like \textcolor{rojo_oscuro}{def}?

I'm finally getting around to writing HTML, and I'm faced with a catch: when I test my web page in a desktop browser, it looks just like I want it to, but when I test it in a mobile browser, it looks too small ( I mean, it looks just like the desktop version, but like I zoomed in at like 25%).

Is there a simple way to ensure that when the page is visited from a mobile phone, it has an appropriate size? If so, how?