Help with MSSQL/PHP

Hi wondering if anyone can help.

What i'm trying to do is get a company from a MSSQL database with the
COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to
reference it to another table (on same database) to extract more details.

When I get the company name and company no the company no outputs like...
63180001000000000000000000000000
In basic terms I'm only getting it like...
$query = "SELECT COMPANYNO FROM CONTACT";

Now I know this isn't a 16 byte binary - is it?

I wanted to reference another table so I...
$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$companyno'";

And of course this wouldn't work and the error was:

Warning: Sybase message: Syntax error converting from a character string to
uniqueidentifier.

I need help with this as binary/hex sometimes goes over my head. If anyone
can point me in the right direction that would be great. I've had this same
problem before and did a crappy 'work around' - now i've realised why I did
the work around!

Thanks a lot for ANY help at all :)

(if nothing makes sense please say so :))

Darren

re: Help with MSSQL/PHP

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <zeen@zeen.co.uk>
wrote:
[color=blue]
>Hi wondering if anyone can help.
>
>When I get the company name and company no the company no outputs like...
>63180001000000000000000000000000
>In basic terms I'm only getting it like...
>$query = "SELECT COMPANYNO FROM CONTACT";
>
>Now I know this isn't a 16 byte binary - is it?[/color]

So really, you need to know what datatype that field is?

mysql_fetch_field($result) should get you an object holding the
appropriate information.

re: Help with MSSQL/PHP

> >When I get the company name and company no the company no outputs like...[color=blue][color=green]
> >63180001000000000000000000000000
> >In basic terms I'm only getting it like...
> >$query = "SELECT COMPANYNO FROM CONTACT";
> >
> >Now I know this isn't a 16 byte binary - is it?[/color]
>
> So really, you need to know what datatype that field is?
>
> mysql_fetch_field($result) should get you an object holding the
> appropriate information.[/color]

No no, i want to use the first SQL output of the GUID into another SELECT
and i get the error..

Warning: Sybase message: Syntax error converting from a character string to
uniqueidentifier.

I'm not sure how to use '63180001000000000000000000000000' GUID output into
another SQL SELECT.

(sorry for emailing you directly a second ago, haven't used newsgroups for
ages :P)

re: Help with MSSQL/PHP

I have been looking at...

http://uk.php.net/manual/en/function...uid-string.php

But when using mssql_guid_string i get...

Fatal error: Call to undefined function: mssql_guid_string() in
/export/home/darrenm/web/scripts/guid.php on line 32.

php-4.3.1
freetds-0.61

and yes it was compiled with --enable-msdblib --with-tdsver=7.0

I guess this is the function i need!

If i do...

$query = "SELECT cast(COMPANYNO as varchar(36)),USERSTRING1 FROM CONTACT
WHERE USERSTRING1='$iusername'";

it gets one result (which is correct) but COMPANYNO is blank when i try and
print it (because it's something that can't be printed? i have no idea!)

Welp!

re: Help with MSSQL/PHP

On Thu, 13 Nov 2003 14:43:34 +0000 (UTC), "Darren" <zeen@zeen.co.uk>
wrote:
[color=blue]
>No no, i want to use the first SQL output of the GUID into another SELECT
>and i get the error..
>
>Warning: Sybase message: Syntax error converting from a character string to
>uniqueidentifier.
>
>I'm not sure how to use '63180001000000000000000000000000' GUID output into
>another SQL SELECT.[/color]

It is still a data representation problem. You need to find some
documentation on the syntactic representation of data types for the
Sybase SQL engine.

63180001 00000000 00000000 00000000

The above data looks like binary to me... or perhaps it is a complete
fluke that it splits nicely into a 64 bit word ?


kafooey
- kafooey@nospam.yahoo.co.uk
- http://www.pluggedout.com/blog

re: Help with MSSQL/PHP

"Darren" <zeen@zeen.co.uk> wrote in message
news:bovtqo$rlm$1@sparta.btinternet.com...[color=blue]
> Hi wondering if anyone can help.
>
> What i'm trying to do is get a company from a MSSQL database with the
> COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want[/color]
to[color=blue]
> reference it to another table (on same database) to extract more details.[/color]

Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
often used as "synthetic" primary keys!

The uniqueidentifier data type stores 16-byte binary values that operate
as globally unique identifiers (GUIDs). A GUID is a unique binary number;
no other computer in the world will generate a duplicate of that GUID value.
The main use for a GUID is for assigning an identifier that must be unique
in a network that has many computers at many sites.

