Testing

08Jul09

first post from ipod… works!

Filed under: iPod   |  Leave a Comment
Tags: iPod, post, touch

Converting seconds – SQL

16Jun09

For those works with many databases… mssql, oracle, sybase, progress sometimes has troubles with migrations.

The Date/Time types sometimes are a big problem.

This tip is how to convert seconds.

– Convert date to seconds
select   (datepart(hour,getdate()) * 3600)
+ (datepart(minute,getdate()) * 60)
+ (datepart(second,getdate()))

– Convert seconds to HH:MM:SS
select convert(varchar,44235/3600)
+ “:”
+ convert(varchar,(44235 % 3600) / 60)
+ “:”
+ convert(varchar,44235 % 60)

Tip very useful…
That’s all… keep rocking!!!!

Filed under: Sql   |  Leave a Comment
Tags: ase, Convert, Date, datepart, example, hh:mm:ss, hour, microsoft, minute, mssql, second, Server, Sql, Sybase, T-Sql, time

Encrypting a view – SQL

16Jun09

Sometimes we need to hide some information of a database.
Here is a tip to encrypt a description of a view.

– Creating a table
create table MYTABLE
(
mycode  int
,myname  varchar(50)
,mysal   float
)

– Inserting values
insert into MYTABLE values (1,’sl4v3r’,6000)
insert into MYTABLE values (2,’james’,7000)
insert into MYTABLE values (3,’bond’,8000)

– Select the table
select *
from MYTABLE

1             sl4v3r    6000.0
2             james   7000.0
3             bond     8000.0

– Creating with encryption
CREATE VIEW vwMYTABLE
WITH ENCRYPTION
AS
SELECT MYCODE
,MYNAME
FROM MYTABLE

– Select the view
select *
from vwMYTABLE

1             sl4v3r
2             james
3             bond

– Getting Information
sp_helptext vwMYTABLE

The object comments have been encrypted.

See ya…

Filed under: Sql   |  Leave a Comment
Tags: Sql, T-Sql, Server, view, example, mssql, microsoft, with, encrypt, ENCRYPTION

Opening Internet Explorer via Progress

16Jun09

This tip is for curious like me… lol
How to open IE via progress… only for GUI.

DEFINE VARIABLE chWeb AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWebAppl AS COM-HANDLE NO-UNDO.

CREATE “InternetExplorer.Application.1″ chWeb.

ASSIGN chWebAppl = chWeb:APPLICATION
chWebAppl:VISIBLE = TRUE.

chWebAppl:Navigate(”sl4v3r.wordpress.com”).

Work’s fine…

Filed under: Progress   |  Leave a Comment
Tags: 4gl, abl, application, COM-HANDLE, explorer, gui, Ie, internet, navigate, openedge, Progress

Generating a XML from prodataset

16Jun09

It’s very simple generate a XML in Progress ABL.
In my opinion with PRODATASET is the easier way.

This is a sample how to generate a XML with prodataset (just five lines of code).

DEFINE TEMP-TABLE tt-mytable LIKE MYTABLE.
DEFINE DATA-SOURCE srcMytable FOR MYTABLE.
DEFINE DATASET dsMytable FOR tt-mytable.

BUFFER tt-Mytable:ATTACH-DATA-SOURCE(DATA-SOURCE srcMytable:HANDLE).

