Help with MSSQL/PHP

[Darren]

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 'uniqueidentifi er'. 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...
631800010000000 000000000000000 00
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 ACTIVITYHEADERN O,COMPANYNO FROM ACTIVITYHEADER WHERE
COMPANYNO='$com panyno'";

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

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

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

[Kafooey]

Re: Help with MSSQL/PHP

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <zeen@zeen.co.u k>
wrote:
[color=blue]
>Hi wondering if anyone can help.
>
>When I get the company name and company no the company no outputs like...
>63180001000000 000000000000000 000
>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_fie ld($result) should get you an object holding the
appropriate information.

[Darren]

Re: Help with MSSQL/PHP

> >When I get the company name and company no the company no outputs like...[color=blue][color=green]
> >63180001000000 000000000000000 000
> >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_fie ld($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
uniqueidentifie r.

I'm not sure how to use '63180001000000 000000000000000 000' GUID output into
another SQL SELECT.

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

[Darren]

Re: Help with MSSQL/PHP (tried another thing)

I have been looking at...

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

But when using mssql_guid_stri ng i get...

Fatal error: Call to undefined function: mssql_guid_stri ng() 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)),US ERSTRING1 FROM CONTACT
WHERE USERSTRING1='$i username'";

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!

[Kafooey]

Re: Help with MSSQL/PHP

On Thu, 13 Nov 2003 14:43:34 +0000 (UTC), "Darren" <zeen@zeen.co.u k>
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
>uniqueidentifi er.
>
>I'm not sure how to use '63180001000000 000000000000000 000' 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

[Dag Sunde]

Re: Help with MSSQL/PHP

"Darren" <zeen@zeen.co.u k> wrote in message
news:bovtqo$rlm $1@sparta.btint ernet.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 'uniqueidentifi er'. 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 'uniqueidentifi er' datatype
of MSSQL. 'uniqueidentifi er' is *not* an integer/autoincrement value
often used as "synthetic" primary keys!

The uniqueidentifie r 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
0xff19966f868b1 1d0b42d00c04fc9 64ff

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...
> 631800010000000 000000000000000 00
> 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 ACTIVITYHEADERN O,COMPANYNO FROM ACTIVITYHEADER WHERE
> COMPANYNO='$com panyno'";
>
> 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]
> uniqueidentifie r.
>
> 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]

[Dag Sunde]

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@n ews.wineasy.se. ..[color=blue]
> "Darren" <zeen@zeen.co.u k> wrote in message
> news:bovtqo$rlm $1@sparta.btint ernet.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 'uniqueidentifi er'. 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 'uniqueidentifi er' datatype
> of MSSQL. 'uniqueidentifi er' is *not* an integer/autoincrement value
> often used as "synthetic" primary keys!
>
> The uniqueidentifie r 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
> 0xff19966f868b1 1d0b42d00c04fc9 64ff
>
> 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]
> > 631800010000000 000000000000000 00
> > 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 ACTIVITYHEADERN O,COMPANYNO FROM ACTIVITYHEADER WHERE
> > COMPANYNO='$com panyno'";
> >
> > 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]
> > uniqueidentifie r.
> >
> > 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]

[Dag Sunde]

Re: Help with MSSQL/PHP

I think i found the answer...

You can't compare a 'uniqueidentifi er' 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...

631800010000000 000000000000000 00
'00000000-0000-0000-D12F-846681000000'

hth...

--
Dag


"Dag Sunde" <dag.nope@orion .no.way> wrote in message
news:3fb3bf51$1 @news.wineasy.s e...[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@n ews.wineasy.se. ..[color=green]
> > "Darren" <zeen@zeen.co.u k> wrote in message
> > news:bovtqo$rlm $1@sparta.btint ernet.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 'uniqueidentifi er'. 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 'uniqueidentifi er' datatype
> > of MSSQL. 'uniqueidentifi er' is *not* an integer/autoincrement value
> > often used as "synthetic" primary keys!
> >
> > The uniqueidentifie r 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
> > 0xff19966f868b1 1d0b42d00c04fc9 64ff
> >
> > 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]
> > > 631800010000000 000000000000000 00
> > > 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 ACTIVITYHEADERN O,COMPANYNO FROM ACTIVITYHEADER WHERE
> > > COMPANYNO='$com panyno'";
> > >
> > > 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]
> > > uniqueidentifie r.
> > >
> > > 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]

[Jochen Daum]

Re: Help with MSSQL/PHP

Hi Darren!

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <zeen@zeen.co.u k>
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 'uniqueidentifi er'. 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...
>63180001000000 000000000000000 000
>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 ACTIVITYHEADERN O,COMPANYNO FROM ACTIVITYHEADER WHERE
>COMPANYNO='$co mpanyno'";
>[/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/

[Darren]

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.s e...[color=blue]
> I think i found the answer...
>
> You can't compare a 'uniqueidentifi er' 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...
>
> 631800010000000 000000000000000 00
> '00000000-0000-0000-D12F-846681000000'
>
> hth...
>
> --
> Dag
>
>
> "Dag Sunde" <dag.nope@orion .no.way> wrote in message
> news:3fb3bf51$1 @news.wineasy.s e...[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@n ews.wineasy.se. ..[color=darkred]
> > > "Darren" <zeen@zeen.co.u k> wrote in message
> > > news:bovtqo$rlm $1@sparta.btint ernet.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 'uniqueidentifi er'. 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 'uniqueidentifi er' datatype
> > > of MSSQL. 'uniqueidentifi er' is *not* an integer/autoincrement value
> > > often used as "synthetic" primary keys!
> > >
> > > The uniqueidentifie r 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
> > > 0xff19966f868b1 1d0b42d00c04fc9 64ff
> > >
> > > 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]
> > > > 631800010000000 000000000000000 00
> > > > 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 ACTIVITYHEADERN O,COMPANYNO FROM ACTIVITYHEADER[/color][/color][/color]
WHERE[color=blue][color=green][color=darkred]
> > > > COMPANYNO='$com panyno'";
> > > >
> > > > 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
> > > > uniqueidentifie r.
> > > >
> > > > 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]