It is usually of the form:
* Character string format
'6F9619FF-8B86-D011-B42D-00C04FC964FF'

* Binary format
0xff19966f868b11d0b42d00c04fc964ff

The thing i suspect you want is an "int identity" Primary key, and you make
one like this:


create table CONTACT
(
Id_Contact int identity,
Name int not null,
Phone varchar(255) null,
constraint PK_Contact primary key (Id_Contact)
)
go

--
Dag.

[color=blue]
>
> When I get the company name and company no the company no outputs like...
> 63180001000000000000000000000000
> In basic terms I'm only getting it like...
> $query = "SELECT COMPANYNO FROM CONTACT";
>
> Now I know this isn't a 16 byte binary - is it?
>
> I wanted to reference another table so I...
> $query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
> COMPANYNO='$companyno'";
>
> And of course this wouldn't work and the error was:
>
> Warning: Sybase message: Syntax error converting from a character string[/color]
to[color=blue]
> uniqueidentifier.
>
> I need help with this as binary/hex sometimes goes over my head. If anyone
> can point me in the right direction that would be great. I've had this[/color]
same[color=blue]
> problem before and did a crappy 'work around' - now i've realised why I[/color]
did[color=blue]
> the work around!
>
> Thanks a lot for ANY help at all :)
>
> (if nothing makes sense please say so :))
>
> Darren
>
>[/color]

re: Help with MSSQL/PHP

Forget I said something, Darren!

(Just read your follow-up posts, and it seems you are
aware of what a GUID is).

:-)

--
Dag.


"Dag Sunde" <dag.nope@orion.no.way> wrote in message
news:3fb3bea6@news.wineasy.se...[color=blue]
> "Darren" <zeen@zeen.co.uk> wrote in message
> news:bovtqo$rlm$1@sparta.btinternet.com...[color=green]
> > Hi wondering if anyone can help.
> >
> > What i'm trying to do is get a company from a MSSQL database with the
> > COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want[/color]
> to[color=green]
> > reference it to another table (on same database) to extract more[/color][/color]
details.[color=blue]
>
> Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
> of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
> often used as "synthetic" primary keys!
>
> The uniqueidentifier data type stores 16-byte binary values that operate
> as globally unique identifiers (GUIDs). A GUID is a unique binary number;
> no other computer in the world will generate a duplicate of that GUID[/color]
value.[color=blue]
> The main use for a GUID is for assigning an identifier that must be unique
> in a network that has many computers at many sites.
>
> It is usually of the form:
> * Character string format
> '6F9619FF-8B86-D011-B42D-00C04FC964FF'
>
> * Binary format
> 0xff19966f868b11d0b42d00c04fc964ff
>
> The thing i suspect you want is an "int identity" Primary key, and you[/color]
make[color=blue]
> one like this:
>
>
> create table CONTACT
> (
> Id_Contact int identity,
> Name int not null,
> Phone varchar(255) null,
> constraint PK_Contact primary key (Id_Contact)
> )
> go
>
> --
> Dag.
>
>[color=green]
> >
> > When I get the company name and company no the company no outputs[/color][/color]
like...[color=blue][color=green]
> > 63180001000000000000000000000000
> > In basic terms I'm only getting it like...
> > $query = "SELECT COMPANYNO FROM CONTACT";
> >
> > Now I know this isn't a 16 byte binary - is it?
> >
> > I wanted to reference another table so I...
> > $query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
> > COMPANYNO='$companyno'";
> >
> > And of course this wouldn't work and the error was:
> >
> > Warning: Sybase message: Syntax error converting from a character[/color][/color]
string[color=blue]
> to[color=green]
> > uniqueidentifier.
> >
> > I need help with this as binary/hex sometimes goes over my head. If[/color][/color]
anyone[color=blue][color=green]
> > can point me in the right direction that would be great. I've had this[/color]
> same[color=green]
> > problem before and did a crappy 'work around' - now i've realised why I[/color]
> did[color=green]
> > the work around!
> >
> > Thanks a lot for ANY help at all :)
> >
> > (if nothing makes sense please say so :))
> >
> > Darren
> >
> >[/color]
>
>[/color]

re: Help with MSSQL/PHP

I think i found the answer...

