Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Kees Nuyt 
On Fri, 12 Sep 2008 11:19:41 -0700 (PDT), you wrote:

>I am new to SQLite

Since 51 weeks, to be exact ;)
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham 
Thank a ton Dennis. 
I will try with the view then,
JP



- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Friday, September 12, 2008 11:38:46 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
>  
> Can we convert these sql statement to function/store procedure so we can pass 
> in the number and the return value back the character format.
> For example : ConvertMAC(29672054730752  ) and the return value back : 
> 00:30:48:90:FC:1A

No, you can't create user defined functions in SQL, and SQLite does not 
support stored procedures.

You could create a view that returns the same columns as the base table 
with the mac address column converted to a string using this SQL 
expression.

Given

    create table t (id, mac integer, data text);

You could create a view like this

    create view tv as
    select id,
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
        as mac_addr,
        data
    from t;

Now you can use the view in all your queries and get the string form of 
the mac address when ever you need it.

You could also do a join to the view using the id column whenever you 
want do get the mac address string in a query that still needs to use 
the original mac address as an integer.

    select data, mac_addr
    from t
    join tv on tv.id = t.id
    where t.mac in (select ...)

HTH
Dennis Cote


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Dennis Cote 
Joanne Pham wrote:
>  
> Can we convert these sql statement to function/store procedure so we can pass 
> in the number and the return value back the character format.
> For example : ConvertMAC(29672054730752  ) and the return value back : 
> 00:30:48:90:FC:1A

No, you can't create user defined functions in SQL, and SQLite does not 
support stored procedures.

You could create a view that returns the same columns as the base table 
with the mac address column converted to a string using this SQL 
expression.

Given

 create table t (id, mac integer, data text);

You could create a view like this

 create view tv as
 select id,
 substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
 as mac_addr,
 data
 from t;

Now you can use the view in all your queries and get the string form of 
the mac address when ever you need it.

You could also do a join to the view using the id column whenever you 
want do get the mac address string in a query that still needs to use 
the original mac address as an integer.

 select data, mac_addr
 from t
 join tv on tv.id = t.id
 where t.mac in (select ...)

HTH
Dennis Cote


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham 
Thanks a lot Dennis.It worked!
Sorry for asking one more question. I am new to SQLite so sorry for the 
question.
 
Can we convert these sql statement to function/store procedure so we can pass 
in the number and the return value back the character format.
For example : ConvertMAC(29672054730752  ) and the return value back : 
00:30:48:90:FC:1A
Thansk,
JP



- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Friday, September 12, 2008 11:12:32 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> Sorry! the conversion is correct but it is in reverse order.
> The select statement return :
> 1A:FC:90:48:30:00
>  
> and I checked the MAC Address:
>  
>  00:30:48:90:fc:1a
> How to change it to correct order or may be the number 29672054730752  needs 
> to be reverse.
> Once again thanks for the help,

Simply rearrange the order of the byte pairs.

    select
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
        as 'MAC Address'
    from t;

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Dennis Cote 
Joanne Pham wrote:
> Sorry! the conversion is correct but it is in reverse order.
> The select statement return :
> 1A:FC:90:48:30:00
>  
> and I checked the MAC Address:
>  
>  00:30:48:90:fc:1a
> How to change it to correct order or may be the number 29672054730752  needs 
> to be reverse.
> Once again thanks for the help,

Simply rearrange the order of the byte pairs.

 select
 substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
 as 'MAC Address'
 from t;

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham 
Sorry! the conversion is correct but it is in reverse order.
The select statement return :
1A:FC:90:48:30:00
 
and I checked the MAC Address:
 
 00:30:48:90:fc:1a
How to change it to correct order or may be the number 29672054730752  needs to 
be reverse.
Once again thanks for the help,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Friday, September 12, 2008 10:58:31 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Thanks a lto Dennis!
But I got the value in reverse order and not correct with the 1A vs 1B.
Select statement return 
1A:FC:90:48:30:00

but When I checked the MAC address on the server the return value is
 00:30:48:90:fc:1b

May be the number is not correct  29672054730752 ?
Or our conversion is not correct.
Thanks,
JP


- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Friday, September 12, 2008 10:12:37 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> Thanks a lot for quick respond.
> I would like to have the format as : 00:15:C5:F1:1D:45 
> Please help me how to convert this number 224577687400448 to 
> this format 00:15:C5:F1:1D:45 

This should do the trick. It's not pretty in SQL, and it might make more 
sense to do it in your application's programming language, but it does work.

    select
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1)
        as 'MAC Address'
    from t;

This assumes that the table t has an integer column mac that hods the 
mac address to be displayed.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



      
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham 
Thanks a lto Dennis!
But I got the value in reverse order and not correct with the 1A vs 1B.
Select statement return 
1A:FC:90:48:30:00

but When I checked the MAC address on the server the return value is
 00:30:48:90:fc:1b

May be the number is not correct  29672054730752 ?
Or our conversion is not correct.
Thanks,
JP


- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Friday, September 12, 2008 10:12:37 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> Thanks a lot for quick respond.
> I would like to have the format as : 00:15:C5:F1:1D:45 
> Please help me how to convert this number 224577687400448 to 
> this format 00:15:C5:F1:1D:45 

This should do the trick. It's not pretty in SQL, and it might make more 
sense to do it in your application's programming language, but it does work.

    select
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1)
        as 'MAC Address'
    from t;

This assumes that the table t has an integer column mac that hods the 
mac address to be displayed.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Dennis Cote 
Joanne Pham wrote:
> Thanks a lot for quick respond.
> I would like to have the format as : 00:15:C5:F1:1D:45 
> Please help me how to convert this number 224577687400448 to 
> this format 00:15:C5:F1:1D:45 

This should do the trick. It's not pretty in SQL, and it might make more 
sense to do it in your application's programming language, but it does work.

 select
 substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
 ':' ||
 substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
 substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1)
 as 'MAC Address'
 from t;

This assumes that the table t has an integer column mac that hods the 
mac address to be displayed.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham 
Thanks a lot for quick respond.
I would like to have the format as : 00:15:C5:F1:1D:45 
Please help me how to convert this number 224577687400448 to 
this format 00:15:C5:F1:1D:45 
Once again thanks a ton,
JP



- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Friday, September 12, 2008 9:22:15 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> I have this MAC Address as integer 224577687400448. Is there any
> buildin function in SQLite to convert this MAC Address from integer
> to character format (IP Address format) as
> ...

No, there is not, but you can do it using a simple (well maybe not so 
simple) expression using bit manipulation and concatenation.

Note, you say you have a MAC address (i.e. 48 bits) which are usually 
displayed as a set of 6 hex bytes (i.e. XX-XX-XX-XX-XX-XX) not in the 
dotted quad format used for IP addresses (which are only 32 bits in 
IPv4). Which do you really have, and what format do you really want to 
use to display it?

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Dennis Cote 
Joanne Pham wrote:
> I have this MAC Address as integer 224577687400448. Is there any
> buildin function in SQLite to convert this MAC Address from integer
> to character format (IP Address format) as
> ...

No, there is not, but you can do it using a simple (well maybe not so 
simple) expression using bit manipulation and concatenation.

Note, you say you have a MAC address (i.e. 48 bits) which are usually 
displayed as a set of 6 hex bytes (i.e. XX-XX-XX-XX-XX-XX) not in the 
dotted quad format used for IP addresses (which are only 32 bits in 
IPv4). Which do you really have, and what format do you really want to 
use to display it?

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Jay A. Kreibich 
On Fri, Sep 12, 2008 at 09:11:02AM -0700, Joanne Pham scratched on the wall:
> Hi All,
> I have this MAC Address as integer 224577687400448. Is there any 
> buildin function in SQLite to convert this MAC Address from integer
> to character format?(IP Address format) as?...

  ARP?



  MAC addresses are not IP addresses.


  MAC addresses are usually written in hex, like:

  AA:BB:CC:DD:EE:FF or sometimes AABB:CCDD:EEFF.

   -j
 
-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham 
Hi All,
I have this MAC Address as integer 224577687400448. Is there any buildin 
function in SQLite to convert this MAC Address from integer to character 
format (IP Address format) as ...
Thanks,
JP





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users