DATASET dsMytable:FILL().
DATASET dsMytable:WRITE-XML (’FILE’,'myfile.xml’, TRUE).

Quite ahn?…

Filed under: Progress   |  Leave a Comment
Tags: 4gl, abl, data-source, dataset, example, fill, generate, openedge, prodataset, Progress, temp-table, write-xml, xml

Errors in SQL Server

09Jun09

If you had worked with Sql certainly you saw the error message below.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_MYTABLE’. Cannot insert duplicate key in object ‘MYTABLE’.
The statement has been terminated.

The message has five components:

Msg
Each error message has a number starting with 0.
Message numbers from 50001 and up are reserved for user-defined use. Lower numbers are system defined.

Level

This defines the severity of the error generated.
It is a number between 0 and 25

0-10 defines some informational message or a warning
11-16 are the errors in the programming
17 and above defines problems with the lack of server resources or hardware problems

Above 20 will terminate the connection

State
This is a number between 0 and 127.
There is not much information disclosed about this by Microsoft… so I will not discuss this in this post.

Line
This specifies the line number in the procedure/function/trigger/batch where the error has actually occurred.

Msg Desc
Check it out the table sysmessages

select * from master..sysmessages

That’s all folks…

Filed under: Sql   |  Leave a Comment
Tags: error, example, level, line, msg, mssql, Server, Sql, state, sysmessages, T-Sql

Retrieving XML in HTML

09Jun09

Retrieving XML in HTML

Is very useful to show XML data in a table.

XML used

<?xml version=’1.0′ encoding=’UTF-8′?>
<autenticacao>
<usuario>
<login>teste</login>
<senha>1234</senha>
</usuario>
<usuario>
<login>fulano</login>
<senha>4321</senha>
</usuario>
</autenticacao>

The HTML

<HTML>
<HEAD>
<META NAME=”GENERATOR” Content=”Microsoft Visual Studio 6.0″>
<TITLE>Test XML</TITLE>
</HEAD>
<BODY>
<xml id=”xml_test” ondatasetcomplete=”Complete();” src=”test.xml”></xml>
<table border=’1′ width=’100%’ datasrc=”#xml_test”>
<tr>
<td><SPAN datafld=”login”></SPAN></td>
<td><SPAN datafld=”senha”></SPAN></td>
</tr>
</table>
</BODY>
<script language=”javascript”>
function Complete()
{
alert(’Completou!’);
}
</script>
</HTML>

Works fine…

<?xml version=’1.0′ encoding=’UTF8′?>

<autenticacao>

<usuario>

<login>teste</login>

<senha>1234</senha>

</usuario>

<usuario>

<login>fulano</login>

<senha>4321</senha>

</usuario>

</autenticacao>

Filed under: Web   |  Leave a Comment
Tags: datafld, datasrc, example, html, Java, javascript, ondatasetcomplete, retrieving, script, src, table, xml

Use JOIN or WHERE to join tables?

09Jun09

There are two methods…

WHERE

SELECT MYTABLE.Name, MYTABLE2.Age

FROM MYTABLE, MYTABLE2

WHERE MYTABLE.Id = MYTABLE2.Id

JOIN

SELECT MYTABLE.Name, MYTABLE2.Age

FROM MYTABLE

INNER JOIN MYTABLE2

ON MYTABLE.Id = MYTABLE2.Id

The best performance is JOIN

SELECT MYTABLE.Name, MYTABLE2.Age

FROM MYTABLE

INNER JOIN MYTABLE2

ON MYTABLE.Id = MYTABLE2.Id

source: http://www.tek-tips.com/faqs.cfm?fid=5168

There are two methods…

WHERE
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE, MYTABLE2
WHERE MYTABLE.Id = MYTABLE2.Id

JOIN
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE
INNER JOIN MYTABLE2
ON MYTABLE.Id = MYTABLE2.Id

The best performance is JOIN
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE
INNER JOIN MYTABLE2
ON MYTABLE.Id = MYTABLE2.Id

source: http://www.tek-tips.com/faqs.cfm?fid=5168

Filed under: Sql   |  Leave a Comment
Tags: ase, best, example, join, mssql, on, Performance, Server, Sql, Sybase, T-Sql, Where

Using TOP with SELECT Statements

09Jun09

Hi All,

I’m back… Long time anh???… lol

The TOP option is used for limiting the output of a query result set.

– Syntax for returning n number of rows:
SELECT TOP n *
FROM table

– Example for returning 10 rows
SELECT TOP 10 *
FROM mytable

– Syntax for returning n percentage of rows:
SELECT TOP n PERCENT *
FROM table

– Example for returning 5 percent of rows
SELECT TOP 5 PERCENT *
FROM mytable

See u

Filed under: Sql   |  Leave a Comment
Tags: ase, mssql, option, percent, sample, Server, Sql, Sybase, T-Sql, top

Compute clause in SQL

26Mai09

Hi,

One of my favorites languages is SQL.
Almost six years working with it… MSSQL, Oracle, Mysql, Progress doesn’t matter which.

The tip of today is how to use compute clause.
It’s very common somebody ask you the sum of a column in a select.

The compute clause might help you…

Sample

select name
,sal
from mysq
compute sum(sal)

In this sample… in the end of result the sql appends a line with the total of sal column.

These r the agregate functions that you may use with compute…

AVG Average of the values in the numeric expression
COUNT Number of selected rows
MAX Highest value in the expression
MIN Lowest value in the expression
STDEV Statistical standard deviation for all values in the expression
STDEVP Statistical standard deviation for the population for all values in the expression
SUM Total of the values in the numeric expression
VAR Statistical variance for all values in the expression
VARP Statistical variance for the population for all values in the expression

see u…

Filed under: Sql   |  Leave a Comment
Tags: agregate, ase, clause, code, compute, sample, Server, Sql, sum, Sybase, T-Sql