You can't compare a 'uniqueidentifier' to just any string,
it *MUST* be on the format (Like a GUID:

'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

Where each x is a hex digit. So you must convert your number
to hex, and add the hypens...

63180001000000000000000000000000
'00000000-0000-0000-D12F-846681000000'

hth...

--
Dag


"Dag Sunde" <dag.nope@orion.no.way> wrote in message
news:3fb3bf51$1@news.wineasy.se...[color=blue]
> Forget I said something, Darren!
>
> (Just read your follow-up posts, and it seems you are
> aware of what a GUID is).
>
> :-)
>
> --
> Dag.
>
>
> "Dag Sunde" <dag.nope@orion.no.way> wrote in message
> news:3fb3bea6@news.wineasy.se...[color=green]
> > "Darren" <zeen@zeen.co.uk> wrote in message
> > news:bovtqo$rlm$1@sparta.btinternet.com...[color=darkred]
> > > Hi wondering if anyone can help.
> > >
> > > What i'm trying to do is get a company from a MSSQL database with the
> > > COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I[/color][/color][/color]
want[color=blue][color=green]
> > to[color=darkred]
> > > reference it to another table (on same database) to extract more[/color][/color]
> details.[color=green]
> >
> > Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
> > of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
> > often used as "synthetic" primary keys!
> >
> > The uniqueidentifier data type stores 16-byte binary values that operate
> > as globally unique identifiers (GUIDs). A GUID is a unique binary[/color][/color]
number;[color=blue][color=green]
> > no other computer in the world will generate a duplicate of that GUID[/color]
> value.[color=green]
> > The main use for a GUID is for assigning an identifier that must be[/color][/color]
unique[color=blue][color=green]
> > in a network that has many computers at many sites.
> >
> > It is usually of the form:
> > * Character string format
> > '6F9619FF-8B86-D011-B42D-00C04FC964FF'
> >
> > * Binary format
> > 0xff19966f868b11d0b42d00c04fc964ff
> >
> > The thing i suspect you want is an "int identity" Primary key, and you[/color]
> make[color=green]
> > one like this:
> >
> >
> > create table CONTACT
> > (
> > Id_Contact int identity,
> > Name int not null,
> > Phone varchar(255) null,
> > constraint PK_Contact primary key (Id_Contact)
> > )
> > go
> >
> > --
> > Dag.
> >
> >[color=darkred]
> > >
> > > When I get the company name and company no the company no outputs[/color][/color]
> like...[color=green][color=darkred]
> > > 63180001000000000000000000000000
> > > In basic terms I'm only getting it like...
> > > $query = "SELECT COMPANYNO FROM CONTACT";
> > >
> > > Now I know this isn't a 16 byte binary - is it?
> > >
> > > I wanted to reference another table so I...
> > > $query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
> > > COMPANYNO='$companyno'";
> > >
> > > And of course this wouldn't work and the error was:
> > >
> > > Warning: Sybase message: Syntax error converting from a character[/color][/color]
> string[color=green]
> > to[color=darkred]
> > > uniqueidentifier.
> > >
> > > I need help with this as binary/hex sometimes goes over my head. If[/color][/color]
> anyone[color=green][color=darkred]
> > > can point me in the right direction that would be great. I've had this[/color]
> > same[color=darkred]
> > > problem before and did a crappy 'work around' - now i've realised why[/color][/color][/color]
I[color=blue][color=green]
> > did[color=darkred]
> > > the work around!
> > >
> > > Thanks a lot for ANY help at all :)
> > >
> > > (if nothing makes sense please say so :))
> > >
> > > Darren
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Help with MSSQL/PHP

Hi Darren!

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <zeen@zeen.co.uk>
wrote:
[color=blue]
>Hi wondering if anyone can help.
>
>What i'm trying to do is get a company from a MSSQL database with the
>COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I want to
>reference it to another table (on same database) to extract more details.
>
>When I get the company name and company no the company no outputs like...
>63180001000000000000000000000000
>In basic terms I'm only getting it like...
>$query = "SELECT COMPANYNO FROM CONTACT";
>
>Now I know this isn't a 16 byte binary - is it?
>
>I wanted to reference another table so I...
>$query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER WHERE
>COMPANYNO='$companyno'";
>[/color]

Apart from your problems, can't you use a join and one query or even a
view?

HTH, Jochen
--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

re: Help with MSSQL/PHP

Excellent, I'll give this a shot!

Cheers

:Darren


