• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

ODBC PHP and pass through queries

what is wrong?

qry_getntusername is a pass thorugh query in MS Acess DB getting info from MS SQL server, but cant seem to execute the MS Access query in PHP

$query = "qry_getntusername";
$result = odbc_exec($connection, "{CALL $query}");
0
ellandrd
Asked:
ellandrd
  • 6
  • 6
1 Solution
 
AndyAelbrechtCommented:
try adding, after the $result line, following code and tell us what the output is to assist you in solving this:

echo odbc_errormsg();

cheers,

Andy
0
 
ellandrdAuthor Commented:
could not find stored procedure, but its just a query i created in ms access...and i want to execute it instead of using select * from table...
0
 
AndyAelbrechtCommented:
I would try adding parantheses after your SP name: $query = "qry_getntusername()";

If not helping yet, try:

$procs = odbc_procedures($connection);
echo "<pre>";
print_r($procs);
echo "</pre>";

this will print out all the Stored Procedure in your connection; see if you can actually find your stored procedure in this list ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ellandrdAuthor Commented:
whole on - maybe im thinking something different...

when i use the code :

$query = "qry_getntusername";
.. {CALL $query} ..

does this not run the query in MS Access?  does it just run straight through to MS SQL and execute the SP?

i think my understanding of how it works was wrong?
0
 
ellandrdAuthor Commented:
$procs = odbc_procedures($connection);
echo "<pre>";
print_r($procs);
echo "</pre>";

print out just:

Resource id #2


doesnt look very useful...
0
 
AndyAelbrechtCommented:
right, my mistake ofcoz :-)

$procs = odbc_procedures($connection);
echo "<pre>";
while ($row = odbc_fetch_array($procs)){
  print_r($row);
}
echo "</pre>";
0
 
ellandrdAuthor Commented:
ok, i understand now..
0
 
AndyAelbrechtCommented:
you found the problem then mate ?

i mean, thanks for the points, but for the sake of completing this question, would you be so kind as to say what solution you used to save your problem (don't say it was typo that killed you ;-))

cheers,
Andy
0
 
ellandrdAuthor Commented:
yes, but im still having problems..

see what im trying to do is idenfiy user viewing webpage.  MS SQL has a command to do this (SELECT SYSTEM_USER) but if i connect to MS SQL with a username and password combo, the above command always returns the username i connect with, whereas i want it to return the nt username of user viewing the page and not the username i connected to the MS SQL with..

my idea was then to mirror the MS SQL server using MS Access and i linked all the tables and SP's..

i then can connect to MS access using DSN and query MS SQL server through Access using PHP with using a username and password to connect as im not connecting to a ODBC connect (.mdb which is a mirror of the MS SQLserver)

i SP i want to run is called sp_getntusername, but i created a pass through queryt o run the sp_getntusername SP whihc all works fine.

but i though i could actually run qry_getntusername, but actaully what qry_getntusername does, is just basically call sp_getntusername hence why its a pass through query.  but i didnt fully understand this until now so ive still no got where in solving my problem as now when i try running :

$query = "sp_getntusername";
$result = odbc_exec($connection, "{CALL $query}");

i just get the username of login account i use to connect in the first place and not the user who's viewing the page...

complex - yes i know
0
 
AndyAelbrechtCommented:
I'm suspecting we are talking about an intranet application then, right ?

OK, getting the username of the currently logged in user is a Windows feature; it's called NTLM (NT login mechanism) and it is supported by *every* language except PHP ;-)
you can work around it (http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_20562144.html), but it's all kinda botchy :(

if you are running IIS, the changes of you completing this fast are pretty doable; if you are using apache, you need the mod_ntlm and that could prove to be very less intresting :( If you are running the webserver (Apache) on Linux, the mod_ntlm might not even be enough !

this dude here (http://www.phpcs.com/code.aspx?id=25272) has a solution which hardcodes the NTML authentication; maybe you could use some of that.

but for me personally, I'm afraid I have to stop giving advice about this now, because I've never actually done any NTML authentication with PHP ...

I hope I could help you a bit at least and hope you can fix it soon !

cheers,
Andy
0
 
ellandrdAuthor Commented:
>>I'm suspecting we are talking about an intranet application then, right ?

yes

>>OK, getting the username of the currently logged in user is a Windows feature; it's called NTLM (NT login mechanism) >>and it is supported by *every* language except PHP ;-)

that is fine - our server can use PERL/CGI/ASP/PHP

i dont know any fo the above except PHP so im kinda stuck...

if you can give adivce towards other languages - im happy to open another question and we can play about with code?

yes i use IIS..
0
 
AndyAelbrechtCommented:
in asp, you get the user like this:

<%
Response.Write(request.servervariables("logon_user"));
%>

if you put this in an asp file, you could use a small trick in your PHP file to get this username:

<?
$username = file_get_contents("http://your.web.server/getusername.asp");
?>

you could try $_SERVER["logon_user"] when running PHP on IIS actually, but I really can't promise this will hold the actual username ...

Cheers,
Andy
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now