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 '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

[Kafooey]

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <ze**@zeen.co.uk>
wrote:

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?

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

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

[Darren]

> >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?

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

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

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)

[Darren]

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!

[Kafooey]

On Thu, 13 Nov 2003 14:43:34 +0000 (UTC), "Darren" <ze**@zeen.co.uk>
wrote:

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.

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
- ka*****@nospam.yahoo.co.uk
- http://www.pluggedout.com/blog

[Dag Sunde]

"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...

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.
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.


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

[Dag Sunde]

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" <da******@orion.no.way> wrote in message
news:3f******@news.wineasy.se...

"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...

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.
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.

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

[Dag Sunde]

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" <da******@orion.no.way> wrote in message
news:3f********@news.wineasy.se...

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" <da******@orion.no.way> wrote in message
news:3f******@news.wineasy.se...

"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...

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.

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.

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

[Jochen Daum]

Hi Darren!

On Thu, 13 Nov 2003 12:35:36 +0000 (UTC), "Darren" <ze**@zeen.co.uk>
wrote:

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'";

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]

Excellent, I'll give this a shot!

Cheers

:Darren
"Dag Sunde" <da******@orion.no.way> wrote in message
news:3f********@news.wineasy.se...

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" <da******@orion.no.way> wrote in message
news:3f********@news.wineasy.se...

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" <da******@orion.no.way> wrote in message
news:3f******@news.wineasy.se...

"Darren" <ze**@zeen.co.uk> wrote in message
news:bo**********@sparta.btinternet.com...
> 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.

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.
>
> 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
>
>