"Dag Sunde" <dag.nope@orion.no.way> wrote in message
news:3fb3c15c$1@news.wineasy.se...[color=blue]
> I think i found the answer...
>
> You can't compare a 'uniqueidentifier' to just any string,
> it *MUST* be on the format (Like a GUID:
>
> 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
>
> Where each x is a hex digit. So you must convert your number
> to hex, and add the hypens...
>
> 63180001000000000000000000000000
> '00000000-0000-0000-D12F-846681000000'
>
> hth...
>
> --
> Dag
>
>
> "Dag Sunde" <dag.nope@orion.no.way> wrote in message
> news:3fb3bf51$1@news.wineasy.se...[color=green]
> > Forget I said something, Darren!
> >
> > (Just read your follow-up posts, and it seems you are
> > aware of what a GUID is).
> >
> > :-)
> >
> > --
> > Dag.
> >
> >
> > "Dag Sunde" <dag.nope@orion.no.way> wrote in message
> > news:3fb3bea6@news.wineasy.se...[color=darkred]
> > > "Darren" <zeen@zeen.co.uk> wrote in message
> > > news:bovtqo$rlm$1@sparta.btinternet.com...
> > > > Hi wondering if anyone can help.
> > > >
> > > > What i'm trying to do is get a company from a MSSQL database with[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > COMPANYNO which is a 'uniqueidentifier'. Then with this COMPANYNO I[/color][/color]
> want[color=green][color=darkred]
> > > to
> > > > reference it to another table (on same database) to extract more[/color]
> > details.[color=darkred]
> > >
> > > Somebody (or you) have misunderstood the 'uniqueidentifier' datatype
> > > of MSSQL. 'uniqueidentifier' is *not* an integer/autoincrement value
> > > often used as "synthetic" primary keys!
> > >
> > > The uniqueidentifier data type stores 16-byte binary values that[/color][/color][/color]
operate[color=blue][color=green][color=darkred]
> > > as globally unique identifiers (GUIDs). A GUID is a unique binary[/color][/color]
> number;[color=green][color=darkred]
> > > no other computer in the world will generate a duplicate of that GUID[/color]
> > value.[color=darkred]
> > > The main use for a GUID is for assigning an identifier that must be[/color][/color]
> unique[color=green][color=darkred]
> > > in a network that has many computers at many sites.
> > >
> > > It is usually of the form:
> > > * Character string format
> > > '6F9619FF-8B86-D011-B42D-00C04FC964FF'
> > >
> > > * Binary format
> > > 0xff19966f868b11d0b42d00c04fc964ff
> > >
> > > The thing i suspect you want is an "int identity" Primary key, and you[/color]
> > make[color=darkred]
> > > one like this:
> > >
> > >
> > > create table CONTACT
> > > (
> > > Id_Contact int identity,
> > > Name int not null,
> > > Phone varchar(255) null,
> > > constraint PK_Contact primary key (Id_Contact)
> > > )
> > > go
> > >
> > > --
> > > Dag.
> > >
> > >
> > > >
> > > > When I get the company name and company no the company no outputs[/color]
> > like...[color=darkred]
> > > > 63180001000000000000000000000000
> > > > In basic terms I'm only getting it like...
> > > > $query = "SELECT COMPANYNO FROM CONTACT";
> > > >
> > > > Now I know this isn't a 16 byte binary - is it?
> > > >
> > > > I wanted to reference another table so I...
> > > > $query = "SELECT ACTIVITYHEADERNO,COMPANYNO FROM ACTIVITYHEADER[/color][/color][/color]
WHERE[color=blue][color=green][color=darkred]
> > > > COMPANYNO='$companyno'";
> > > >
> > > > And of course this wouldn't work and the error was:
> > > >
> > > > Warning: Sybase message: Syntax error converting from a character[/color]
> > string[color=darkred]
> > > to
> > > > uniqueidentifier.
> > > >
> > > > I need help with this as binary/hex sometimes goes over my head. If[/color]
> > anyone[color=darkred]
> > > > can point me in the right direction that would be great. I've had[/color][/color][/color]
this[color=blue][color=green][color=darkred]
> > > same
> > > > problem before and did a crappy 'work around' - now i've realised[/color][/color][/color]
why[color=blue]
> I[color=green][color=darkred]
> > > did
> > > > the work around!
> > > >
> > > > Thanks a lot for ANY help at all :)
> > > >
> > > > (if nothing makes sense please say so :))
> > > >
> > > > Darren
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]