Can't get user reading from custom database

[CLOWNISIUS]

I've converted most queries to read the ban lists from my forums, and everything works fine, except now i have this problem, my read_client_login.sql
the query works from within the Query browser, and from the command line, i'm using everything as it sees fit, but something isnt right. the server is refusing the data returned by this query.

I even made sure the datetime was returning a char that is under 20 characters long, i made sure they weren't int's because i know that the C application at the core cares about those things.

Now my question to devs, any reason why this query wont work in place of the data given?

I am using Invision Power Boards 2.2.x this is experimental, as the admins are the only people allowed to edit something, and it's reading their group for their status on the forums, anything else is a normal user.

SELECT 
	m.id as i_client_id,
	:iServerID as i_client_server_id,
	case m.mgroup when 4 then -1 else 0 END as b_client_priveleg_serveradmin, 
	m.name as s_client_name,
	c.converge_pass_hash AS s_client_password,
	concat( FROM_UNIXTIME(m.joined,'%d%m%Y%H%i%s'),'000' ) as dt_client_created,
	concat( FROM_UNIXTIME(m.last_activity,'%d%m%Y%H%i%s'),'000' ) as dt_client_lastonline

FROM 
	lightside.ibf_members m 

LEFT JOIN lightside.ibf_members_converge c ON (m.id = c.converge_id)

WHERE 
	m.name = :sName 
AND 
	c.converge_pass_hash = MD5(CONCAT(MD5(c.converge_pass_salt), MD5(:sPassword)))

And another question, how do i block out all registered users? If it's possible even, so far, i havce it set that the default chat room is for R and it's set to M,

any other channels are R'd so that no unregistered users can log in.

EDIT: i had forgotten to put in the s_client_password, but it still doesnt work, i still receive a

16-01-07 19:26:09,ERROR,All,CLIENT,     SID: 1 DBValidLogin Exception: EDatabaseError.dbExpress Error: Operation Not Supported

This is the last query i have a problem with

EDIT:

Results from the original query that came with TS

      1, 1, -1, 'admin'  , '588eb055d7b9b5855da198db986f830a', '16012007145451992', '16012007165250234'

Results from my query on my custom table.... same exact results, even the date stamp is in the proper format, with exception to the micro seconds there...
I just leveled them out to 0.

2000002, 1, -1, 'CuteBoi', 'a326a1380fcd38f6b3c8fce6fafa19d8', '14052004061242000', '16012007172928000'

Both queries on top of each other

      1, 1, -1, 'admin'  , '588eb055d7b9b5855da198db986f830a', '16012007145451992', '16012007165250234'
2000002, 1, -1, 'CuteBoi', 'a326a1380fcd38f6b3c8fce6fafa19d8', '14052004061242000', '16012007172928000'

[maggy]

it's probably unlikely that the devs would give any support to such a highly modified setup imo

[CLOWNISIUS]

oh, i hope so =[

my dream is to integrate a VOIP service to my forums... it's the only thing left to do, Ventrilo is too... eww to do anything like that.

everything else works, except for this query, full integration that is missing is this one query....

[Reedy Boy]

it's probably unlikely that the devs would give any support to such a highly modified setup imo

Can't see why they wouldnt, its not like he's modifying the actual software

oh, i hope so =[

my dream is to integrate a VOIP service to my forums... it's the only thing left to do, Ventrilo is too... eww to do anything like that.

everything else works, except for this query, full integration that is missing is this one query....

You should be ok


I havent got time to try and help now, but i should have time later to at least throw some ideas your way

[CLOWNISIUS]

I got it working, there is something wrong with the Left Join statement and the client.

I ended up updating the database from 4.1.x to 5.0.x and created a view that did the left join automatically, creating the names of all the columns along with it.

I just ended up reverting the query back to the original one that came with teamspeak, and set that.

Now the last thing, how do i prevent unregistered users from logging in to the TeamSpeak server.

[Reedy Boy]

I got it working, there is something wrong with the Left Join statement and the client.

I ended up updating the database from 4.1.x to 5.0.x and created a view that did the left join automatically, creating the names of all the columns along with it.

I just ended up reverting the query back to the original one that came with teamspeak, and set that.

Now the last thing, how do i prevent unregistered users from logging in to the TeamSpeak server.

Other than a server password...


You could post your working script then others could use it if they wanted?

[CLOWNISIUS]

there isnt a script, it's just a left join view...:

I'll paste the query i used from home.

I don't get what is so special, but i keep wondering why the 3.23 can't do a left join the way i did.

[Reedy Boy]

there isnt a script, it's just a left join view...:

I'll paste the query i used from home.

I don't get what is so special, but i keep wondering why the 3.23 can't do a left join the way i did.

Its just then there for the people who arent able to do it themself to be able to do it...

[CLOWNISIUS]

this is what i use for my IPB setup.

the NOT IN() portion are the banned groups and the guest/validating groups.

That way they can't log in if banned from forums, or havent finished setting up their accounts.

I have yet to implement the temp ban system, but meh, this is for you guys to use. Best way to do a join statement without really making a join statement in the Teamspeak SQL templates. (since they aren't supported)

create OR REPLACE view view_ts2_clients AS
SELECT
	m.id as i_client_id,
	1 as i_client_server_id,
	case m.mgroup when 4 then -1 else 0 END as b_client_privilege_serveradmin,
	m.name as s_client_name,
	c.converge_pass_hash AS s_client_password,
  c.converge_pass_salt AS s_client_password_hash,
	concat( FROM_UNIXTIME(m.joined,'%d%m%Y%H%i%s'),'000' ) as dt_client_created,
	concat( FROM_UNIXTIME(m.last_activity,'%d%m%Y%H%i%s'),'000' ) as dt_client_lastonline

FROM
	lightside.ibf_members m

LEFT JOIN lightside.ibf_members_converge c ON m.id = c.converge_id where m.id NOT IN(5,6,7,8,9,1,2)